1 2 Previous Next 18 Replies Latest reply: Apr 27, 2008 8:40 PM by 26741 Go to original post RSS
      • 15. Re: total cost in explain plan
        Charles Hooper
        Thank you, Mark and Chris, for your comments.

        I am not sure how much a DBA can do to tune a query.
        I just want to know if I have used all the tricks out
        there. Before I posted my question I have talked with
        the developer if he can add another filter in the
        where clause to reduce the result set or get rid of
        the order by clause but he accept none. Oh well, I
        think I feel better now.

        Thanks again and have a great weekend!!

        Shirley
        Shirley,

        Is the explain plan that you posted:
        -----------------------------------------------------------------------------------------------
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
        -----------------------------------------------------------------------------------------------
        | 0 | SELECT STATEMENT | | 1 | 20 | 2747 (1)|
        | 1 | SORT AGGREGATE | | 1 | 20 | |
        |* 2 | CONNECT BY WITH FILTERING | | | | |
        |* 3 | INDEX RANGE SCAN | INSPECTION_COMPD_INDX02 | 1 | 26 | 3 (0)|
        | 4 | NESTED LOOPS | | | | |
        | 5 | BUFFER SORT | | | | |
        | 6 | CONNECT BY PUMP | | | | |
        | 7 | TABLE ACCESS BY INDEX ROWID| INSPECTION | 7821 | 152K| 2747 (1)|
        |* 8 | INDEX RANGE SCAN | INSPECTION_COMPD_INDX10 | 6407 | | 21 (0)|
        | 9 | INDEX FULL SCAN | INSPECTION_I_PARENT_ID | 3360K| 64M| 14313 (2)|
        -----------------------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
        2 - access("PARENT_ID"=PRIOR "ID")
        3 - access("C"."ID"=76801811)
        8 - access("PARENT_ID"=PRIOR "ID")
        For this query that you posted in a later reply:
        select * from event where log_id = 1 and date_posted > trunc(sysdate-1) order by transaction_id desc, id desc
        Please post the DBMS_XPLAN for the query which shows the predicted and actual number of rows, and timings returned at each stage of the plan. I believe that Jonathan provided a link that shows how to produce such output. (Use the [ pre ] tags when posting the plan - see the FAQ)

        If the DBMS_XPLAN fails to show sufficient detail, you might find a 10046 trace at level 8 or 12 helpful. It takes a little practice to learn to read the raw output of a 10046 trace, but such a trace provides a significant amount of detail related to why a specific operation in the plan takes a long time.

        If you are using PGA_AGGREGATE_TARGET, you might verify that the value is appropriately large as the memory allocated due to this parameter is not session specific, unlike SORT_AREA_SIZE.

        As an example of a DBMS_XPLAN, and looking through a 10046 trace captured at level 8. First, a simple looking query:
        SELECT 
          T1.C1,
          T1.C2,
          T1.C3
        FROM
          T1,
          VIEW_VIEW_T2 T2
        WHERE
          T1.C2 BETWEEN 250000 AND 270000
          AND T1.C1=T2.C1
        The DBMS_XPLAN showing actuals:
        -----------------------------------------------------------------------------------------------------------------------------------------------------------------
        | Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
        -----------------------------------------------------------------------------------------------------------------------------------------------------------------
        |*  1 |  HASH JOIN                      |              |      1 |  30027 |  20001 |00:00:20.10 |   39275 |  40454 |   1992 |  1882K|  1004K| 2594K (0)|         |
        |   2 |   NESTED LOOPS                  |              |      1 |  20002 |  20001 |00:00:01.50 |    1760 |    956 |      0 |       |       |          |         |
        |   3 |    TABLE ACCESS BY INDEX ROWID  | T1           |      1 |  20002 |  20001 |00:00:01.17 |     880 |    880 |      0 |       |       |          |         |
        |*  4 |     INDEX RANGE SCAN            | T1_C2        |      1 |  20002 |  20001 |00:00:00.16 |      45 |     45 |      0 |       |       |          |         |
        |*  5 |    INDEX UNIQUE SCAN            | SYS_C0013858 |  20001 |      1 |  20001 |00:00:00.17 |     880 |     76 |      0 |       |       |          |         |
        |   6 |   VIEW                          | VIEW_VIEW_T3 |      1 |    450K|    450K|00:00:16.10 |   37515 |  39498 |   1992 |       |       |          |         |
        |   7 |    SORT UNIQUE                  |              |      1 |    450K|    450K|00:00:15.20 |   37515 |  39498 |   1992 |    16M|  1559K|   11M (0)|   16384 |
        |   8 |     UNION-ALL                   |              |      1 |        |    460K|00:00:10.73 |   37513 |  37506 |      0 |       |       |          |         |
        |*  9 |      TABLE ACCESS FULL          | T3           |      1 |  90354 |    100K|00:00:04.10 |   18755 |  18752 |      0 |       |       |          |         |
        |* 10 |      TABLE ACCESS FULL          | T3           |      1 |    360K|    360K|00:00:03.74 |   18755 |  18751 |      0 |       |       |          |         |
        |  11 |      TABLE ACCESS BY INDEX ROWID| T3           |      1 |      1 |      0 |00:00:00.09 |       3 |      3 |      0 |       |       |          |         |
        |* 12 |       INDEX RANGE SCAN          | SYS_C0013859 |      1 |      1 |      0 |00:00:00.09 |       3 |      3 |      0 |       |       |          |         |
        -----------------------------------------------------------------------------------------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - access("C1"="T3"."C1")
           4 - access("T1"."C2">=250000 AND "T1"."C2"<=270000)
           5 - access("T1"."C1"="C1")
           9 - filter(("C1">='K 0000000000250000' AND "C1"<='K 0000000000350000'))
          10 - filter(("C1">='K 0000000000340000' AND "C1"<='K 0000000000750000'))
          12 - access("C1">='K 0000000000740000' AND "C1"<='K 0000000000850000')
        Part of the 10046 trace:
        PARSING IN CURSOR #5 len=153 dep=0 uid=66 oct=3 lid=66 tim=517291736567 hv=3341402728 ad='1c5ddff8' sqlid='fh10z0r3kmhm8'
        SELECT 
          T1.C1, 
          T1.C2, 
          T1.C3 
        FROM 
          custapp.T1, 
          custapp.VIEW_VIEW_T2 T2 
        WHERE 
          T1.C2 BETWEEN 250000 AND 270000 
          AND T1.C1=T2.C1
        END OF STMT
        PARSE #5:c=78000,e=272284,p=15,cr=372,cu=0,mis=1,r=0,dep=0,og=4,tim=517291736563
        EXEC #5:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=517291736912
        WAIT #5: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=517291736958
        WAIT #5: nam='db file sequential read' ela= 47677 file#=1 block#=239354 blocks=1 obj#=72115 tim=517291784853
        WAIT #5: nam='db file sequential read' ela= 6255 file#=1 block#=164201 blocks=1 obj#=72115 tim=517291791225
        WAIT #5: nam='db file sequential read' ela= 5667 file#=1 block#=163987 blocks=1 obj#=72115 tim=517291796934
        ...
        WAIT #5: nam='direct path read' ela= 11729 file number=1 first dba=195796 block cnt=55 obj#=72118 tim=517297207472
        WAIT #5: nam='direct path read' ela= 1458 file number=1 first dba=195851 block cnt=55 obj#=72118 tim=517297209660
        WAIT #5: nam='direct path read' ela= 10960 file number=1 first dba=195906 block cnt=55 obj#=72118 tim=517297220979
        WAIT #5: nam='direct path read' ela= 9253 file number=1 first dba=195961 block cnt=55 obj#=72118 tim=517297230665
        ...
        WAIT #5: nam='direct path read temp' ela= 26490 file number=201 first dba=73225 block cnt=7 obj#=72119 tim=517307201418
        FETCH #5:c=4867231,e=15466913,p=38476,cr=39273,cu=2,mis=0,r=100,dep=0,og=4,tim=517307203915
        WAIT #5: nam='SQL*Net message from client' ela= 26964 driver id=1413697536 #bytes=1 p3=0 obj#=72119 tim=517307230988
        WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72119 tim=517307231124
        FETCH #5:c=0,e=1247,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=517307232341
        WAIT #5: nam='SQL*Net message from client' ela= 551 driver id=1413697536 #bytes=1 p3=0 obj#=72119 tim=517307232942
        WAIT #5: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=72119 tim=517307233007
        FETCH #5:c=0,e=1223,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=517307234210
        WAIT #5: nam='direct path read temp' ela= 20620 file number=201 first dba=71305 block cnt=1 obj#=72119 tim=517311889244
        FETCH #5:c=3759624,e=4290106,p=1876,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=517312031188
        STAT #5 id=1 cnt=20001 pid=0 pos=1 obj=0 op='HASH JOIN  (cr=39273 pr=40454 pw=40454 time=350225 us cost=29274 size=1801620 card=30027)'
        STAT #5 id=2 cnt=20001 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=1760 pr=956 pw=956 time=1408939 us cost=20880 size=960096 card=20002)'
        STAT #5 id=3 cnt=20001 pid=2 pos=1 obj=72111 op='TABLE ACCESS BY INDEX ROWID T1 (cr=880 pr=880 pw=880 time=1115144 us cost=878 size=580058 card=20002)'
        STAT #5 id=4 cnt=20001 pid=3 pos=1 obj=72115 op='INDEX RANGE SCAN T1_C2 (cr=45 pr=45 pw=45 time=110836 us cost=44 size=0 card=20002)'
        STAT #5 id=5 cnt=20001 pid=2 pos=2 obj=72117 op='INDEX UNIQUE SCAN SYS_C0013858 (cr=880 pr=76 pw=76 time=0 us cost=1 size=19 card=1)'
        STAT #5 id=6 cnt=450001 pid=1 pos=2 obj=72124 op='VIEW  VIEW_VIEW_T3 (cr=37513 pr=39498 pw=39498 time=2663537 us cost=8212 size=5404284 card=450357)'
        STAT #5 id=7 cnt=450001 pid=6 pos=1 obj=0 op='SORT UNIQUE (cr=37513 pr=39498 pw=39498 time=1408047 us cost=8212 size=13060353 card=450357)'
        STAT #5 id=8 cnt=460002 pid=7 pos=1 obj=0 op='UNION-ALL  (cr=37513 pr=37506 pw=37506 time=10794358 us)'
        STAT #5 id=9 cnt=100001 pid=8 pos=1 obj=72118 op='TABLE ACCESS FULL T3 (cr=18755 pr=18752 pw=18752 time=744224 us cost=2847 size=2620266 card=90354)'
        STAT #5 id=10 cnt=360001 pid=8 pos=2 obj=72118 op='TABLE ACCESS FULL T3 (cr=18755 pr=18751 pw=18751 time=3229356 us cost=2847 size=10440058 card=360002)'
        STAT #5 id=11 cnt=0 pid=8 pos=3 obj=72118 op='TABLE ACCESS BY INDEX ROWID T3 (cr=3 pr=3 pw=3 time=0 us cost=4 size=29 card=1)'
        STAT #5 id=12 cnt=0 pid=11 pos=1 obj=72119 op='INDEX RANGE SCAN SYS_C0013859 (cr=3 pr=3 pw=3 time=0 us cost=3 size=0 card=1)'
        By analyzing the 10046 trace, one would be able to pinpoint the exact data files, objects, and blocks causing the delay. And, by reviewing the accumulated wait events as well as the PARSE, EXEC, and FETCH lines, determine what approach, if any, may be used to improve performance.

        For example:
        PARSEs       1|CPU S    0.078000|CLOCK S    0.272284|ROWs        0|PHY RD BLKs        15|CON RD BLKs (Mem)       372|CUR RD BLKs (Mem)         0|SHARED POOL MISs      1|
        |EXECs        1|CPU S    0.000000|CLOCK S    0.000056|ROWs        0|PHY RD BLKs         0|CON RD BLKs (Mem)         0|CUR RD BLKs (Mem)         0|SHARED POOL MISs      0|
        |FETCHs     201|CPU S    8.860857|CLOCK S   20.146992|ROWs    20001|PHY RD BLKs     40454|CON RD BLKs (Mem)     39273|CUR RD BLKs (Mem)         2|SHARED POOL MISs      0|

        0.000s SQL*Net message to client
        0.265s SQL*Net message from client
        1.182s db file sequential read
        0.001s reliable message
        7.103s direct path read
        0.000s enq: KO - fast object checkpoint
        1.978s direct path write temp
        0.683s direct path read temp
        By examing the above, a DBA might decide to make a change to the database instance configuration, suggest a hint, suggest that the query be rewritten into a more efficient form, or do nothing if the performance of the query is not business critical.

        Charles Hooper
        IT Manager/Oracle DBA
        K&M Machine-Fabricating, Inc.
        • 16. Re: total cost in explain plan
          26741
          Do you really need ALL the columns from the "event" table ?
          If you can do with only 3 or 4 columns, you could create an index on those columns
          and a select can do an ordered read from the index. However, if the number of
          distinct log_ids is very low Oracle would ignore the index because of your log_id=1
          filter.
          • 17. Re: total cost in explain plan
            Jonathan Lewis
            Shirley,

            Your question prompted me into writing up a few notes on a slightly unorthodox strategy for getting the data you want with a possible reduction in resource usage. It's just one of several ways to do things that the optimizer can't (yet) do automatically - it's got the title: Manual Optimisation

            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            http://www.jlcomp.demon.co.uk
            • 18. Re: total cost in explain plan
              26741
              One test I ran shows the impact of including additional columns in the
              SELECT clause when you do an ORDER BY.

              See See http://hemantoracledba.blogspot.com/2008/04/row-sizes-and-sort-operations.html
              1 2 Previous Next