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
  • 60. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    FROM TABLE (ADMIN.find_record_dtl (8, pc.pay_calendar_id, 'HQPRM003')) rd,
    ORA-00904: "PC"."PAY_CALENDAR_ID": invalid identifier


    if i pass a value to the calendar_id..it runs fast but i am just looking on how to get the calendar_id before passing to the function...

    Edited by: Maran Viswarayar on Nov 19, 2009 11:28 AM
  • 61. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Try something like this one I have tested with substr function:
    SQL> select substr(pad,2,3) from t where id=368;
    
    SUB
    ---
    WET
    
    SQL> select substr((select pad from t where id=368),2,3) from dual;
    
    SUB
    ---
    WET
    I used sql statement to replace function parameter. Of course it is stupid to use this way if you don't need to do that, but for your case this might be a solution. In your case you could write:
    FROM TABLE (ADMIN.find_record_dtl (
                         8, 
                         (select SUBSTR(pc.pay_calendar_id, 0,3) from pr_calendar pc where  = SUBSTR(pc.pay_calendar_id, 0,3) = 'RET')), 
                         'HQPRM003')
    Therefore you put the whole sql which returns the right value for your second input parameter for your function. I hope I have written the expression right as yesterday I got some garbage inside the text when I copied it.

    From the performance perspective I wouldn't use the function to retrieve rows although it uses BULK COLLECT but would rather use SQL from function and incorporate it inside main query and get something like:
    ...FROM( SELECT  DISTINCT pc.pay_calendar_id, 
                     CASE WHEN ...
                     END AS pay_amount,
               rd.employee_id, cal_month, data_name, data_desc, data_seq
               FROM (SELECT distinct report_id, employee_id,  employee_name,
                                     pay_calendar_id, data_name, data_desc,
                                  data_seq, display_name, section_seq,
                                  item_id, base_amt, input_amt,
                                  calculated_amt
                        FROM list_record_dtl) rd, 
                            pr_calendar pc 
                 WHERE pc.pay_calendar_id = rd.pay_calendar_id 
                 AND   SUBSTR(pc.pay_calendar_id, 0,3) = 'RET' 
                 AND   rd.employee_id = 'HQPRM003'
                       and   rd.report_id = 8 ) a 
             RIGHT JOIN ....
    So now you have again two options :-)

    Generally I would prefer rather not to use function due to the following reasons:
    1.) context switching between sql and pl/sql engine - fortunately in your case the function is executed only once (see Starts column from your run-time execution plan)
    2.) allocation of xxx instances of pay_item_t datatype for the result of the query inside function can use a lot of memory. This memory is allocated in PGA and would be seen in v$process_memory under category 'Other'. Of course you can also monitor the PGA memory consumption from v$sesstat. Now you will use all three parameters for function execution and there will be less rows returned as it was before when the function returned 192076 rows.
    3.) And last the most important one - if you use inline view you give the CBO chance to optimize the statement. If you use function the CBO can't push predicates from main query inside the query which is executed within the function and thus this may turn out as a very suboptimal plan what was exactly your case when you started this thread. Obviously was the person who developed the function aware of this problem and tried to solve this by additional parameters which are passed to the function and are used to narrow down the number of rows returned by the function. Unfortunately those two parameters were not used in your case.
    4.) It is always a good practice using only those resources which you really need and not much more!

    Edited by: Joze Senegacnik on 19.11.2009 10:50
  • 62. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    I will comeback in a while.....apologies...
  • 63. Re: Is it possible to tune this query.....
    Yasu Newbie
    Currently Being Moderated
    Also refer these below links

    http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

    http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

    http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/

    -Yasser
  • 64. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    I tried rewriting but with different condition but the subquery returns more than 1 row...but i will comeback again...

    Edited by: Maran Viswarayar on Nov 20, 2009 5:11 PM
  • 65. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    For testing purposes just add
    and rownum < 2
    This should work, the other possibility is to use DISTINCT. However I can't really understand this condition
    ...
    AND substr(pc.pay_calendar_id, 0,3)  'RET' 
    AND...
    
    
    SQL> select * from dual where substr('123456789', 0,3) 'RET';
    select * from dual where substr('123456789', 0,3) 'RET'
                                                      *
    ERROR at line 1:
    ORA-00920: invalid relational operator
    If you use literal value for the function parameter what is the execution time?
  • 66. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    I did using max function it worked but the problem is it should returun more rows.....I'm on the way back home..will reply once I reach home......

    Thanks for guiding me and for showing patience....just to add on this pronlem. Reverse proxy throws error after 5 min and the reprt gets disconnected at webserver.,.. So fixing that;-)
  • 67. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Joze...will comeback to you tomorrow...VPN not working..:)
  • 68. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    No problem, I had also some obligations and was off-line until now.
    I did using max function it worked but the problem is it should return more rows....
    If there should be more values then you can either omit that parameter in the function call (bad - we will be faced again with performance issues) or use the inline view which will return the right number of rows.

    Edited by: Joze Senegacnik on 19.11.2009 17:16
  • 69. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
             
             with b as (SELECT DISTINCT start_date,cal_month,TO_CHAR(start_date,'Mon-yyyy')calendar
                FROM pr_calendar
                WHERE cal_month BETWEEN (SELECT cal_month
                                          FROM pr_calendar
                                         WHERE pay_calendar_id = '00000062')-(SELECT TO_CHAR (start_date, 'mm') - 1
                                          FROM pr_calendar
                                         WHERE pay_calendar_id = '00000062')
                                   AND (SELECT cal_month
                                          FROM pr_calendar
                                         WHERE pay_calendar_id = '00000062'))
    select  /*+ gather_plan_statistics */  * from
    (SELECT DISTINCT a.employee_id  as employee_id,data_name,TO_CHAR(data_seq, '9999999') || ':' || data_desc data_desc,data_seq,b.cal_month,
         CASE WHEN a.pay_amount IS NOT NULL THEN a.pay_amount ELSE 0 END pay_amount,
         b.cal_month || ':' || b.calendar calendar
            FROM(SELECT /*+  cardinality( rd 192076) */ DISTINCT pc.pay_calendar_id,case
               when (item_id ='ORD_WAGES' or item_id ='ADL_WAGES')
                    then(SUM(NVL(calculated_amt, 0)) OVER (PARTITION BY rd.employee_id,cal_month,data_name)) 
               else (SUM(NVL(base_amt,0)) OVER (PARTITION BY rd.employee_id,cal_month,data_name))
               end as pay_amount,rd.employee_id, cal_month, data_name, data_desc, data_seq
               FROM (SELECT distinct report_id, employee_id,employee_name,pay_calendar_id, data_name, data_desc, data_seq, 
                         display_name,section_seq,item_id, base_amt,input_amt,calculated_amt FROM list_record_dtl
                   WHERE report_id = 8 AND 
                   pay_calendar_id LIKE '%' AND 
                   employee_id LIKE '%')  rd, 
                    pr_calendar pc WHERE 
               pc.pay_calendar_id = rd.pay_calendar_id and 
               substr(pc.pay_calendar_id, 0,3) <> 'RET' AND
                rd.employee_id = 'HQPRM003') a RIGHT JOIN   
        b ON a.cal_month = b.cal_month
              and rownum<20 union all 
              SELECT DISTINCT a.employee_id  as employee_id,data_name, TO_CHAR(9999, '9999999') || ':Additional Wage Limit' data_desc, data_seq,  b.cal_month,
               CASE WHEN a.pay_amount IS NOT NULL THEN a.pay_amount ELSE 0 END pay_amount,b.cal_month || ':' || b.calendar calendar
    FROM
         (SELECT DISTINCT pc.pay_calendar_id,SUM(NVL(value,0)) OVER (PARTITION BY pesr.employee_id,cal_month,name) pay_amount, 
            pesr.employee_id, cal_month, pesr.name as data_name, 9999 as data_seq FROM 
                 PR_EMP_SETUP_REG pesr, pr_calendar pc WHERE 
                 pesr.start_date between pc.start_date and pc.end_date AND
                 pesr.employee_id = 'HQPRM003' and
                 substr(pc.pay_calendar_id, 0,3)<>'RET' and
                 substr(pc.pay_calendar_id, 0,3)<>'SUP' and pc.is_last_month ='Y' and
                 pesr.name = 'CPF_ADWAGE_LIMIT') a RIGHT JOIN
             b ON a.cal_month = b.cal_month
    )
    ORDER BY cal_month
    Finally the code has been rewritten by replacing the function a SQL...below..
    FROM (SELECT distinct report_id, employee_id,employee_name,pay_calendar_id, data_name, data_desc, data_seq, 
                         display_name,section_seq,item_id, base_amt,input_amt,calculated_amt FROM list_record_dtl
                   WHERE report_id = 8 AND 
                   pay_calendar_id LIKE '%' AND 
                   employee_id LIKE '%')  rd
    Le t me post the plans with additional indexes to force Index scanning...
  • 70. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Maran,

    In the SQL which replaced function just omit
     .. AND pay_calendar_id LIKE '%' AND employee_id LIKE '%'
    First those two conditions are completely useless. CBO will push predicates from the main query inside the inline view to limit rows. Please post then the run-time execution plan. Don't forget to omit the cardinality hint because it is not necessary any more and would be harmful !!!

    Edited by: Joze Senegacnik on 20.11.2009 9:53
  • 71. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    OK give me a second...

    This is it...finally...
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                   | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   1 |  TEMP TABLE TRANSFORMATION                  |                            |      1 |        |      2 |00:00:00.08 |    7593 |      1 |      1 |       |       |          |
    |   2 |   LOAD AS SELECT                            |                            |      1 |        |      1 |00:00:00.01 |      17 |      0 |      1 |   260K|   260K|  260K (0)|
    |   3 |    HASH UNIQUE                              |                            |      1 |      1 |      1 |00:00:00.01 |      13 |      0 |      0 |       |       |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |      1 |      1 |      3 |00:00:00.01 |      13 |      0 |      0 |       |       |          |
    |*  5 |      INDEX RANGE SCAN                       | PR_CALENDAR_IDX4           |      1 |      1 |      3 |00:00:00.01 |      11 |      0 |      0 |       |       |          |
    |   6 |       TABLE ACCESS BY INDEX ROWID           | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
    |*  7 |        INDEX UNIQUE SCAN                    | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   8 |        TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
    |*  9 |         INDEX UNIQUE SCAN                   | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
    |  10 |       TABLE ACCESS BY INDEX ROWID           | PR_CALENDAR                |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
    |* 11 |        INDEX UNIQUE SCAN                    | PK_PR_CALENDAR             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
    |  12 |   SORT ORDER BY                             |                            |      1 |     16 |      2 |00:00:00.07 |    7573 |      1 |      0 |  2048 |  2048 | 2048  (0)|
    |  13 |    VIEW                                     |                            |      1 |     16 |      2 |00:00:00.07 |    7573 |      1 |      0 |       |       |          |
    |  14 |     UNION-ALL                               |                            |      1 |        |      2 |00:00:00.07 |    7573 |      1 |      0 |       |       |          |
    |  15 |      HASH UNIQUE                            |                            |      1 |     15 |      1 |00:00:00.06 |    7431 |      1 |      0 |       |       |          |
    |  16 |       COUNT                                 |                            |      1 |        |      1 |00:00:00.06 |    7431 |      1 |      0 |       |       |          |
    |  17 |        NESTED LOOPS OUTER                   |                            |      1 |     15 |      1 |00:00:00.06 |    7431 |      1 |      0 |       |       |          |
    |  18 |         VIEW                                |                            |      1 |      1 |      1 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  19 |          TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6613_20A8D2E |      1 |      1 |      1 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
    |  20 |         VIEW                                |                            |      1 |     15 |      0 |00:00:00.06 |    7425 |      0 |      0 |       |       |          |
    |* 21 |          VIEW                               |                            |      1 |     15 |      0 |00:00:00.06 |    7425 |      0 |      0 |       |       |          |
    |  22 |           SORT UNIQUE                       |                            |      1 |     15 |      0 |00:00:00.06 |    7425 |      0 |      0 |  1024 |  1024 |          |
    |  23 |            WINDOW SORT                      |                            |      1 |     15 |      0 |00:00:00.06 |    7425 |      0 |      0 |  1024 |  1024 |          |
    |* 24 |             HASH JOIN                       |                            |      1 |     15 |      0 |00:00:00.06 |    7425 |      0 |      0 |   816K|   816K| 1124K (0)|
    |* 25 |              TABLE ACCESS FULL              | PR_CALENDAR                |      1 |     21 |    333 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    |* 26 |              VIEW                           |                            |      1 |    881 |      0 |00:00:00.06 |    7410 |      0 |      0 |       |       |          |
    |  27 |               SORT UNIQUE                   |                            |      1 |    881 |   5332 |00:00:00.06 |    7410 |      0 |      0 |   793K|   505K|  704K (0)|
    |* 28 |                FILTER                       |                            |      1 |        |   5357 |00:00:00.07 |    7410 |      0 |      0 |       |       |          |
    |* 29 |                 HASH JOIN                   |                            |      1 |    881 |   5357 |00:00:00.06 |    6313 |      0 |      0 |   808K|   808K| 1246K (0)|
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  30 |                  TABLE ACCESS FULL          | HRM_EMPLOYEE               |      1 |   2986 |   3014 |00:00:00.01 |     280 |      0 |      0 |       |       |          |
    |  31 |                  NESTED LOOPS               |                            |      1 |    885 |   5357 |00:00:00.04 |    6033 |      0 |      0 |       |       |          |
    |* 32 |                   HASH JOIN                 |                            |      1 |   2633 |   5357 |00:00:00.01 |     674 |      0 |      0 |   709K|   709K| 1174K (0)|
    |* 33 |                    HASH JOIN                |                            |      1 |    142 |    150 |00:00:00.01 |      61 |      0 |      0 |   713K|   713K| 1168K (0)|
    |* 34 |                     HASH JOIN               |                            |      1 |    142 |    150 |00:00:00.01 |      46 |      0 |      0 |   727K|   727K| 1168K (0)|
    |* 35 |                      HASH JOIN              |                            |      1 |    103 |    109 |00:00:00.01 |      31 |      0 |      0 |   801K|   801K|  324K (0)|
    |  36 |                       NESTED LOOPS          |                            |      1 |      7 |      1 |00:00:00.01 |      16 |      0 |      0 |       |       |          |
    |* 37 |                        INDEX UNIQUE SCAN    | PR_REPORT_PK               |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |      0 |       |       |          |
    |* 38 |                        TABLE ACCESS FULL    | PR_REPORT_SECTION          |      1 |      7 |      1 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    |  39 |                       TABLE ACCESS FULL     | PR_REPORT_DATA             |      1 |   1114 |   1114 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    |  40 |                      TABLE ACCESS FULL      | PR_MAPPING_DTL             |      1 |   1468 |   1468 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  41 |                     TABLE ACCESS FULL       | PR_MAPPING                 |      1 |    420 |    420 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    |* 42 |                    TABLE ACCESS FULL        | PR_RECORD_DTL              |      1 |   8322 |   8321 |00:00:00.01 |     613 |      0 |      0 |       |       |          |
    |* 43 |                   INDEX UNIQUE SCAN         | PK_PR_CALENDAR             |   5357 |      1 |   5357 |00:00:00.01 |    5359 |      0 |      0 |       |       |          |
    |  44 |                 NESTED LOOPS                |                            |    518 |      1 |      0 |00:00:00.01 |    1097 |      0 |      0 |       |       |          |
    |* 45 |                  INDEX RANGE SCAN           | IDX_PR_RECORD_DTL          |    518 |      1 |      0 |00:00:00.01 |    1097 |      0 |      0 |       |       |          |
    |* 46 |                  TABLE ACCESS BY INDEX ROWID| PR_CALENDAR                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
    |* 47 |                   INDEX UNIQUE SCAN         | PK_PR_CALENDAR             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
    |  48 |      HASH UNIQUE                            |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0 |      0 |       |       |          |
    |* 49 |       HASH JOIN OUTER                       |                            |      1 |      1 |      1 |00:00:00.01 |     142 |      0 |      0 |   855K|   855K|  278K (0)|
    |  50 |        VIEW                                 |                            |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
    |  51 |         TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6613_20A8D2E |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  52 |        VIEW                                 |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          |
    |  53 |         HASH UNIQUE                         |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          |
    |  54 |          WINDOW SORT                        |                            |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |  1024 |  1024 |          |
    |  55 |           TABLE ACCESS BY INDEX ROWID       | PR_EMP_SETUP_REG           |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          |
    |  56 |            NESTED LOOPS                     |                            |      1 |      1 |    123 |00:00:00.01 |     139 |      0 |      0 |       |       |          |
    |* 57 |             TABLE ACCESS FULL               | PR_CALENDAR                |      1 |      1 |    122 |00:00:00.01 |      15 |      0 |      0 |       |       |          |
    |* 58 |             INDEX RANGE SCAN                | PK_PR_EMP_SETUP_REG        |    122 |      1 |      0 |00:00:00.01 |     124 |      0 |      0 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    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(SUBSTR("PC"."PAY_CALENDAR_ID",1,3)=SUBSTR("RD"."PAY_CALENDAR_ID",1,3) AND "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
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      29 - 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)<>'SUP' AND "PC"."IS_LAST_MONTH"='Y'))
      58 - 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')
    
    
    106 rows selected.
    
    Elapsed: 00:00:01.56
  • 72. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    And what is the actual time of execution?
  • 73. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    That was very short becuase i ran in UAT...less than a secod..if you the plan with function i can post now...

    Old SQL with function 0.56 sec

    New sql without function and WITH..0.12 secs...

    OLD SQL PLAN with FUNCTION
    Plan hash value: 4046579679
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem 
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT ORDER BY                               |                     |      1 |      2 |      2 |00:00:00.49 |     271K|    644 |    644 |  2048 |  2048 | 2048  (0)
    |   2 |   VIEW                                       |                     |      1 |      2 |      2 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |   3 |    UNION-ALL                                 |                     |      1 |        |      2 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |   4 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |   5 |      COUNT                                   |                     |      1 |        |      1 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |   6 |       NESTED LOOPS OUTER                     |                     |      1 |      1 |      1 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |   7 |        VIEW                                  |                     |      1 |      1 |      1 |00:00:00.01 |      13 |      0 |      0 |       |       |          
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   8 |         HASH UNIQUE                          |                     |      1 |      1 |      1 |00:00:00.01 |      13 |      0 |      0 |       |       |          
    |   9 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |      13 |      0 |      0 |       |       |          
    |* 10 |           INDEX RANGE SCAN                   | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      11 |      0 |      0 |       |       |          
    |  11 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 12 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    |  13 |             TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 14 |              INDEX UNIQUE SCAN               | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    |  15 |            TABLE ACCESS BY INDEX ROWID       | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 16 |             INDEX UNIQUE SCAN                | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    |  17 |        VIEW                                  |                     |      1 |      1 |      0 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |* 18 |         VIEW                                 |                     |      1 |      1 |      0 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  19 |          SORT UNIQUE                         |                     |      1 |      1 |      0 |00:00:00.49 |     271K|    644 |    644 |  1024 |  1024 |          
    |  20 |           WINDOW SORT                        |                     |      1 |      1 |      0 |00:00:00.49 |     271K|    644 |    644 |  1024 |  1024 |          
    |* 21 |            HASH JOIN                         |                     |      1 |      1 |      0 |00:00:00.49 |     271K|    644 |    644 |   816K|   816K| 1139K (0)
    |* 22 |             TABLE ACCESS FULL                | PR_CALENDAR         |      1 |     21 |    333 |00:00:00.01 |      15 |      0 |      0 |       |       |          
    |* 23 |             COLLECTION ITERATOR PICKLER FETCH| FIND_RECORD_DTL     |      1 |        |      0 |00:00:00.49 |     271K|    644 |    644 |       |       |          
    |  24 |     HASH UNIQUE                              |                     |      1 |      1 |      1 |00:00:00.01 |     152 |      0 |      0 |       |       |          
    |* 25 |      HASH JOIN OUTER                         |                     |      1 |      1 |      3 |00:00:00.01 |     152 |      0 |      0 |   862K|   862K|  289K (0)
    |  26 |       TABLE ACCESS BY INDEX ROWID            | PR_CALENDAR         |      1 |      1 |      3 |00:00:00.01 |      13 |      0 |      0 |       |       |          
    |* 27 |        INDEX RANGE SCAN                      | PR_CALENDAR_IDX4    |      1 |      1 |      3 |00:00:00.01 |      11 |      0 |      0 |       |       |          
    |  28 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 29 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  30 |          TABLE ACCESS BY INDEX ROWID         | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 31 |           INDEX UNIQUE SCAN                  | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    |  32 |         TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          
    |* 33 |          INDEX UNIQUE SCAN                   | PK_PR_CALENDAR      |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          
    |  34 |       VIEW                                   |                     |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          
    |  35 |        HASH UNIQUE                           |                     |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          
    |  36 |         WINDOW SORT                          |                     |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |  1024 |  1024 |          
    |  37 |          TABLE ACCESS BY INDEX ROWID         | PR_EMP_SETUP_REG    |      1 |      1 |      0 |00:00:00.01 |     139 |      0 |      0 |       |       |          
    |  38 |           NESTED LOOPS                       |                     |      1 |      1 |    123 |00:00:00.01 |     139 |      0 |      0 |       |       |          
    |* 39 |            TABLE ACCESS FULL                 | PR_CALENDAR         |      1 |      1 |    122 |00:00:00.01 |      15 |      0 |      0 |       |       |          
    |* 40 |            INDEX RANGE SCAN                  | PK_PR_EMP_SETUP_REG |    122 |      1 |      0 |00:00:00.01 |     124 |      0 |      0 |       |       |          
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    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')
      18 - filter("A"."CAL_MONTH"="B"."CAL_MONTH")
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      21 - access(SUBSTR("PC"."PAY_CALENDAR_ID",1,3)=SUBSTR(SYS_OP_ATG(VALUE(KOKBF$),4,5,2),1,3) AND "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)<>'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
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  "PESR"."START_DATE"<="PC"."END_DATE")
           filter("PESR"."NAME"='CPF_ADWAGE_LIMIT')
    
    
    80 rows selected.
    
    Elapsed: 00:00:01.21
    SQL> 
    FYI....i tied more than time..

    SQL witout FUNCTIONis always..0.09-0.11
    OLD SQL always >0.50

    Looks like 5 times faster...Is it something like that:)

    Edited by: Maran Viswarayar on Nov 20, 2009 6:03 PM

    Edited by: Maran Viswarayar on Nov 20, 2009 6:07 PM
  • 74. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    I'm glad to hear that it runs now five times faster. As the function call was removed and now we have just one query there is probably still some place for further optimization. However, the effort required to do further optimization is probably much bigger than the benefit of reduced response time we could achieve.

    In the first post you wrote:
    This query takes 10 minutes to run on Windows 2003,raid10,Oracle 10.2.0.4.Is there any portion i can look into optimize the query..
    Can you just comment what is the final result?

    According to the number of people who read this thread it was obviously a very interesting topic and I assume everybody has learned at least something.

    I assume that we solved the problem. If not, we can go further ... :-)
    Have a nice weekend!
1 3 4 5 6 7 Previous Next

Legend

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