Thank you very much for your intereset.I hope you will learn something new.
Problem
statement: Recently
I faced an query performance issue where one of the query was accessing clob
column as one of the predicate but it has no index.Below steps will guide you
how to troubleshoot and resolve this problem.
Please find the below execution plan
what was using by the query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID asnvud9knm10w, child number 0
-------------------------------------
SELECT CRE_DTTM , OUTMSG_ID FROM F1_OUTMSG
WHERE
OUTMSG_TYPE_CD = :1 AND XML_SOURCE LIKE :2 /* SQL for Zone
'CM_RETOUTMSG' */ /*
com.splwg.base.web.dataExplorer.common.DataExplorer
SQLExecuter */
Plan hash value: 1903859126
-------------------------------------------------------------------------------------------------
| Id
| Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | |
| 44458 (100)| |
|*
1 | TABLE ACCESS BY INDEX ROWID
BATCHED| F1_OUTMSG | 4746 | 7791K| 44458 (1)| 00:00:02 |
|*
2 | INDEX SKIP SCAN | FT010S1 | 94919 | |
2003 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('optimizer_index_caching' 100)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "F1_OUTMSG"@"SEL$1"
("F1_OUTMSG"."OUTMSG_STATUS_FLG"
"F1_OUTMSG"."OUTMSG_TYPE_CD"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1"
"F1_OUTMSG"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by
position):
--------------------------------------
1 - :1 (CHAR(30), CSID=871): 'CM_MWM_FA'
2 - :2 (CHAR(30), CSID=871): '%11547883006349%'
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("XML_SOURCE" /*+ LOB_BY_VALUE */ LIKE :2)
2 - access("OUTMSG_TYPE_CD"=:1)
filter("OUTMSG_TYPE_CD"=:1)
47 rows selected.
Plan Statistics extracted using
awrsqrpt shows high elapsed time,CPU time and I/O wait time
- % Snap Total shows the % of the statistic for the
SQL statement compared to the instance total
|
Stat
Name |
Statement
Total |
Per
Execution |
%
Snap Total |
|
Elapsed
Time (ms) |
100,290,508 |
33,733.77 |
29.16 |
|
CPU
Time (ms) |
67,214,569 |
22,608.33 |
28.20 |
|
Executions |
2,973 |
1.00 |
0.00 |
|
Buffer
Gets |
913,937,049 |
307,412.39 |
4.79 |
|
Disk
Reads |
839,221,506 |
282,281.03 |
34.62 |
|
Parse
Calls |
179 |
0.06 |
0.00 |
|
Rows |
2,959 |
1.00 |
|
|
User
I/O Wait Time (ms) |
37,445,098 |
12,595.05 |
33.46 |
|
Cluster
Wait Time (ms) |
0 |
0.00 |
0.00 |
|
Application
Wait Time (ms) |
0 |
0.00 |
0.00 |
|
Concurrency
Wait Time (ms) |
5,654 |
1.90 |
1.00 |
|
Invalidations |
0 |
|
|
|
Version
Count |
47 |
|
|
|
Sharable
Mem(KB) |
1,531 |
|
We will test this manually using bind
variables we have found in above steps
Created script like below to test it
using bind variables
set serverout on
set time on
alter session set
current_schema=CISUSER;
alter session set
nls_date_format='/mm/dd/yyyy HH24:MI:SS';
declare
v1 CHAR(128) :='CMAMIMTRONOF';
v2 CHAR(128)
:='%89928748669102%';
vSQL varchar2(4000);
begin
vSQL :='SELECT CRE_DTTM , OUTMSG_ID FROM F1_OUTMSG
WHERE OUTMSG_TYPE_CD =
:1 AND XML_SOURCE LIKE :2 /* SQL for Zone ''CM_RETOUTMSG'' */ /*
com.splwg.base.web.dataExplorer.common.DataExplorerSQLExecuter */';
dbms_output.put_line(vSQL);
execute immediate vSQL using v1,v2;
end;
/
Recent execution highlighted below
shows same plan hash value with zero rows but previous execution per rows
becomes (23312935043/1000000/676=34.48) which is very high for
OLTP application.
CHILD_NUMBER LAST_LOAD_TIME PLAN_HASH_VALUE
SQL_PLAN_BASELINE
ELAPSED_TIME ROWS_PROCESSED
------------ -------------------------- ----------------------------
------------------------------ ---------------------
--------------
0 2025-07-16/03:02:41 1903859126 23312935043 676
1 2025-07-16/08:01:30 1903859126 30535504 102
2 2025-07-16/08:19:22 1903859126 1686 0
Since above bind variables does not
return any value so I have filtered based on highlighted predicate to get the
outmsg_id and using that I have found some matching data
SELECT CRE_DTTM , OUTMSG_ID FROM F1_OUTMSG
WHERE OUTMSG_TYPE_CD = 'CMAMIMTRONOF' AND XML_SOURCE LIKE '%58343768923659%' /* SQL for Zone ''CM_RETOUTMSG'' */ /*
com.splwg.base.web.dataExplorer.common.DataExplorerSQLExecuter */;
Created below domain index
CREATE INDEX
cisadm.cm_idex_xml_source tablespace cists_01 ON cisadm.f1_outmsg(xml_source)
INDEXTYPE IS CTXSYS.CONTEXT;
When I rewrite the query like this I
get below execution plan
SQL> SELECT CRE_DTTM , OUTMSG_ID FROM F1_OUTMSG
WHERE OUTMSG_TYPE_CD = 'CMAMIMTRONOF' and contains(XML_SOURCE,'2657136589')>0;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------
| Id
| Operation |
Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 1 |
1689 | 1 (0)|
|*
1 | TABLE ACCESS BY INDEX ROWID|
F1_OUTMSG | 1 |
1689 | 1 (0)|
|*
2 | DOMAIN INDEX | CM_IDEX_XML_SOURCE | 16801
| | 1
(0)|
---------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("OUTMSG_TYPE_CD"='CMAMIMTRONOF')
2 -
access("CTXSYS"."CONTAINS"("XML_SOURCE",'2657136589')>0)
So we see that I/O and CPU cost has
come down significantly.
OLD execution plan:-
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------------
| Id
| Operation
| Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 841 |
1377K| 9158 (1)|
|*
1 | TABLE ACCESS BY INDEX ROWID
BATCHED| F1_OUTMSG | 841 | 1377K|
9158 (1)|
|*
2 | INDEX SKIP SCAN | FT010S1 | 16819 | |
1612 (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("XML_SOURCE" /*+ LOB_BY_VALUE */ LIKE '%58343768923659%')
2 - access("OUTMSG_TYPE_CD"='CMAMIMTRONOF')
filter("OUTMSG_TYPE_CD"='CMAMIMTRONOF')
So you can see CPU and I/O cost is
very hish compared to new plan which is found after creating domain index and changing the
query.
Conclusion: When there is a predicate
in your query which is accessing CLOB column better create Oracle Text based
index.
