Hi, We are using version 11.2.0.4 of Oracle. We have below query which executes ~50K+ times in an hour and many times we saw cpu utilization reaches ~100% making other queries crawl. This query consumes ~30-40% CPU time alone during those issue period, so we want to make this query efficient such that CPU utilization can be reduced. From ASH its evident its plan_line_id 6 and 7 below which consumes major part of CPU, and then looking into the predicate section made it clear that the evaluation of functions INSTR/SUBSTR/LENGTH etc is actually consuming those extra CPU cycles for each execution.
1)So wants experts suggestion, what is the possible options to reduce the CPU consumption for this query(apart from asking application team to reduce the number of execution for this query from application side itself)?
2)In general, do see other queries which are executing many number of times(50k+ times) in an hour even the individual execution finishing in less than seconds but from the ASH the major contributor of CPU appears to be a full table scan(which holds 390k records and 50mb in total size but filtering 1/2 rows in the query) part of the query and in some cases "index skip scan". How to make the query consume lesser CPU in these scenarios? Does creating new/better selective indexes is the only solution?
Below is the query and its sqlmonitor by manually executing it. The actual query does select specific columns from these two tables, but i have mentioned as "SELECT *" to make the query look simple.
SELECT *
FROM TAB_QUE TQ, TAB_MSTR TM
WHERE TQ.RTYP = TM.RTYP
AND TQ.RUN_DT <= SYSDATE
AND TQ.RUN_DT >= SYSDATE - 7
AND TQ.A_USR = :b1
AND SUBSTR (TQ.VAL,INSTR (TQ.VAL, 'MID=') + LENGTH ('MID='), INSTR (TQ.VAL,',', INSTR (TQ.VAL, 'MID')) - (INSTR (TQ.VAL, 'MID') + LENGTH ('MID='))) = :b2
AND SUBSTR ( TQ.VAL, INSTR (TQ.VAL, 'XXX=') + LENGTH ('XXX='),INSTR (TQ.VAL, ',', INSTR (TQ.VAL, 'XXX'))
- (INSTR (TQ.VAL, 'XXX') + LENGTH ('XXX='))) = :b3
AND SUBSTR (TQ.VAL,INSTR (TQ.VAL, 'TID=') + LENGTH ('TID='),INSTR (TQ.VAL,',',INSTR (TQ.VAL, 'TID'))
- (INSTR (TQ.VAL, 'TID') + LENGTH ('TID='))) = :b4
ORDER BY TQ.RUN_DT DESC;
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 04/24/2019 05:29:28
First Refresh Time : 04/24/2019 05:29:28
Last Refresh Time : 04/24/2019 05:30:27
Duration : 59s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=================================================================
| 61 | 5.61 | 55 | 2 | 161K | 34680 | 271MB |
=================================================================
SQL Plan Monitoring Details (Plan Hash Value=2553650014)
=======================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 53 | +7 | 1 | 2 | | | | | |
| 1 | SORT ORDER BY | | 1 | 74682 | 53 | +7 | 1 | 2 | | | 2048 | | |
| 2 | FILTER | | | | 34 | +7 | 1 | 2 | | | | | |
| 3 | NESTED LOOPS | | 1 | 74681 | 34 | +7 | 1 | 2 | | | | | |
| 4 | NESTED LOOPS | | 1 | 74681 | 34 | +7 | 1 | 2 | | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 74680 | 34 | +7 | 1 | 2 | | | | | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_QUE | 1 | 74680 | 59 | +1 | 2 | 2 | 33212 | 259MB | | 96.49 | Cpu (3) |
| | | | | | | | | | | | | | db file sequential read (52) |
| 7 | INDEX SKIP SCAN | PK_TAB_QUE | 1 | 74679 | 57 | +3 | 2 | 150K | 1466 | 11MB | | 3.51 | db file sequential read (2) |
| 8 | INDEX UNIQUE SCAN | PK_TAB_MSTR | 1 | | 53 | +7 | 2 | 2 | 1 | 8192 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | TAB_MSTR | 1 | 1 | 34 | +7 | 2 | 2 | 1 | 8192 | | | |
=======================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 2553650014
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 635 | 74682 (1)| 00:14:57 | | |
| 1 | SORT ORDER BY | | 1 | 635 | 74682 (1)| 00:14:57 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 635 | 74681 (1)| 00:14:57 | | |
| 4 | NESTED LOOPS | | 1 | 635 | 74681 (1)| 00:14:57 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 591 | 74680 (1)| 00:14:57 | KEY | KEY |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB_QUE | 1 | 591 | 74680 (1)| 00:14:57 | KEY | KEY |
|* 7 | INDEX SKIP SCAN | PK_TAB_QUE | 1 | | 74679 (1)| 00:14:57 | KEY | KEY |
|* 8 | INDEX UNIQUE SCAN | PK_TAB_MSTR | 1 | | 0 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | TAB_MSTR | 1 | 44 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-7<=SYSDATE@!)
6 - filter("TQ"."A_USR"=:B1 AND SUBSTR("TQ"."VAL",INSTR("TQ"."VAL",'MID=')+11,INSTR("TQ"."VAL",',',INSTR("TQ"."VAL",'MID'))-(INSTR("TQ"."VAL",'MID')+11))=:B2 AND SUBSTR("TQ"."VAL",INSTR("TQ"."VAL",'XXX=')+4,INSTR("TQ"."VAL",',',INSTR("TQ"."VAL",'XXX'))-(INSTR("TQ"."VAL",'XXX')+4))=:B3 AND SUBSTR("TQ"."VAL",INSTR("TQ"."VAL",'TID=')+11,INSTR("TQ"."VAL",',',INSTR("TQ"."VAL",'TID'))-(INSTR("TQ"."VAL",'TID')+11))=:B4)
7 - access("TQ"."RUN_DT">=SYSDATE@!-7 AND "TQ"."RUN_DT"<=SYSDATE@!)
filter("TQ"."RUN_DT">=SYSDATE@!-7 AND "TQ"."RUN_DT"<=SYSDATE@!)
8 - access("TQ"."RTYP"="TM"."RTYP")