This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Nov 16, 2012 2:22 AM by Dom Brooks RSS

CPU intensive query

user10274093 Explorer
Currently Being Moderated
Hi,
on 11g R2 64 bits on Win 2008 R2, when query runs CPU is used at 100%.

AWR says :
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            2,805          80.1
db file sequential read               5,189         100     19    2.8 User I/O
direct path read                      1,633           9      5     .2 User I/O
log file sync                         1,245           7      5     .2 Commit
db file scattered read                   73           2     24     .0 User I/O
Host CPU (CPUs:    1 Cores:    1 Sockets:    1)
And SQL Tunning job, when ran said :
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Schema Name: userSQL ID     : ad7t7wmzzdfmu
SQL Text   : SELECT * FROM VW_I

------------------------------------------------------------------------------
There are no recommendations to improve the statement.
Is the CPU utilization to 100% generated from an Oracle process ?

Is there any utility in Win 2008 or Oracle to check the threads from the Windows side ?

Thank you.
  • 1. Re: CPU intensive query
    asifkabirdba Guru
    Currently Being Moderated
    Check your SQL execution plan for that query. Create index in that table which avoid the full table scan.


    Regards
    Asif Kabir
  • 2. Re: CPU intensive query
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    I suggest please review:
         Troubleshooting: High CPU Utilization [ID 164768.1]

    Regard
    Helios
  • 3. Re: CPU intensive query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Post EXPLAIN PLAN Please


    http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm
  • 4. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thanks to all.
    my explain plan is too large and we are limited to 30000 characthers. What can I do ? Here is a part
                                                                                                                                               
    ----------------------------------------------------------------------------------------------                                              
    | Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)|                                              
    ----------------------------------------------------------------------------------------------                                              
    |   0 | SELECT STATEMENT                   |                    |     1 |   255 |  1009   (0)|                                              
    |   1 |  TABLE ACCESS BY INDEX ROWID       | PS_NAME_PREFIX_LNG |     1 |    12 |     1   (0)|                                              
    |*  2 |   INDEX UNIQUE SCAN                | PS_NAME_PREFIX_LNG |     1 |       |     0   (0)|                                              
    |   3 |  TABLE ACCESS BY INDEX ROWID       | PS_PERSONAL_PHONE  |     1 |    29 |     2   (0)|                                              
    |*  4 |   INDEX UNIQUE SCAN                | PS_PERSONAL_PHONE  |     1 |       |     1   (0)|                                              
    |   5 |  TABLE ACCESS BY INDEX ROWID       | PS_EMAIL_ADDRESSES |     1 |    37 |     2   (0)|                                              
    |*  6 |   INDEX UNIQUE SCAN                | PS_EMAIL_ADDRESSES |     1 |       |     1   (0)|                                              
    |   7 |  SORT UNIQUE                       |                    |     1 |    24 |     8  (13)|                                              
    |   8 |   TABLE ACCESS BY INDEX ROWID      | PS_WKF_CNT_TYPE    |     1 |    24 |     3   (0)|                                              
    |*  9 |    INDEX RANGE SCAN                | PS_WKF_CNT_TYPE    |     1 |       |     2   (0)|                                              
    |  10 |     SORT AGGREGATE                 |                    |     1 |    20 |            |                                              
    |* 11 |      INDEX RANGE SCAN              | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                              
    |* 12 |  INDEX RANGE SCAN                  | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                              
    |  13 |   SORT AGGREGATE                   |                    |     1 |    20 |            |                                              
    |* 14 |    INDEX RANGE SCAN                | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                              
    |* 15 |  TABLE ACCESS BY INDEX ROWID       | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                              
    |* 16 |   INDEX RANGE SCAN                 | PS4DEPT_TBL        |     1 |       |     2   (0)|                                              
    |  17 |    SORT AGGREGATE                  |                    |     1 |    21 |            |                                              
    |* 18 |     INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |* 19 |  TABLE ACCESS BY INDEX ROWID       | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                              
    |* 20 |   INDEX RANGE SCAN                 | PS4DEPT_TBL        |     1 |       |     2   (0)|                                              
    |  21 |    SORT AGGREGATE                  |                    |     1 |    21 |            |                                              
    |* 22 |     INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |* 23 |  TABLE ACCESS BY INDEX ROWID       | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                              
    |* 24 |   INDEX RANGE SCAN                 | PS4DEPT_TBL        |     1 |       |     2   (0)|                                              
    |  25 |    SORT AGGREGATE                  |                    |     1 |    21 |            |                                              
    |* 26 |     INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |* 27 |  TABLE ACCESS BY INDEX ROWID       | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                              
    |* 28 |   INDEX RANGE SCAN                 | PS4DEPT_TBL        |     1 |       |     2   (0)|                                              
    |  29 |    SORT AGGREGATE                  |                    |     1 |    21 |            |                                              
    |* 30 |     INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |  31 |  SORT AGGREGATE                    |                    |     1 |    28 |            |                                              
    |* 32 |   TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     8   (0)|                                              
    |* 33 |    INDEX RANGE SCAN                | PS_DEPT_TBL        |     1 |       |     7   (0)|                                              
    |  34 |     SORT AGGREGATE                 |                    |     1 |    21 |            |                                              
    |* 35 |      INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |* 36 |  TABLE ACCESS BY INDEX ROWID       | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                              
    |* 37 |   INDEX RANGE SCAN                 | PS4DEPT_TBL        |     1 |       |     2   (0)|                                              
    |  38 |    SORT AGGREGATE                  |                    |     1 |    21 |            |                                              
    |* 39 |     INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    |* 40 |  FILTER                            |                    |       |       |            |                                              
    |  41 |   NESTED LOOPS                     |                    |     1 |   255 |    77   (0)|                                              
    |  42 |    NESTED LOOPS                    |                    |     1 |   218 |    76   (0)|                                              
    |  43 |     NESTED LOOPS                   |                    |     1 |   192 |    74   (0)|                                              
    |  44 |      MERGE JOIN CARTESIAN          |                    |     1 |   101 |    72   (0)|                                              
    |  45 |       NESTED LOOPS                 |                    |     1 |    75 |    69   (0)|                                              
    |  46 |        NESTED LOOPS                |                    |     3 |   165 |    63   (0)|                                              
    |  47 |         TABLE ACCESS BY INDEX ROWID| PS_NAMES           |     3 |   120 |    60   (0)|                                              
    |* 48 |          INDEX SKIP SCAN           | PSANAMES           |     3 |       |    57   (0)|                                              
    |  49 |           SORT AGGREGATE           |                    |     1 |    19 |            |                                              
    |* 50 |            INDEX RANGE SCAN        | PS_NAMES           |     1 |    19 |     2   (0)|                                              
    |  51 |         TABLE ACCESS BY INDEX ROWID| PS_PERSON          |     1 |    15 |     1   (0)|                                              
    |* 52 |          INDEX UNIQUE SCAN         | PS_PERSON          |     1 |       |     0   (0)|                                              
    |  53 |        TABLE ACCESS BY INDEX ROWID | PS_PERS_DATA_EFFDT |     1 |    20 |     2   (0)|                                              
    |* 54 |         INDEX RANGE SCAN           | PS_PERS_DATA_EFFDT |     1 |       |     1   (0)|                                              
    |  55 |          SORT AGGREGATE            |                    |     1 |    15 |            |                                              
    |* 56 |           INDEX RANGE SCAN         | PS_PERS_DATA_EFFDT |     1 |    15 |     2   (0)|                                              
    |  57 |       BUFFER SORT                  |                    |  1013 | 26338 |    70   (0)|                                              
    |  58 |        INDEX FAST FULL SCAN        | PS4DEPT_TBL        |  1013 | 26338 |     3   (0)|                                              
    |* 59 |      TABLE ACCESS BY INDEX ROWID   | PS_JOB             |     1 |    91 |     2   (0)|                                              
    |* 60 |       INDEX RANGE SCAN             | PS1JOB             |     1 |       |     1   (0)|                                              
    |* 61 |        TABLE ACCESS BY INDEX ROWID | PS_JOB             |     1 |    26 |     3   (0)|                                              
    |* 62 |         INDEX RANGE SCAN           | PSAJOB             |     1 |       |     2   (0)|                                              
    |  63 |          SORT AGGREGATE            |                    |     1 |    17 |            |                                              
    |* 64 |           INDEX RANGE SCAN         | PSAJOB             |     4 |    68 |     2   (0)|                                              
    |  65 |          SORT AGGREGATE            |                    |     1 |    20 |            |                                              
    |* 66 |           INDEX RANGE SCAN         | PSAJOB             |     1 |    20 |     2   (0)|                                              
    |  67 |        SORT AGGREGATE              |                    |     1 |    17 |            |                                              
    |* 68 |         INDEX RANGE SCAN           | PSAJOB             |     4 |    68 |     2   (0)|                                              
    |  69 |        SORT AGGREGATE              |                    |     1 |    20 |            |                                              
    |* 70 |         INDEX RANGE SCAN           | PSAJOB             |     1 |    20 |     2   (0)|                                              
    |  71 |     TABLE ACCESS BY INDEX ROWID    | PS_JOBCODE_TBL     |     1 |    26 |     2   (0)|                                              
    |* 72 |      INDEX RANGE SCAN              | PS_JOBCODE_TBL     |     1 |       |     1   (0)|                                              
    |  73 |       SORT AGGREGATE               |                    |     1 |    20 |            |                                              
    |* 74 |        INDEX RANGE SCAN            | PS_JOBCODE_TBL     |     1 |    20 |     2   (0)|                                              
    |  75 |    TABLE ACCESS BY INDEX ROWID     | PS_JOB_CL_I        |     1 |    37 |     1   (0)|                                              
    |* 76 |     INDEX UNIQUE SCAN              | PS_JOB_CL_I        |     1 |       |     0   (0)|                                              
    |  77 |   SORT AGGREGATE                   |                    |     1 |    21 |            |                                              
    |* 78 |    INDEX RANGE SCAN                | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                              
    ----------------------------------------------------------------------------------------------                                              
                                                                                                                                                
    Predicate Information (identified by operation id):                                                                                         
    ---------------------------------------------------                                                                                                                                                                                                                                
       2 - access("L"."NAME_PREFIX"=:B1 AND "L"."LANGUAGE_CD"='FRA')                                                                            
       4 - access("F"."EMPLID"=:B1 AND "F"."PHONE_TYPE"='BUSN')                                                                                 
       6 - access("G"."EMPLID"=:B1 AND "G"."E_ADDR_TYPE"='BUSN')                                                                                
       9 - access("H"."EMPLID"=:B1 AND "H"."CONTRACT_NUM"=:B2)                                                                                  
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_WKF_CNT_TYPE" "W"                                              
                  WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND                                                                                 
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND "W"."CONTRACT_NUM"=:B1 AND "W"."EMPLID"=:B2 AND                                                                  
                  SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YY                                        
                  YY-MM-DD')))                                                                                                                  
      11 - access("W"."EMPLID"=:B1 AND "W"."CONTRACT_NUM"=:B2 AND                                                                               
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)                                                                             
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YY                                                      
                  YY-MM-DD'),'YYYY-MM-DD'))                                                                                                     
      12 - access("H"."EMPLID"=:B1 AND "H"."CONTRACT_NUM"=:B2)                                                                                  
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_WKF_CNT_TYPE" "W"                                              
                  WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND                                                                                 
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND "W"."CONTRACT_NUM"=:B1 AND "W"."EMPLID"=:B2 AND                                                                  
                  SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YY                                        
                  YY-MM-DD')))                                                                                                                  
      14 - access("W"."EMPLID"=:B1 AND "W"."CONTRACT_NUM"=:B2 AND                                                                               
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)                                                                             
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YY                                                      
                  YY-MM-DD'),'YYYY-MM-DD'))                                                                                                     
      15 - filter("DEPT"."EFF_STATUS"='A')                                                                                                      
      16 - access("DEPT"."MANAGER_ID"=:B1 AND "DEPT"."SETID"='SHARE' AND                                                                        
                  "DEPT"."DEPTID"='F0100R')                                                                                                     
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "DEPT1"                                              
                  WHERE "DEPT1"."DEPTID"=:B1 AND "DEPT1"."SETID"=:B2))                                                                          
      18 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      19 - filter("DEPT"."EFF_STATUS"='A')                                                                                                      
      20 - access("DEPT"."MANAGER_ID"=:B1 AND "DEPT"."SETID"='SHARE')                                                                           
           filter(SUBSTR("DEPT"."DEPTID",4,1)='0' AND (SUBSTR("DEPT"."DEPTID",1,1)='R' OR                                                       
                  SUBSTR("DEPT"."DEPTID",1,1)='C') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=                                               
                  (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL"                                              
                  "DEPT1" WHERE "DEPT1"."DEPTID"=:B1 AND "DEPT1"."SETID"=:B2))                                                                  
      22 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      23 - filter("DEPT"."EFF_STATUS"='A')                                                                                                      
      24 - access("DEPT"."MANAGER_ID"=:B1 AND "DEPT"."SETID"='SHARE')                                                                           
           filter(SUBSTR("DEPT"."DEPTID",1,1)='F' AND SUBSTR("DEPT"."DEPTID",4,1)='0' AND                                                       
                  "DEPT"."DEPTID"<>'F0100R' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                              
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "DEPT1"                                              
                  WHERE "DEPT1"."DEPTID"=:B1 AND "DEPT1"."SETID"=:B2))                                                                          
      26 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      27 - filter("DEPT"."EFF_STATUS"='A')                                                                                                      
      28 - access("DEPT"."MANAGER_ID"=:B1 AND "DEPT"."SETID"='SHARE')                                                                           
           filter(SUBSTR("DEPT"."DEPTID",4,1)<>'0' AND                                                                                          
                  SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "DEPT1"                                              
                  WHERE "DEPT1"."DEPTID"=:B1 AND "DEPT1"."SETID"=:B2))                                                                          
      30 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      32 - filter("DEPTM"."EFF_STATUS"='A')                                                                                                     
      33 - access("DEPTM"."SETID"='SHARE')                                                                                                      
           filter(SUBSTR("DEPTM"."DEPTID",4,1)='0' AND                                                                                          
                  SUBSTR("DEPTM"."DEPTID",1,3)=SUBSTR(:B1,1,3) AND                                                                              
                  SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "DEPT1"                                              
                  WHERE "DEPT1"."DEPTID"=:B2 AND "DEPT1"."SETID"=:B3))                                                                          
      35 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      36 - filter("DEPT"."EFF_STATUS"='A')                                                                                                      
      37 - access("DEPT"."MANAGER_ID"=:B1 AND "DEPT"."SETID"='SHARE')                                                                           
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "DEPT1"                                              
                  WHERE "DEPT1"."DEPTID"=:B1 AND "DEPT1"."SETID"=:B2))                                                                          
      39 - access("DEPT1"."SETID"=:B1 AND "DEPT1"."DEPTID"=:B2)                                                                                 
      40 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "Y" WHERE                                            
                  SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DAT                                        
                  E(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "Y"."DEPTID"=:B1 AND                                                     
                  "Y"."SETID"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE                                        
                  @!,'YYYY-MM-DD'),'YYYY-MM-DD')))                                                                                              
      48 - access("C"."NAME_TYPE"='PRI')                                                                                                        
           filter("C"."NAME_TYPE"='PRI' AND "C"."EFFDT"= (SELECT                                                                                
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_NAMES" "J" WHERE                                               
                  SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DAT                                        
                  E(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "J"."NAME_TYPE"=:B1 AND                                                  
                  "J"."EMPLID"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDAT                                        
                  E@!,'YYYY-MM-DD'),'YYYY-MM-DD')))                                                                                             
      50 - access("J"."EMPLID"=:B1 AND "J"."NAME_TYPE"=:B2 AND                                                                                  
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)                                                                             
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YY                                                      
                  YY-MM-DD'),'YYYY-MM-DD'))                                                                                                     
      52 - access("C"."EMPLID"="D"."EMPLID")                                                                                                    
      54 - access("D"."EMPLID"="E"."EMPLID")                                                                                                    
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT                                                                            
                  MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_PERS_DATA_EFFDT" "S"                                           
                  WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND                                                                                 
                  SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-                                        
                  MM-DD')) AND "S"."EMPLID"=:B1 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(T                                        
                  O_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')))                                                                                
      56 - access("S"."EMPLID"=:B1 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_                                                      
                  CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)                                          
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YY                                                      
                  YY-MM-DD'),'YYYY-MM-DD'))                                                                                                     
                                                                                                   
       - 'PLAN_TABLE' is old version                                                                                                            
  • 5. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    See tuning by cardinality feedback:
    http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf

    Perhaps your actual cardinalities in so many of these steps are not 1, estimates which lead to poor plan choices.

    Are the predicate functions SYS_OP_UNDESCEND and SYS_OP_DESCEND internal only and symptomatic of you having a descending index?

    Also perhaps you're using subqueries where analytic functions might be better?

    Are the underlying object statistics uptodate and reasonably accurate?

    Estimates of 1 can lead to particular poor choices around MERGE JOIN CARTESIAN/BUFFER SORT operations (lines 44,57) and also INDEX SKIP SCAN decisions (line 48).

    Unfortunately, we can only guess about this small fragment of the plan.

    See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request

    What you really need to know is where the time is being spent, which steps have significantly inaccurate estimates

    As you're on 11gR2, if you're licensed for diagnostic pack then I would recommend using real time sql monitoring via DBMS_SQLTUNE.REPORT_SQL_MONITOR.
    But, given that you have a long sql statement, you're highly likely to hit the length limit enforced by parameter sqlmonmax_planlines.

    So, fall back to good old fashioned sql trace.

    If licensed for diagnostic pack, you might also look at the raw ASH data in v$active_session_history to see what steps the sql statement spends most time on. This is sampled so is a limited view and not ideal for sql tuning.
  • 6. Re: CPU intensive query
    Mohamed Houri Pro
    Currently Being Moderated
    Hi,
    on 11g R2 64 bits on Win 2008 R2, when query runs CPU is used at 100%.
    
    AWR says :
    
    
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            2,805          80.1
    db file sequential read               5,189         100     19    2.8 User I/O
    direct path read                      1,633           9      5     .2 User I/O
    log file sync                         1,245           7      5     .2 Commit
    db file scattered read                   73           2     24     .0 User I/O
    Host CPU (CPUs:    1 Cores:    1 Sockets:    1)
    
    
    Is the CPU utilization to 100% generated from an Oracle process ?
    The Top 5 timed events section of the AWR you have posted showing DB CPU to be 80% is not sufficient at all to state about wether your are CPU bound or not. In order to have an idea about the real CPU utilisation you need to have (a) the duration of the AWR snapshot and (b) the number of CPU your system has.

    Let me suppose that the snapshot of this AWR is 60 minutes(3600 seconds) and you have 40 CPU machines. Then the arithmetic to know if you are CPU bound or not is:

    3600/2805 = *1,28 CPU* used at 100%

    If you have only one CPU then of course you are CPU bound. But when you have 40 CPU, you need to divide the above result by the number of available CPU

    1,28/40 = 0,032

    Which means in this case that your CPU is used at *3%* of its total capacity.

    Always cross check and do few arithmetics before jumping to wrong conclusions

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 7. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thank you Dom,
    -how can I know where the time is being spent ?

    -Even if I format my tuning request and Explain Plan can I post it here being larger than 30000 characters ?

    Regrads.
  • 8. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    Follow advice in the tuning thread.
    It tells you how to trace it.

    First check the obvious - relevant table & index stats exist and are broadly accurate, at least non zero.

    Otherwise get your trace or dbms_xplan.dispaly_cursor execution metrics (see thread) and then follow the principles of tuning by cardinality feedback.
  • 9. Re: CPU intensive query
    Nikolay Savvinov Guru
    Currently Being Moderated
    hi,

    1) you only have one CPU -- so whenever a session is performing a high amount of work, your CPU usage will be near 100% (unless you use Resource Manager to enforce CPU quotas). it's not a good idea to run an Oracle database on a single CPU server
    2) the main source of CPU usage by queries is logical reads -- your query is probably doing hundreds of thousands of them; however, the plan you posted shows that the optimizer only expects much less. That means that the optimizer doesn't have the correct information about the amount of data your query will have to process in order to answer it -- either table stats are incorrect (stale or non-representative) or because the predicates are too complex to allow accurate selectivity estimation. In any case, since the optimizer is wrong about the cost of the query, we cannot trust its judgement
    3) since you're on 11g, you can use also SQL monitor to see where your query is spending most of the time, in addition to other tools like dbms_xplan.

    Best regards,
    Nikolay
  • 10. Re: CPU intensive query
    PavanKumar Guru
    Currently Being Moderated
    Hi,


    1. Repeated aggregate sorts on segment "PS_DEPT_TBL" (from steps 15 - 39).
    I hope you can re-write your query to get one-fetch of aggregate data once instead of repeated fetches with IRC(Index range scan)

    2. Step 44 - 44 | MERGE JOIN CARTESIAN
    further .. 57 | BUFFER SORT
    INDEX SKIP SCAN | PSANAMES

         segments - PS_JOB, PS_PERSON, PSANAMES

    Can you perform the 10032 trace on the query to find the sorts (memory), how many comparisons are carried.
    I suspect, you can try to check existing index or create new index with above segments which hold information on your
    caridnality. So that we can re-write the query to use the PGA for sorting (we can try to reduce)

    I see out 1013 - finally a row of aggreate one is fetching across.

    3. SYS_OP_DESCEND -- I hope your indexes are picking FBI (function based index), we might try to optmize with correct index (doubtful on this step)

    - Pavan Kumar N
  • 11. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Hi,
    Thanks to all.

    What is the reason to have buffer sort ? How to reduce it ?
    Why in Explain plan I do not have Time column ? How to have it ?

    I display it by :
    SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

    Regards.
  • 12. Re: CPU intensive query
    PavanKumar Guru
    Currently Being Moderated
    Hi,
    What is the reason to have buffer sort ? How to reduce it ? 
    I can only say "MERGE JOIN CARTESIAN" which was influencing the sort - reasons for that would be missing joining columns with respect choosen indexes for query.
    Secondly, I see or might be data type issue/conversions.
    Why in Explain plan I do not have Time column ? How to have it ? 
    Check whether your plan table is older, just incase, else it would have dispalyed across.

    - Pavan Kumar N
  • 13. Re: CPU intensive query
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    >
    What is the reason to have buffer sort ?
    Merge joins (including cartesian merge joins) sort their inputs before joining.
    How to reduce it ?
    So far there is no indication whatsoever that it needs to be "reduced".
    Why in Explain plan I do not have Time column ? How to have it ?
    You don't need the Time column. It's the same as COST, only expressed in different units. The important thing is that this is not the actual timing, it's an optimizer estimate thereof. Actual timings show up in A-time column. It's displayed if:

    1) the query was executed with statistics_level = all or gather_plan_statistics hint
    2) the format parameter of dbms_xplan was set appropriately (e.g. 'iostats last' or 'allstats last')

    You can find some useful information about displaying A-time and A-rows in my blog: http://savvinov.com/category/dbms_xplan/

    Best regards,
    Nikolay
  • 14. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thanks to all.
    The same query , on another server runs quickly (just 15 mins). I compared the explain plan of both.
    In the good one there is no BUFFER SORT. In the very very slow one (we should do ctrl ^c to stop it) I have :
    ----------------------------------------------------------------------------------------------                                              
    | Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)|                                              
    ---------------------------------------------------------------------------------------------- 
      57 |       BUFFER SORT                  |                    |  1013 | 26338 |    70   (0)|
    If buffer sort comes from merge joins , they are the same on both DBs.

    Why there is no buffer sort on one of the database but on another ?

    Thanks and regards.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points