1 2 3 4 5 Previous Next 98 Replies Latest reply: Dec 15, 2009 1:01 AM by 695836 Go to original post RSS
      • 30. Re: Is it possible to tune this query.....
        695836
        Hi,
        You can go for higher level tuning viz materialized view if the query cannot be tuned further.
        • 31. Re: Is it possible to tune this query.....
          Maran Viswarayar
          let us see.....

          Edited by: Maran Viswarayar on Nov 17, 2009 2:36 PM
          • 32. Re: Is it possible to tune this query.....
            Joze Senegacnik
            Ok, the plan you posted is the plan of executing dbms_xplan and not right one. Could you run the whole statement (that one you have posted) and gather plan statistics and then find its sql_id in v$sql and post the output of dbms_xplan.display_cursor.
            I'm afraid the problem is that the optimizer has to execute the function first to its completion and then perform the join with other tables and evaluate other predicates. An execution plan for whole statement should show what's actually going on.

            Unfortunately I'll be at my customer site today and I will not be able to respond quickly, but I'll try my best.
            • 33. Re: Is it possible to tune this query.....
              Maran Viswarayar
              I am getting the same stuff again..but attached is the plan for the entire sql and the function is called insde the SQL

              --------------------------------------------------------------------------------------------------------------------
              | Id  | Operation                                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
              --------------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                             |                     |     2 |   334 |    59  (16)| 00:00:01 |
              |   1 |  SORT ORDER BY                               |                     |     2 |   334 |    59  (16)| 00:00:01 |
              |   2 |   VIEW                                       |                     |     2 |   334 |    58  (14)| 00:00:01 |
              |   3 |    UNION-ALL                                 |                     |       |       |            |          |
              |   4 |     HASH UNIQUE                              |                     |     1 |   136 |    41  (13)| 00:00:01 |
              |   5 |      COUNT                                   |                     |       |       |            |          |
              |   6 |       NESTED LOOPS OUTER                     |                     |     4 |   544 |    40  (10)| 00:00:01 |
              |   7 |        VIEW                                  |                     |     1 |    17 |     8  (13)| 00:00:01 |
              |   8 |         HASH UNIQUE                          |                     |     1 |    15 |     8  (13)| 00:00:01 |
              |   9 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |     1 |    15 |     3   (0)| 00:00:01 |
              |* 10 |           INDEX RANGE SCAN                   | PR_CALENDAR_IDX4    |     1 |       |     2   (0)| 00:00:01 |
              |  11 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |     1 |    18 |     2   (0)| 00:00:01 |
              |* 12 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  13 |             TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR         |     1 |    19 |     2   (0)| 00:00:01 |
              |* 14 |              INDEX UNIQUE SCAN               | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  15 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |     1 |    18 |     2   (0)| 00:00:01 |
              |* 16 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  17 |        VIEW                                  |                     |     4 |   476 |    32  (10)| 00:00:01 |
              |* 18 |         VIEW                                 |                     |     4 |   496 |    32  (10)| 00:00:01 |
              |  19 |          SORT UNIQUE                         |                     |     4 |    80 |    32  (10)| 00:00:01 |
              |  20 |           WINDOW SORT                        |                     |     4 |    80 |    32  (10)| 00:00:01 |
              |* 21 |            HASH JOIN                         |                     |     4 |    80 |    30   (4)| 00:00:01 |
              |* 22 |             TABLE ACCESS FULL                | PR_CALENDAR         |    21 |   378 |     5   (0)| 00:00:01 |
              |* 23 |             COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL     |       |       |            |          |
              |  24 |     HASH UNIQUE                              |                     |     1 |    62 |    18  (23)| 00:00:01 |
              |* 25 |      HASH JOIN OUTER                         |                     |     1 |    62 |    13  (24)| 00:00:01 |
              |  26 |       TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR         |     1 |    15 |     3   (0)| 00:00:01 |
              |* 27 |        INDEX RANGE SCAN                      | PR_CALENDAR_IDX4    |     1 |       |     2   (0)| 00:00:01 |
              |  28 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |     1 |    18 |     2   (0)| 00:00:01 |
              |* 29 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  30 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |     1 |    19 |     2   (0)| 00:00:01 |
              |* 31 |           INDEX UNIQUE SCAN                  | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  32 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |     1 |    18 |     2   (0)| 00:00:01 |
              |* 33 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |     1 |       |     1   (0)| 00:00:01 |
              |  34 |       VIEW                                   |                     |     1 |    47 |     9  (23)| 00:00:01 |
              |  35 |        HASH UNIQUE                           |                     |     1 |    69 |     9  (23)| 00:00:01 |
              |  36 |         WINDOW SORT                          |                     |     1 |    69 |     9  (23)| 00:00:01 |
              |  37 |          TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG    |     1 |    33 |     2   (0)| 00:00:01 |
              |  38 |           NESTED LOOPS                       |                     |     1 |    69 |     7   (0)| 00:00:01 |
              |* 39 |            TABLE ACCESS FULL                 | PR_CALENDAR         |     1 |    36 |     5   (0)| 00:00:01 |
              |* 40 |            INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG |     1 |       |     1   (0)| 00:00:01 |
              --------------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                10 - access("CAL_MONTH"<= (SELECT /*+ */ "CAL_MONTH" FROM "PR_CALENDAR" "PR_CALENDAR" WHERE
                            "PAY_CALENDAR_ID"='00000062'))
                     filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER( (SELECT /*+ */ "CAL_MONTH" FROM "PR_CALENDAR"
                            "PR_CALENDAR" WHERE "PAY_CALENDAR_ID"='00000062'))- (SELECT /*+ */
                            TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("START_DATE"),'mm'))-1 FROM "PR_CALENDAR" "PR_CALENDAR" WHERE
                            "PAY_CALENDAR_ID"='00000062'))
                12 - access("PAY_CALENDAR_ID"='00000062')
                14 - access("PAY_CALENDAR_ID"='00000062')
                16 - access("PAY_CALENDAR_ID"='00000062')
                18 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
                21 - access("PC"."PAY_CALENDAR_ID"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
                22 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
                23 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')
                25 - access("A"."CAL_MONTH"(+)="CAL_MONTH")
                27 - access("CAL_MONTH"<= (SELECT /*+ */ "CAL_MONTH" FROM "PR_CALENDAR" "PR_CALENDAR" WHERE
                            "PAY_CALENDAR_ID"='00000062'))
                     filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER( (SELECT /*+ */ "CAL_MONTH" FROM "PR_CALENDAR"
                            "PR_CALENDAR" WHERE "PAY_CALENDAR_ID"='00000062'))- (SELECT /*+ */
                            TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("START_DATE"),'mm'))-1 FROM "PR_CALENDAR" "PR_CALENDAR" WHERE
                            "PAY_CALENDAR_ID"='00000062'))
                29 - access("PAY_CALENDAR_ID"='00000062')
                31 - access("PAY_CALENDAR_ID"='00000062')
                33 - access("PAY_CALENDAR_ID"='00000062')
               39 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SUP' AND
                           "PC"."IS_LAST_MONTH"='Y')
               40 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND
                           "PESR"."NAME"='CPF_ADWAGE_LIMIT' AND "PESR"."START_DATE"<="PC"."END_DATE")
                    filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
              • 34. Re: Is it possible to tune this query.....
                Charles Hooper
                Maran Viswarayar wrote:
                I am getting the same stuff again..but attached is the plan for the entire sql and the function is called insde the SQL
                Maran,

                See this blog entry:
                http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
                "A couple of odd notes – you’ll see that I set serveroutput off. If serveroutput is on when you call this function, the last statement you will have run will be the (hidden) call to dbms_output that follows your execution of any other statement – so you won’t get the plan and statistics."

                The "COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR" output indicates that you have server output enabled in the session. Turn off autotrace if you use DBMS_XPLAN to display the plan for the last executed SQL statement.

                Charles Hooper
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.
                • 35. Re: Is it possible to tune this query.....
                  Joze Senegacnik
                  I have just few moments of time, thus only short message.

                  I would like to get answers to these questions:

                  1.) Are you able to change the text of the sql statement in your application?

                  2.) What is the execution time of the statement inside your function if you add this condition from the base query: employee_id = 'HQPRM003'

                  The "COLLECTION ITERATOR PICKLER FETCH" returns rows from the function and in this case has nothing to do with dbms_output, but it rather actually returns rows from the function FIND_RECORD_DTL.
                  • 36. Re: Is it possible to tune this query.....
                    Joze Senegacnik
                    Maran,

                    Additionally to my previous questions I would like to ask you if you can do the following:

                    1.) execute your original statement which includes also the function FIND_RECORD_DT - before execution please insert optimizer hint /*+ gather_plan_statistics */ in the text like:
                    SELECT /*+ gather_plan_statistics */ * FROM (SELECT DISTINCT a.employee_id .....
                    2.) execute the following statement immediately after that:
                    select * from table(dbms_xplan.display_cursor(NULL,NULL, 'allstats last'));
                    and paste the output in the answer. Alternatively you can replace first NULL parameter with sql_id of the statement which you find in v$sql

                    3.) Repeat step #1 but this time with an additional hint inside the part where you tech rows from function):
                    .... FROM( SELECT /*+  cardinality( rd 192076) */  DISTINCT pc.pay_calendar_id, CASE WHEN ....
                    You will find the right place in the text of your statement
                    And then repeat step #2.
                    Probably the execution plan will be the same but actually we should see different numbers in "E-Rows" column of execution plan


                    3.) Repeat the same for the query inside the function and paste results.

                    Now I would like to ask you if you can rewrite the statement so that you create in inline or real view for the query which is used inside your function and instead of the function use this inline or real view and join it. This will most likely significantly improve the execution. Another optimization is possible in which you can use "with statement" - the "subquery factoring clause" - for a part of query which is executed as the right join and has alias name b, so you could write something like this at the beginning of your statement:
                    with b as (SELECT DISTINCT start_date, ....cal_month,TO_CHAR(start_date,'Mon-yyyy')calendar
                      ...)))
                    select .... /* here comes the text of the original statement */
                    And don't forget to comment the whole text referred as b which will be now executed only once but used twice, so the text would be : "... RIGHT JOIN b ON a.cal_month = b.cal_month ...."

                    Please repeat the steps #1 and #2 also for the query where the function is replaced by view

                    Looking forward to get your responses.

                    Edited by: Joze Senegacnik on 18.11.2009 6:24
                    • 37. Re: Is it possible to tune this query.....
                      Maran Viswarayar
                      "A couple of odd notes – you’ll see that I set serveroutput off.
                      Oh ok...will do it....
                      • 38. Re: Is it possible to tune this query.....
                        Maran Viswarayar
                        First of all thanks for you patience...
                        And here are the results for first three
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        Plan hash value: 4046579679
                        
                        ----------------------------------------------------------------------------------------------------
                        | Id  | Operation                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem
                        ----------------------------------------------------------------------------------------------------
                        |   1 |  SORT ORDER BY                               |                     |      1 |      2 |      2 |00:00:00.10 |    6497 |  2048 |  2048 
                        |   2 |   VIEW                                       |                     |      1 |      2 |      2 |00:00:00.10 |    6497 |       |       
                        |   3 |    UNION-ALL                                 |                     |      1 |        |      2 |00:00:00.10 |    6497 |       |       
                        |   4 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.10 |    6345 |       |       
                        |   5 |      COUNT                                   |                     |      1 |        |      1 |00:00:00.10 |    6345 |       |       
                        |   6 |       NESTED LOOPS OUTER                     |                     |      1 |      4 |      1 |00:00:00.10 |    6345 |       |      
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |   7 |        VIEW                                  |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |       
                        |   8 |         HASH UNIQUE                          |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |       
                        |   9 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |      13
                        |* 10 |           INDEX RANGE SCAN                   | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      11 |   
                        |  11 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                        |* 12 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                        |  13 |             TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |     
                        |* 14 |              INDEX UNIQUE SCAN               | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |
                        |  15 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                        |* 16 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                        |  17 |        VIEW                                  |                     |      1 |      4 |      0 |00:00:00.10 |    6332 |       |       
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 18 |         VIEW                                 |                     |      1 |      4 |      0 |00:00:00.10 |    6332 |       |       
                        |  19 |          SORT UNIQUE                         |                     |      1 |      4 |      0 |00:00:00.10 |    6332 |  1024 |  1024 
                        |  20 |           WINDOW SORT                        |                     |      1 |      4 |      0 |00:00:00.10 |    6332 |  1024 |  1024 
                        |* 21 |            HASH JOIN                         |                     |      1 |      4 |      0 |00:00:00.10 |    6332 |   842K|   842K
                        |* 22 |             TABLE ACCESS FULL                | PR_CALENDAR         |      1 |     21 |    333 |00:00:00.01 |      15 |     
                        |* 23 |             COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL     |      1 |        |      0 |00:00:00.10
                        |  24 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.01 |     152 |       |       
                        |* 25 |      HASH JOIN OUTER                         |                     |      1 |      1 |      3 |00:00:00.01 |     152 |   862K|   862K
                        |  26 |       TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |    
                        |* 27 |        INDEX RANGE SCAN                      | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      1
                        |  28 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 29 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                        |  30 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3
                        |* 31 |           INDEX UNIQUE SCAN                  | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |    
                        |  32 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                        |* 33 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                        |  34 |       VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |       
                        |  35 |        HASH UNIQUE                           |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |       
                        |  36 |         WINDOW SORT                          |                     |      1 |      1 |      0 |00:00:00.01 |     139 |  1024 |  1024 
                        |  37 |          TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG    |      1 |      1 |      0 |00:00:00.01 |
                        |  38 |           NESTED LOOPS                       |                     |      1 |      1 |    123 |00:00:00.01 |     139 |       |       
                        |* 39 |            TABLE ACCESS FULL                 | PR_CALENDAR         |      1 |      1 |    122 |00:00:00.01 |      15 |      
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 40 |            INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG |    122 |      1 |      0 |00:00:00.01 |     124 |  
                        ----------------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                          10 - access("CAL_MONTH"<=)
                               filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                          12 - access("PAY_CALENDAR_ID"='00000062')
                          14 - access("PAY_CALENDAR_ID"='00000062')
                          16 - access("PAY_CALENDAR_ID"='00000062')
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                          18 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
                          21 - access("PC"."PAY_CALENDAR_ID"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
                          22 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
                          23 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')
                          25 - access("A"."CAL_MONTH"="CAL_MONTH")
                          27 - access("CAL_MONTH"<=)
                               filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                          29 - access("PAY_CALENDAR_ID"='00000062')
                          31 - access("PAY_CALENDAR_ID"='00000062')
                          33 - access("PAY_CALENDAR_ID"='00000062')
                          39 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                          40 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND "PESR".
                                      "PESR"."START_DATE"<="PC"."END_DATE")
                               filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
                        
                        
                        81 rows selected.
                        
                        SQL> 
                        This is generated with gather plan hint
                        The below is the one with cardinality hine
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        Plan hash value: 4046579679
                        
                        ----------------------------------------------------------------------------------------------------
                        | Id  | Operation                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem
                        ----------------------------------------------------------------------------------------------------
                        |   1 |  SORT ORDER BY                               |                     |      1 |      2 |      2 |00:00:00.10 |    6495 |  2048 |  2048 
                        |   2 |   VIEW                                       |                     |      1 |      2 |      2 |00:00:00.10 |    6495 |       |       
                        |   3 |    UNION-ALL                                 |                     |      1 |        |      2 |00:00:00.10 |    6495 |       |       
                        |   4 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.10 |    6343 |       |       
                        |   5 |      COUNT                                   |                     |      1 |        |      1 |00:00:00.10 |    6343 |       |       
                        |   6 |       NESTED LOOPS OUTER                     |                     |      1 |     15 |      1 |00:00:00.10 |    6343 |       |      
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |   7 |        VIEW                                  |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |       
                        |   8 |         HASH UNIQUE                          |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |       
                        |   9 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |      13
                        |* 10 |           INDEX RANGE SCAN                   | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      11 |   
                        |  11 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                        |* 12 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                        |  13 |             TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |     
                        |* 14 |              INDEX UNIQUE SCAN               | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |
                        |  15 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                        |* 16 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                        |  17 |        VIEW                                  |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |       |       
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 18 |         VIEW                                 |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |       |       
                        |  19 |          SORT UNIQUE                         |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |  1024 |  1024 
                        |  20 |           WINDOW SORT                        |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |  1024 |  1024 
                        |* 21 |            HASH JOIN                         |                     |      1 |   9604 |      0 |00:00:00.10 |    6330 |   842K|   842K
                        |* 22 |             TABLE ACCESS FULL                | PR_CALENDAR         |      1 |     21 |    333 |00:00:00.01 |      15 |     
                        |* 23 |             COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL     |      1 |        |      0 |00:00:00.10
                        |  24 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.01 |     152 |       |       
                        |* 25 |      HASH JOIN OUTER                         |                     |      1 |      1 |      3 |00:00:00.01 |     152 |   862K|   862K
                        |  26 |       TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |    
                        |* 27 |        INDEX RANGE SCAN                      | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      1
                        |  28 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 29 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                        |  30 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3
                        |* 31 |           INDEX UNIQUE SCAN                  | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |    
                        |  32 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                        |* 33 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                        |  34 |       VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |       
                        |  35 |        HASH UNIQUE                           |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |       
                        |  36 |         WINDOW SORT                          |                     |      1 |      1 |      0 |00:00:00.01 |     139 |  1024 |  1024 
                        |  37 |          TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG    |      1 |      1 |      0 |00:00:00.01 |
                        |  38 |           NESTED LOOPS                       |                     |      1 |      1 |    123 |00:00:00.01 |     139 |       |       
                        |* 39 |            TABLE ACCESS FULL                 | PR_CALENDAR         |      1 |      1 |    122 |00:00:00.01 |      15 |      
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                        |* 40 |            INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG |    122 |      1 |      0 |00:00:00.01 |     124 |  
                        ----------------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                          10 - access("CAL_MONTH"<=)
                               filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                          12 - access("PAY_CALENDAR_ID"='00000062')
                          14 - access("PAY_CALENDAR_ID"='00000062')
                          16 - access("PAY_CALENDAR_ID"='00000062')
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                          18 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
                          21 - access("PC"."PAY_CALENDAR_ID"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
                          22 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
                          23 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')
                          25 - access("A"."CAL_MONTH"="CAL_MONTH")
                          27 - access("CAL_MONTH"<=)
                               filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                          29 - access("PAY_CALENDAR_ID"='00000062')
                          31 - access("PAY_CALENDAR_ID"='00000062')
                          33 - access("PAY_CALENDAR_ID"='00000062')
                          39 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
                        
                        PLAN_TABLE_OUTPUT
                        ----------------------------------------------------------------------------------------------------
                          40 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND "PESR".
                                      "PESR"."START_DATE"<="PC"."END_DATE")
                               filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
                        There is a significant difference in the E-rows section

                        let me post the other things one by one
                        • 39. Re: Is it possible to tune this query.....
                          Maran Viswarayar
                          Great learning experience......
                          Thanks joze,Justin,satish and Charles for your contribution

                          Edited by: Maran Viswarayar on Nov 18, 2009 11:50 AM
                          • 40. Re: Is it possible to tune this query.....
                            Joze Senegacnik
                            Sorry, but I havent' seen the other results. I would like to know if you have tested the suggestion to rewrite the statement and get rid of function and what were the results?
                            The main problem is that the most time consuming statement is executed within the function and all predicates from the main query which are available (join condition, filtering condition) are used to filter out the excess rows only after its completion and are not pushed inside the function. This means that a lot of rows is produced totally useless and are filtered out only later on instead at the first possible time.

                            Using a function in order to process main query disables CBO to to anything clever with the available predicates. If an inline view is used those predicates are pushed inside the view unless one would use some optimizer hints to prevent this operation.

                            It was a pleasure to work on the problem. However, I have found some new facts which I will have to investigate further in near future and will publish them on my blog about Oracle which I just started. So the benefit is mutual :)

                            Kind regards,
                            Joze
                            • 41. Re: Is it possible to tune this query.....
                              Maran Viswarayar
                              i just browsed your name in the google...brilliant stuff...its a privilege for me to work with you ...
                              Moving on
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                              Plan hash value: 4046579679
                              
                              ----------------------------------------------------------------------------------------------------
                              | Id  | Operation                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem
                              ----------------------------------------------------------------------------------------------------
                              |   1 |  SORT ORDER BY                               |                     |      1 |      2 |      2 |00:00:00.10 |    6495 |  2048 |  2048
                              |   2 |   VIEW                                       |                     |      1 |      2 |      2 |00:00:00.10 |    6495 |       |      
                              |   3 |    UNION-ALL                                 |                     |      1 |        |      2 |00:00:00.10 |    6495 |       |      
                              |   4 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.10 |    6343 |       |      
                              |   5 |      COUNT                                   |                     |      1 |        |      1 |00:00:00.10 |    6343 |       |      
                              |   6 |       NESTED LOOPS OUTER                     |                     |      1 |     15 |      1 |00:00:00.10 |    6343 |       |      
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                              |   7 |        VIEW                                  |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |      
                              |   8 |         HASH UNIQUE                          |                     |      1 |      1 |      1 |00:00:00.01 |      13 |       |      
                              |   9 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |      13
                              |* 10 |           INDEX RANGE SCAN                   | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      11 |   
                              |  11 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                              |* 12 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                              |  13 |             TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |     
                              |* 14 |              INDEX UNIQUE SCAN               | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |
                              |  15 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |      
                              |* 16 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |  
                              |  17 |        VIEW                                  |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |       |      
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                              |* 18 |         VIEW                                 |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |       |      
                              |  19 |          SORT UNIQUE                         |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |  1024 |  1024
                              |  20 |           WINDOW SORT                        |                     |      1 |     15 |      0 |00:00:00.10 |    6330 |  1024 |  1024
                              |* 21 |            HASH JOIN                         |                     |      1 |   9604 |      0 |00:00:00.10 |    6330 |   842K|   842
                              |* 22 |             TABLE ACCESS FULL                | PR_CALENDAR         |      1 |     21 |    333 |00:00:00.01 |      15 |     
                              |* 23 |             COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL     |      1 |        |      0 |00:00:00.10
                              |  24 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.01 |     152 |       |      
                              |* 25 |      HASH JOIN OUTER                         |                     |      1 |      1 |      3 |00:00:00.01 |     152 |   862K|   862
                              |  26 |       TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |    
                              |* 27 |        INDEX RANGE SCAN                      | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      1
                              |  28 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                              |* 29 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                              |  30 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3
                              |* 31 |           INDEX UNIQUE SCAN                  | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |    
                              |  32 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 
                              |* 33 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |     
                              |  34 |       VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |      
                              |  35 |        HASH UNIQUE                           |                     |      1 |      1 |      0 |00:00:00.01 |     139 |       |      
                              |  36 |         WINDOW SORT                          |                     |      1 |      1 |      0 |00:00:00.01 |     139 |  1024 |  1024
                              |  37 |          TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG    |      1 |      1 |      0 |00:00:00.01 |
                              |  38 |           NESTED LOOPS                       |                     |      1 |      1 |    123 |00:00:00.01 |     139 |       |      
                              |* 39 |            TABLE ACCESS FULL                 | PR_CALENDAR         |      1 |      1 |    122 |00:00:00.01 |      15 |      
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                              |* 40 |            INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG |    122 |      1 |      0 |00:00:00.01 |     124 |  
                              ----------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                10 - access("CAL_MONTH"<=)
                                     filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                                12 - access("PAY_CALENDAR_ID"='00000062')
                                14 - access("PAY_CALENDAR_ID"='00000062')
                                16 - access("PAY_CALENDAR_ID"='00000062')
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                                18 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
                                21 - access("PC"."PAY_CALENDAR_ID"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
                                22 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
                                23 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')
                                25 - access("A"."CAL_MONTH"="CAL_MONTH")
                                27 - access("CAL_MONTH"<=)
                                     filter(TO_NUMBER("CAL_MONTH")>=TO_NUMBER()-)
                                29 - access("PAY_CALENDAR_ID"='00000062')
                                31 - access("PAY_CALENDAR_ID"='00000062')
                                33 - access("PAY_CALENDAR_ID"='00000062')
                                39 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------
                                40 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND "PESR".
                                            "PESR"."START_DATE"<="PC"."END_DATE")
                                     filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
                              
                              
                              81 rows selected.
                              This is after rewriting the query using the With command...
                              • 42. Re: Is it possible to tune this query.....
                                Maran Viswarayar
                                This is the latest plan after substituing with WITH command....
                                PLAN_TABLE_OUTPUT
                                ----------------------------------------------------------------------------------------------------
                                
                                ----------------------------------------------------------------------------------------------------
                                | Id  | Operation                                     | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads 
                                ----------------------------------------------------------------------------------------------------
                                |   1 |  TEMP TABLE TRANSFORMATION                    |                            |      1 |        |      2 |00:00:00.10 |    6498 |      1
                                |   2 |   LOAD AS SELECT                              |                            |      1 |        |      1 |00:00:00.01 |      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 |
                                |*  5 |      INDEX RANGE SCAN                         | PR_CALENDAR_IDX4           |      1 |      1 |      3 |00:00:00.01 |      11
                                |   6 |       TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 
                                |*  7 |        INDEX UNIQUE SCAN                      | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       
                                
                                PLAN_TABLE_OUTPUT
                                ----------------------------------------------------------------------------------------------------
                                |   8 |        TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01
                                |*  9 |         INDEX UNIQUE SCAN                     | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |    
                                |  10 |       TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 
                                |* 11 |        INDEX UNIQUE SCAN                      | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       
                                |  12 |   SORT ORDER BY                               |                            |      1 |      2 |      2 |00:00:00.10 |    6478 |      1
                                |  13 |    VIEW                                       |                            |      1 |      2 |      2 |00:00:00.10 |    6478 |      1
                                |  14 |     UNION-ALL                                 |                            |      1 |        |      2 |00:00:00.10 |    6478 |      1
                                |  15 |      HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.10 |    6336 |      1
                                |  16 |       COUNT                                   |                            |      1 |        |      1 |00:00:00.10 |    6336 |      1
                                |  17 |        NESTED LOOPS OUTER                     |                            |      1 |     15 |      1 |00:00:00.10 |    6336 |      1
                                |  18 |         VIEW                                  |                            |      1 |      1 |      1 |00:00:00.01 |       6 |      1
                                
                                PLAN_TABLE_OUTPUT
                                ----------------------------------------------------------------------------------------------------
                                |  19 |          TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6604_20A8D2E |      1 |      1 |      1 |00:00:00.01 |    
                                |  20 |         VIEW                                  |                            |      1 |     15 |      0 |00:00:00.10 |    6330 |      0
                                |* 21 |          VIEW                                 |                            |      1 |     15 |      0 |00:00:00.10 |    6330 |      0
                                |  22 |           SORT UNIQUE                         |                            |      1 |     15 |      0 |00:00:00.10 |    6330 |      0
                                |  23 |            WINDOW SORT                        |                            |      1 |     15 |      0 |00:00:00.10 |    6330 |      0
                                |* 24 |             HASH JOIN                         |                            |      1 |   9604 |      0 |00:00:00.10 |    6330 |      0
                                |* 25 |              TABLE ACCESS FULL                | PR_CALENDAR                |      1 |     21 |    333 |00:00:00.01 |      15 |  
                                |* 26 |              COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL            |      1 |        |      0 |00:00:00.
                                |  27 |      HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0
                                |* 28 |       HASH JOIN OUTER                         |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0
                                |  29 |        VIEW                                   |                            |      1 |      1 |      1 |00:00:00.01 |       3 |      0
                                
                                PLAN_TABLE_OUTPUT
                                ----------------------------------------------------------------------------------------------------
                                |  30 |         TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6604_20A8D2E |      1 |      1 |      1 |00:00:00.01 |     
                                |  31 |        VIEW                                   |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0
                                |  32 |         HASH UNIQUE                           |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0
                                |  33 |          WINDOW SORT                          |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0
                                |  34 |           TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG           |      1 |      1 |      0 |00:00:00.01
                                |  35 |            NESTED LOOPS                       |                            |      1 |      1 |    123 |00:00:00.01 |     139 |      0
                                |* 36 |             TABLE ACCESS FULL                 | PR_CALENDAR                |      1 |      1 |    122 |00:00:00.01 |      15 |   
                                |* 37 |             INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG        |    122 |      1 |      0 |00:00:00.01 |     1
                                ----------------------------------------------------------------------------------------------------
                                
                                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"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
                                  25 - filter(SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET')
                                  26 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='HQPRM003')
                                
                                PLAN_TABLE_OUTPUT
                                ----------------------------------------------------------------------------------------------------
                                  28 - access("A"."CAL_MONTH"="B"."CAL_MONTH")
                                  36 - filter((SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'RET' AND SUBSTR("PC"."PAY_CALENDAR_ID",0,3)<>'SU
                                  37 - access("PESR"."EMPLOYEE_ID"='HQPRM003' AND "PESR"."START_DATE">="PC"."START_DATE" AND "PESR".
                                       filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
                                
                                
                                71 rows selected.
                                
                                SQL> 
                                Fogot to replace with B in the second (union) section of the query..

                                Edited by: Maran Viswarayar on Nov 18, 2009 2:26 PM
                                • 43. Re: Is it possible to tune this query.....
                                  Cmohan.Nayak-Oracle
                                  Hi,

                                  1. I would recommend make statistics_level=all.
                                  2. Download SQLT.zip from metalink.
                                  3. Run the SQLTEXECUTE option in that and you will pretty much know exactly whats causing the problem.

                                  Chandra
                                  • 44. Re: Is it possible to tune this query.....
                                    Joze Senegacnik
                                    Can you replace the function with the inline view as I described in my previous post and post the results.

                                    Thanks, Joze
                                    1 2 3 4 5 Previous Next