Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Improving CPU Bound Query

User_OCZ1TApr 24 2019 — edited May 9 2019

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")

This post has been answered by AndrewSayer on Apr 24 2019
Jump to Answer
Comments
Post Details
Added on Apr 24 2019
7 comments
1,271 views