This discussion is archived
1 3 4 5 6 7 Previous Next 98 Replies Latest reply: Dec 14, 2009 11:01 PM by 695836 Go to original post RSS
  • 90. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Woow..its looks better without indexes.....but still its puzzling how you found out from the plan the estimate time for sql without index is better butanyway..

    with index
    WITH INDEX
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                                     | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads 
    ----------------------------------------------------------------------------------------------------
    |   1 |  TEMP TABLE TRANSFORMATION                    |                            |      1 |        |      2 |00:00:00.63 |     134K|    227
    |   2 |   LOAD AS SELECT                              |                            |      1 |        |      1 |00:00:00.03 |      38 |      0
    |   3 |    HASH UNIQUE                                |                            |      1 |      1 |      1 |00:00:00.01 |      34 |      0
    |   4 |     TABLE ACCESS BY INDEX ROWID               | PR_CALENDAR                |      1 |      1 |      3 |00:00:00.01 |
    |*  5 |      INDEX RANGE SCAN                         | PR_CALENDAR_IDX4           |      1 |      1 |      3 |00:00:00.01 |      32
    |*  6 |       TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 
    |*  7 |        INDEX RANGE SCAN                       | PR_CALENDAR_FIDX1          |      1 |      2 |    244 |00:00:00.01 |     
    |*  8 |        TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |*  9 |         INDEX RANGE SCAN                      | PR_CALENDAR_FIDX1          |      1 |      2 |    244 |00:00:00.01 |       1 |   
    |* 10 |       TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 
    |* 11 |        INDEX RANGE SCAN                       | PR_CALENDAR_FIDX1          |      1 |      2 |    244 |00:00:00.01 |     
    |  12 |   SORT ORDER BY                               |                            |      1 |      2 |      2 |00:00:00.60 |     134K|    227
    |  13 |    VIEW                                       |                            |      1 |      2 |      2 |00:00:00.60 |     134K|    227
    |  14 |     UNION-ALL                                 |                            |      1 |        |      2 |00:00:00.60 |     134K|    227
    |  15 |      HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.60 |     134K|    227
    |  16 |       COUNT                                   |                            |      1 |        |      1 |00:00:00.60 |     134K|    227
    |  17 |        NESTED LOOPS OUTER                     |                            |      1 |      1 |      1 |00:00:00.60 |     134K|    227
    |  18 |         VIEW                                  |                            |      1 |      1 |      1 |00:00:00.01 |       6 |      1
    |  19 |          TABLE ACCESS FULL                    | SYS_TEMP_0FD9D660A_2153D5D |      1 |      1 |      1 |00:00:00.01 |    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |  20 |         VIEW                                  |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |* 21 |          VIEW                                 |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |  22 |           SORT UNIQUE                         |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |  23 |            WINDOW SORT                        |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |  24 |             NESTED LOOPS                      |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |* 25 |              VIEW                             |                            |      1 |      1 |      0 |00:00:00.60 |     134K|    226
    |  26 |               SORT UNIQUE                     |                            |      1 |      1 |   5332 |00:00:00.59 |     134K|    226
    |* 27 |                FILTER                         |                            |      1 |        |   5357 |00:00:00.42 |     134K|    226
    |  28 |                 NESTED LOOPS                  |                            |      1 |      1 |   5357 |00:00:00.40 |     132K|    226
    |  29 |                  NESTED LOOPS                 |                            |      1 |      1 |   5357 |00:00:00.36 |     121K|    226
    |* 30 |                   HASH JOIN                   |                            |      1 |      3 |  56536 |00:00:00.27 |   64762 |    225
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |  31 |                    TABLE ACCESS BY INDEX ROWID| PR_MAPPING_DTL             |      1 |      3 |  52906 |00:00:00.21 |   6
    |  32 |                     NESTED LOOPS              |                            |      1 |      3 |  63810 |00:00:00.19 |   22532 |      0
    |  33 |                      NESTED LOOPS             |                            |      1 |      1 |  10903 |00:00:00.06 |   11534 |      0
    |* 34 |                       HASH JOIN               |                            |      1 |   7785 |  10903 |00:00:00.01 |     629 |      0
    |  35 |                        NESTED LOOPS           |                            |      1 |    420 |    420 |00:00:00.01 |      16 |      0
    |* 36 |                         INDEX UNIQUE SCAN     | PR_REPORT_PK               |      1 |      1 |      1 |00:00:00.01 |       1 |      0
    |  37 |                         TABLE ACCESS FULL     | PR_MAPPING                 |      1 |    420 |    420 |00:00:00.01 |      15 |      0
    |* 38 |                        TABLE ACCESS FULL      | PR_RECORD_DTL              |      1 |   8322 |   8321 |00:00:00.01 |     613
    |* 39 |                       INDEX UNIQUE SCAN       | PK_PR_CALENDAR             |  10903 |      1 |  10903 |00:00:00.03 | 
    |* 40 |                      INDEX RANGE SCAN         | PK_PR_MAPPING_DTL_PR_ID    |  10903 |      4 |  52906 |00:00:00
    |  41 |                    TABLE ACCESS FULL          | PR_REPORT_DATA             |      1 |   1114 |   1114 |00:00:00.01 |      15 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |* 42 |                   TABLE ACCESS BY INDEX ROWID | PR_REPORT_SECTION          |  56536 |      1 |   5357 |00:00:00.24 
    |* 43 |                    INDEX UNIQUE SCAN          | PR_REPORT_SECTION_PK       |  56536 |      1 |  56536 |00:00:00.11 
    |  44 |                  TABLE ACCESS BY INDEX ROWID  | HRM_EMPLOYEE               |   5357 |      1 |   5357 |00:00:00.03 |   
    |* 45 |                   INDEX UNIQUE SCAN           | HRM_EMPLOYEE_PK            |   5357 |      1 |   5357 |00:00:00.01 |    535
    |  46 |                 NESTED LOOPS                  |                            |   1191 |      1 |      0 |00:00:00.01 |    2476 |      0
    |* 47 |                  INDEX RANGE SCAN             | IDX_PR_RECORD_DTL          |   1191 |      1 |      0 |00:00:00.01 |    2476
    |* 48 |                  TABLE ACCESS BY INDEX ROWID  | PR_CALENDAR                |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 49 |                   INDEX RANGE SCAN            | PR_CALENDAR_FIDX1          |      0 |      2 |      0 |00:00:00.01 |       0 |
    |* 50 |              TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR                |      0 |      1 |      0 |00:00:00.01 |
    |* 51 |               INDEX RANGE SCAN                | PR_CALENDAR_FIDX1          |      0 |      2 |      0 |00:00:00.01 |      
    |  52 |      HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.01 |       5 |      0
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |* 53 |       HASH JOIN OUTER                         |                            |      1 |      1 |      1 |00:00:00.01 |       5 |      0
    |  54 |        VIEW                                   |                            |      1 |      1 |      1 |00:00:00.01 |       3 |      0
    |  55 |         TABLE ACCESS FULL                     | SYS_TEMP_0FD9D660A_2153D5D |      1 |      1 |      1 |00:00:00.01 |     
    |  56 |        VIEW                                   |                            |      1 |      1 |      0 |00:00:00.01 |       2 |      0
    |  57 |         HASH UNIQUE                           |                            |      1 |      1 |      0 |00:00:00.01 |       2 |      0
    |  58 |          WINDOW SORT                          |                            |      1 |      1 |      0 |00:00:00.01 |       2 |      0
    |* 59 |           TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR                |      1 |      1 |      0 |00:00:00.01 |     
    |  60 |            NESTED LOOPS                       |                            |      1 |      1 |      1 |00:00:00.01 |       2 |      0
    |  61 |             TABLE ACCESS BY INDEX ROWID       | PR_EMP_SETUP_REG           |      1 |      1 |      0 |00:00:00.
    |* 62 |              INDEX RANGE SCAN                 | PK_PR_EMP_SETUP_REG        |      1 |      1 |      0 |00:00:00.01 |     
    |* 63 |             INDEX RANGE SCAN                  | PR_CALENDAR_TEST           |      0 |      1 |      0 |00:00:00.01 |       0 
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("CAL_MONTH"<=)
           filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
       6 - filter("PAY_CALENDAR_ID"='00000062')
       7 - access("PR_CALENDAR"."SYS_NC00016$"='000')
       8 - filter("PAY_CALENDAR_ID"='00000062')
       9 - access("PR_CALENDAR"."SYS_NC00016$"='000')
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
      10 - filter("PAY_CALENDAR_ID"='00000062')
      11 - access("PR_CALENDAR"."SYS_NC00016$"='000')
      21 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
      25 - filter("RD"."EMPLOYEE_ID"='HQPRM003')
      27 - filter( IS NULL)
      30 - access("PRD"."DATA_NAME"="PMD"."NAME")
      34 - access("PM"."ITEM_ID"="PRD"."ITEM_ID")
      36 - access("PR"."REPORT_ID"=8)
      38 - filter("PRD"."PAY_CALENDAR_ID" NOT LIKE 'RET_%')
      39 - access("PRD"."PAY_CALENDAR_ID"="PC"."PAY_CALENDAR_ID")
      40 - access("PMD"."PR_ID"="PM"."PR_ID")
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
      42 - filter("PR"."REPORT_ID"="PRS"."REPORT_ID")
      43 - access("PRS"."SECTION_ID"="PRD"."REF_SECTION")
      45 - access("HE"."EMPLOYEE_ID"="PRD"."EMPLOYEE_ID")
      47 - access("IPRD"."EMPLOYEE_ID"=:B1 AND "IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
           filter("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
      48 - filter(("IPC"."REF_CALENDAR_ID"=:B1 AND "IPRD"."PAY_CALENDAR_ID"="IPC"."PAY_CALENDAR_ID"))
      49 - access(SUBSTR("IPRD"."PAY_CALENDAR_ID",1,3)="IPC"."SYS_NC00016$")
      50 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND "PC"."PAY_CALENDAR_ID"="RD"."PAY_CALEND
      51 - access("PC"."SYS_NC00016$"=SUBSTR("RD"."PAY_CALENDAR_ID",1,3))
      53 - access("A"."CAL_MONTH"="B"."CAL_MONTH")
      59 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
      62 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."NAME"='CPF_ADWAGE_LIMIT')
           filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
      63 - access("PESR"."START_DATE"<="PC"."END_DATE" AND "PESR"."START_DATE">="PC"."START_DATE")
           filter("PESR"."START_DATE"<="PC"."END_DATE")
    
    
    115 rows selected.
    
    Elapsed: 00:00:00.95
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                                   | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ----------------------------------------------------------------------------------------------------
    |   1 |  TEMP TABLE TRANSFORMATION                  |                            |      1 |        |      2 |00:00:00.18 |    7593 |      1 |
    |   2 |   LOAD AS SELECT                            |                            |      1 |        |      1 |00:00:00.12 |      17 |      0 |
    |   3 |    HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.01 |      13 |      0 |
    |   4 |     TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      3 |00:00:00.01 |      13 |      0 
    |*  5 |      INDEX RANGE SCAN                       | PR_CALENDAR_IDX4           |      1 |      1 |      3 |00:00:00.01 |      11 |      0 |
    |   6 |       TABLE ACCESS BY INDEX ROWID           | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      0
    |*  7 |        INDEX UNIQUE SCAN                    | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   8 |        TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      
    |*  9 |         INDEX UNIQUE SCAN                   | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
    |  10 |       TABLE ACCESS BY INDEX ROWID           | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      0
    |* 11 |        INDEX UNIQUE SCAN                    | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
    |  12 |   SORT ORDER BY                             |                            |      1 |     20 |      2 |00:00:00.06 |    7573 |      1 |
    |  13 |    VIEW                                     |                            |      1 |     20 |      2 |00:00:00.06 |    7573 |      1 |
    |  14 |     UNION-ALL                               |                            |      1 |        |      2 |00:00:00.06 |    7573 |      1 |
    |  15 |      HASH UNIQUE                            |                            |      1 |     19 |      1 |00:00:00.06 |    7431 |      1 |
    |  16 |       COUNT                                 |                            |      1 |        |      1 |00:00:00.06 |    7431 |      1 |
    |  17 |        NESTED LOOPS OUTER                   |                            |      1 |     19 |      1 |00:00:00.06 |    7431 |      1 |
    |  18 |         VIEW                                |                            |      1 |      1 |      1 |00:00:00.01 |       6 |      1 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |  19 |          TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6609_2153D5D |      1 |      1 |      1 |00:00:00.01 |       6 |      1
    |  20 |         VIEW                                |                            |      1 |     19 |      0 |00:00:00.06 |    7425 |      0 |
    |* 21 |          VIEW                               |                            |      1 |    132 |      0 |00:00:00.06 |    7425 |      0 |
    |  22 |           SORT UNIQUE                       |                            |      1 |    132 |      0 |00:00:00.06 |    7425 |      0 |
    |  23 |            WINDOW SORT                      |                            |      1 |    132 |      0 |00:00:00.06 |    7425 |      0 |
    |* 24 |             HASH JOIN                       |                            |      1 |    132 |      0 |00:00:00.06 |    7425 |      0 |
    |* 25 |              TABLE ACCESS FULL              | PR_CALENDAR                |      1 |     21 |    333 |00:00:00.01 |      15 |      0 |
    |* 26 |              VIEW                           |                            |      1 |   2643 |      0 |00:00:00.06 |    7410 |      0 |
    |  27 |               SORT UNIQUE                   |                            |      1 |   2643 |   5332 |00:00:00.06 |    7410 |      0 |
    |* 28 |                FILTER                       |                            |      1 |        |   5357 |00:00:00.06 |    7410 |      0 |
    |  29 |                 NESTED LOOPS                |                            |      1 |   2643 |   5357 |00:00:00.05 |    6313 |      0 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |* 30 |                  HASH JOIN                  |                            |      1 |   2622 |   5357 |00:00:00.03 |     954 |      0 |
    |  31 |                   TABLE ACCESS FULL         | HRM_EMPLOYEE               |      1 |   2986 |   3014 |00:00:00.01 |     280 |      
    |* 32 |                   HASH JOIN                 |                            |      1 |   2633 |   5357 |00:00:00.01 |     674 |      0 |
    |* 33 |                    HASH JOIN                |                            |      1 |    142 |    150 |00:00:00.01 |      61 |      0 |
    |* 34 |                     HASH JOIN               |                            |      1 |    142 |    150 |00:00:00.01 |      46 |      0 |
    |* 35 |                      HASH JOIN              |                            |      1 |    103 |    109 |00:00:00.01 |      31 |      0 |
    |  36 |                       NESTED LOOPS          |                            |      1 |      7 |      1 |00:00:00.01 |      16 |      0 |
    |* 37 |                        INDEX UNIQUE SCAN    | PR_REPORT_PK               |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
    |* 38 |                        TABLE ACCESS FULL    | PR_REPORT_SECTION          |      1 |      7 |      1 |00:00:00.01 |      15 |      0 |
    |  39 |                       TABLE ACCESS FULL     | PR_REPORT_DATA             |      1 |   1114 |   1114 |00:00:00.01 |      15 |     
    |  40 |                      TABLE ACCESS FULL      | PR_MAPPING_DTL             |      1 |   1468 |   1468 |00:00:00.01 |      15 |      
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |  41 |                     TABLE ACCESS FULL       | PR_MAPPING                 |      1 |    420 |    420 |00:00:00.01 |      15 |      0 |
    |* 42 |                    TABLE ACCESS FULL        | PR_RECORD_DTL              |      1 |   8322 |   8321 |00:00:00.01 |     613 |    
    |* 43 |                  INDEX UNIQUE SCAN          | PK_PR_CALENDAR             |   5357 |      1 |   5357 |00:00:00.01 |    5359 |    
    |  44 |                 NESTED LOOPS                |                            |    518 |      1 |      0 |00:00:00.01 |    1097 |      0 |
    |* 45 |                  INDEX RANGE SCAN           | IDX_PR_RECORD_DTL          |    518 |      1 |      0 |00:00:00.01 |    1097 |     
    |* 46 |                  TABLE ACCESS BY INDEX ROWID| PR_CALENDAR                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 47 |                   INDEX UNIQUE SCAN         | PK_PR_CALENDAR             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |  48 |      HASH UNIQUE                            |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0 |
    |* 49 |       HASH JOIN OUTER                       |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0 |
    |  50 |        VIEW                                 |                            |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
    |  51 |         TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6609_2153D5D |      1 |      1 |      1 |00:00:00.01 |       3 |      0 
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |  52 |        VIEW                                 |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |
    |  53 |         HASH UNIQUE                         |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |
    |  54 |          WINDOW SORT                        |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |
    |  55 |           TABLE ACCESS BY INDEX ROWID       | PR_EMP_SETUP_REG           |      1 |      1 |      0 |00:00:00.01 |     13
    |  56 |            NESTED LOOPS                     |                            |      1 |      1 |    123 |00:00:00.01 |     139 |      0 |
    |* 57 |             TABLE ACCESS FULL               | PR_CALENDAR                |      1 |      1 |    122 |00:00:00.01 |      15 |      0 |
    |* 58 |             INDEX RANGE SCAN                | PK_PR_EMP_SETUP_REG        |    122 |      1 |      0 |00:00:00.01 |     124
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
       5 - access("CAL_MONTH"<=)
           filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
       7 - access("PAY_CALENDAR_ID"='00000062')
       9 - access("PAY_CALENDAR_ID"='00000062')
      11 - access("PAY_CALENDAR_ID"='00000062')
      21 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
      24 - access("PC"."PAY_CALENDAR_ID"="RD"."PAY_CALENDAR_ID")
      25 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
      26 - filter("RD"."EMPLOYEE_ID"='HQPRM003')
      28 - filter( IS NULL)
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
      30 - access("HE"."EMPLOYEE_ID"="PRD"."EMPLOYEE_ID")
      32 - access("PM"."ITEM_ID"="PRD"."ITEM_ID")
      33 - access("PMD"."PR_ID"="PM"."PR_ID")
      34 - access("PRD"."DATA_NAME"="PMD"."NAME")
      35 - access("PRS"."SECTION_ID"="PRD"."REF_SECTION")
      37 - access("PR"."REPORT_ID"=8)
      38 - filter("PR"."REPORT_ID"="PRS"."REPORT_ID")
      42 - filter("PRD"."PAY_CALENDAR_ID" NOT LIKE 'RET_%')
      43 - access("PRD"."PAY_CALENDAR_ID"="PC"."PAY_CALENDAR_ID")
      45 - access("IPRD"."EMPLOYEE_ID"=:B1 AND "IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
           filter("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
      46 - filter("IPC"."REF_CALENDAR_ID"=:B1)
      47 - access("IPRD"."PAY_CALENDAR_ID"="IPC"."PAY_CALENDAR_ID")
      49 - access("A"."CAL_MONTH"="B"."CAL_MONTH")
      57 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
      58 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND "PESR".
           filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
    
    
    106 rows selected.
    
    Elapsed: 00:00:00.81..
    just curious ..how did you find that plan without index is better....looks from gather_plan hint..SQL without index is better
  • 91. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    just curious ..how did you find that plan without index is better....looks from gather_plan hint..SQL without index is better
    Just the explain plan when indexes were used was showing increased cost. If you look at the E-Rows and A-Rows columns for plan without indexes you can see that the estimated number of rows (E-Rows) is quite close to actual number of rows (A-Rows) what means that the optimizer estimate was quite close to the actual number of retrieved rows in every step of the execution plan. Due to "good" estimate also the plan was good as CBO used the most appropriate access path to retrieve data. In the majority of cases a suboptimal plan is produced by CBO when there is a big difference between the estimated and actual number of rows. Then CBO due to mis-estimate picks up a plan which has the lowest cost but is unfortunately not the optimal one. Consider the following: the estimate is to retrieve 1 row, but actually it retrieves 10000 rows; this will result in a plan using optimal access path for 1 row instead of 10000 rows. Most likely a different access path is optimal for retrieving 10000 rows.

    Kind regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680

    Edited by: Joze Senegacnik on 23.11.2009 17:47
  • 92. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Thats brilliant....thank you...So the Estimate and Actual rows should be aproximately equal to get a optimal plan to retrieve data...
  • 93. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    An optimal execution plan uses the most appropriate access path to retrieve rows from different rows sources (either table, result of previous join operation,...). When the estimation is close to the actual number of rows CBO can pick the right access path. When they differ for orders of magnitude one can easily spot that and perform corrective actions. Wolfgang Breitling, one of Oakies, named this kind of tuning years ago as "Tuning by cardinality feedback" - see http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf. I suggest you to read his excellent papers although they require quite a lot of time and effort to go trough, but you will get a very good knowledge about CBO. By the way, he was the first one to publish a paper about interpreting CBO trace file.

    Regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680
  • 94. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Thanks again...Joze

    Just started reading..There is so much Science and maths inside Oracle

    Regards
    Maran

    Edited by: Maran Viswarayar on Nov 24, 2009 2:29 PM
  • 95. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Just to close this thread

    Execution time the query after moving to production is *1 Minute*

    We have moved this code to production and it runs in *1* minute which earlier ran for *10* minutes....

    Amazing...its great...

    Oracle is Great but people who knows Oracle are even better... ;-)


    Forgot to add..

    CPU usage has come down from 50% to 10%(average) during the execution..


    Final Thanks to Joze

    Edited by: Maran Viswarayar on Dec 15, 2009 11:53 AM
  • 96. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Maran,

    Thanks for the update and I'm really glad that the problem is solved. It's good that you have posted the final result so everybody reading this thread will get the whole picture.

    I wish you Merry Christmas and a happy new year!

    Kind regards, Joze

    Co-author of the forthcoming book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680

    Oracle related blog: http://joze-senegacnik.blogspot.com/
    Blog about flying: http://jsenegacnik.blogspot.com/
    Blog about Building Ovens, Baking and Cooking: http://senegacnik.blogspot.com
  • 97. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Thanks Joze and wish you the same...
  • 98. Re: Is it possible to tune this query.....
    695836 Journeyer
    Currently Being Moderated
    So, finally it is possible to tune this query ;)
1 3 4 5 6 7 Previous Next

Legend

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