This discussion is archived
1 2 3 7 Previous Next 98 Replies Latest reply: Dec 14, 2009 11:01 PM by 695836 RSS

Is it possible to tune this query.....

Maran Viswarayar Pro
Currently Being Moderated
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..

Removing the query..
Edited by: Maran Viswarayar on Nov 13, 2009 1:51 PM

Edited by: Maran Viswarayar on Nov 16, 2009 2:58 PM
  • 1. Re: Is it possible to tune this query.....
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are your statistics accurate? The optimizer expects that no step is going to return more than 10 rows, that you're dealing with less than 1 kb of data, and that the query will take 1 second. That makes me wager that the optimizer is off in left field because the statistics are out of date.

    Justin
  • 2. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    I just analyzed those relevant tables..
  • 3. Re: Is it possible to tune this query.....
    SatishKandi Guru
    Currently Being Moderated
    Check the wait events for this query. As Justin has pointed out, this query should not take 10 minutes.

    Just noticed that you have some functions also in this query. so a SQL Trace might also help in pinpointing the problem.

    Edited by: Satish Kandi on Nov 13, 2009 11:58 AM
  • 4. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    JFI

    ADMIN.*find_record_dtl

    This is a function which calls a view and i suspect the function is causing the slownes...
  • 5. Re: Is it possible to tune this query.....
    Justin Cave Oracle ACE
    Currently Being Moderated
    So are the cardinality estimates correct (or at least reasonable)? Your query only returns 2 rows and processes less than 1 kb of data?

    Justin
  • 6. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    It returns 138 rows and data is less than 2KB...
    Statistics
    ----------------------------------------------------------
            892  recursive calls
              0  db block gets
        5508316  consistent gets
          32495  physical reads
              0  redo size
           8570  bytes sent via SQL*Net to client
           1796  bytes received via SQL*Net from client
             11  SQL*Net roundtrips to/from client
             33  sorts (memory)
              0  sorts (disk)
            138  rows processed
  • 7. Re: Is it possible to tune this query.....
    Justin Cave Oracle ACE
    Currently Being Moderated
    OK, then I would tend to agree with Satish that the function is almost certainly the source of the problem. Have you traced the function call?

    Justin
  • 8. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Yes i am looking into the function....which calls a view and the view has few sqls...
  • 9. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    This is the plan/statistics for the view called inside by the function
    SQL> 
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1034286170
    
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                    |   278K|   122M|       | 27649   (1)| 00:05:32 |
    |   1 |  HASH UNIQUE               |                    |   278K|   122M|   255M| 27649   (1)| 00:05:32 |
    |*  2 |   HASH JOIN                |                    |   278K|   122M|       |   275   (6)| 00:00:04 |
    |   3 |    TABLE ACCESS FULL       | HRM_EMPLOYEE       |  2389 | 86004 |       |    52   (0)| 00:00:01 |
    |*  4 |    HASH JOIN               |                    |   278K|   113M|       |   220   (6)| 00:00:03 |
    |   5 |     INDEX FAST FULL SCAN   | PK_PR_CALENDAR     |   201 |  2412 |       |     3   (0)| 00:00:01 |
    |*  6 |     HASH JOIN RIGHT ANTI   |                    |   278K|   110M|       |   214   (5)| 00:00:03 |
    |   7 |      VIEW                  | VW_SQ_1            |  2060 | 45320 |       |    26   (4)| 00:00:01 |
    |*  8 |       HASH JOIN            |                    |  2060 | 94760 |       |    26   (4)| 00:00:01 |
    |*  9 |        TABLE ACCESS FULL   | PR_CALENDAR        |    36 |   864 |       |     5   (0)| 00:00:01 |
    |* 10 |        INDEX RANGE SCAN    | PR_RECORD_DTL_IDX1 |  2060 | 45320 |       |    20   (0)| 00:00:01 |
    |* 11 |      HASH JOIN             |                    |   278K|   104M|       |   186   (4)| 00:00:03 |
    |* 12 |       HASH JOIN            |                    |  1831 |   572K|       |    22  (10)| 00:00:01 |
    |  13 |        TABLE ACCESS FULL   | PR_MAPPING         |   400 |  9600 |       |     5   (0)| 00:00:01 |
    |* 14 |        HASH JOIN           |                    |  1831 |   529K|       |    16   (7)| 00:00:01 |
    |  15 |         TABLE ACCESS FULL  | PR_MAPPING_DTL     |  1474 |   109K|       |     5   (0)| 00:00:01 |
    |* 16 |         HASH JOIN          |                    |  1083 |   232K|       |    11  (10)| 00:00:01 |
    |  17 |          NESTED LOOPS      |                    |    80 |  1040 |       |     5   (0)| 00:00:01 |
    |  18 |           TABLE ACCESS FULL| PR_REPORT_SECTION  |    80 |   800 |       |     5   (0)| 00:00:01 |
    |* 19 |           INDEX UNIQUE SCAN| PR_REPORT_PK       |     1 |     3 |       |     0   (0)| 00:00:01 |
    |  20 |          TABLE ACCESS FULL | PR_REPORT_DATA     |  1083 |   218K|       |     5   (0)| 00:00:01 |
    |* 21 |       TABLE ACCESS FULL    | PR_RECORD_DTL      | 29499 |  2102K|       |   161   (2)| 00:00:02 |
    ---------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("HE"."EMPLOYEE_ID"="PRD"."EMPLOYEE_ID")
       4 - access("PRD"."PAY_CALENDAR_ID"="PC"."PAY_CALENDAR_ID")
       6 - access("EMPLOYEE_ID"="PRD"."EMPLOYEE_ID" AND "REF_CALENDAR_ID"="PRD"."PAY_CALENDAR_ID")
       8 - access("IPRD"."PAY_CALENDAR_ID"="IPC"."PAY_CALENDAR_ID")
       9 - filter("IPC"."PAY_CALENDAR_ID" LIKE 'SUP_%')
      10 - access("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
           filter("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
      11 - access("PM"."ITEM_ID"="PRD"."ITEM_ID")
      12 - access("PMD"."PR_ID"="PM"."PR_ID")
      14 - access("PRD"."DATA_NAME"="PMD"."NAME")
      16 - access("PRS"."SECTION_ID"="PRD"."REF_SECTION")
      19 - access("PR"."REPORT_ID"="PRS"."REPORT_ID")
      21 - filter("PRD"."PAY_CALENDAR_ID" NOT LIKE 'RET_%')
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    Statistics
    ----------------------------------------------------------
            169  recursive calls
              0  db block gets
           1771  consistent gets
           3390  physical reads
              0  redo size
       14843863  bytes sent via SQL*Net to client
         141254  bytes received via SQL*Net from client
          12807  SQL*Net roundtrips to/from client
             21  sorts (memory)
              0  sorts (disk)
         192076  rows processed
    Edited by: Maran Viswarayar on Nov 18, 2009 11:30 AM
  • 10. Re: Is it possible to tune this query.....
    SatishKandi Guru
    Currently Being Moderated
    Estimate of 5.32 minutes per call seems to add up in your case (resulting in two rows in final output). So this is the source of problem. Again, just to confirm, you can remove this function call from the original SQL and see if that improves the performance.

    Coming back to the SQL, does this function need to fetch 192076 rows for every execution?
    Almost all tables involved in this query are being scanned fully.

    Can you add some other/additional conditions here?
    Is it missing a predicate for the input parameter (value 8 that is passed)?
  • 11. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    let me try by adding some other conditions...


    Thanks Justin and Sathish...

    Edited by: Maran Viswarayar on Nov 13, 2009 6:38 PM
  • 12. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    This is the plan for the view query called inside the function...I did create new indexes to force index scans and plans are here for the same query with index and without index

    Without Index
    PROD REPORT
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3032672189
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                   |   136K|    26M|       |  6443   (1)| 00:01:18 |
    |   1 |  HASH UNIQUE                    |                   |   136K|    26M|    56M|  6443   (1)| 00:01:18 |
    |*  2 |   HASH JOIN RIGHT ANTI          |                   |   136K|    26M|       |   449   (3)| 00:00:06 |
    |   3 |    VIEW                         | VW_SQ_1           |     1 |    12 |       |     4   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS                |                   |     1 |    33 |       |     4   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN           | PR_RECORD_DTL_IDX |     1 |    19 |       |     3   (0)| 00:00:01 |
    |   6 |      TABLE ACCESS BY INDEX ROWID| PR_CALENDAR       |     1 |    14 |       |     1   (0)| 00:00:01 |
    |*  7 |       INDEX UNIQUE SCAN         | PK_PR_CALENDAR    |     1 |       |       |     0   (0)| 00:00:01 |
    |*  8 |    HASH JOIN                    |                   |   136K|    24M|       |   444   (3)| 00:00:06 |
    |   9 |     TABLE ACCESS FULL           | HRM_EMPLOYEE      |  3468 |   128K|       |    69   (0)| 00:00:01 |
    |* 10 |     HASH JOIN                   |                   |   151K|    21M|       |   373   (2)| 00:00:05 |
    |  11 |      INDEX FAST FULL SCAN       | PK_PR_CALENDAR    |    74 |   814 |       |     2   (0)| 00:00:01 |
    |* 12 |      HASH JOIN                  |                   |   298K|    39M|       |   370   (2)| 00:00:05 |
    |* 13 |       HASH JOIN                 |                   |  1720 |   169K|       |    22  (10)| 00:00:01 |
    |  14 |        TABLE ACCESS FULL        | PR_MAPPING        |   422 |  5064 |       |     5   (0)| 00:00:01 |
    |* 15 |        HASH JOIN                |                   |  1726 |   150K|       |    16   (7)| 00:00:01 |
    |  16 |         TABLE ACCESS FULL       | PR_MAPPING_DTL    |  1466 | 45446 |       |     5   (0)| 00:00:01 |
    |* 17 |         HASH JOIN               |                   |  1178 | 68324 |       |    11  (10)| 00:00:01 |
    |  18 |          NESTED LOOPS           |                   |    77 |  1001 |       |     5   (0)| 00:00:01 |
    |  19 |           TABLE ACCESS FULL     | PR_REPORT_SECTION |    77 |   770 |       |     5   (0)| 00:00:01 |
    |* 20 |           INDEX UNIQUE SCAN     | PR_REPORT_PK      |     1 |     3 |       |     0   (0)| 00:00:01 |
    |  21 |          TABLE ACCESS FULL      | PR_REPORT_DATA    |  1178 | 53010 |       |     5   (0)| 00:00:01 |
    |* 22 |       TABLE ACCESS FULL         | PR_RECORD_DTL     | 74734 |  2846K|       |   347   (1)| 00:00:05 |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EMPLOYEE_ID"="PRD"."EMPLOYEE_ID" AND "REF_CALENDAR_ID"="PRD"."PAY_CALENDAR_ID")
       5 - access("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
           filter("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
       7 - access("IPRD"."PAY_CALENDAR_ID"="IPC"."PAY_CALENDAR_ID")
           filter("IPC"."PAY_CALENDAR_ID" LIKE 'SUP_%')
       8 - access("HE"."EMPLOYEE_ID"="PRD"."EMPLOYEE_ID")
      10 - access("PRD"."PAY_CALENDAR_ID"="PC"."PAY_CALENDAR_ID")
      12 - access("PM"."ITEM_ID"="PRD"."ITEM_ID")
      13 - access("PMD"."PR_ID"="PM"."PR_ID")
      15 - access("PRD"."DATA_NAME"="PMD"."NAME")
      17 - access("PRS"."SECTION_ID"="PRD"."REF_SECTION")
      20 - access("PR"."REPORT_ID"="PRS"."REPORT_ID")
      22 - filter("PRD"."PAY_CALENDAR_ID" NOT LIKE 'RET_%')
    
    
    Statistics
    ----------------------------------------------------------
             88  recursive calls
              0  db block gets
           2026  consistent gets
          11080  physical reads
              0  redo size
       45539910  bytes sent via SQL*Net to client
         435394  bytes received via SQL*Net from client
          39547  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         593178  rows processed
         
     
    After index creation
    After Index creation--------    
         
    xecution Plan
    ---------------------------------------------------------
    lan hash value: 1520293404
    
    ---------------------------------------------------------------------------------------------------
     Id  | Operation                           | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
       0 | SELECT STATEMENT                    |                   |  1366 |   268K|       |   508   (2)| 00:00:07 |
       1 |  HASH UNIQUE                        |                   |  1366 |   268K|   584K|   508   (2)| 00:00:07 |
       2 |   NESTED LOOPS                      |                   |  1366 |   268K|       |   446   (2)| 00:00:06 |
    *  3 |    HASH JOIN                        |                   |  1366 |   264K|       |   446   (2)| 00:00:06 |
       4 |     TABLE ACCESS FULL               | PR_REPORT_SECTION |    77 |   770 |       |     5   (0)| 00:00:01 |
    *  5 |     HASH JOIN                       |                   |  1366 |   250K|       |   440   (2)| 00:00:06 |
       6 |      TABLE ACCESS FULL              | PR_REPORT_DATA    |  1178 | 53010 |       |     5   (0)| 00:00:01 |
    *  7 |      HASH JOIN                      |                   |  1160 |   161K|       |   435   (2)| 00:00:06 |
    *  8 |       HASH JOIN RIGHT ANTI          |                   |   335 | 37520 |       |   429   (2)| 00:00:06 |
       9 |        VIEW                         | VW_SQ_1           |     1 |    12 |       |     4   (0)| 00:00:01 |
      10 |         NESTED LOOPS                |                   |     1 |    33 |       |     4   (0)| 00:00:01 |
    * 11 |          INDEX RANGE SCAN           | PR_RECORD_DTL_IDX |     1 |    19 |       |     3   (0)| 00:00:01 |
      12 |          TABLE ACCESS BY INDEX ROWID| PR_CALENDAR       |     1 |    14 |       |     1   (0)| 00:00:01 |
    * 13 |           INDEX UNIQUE SCAN         | PK_PR_CALENDAR    |     1 |       |       |     0   (0)| 00:00:01 |
    * 14 |        HASH JOIN                    |                   |   335 | 33500 |       |   425   (2)| 00:00:06 |
      15 |         TABLE ACCESS FULL           | PR_MAPPING        |   422 |  5064 |       |     5   (0)| 00:00:01 |
    * 16 |         HASH JOIN                   |                   |   343 | 30184 |       |   419   (1)| 00:00:06 |
    * 17 |          HASH JOIN                  |                   |   379 | 18950 |       |   349   (1)| 00:00:05 |
      18 |           INDEX FAST FULL SCAN      | PK_PR_CALENDAR    |    74 |   814 |       |     2   (0)| 00:00:01 |
    * 19 |           TABLE ACCESS FULL         | PR_RECORD_DTL     | 74734 |  2846K|       |   347   (1)| 00:00:05 |
      20 |          TABLE ACCESS FULL          | HRM_EMPLOYEE      |  3468 |   128K|       |    69   (0)| 00:00:01 |
      21 |       TABLE ACCESS FULL             | PR_MAPPING_DTL    |  1466 | 45446 |       |     5   (0)| 00:00:01 |
    * 22 |    INDEX UNIQUE SCAN                | PR_REPORT_PK      |     1 |     3 |       |     0   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
    
    redicate Information (identified by operation id):
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("PRS"."SECTION_ID"="PRD"."REF_SECTION")
       5 - access("PRD"."DATA_NAME"="PMD"."NAME")
       7 - access("PMD"."PR_ID"="PM"."PR_ID")
       8 - access("EMPLOYEE_ID"="PRD"."EMPLOYEE_ID" AND SUBSTR("VW_SQ_1"."REF_CALENDAR_ID",1,4)=SUBSTR("
                  "PAY_CALENDAR_ID",1,4) AND "REF_CALENDAR_ID"="PRD"."PAY_CALENDAR_ID")
      11 - access("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
           filter("IPRD"."PAY_CALENDAR_ID" LIKE 'SUP_%')
      13 - access("IPRD"."PAY_CALENDAR_ID"="IPC"."PAY_CALENDAR_ID")
           filter("IPC"."PAY_CALENDAR_ID" LIKE 'SUP_%' AND
                  SUBSTR("IPRD"."PAY_CALENDAR_ID",1,4)=SUBSTR("IPC"."PAY_CALENDAR_ID",1,4))
      14 - access("PM"."ITEM_ID"="PRD"."ITEM_ID")
      16 - access("HE"."EMPLOYEE_ID"="PRD"."EMPLOYEE_ID")
      17 - access(SUBSTR("PRD"."PAY_CALENDAR_ID",1,4)=SUBSTR("PC"."PAY_CALENDAR_ID",1,4) AND
                  "PRD"."PAY_CALENDAR_ID"="PC"."PAY_CALENDAR_ID")
      19 - filter("PRD"."PAY_CALENDAR_ID" NOT LIKE 'RET_%')
      22 - access("PR"."REPORT_ID"="PRS"."REPORT_ID")
    
    
    Statistics
    ----------------------------------------------------------
            111  recursive calls
              0  db block gets
           2028  consistent gets
           9540  physical reads
              0  redo size
       33396834  bytes sent via SQL*Net to client
         435394  bytes received via SQL*Net from client
          39547  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         593178  rows processed
    The cost has changed dramatically....i am not sure this can help in bringing down the response time....

    any clues can be helpfuel..and i did go through CBO articles/discussions byt jonathan ,richardfoote...

    Can this help ?

    Performace tuning looks to be the Toughest job for a DBA :)
  • 13. Re: Is it possible to tune this query.....
    Justin Cave Oracle ACE
    Currently Being Moderated
    There are a couple orders of magnitude difference in the optimizer's estimates of how many rows will be returned by the query with and without the indexes. Which estimate is correct? And if the estimate has changes, that implies that the statistics have changed. That leads me to suspect that the original statistics were not accurate-- you indicated that the statistics were not stale, so perhaps it was a histogram that was missing or present or inaccurate? Is the query really returning half a million rows?

    It doesn't appear that there is an appreciable difference in the number of consistent gets between the two executions, so I wouldn't expect a large difference in the execution times. Are you seeing a difference in the execution times?

    Justin
  • 14. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Which estimate is correct?
    I havent changed anything..just created few indexes on the relevant tables/column to force index scan..but that didnt happen
    And if the estimate has changes, that implies that the statistics have changed. That leads me to suspect that the original statistics were >not accurate-- you indicated that the statistics were not stale, so perhaps it was a histogram that was missing or present or inaccurate? Is >the query really returning half a million rows?
    Yes it does..if i remove those indexes i get back the original plan but this is difference if i create the index
    It doesn't appear that there is an appreciable difference in the number of consistent gets between the two executions, so I >wouldn't .expect a large difference in the execution times. Are you seeing a difference in the execution times?
    I cant do it now inproducton..as this report consume more than 70% CPU..have to wait...
1 2 3 7 Previous Next

Legend

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