1 2 3 4 Previous Next 117 Replies Latest reply: May 5, 2008 12:19 PM by 181444 Go to original post RSS
      • 15. Re: query tuning
        Aman....
        Do this,
        sql>set autotrace trace explain
        sql><Run your query>
        It will create a huge tree like structure.Copy the entire output and paste here.
        Aman....
        • 16. Re: query tuning
          AlokKumar
          We really can not offer to you, unless we know what your query doing at database level ? well, you need to generate an explain plan and post it on the board with PRE tag.Unless, we have such information, no one really offer something which make sense.

          hare krishna
          Alok
          • 17. Re: query tuning
            155651
            Get the output of the following queries

            --Analyze all tables

            select 'analyze table '||table_name||' compute statistics;' from user_tables where table_name IN('CASE_SIZE', 'ITEM', 'MASTER', 'WAREHOUSE');


            --See what indexes are existing
            select a.table_name||' '||a.index_name||' '||a.column_name||' '||b.index_type||' '||b.uniqueness||' '||a.column_position from user_ind_columns a, user_indexes b where a.index_name=b.index_name and table_name IN('CASE_SIZE', 'ITEM', 'MASTER', 'WAREHOUSE') and index_type IN('NORMAL', 'BITMAP');


            Also get the output of the Explain Plan
            http://www.myoracleguide.com/s/explainplan.htm
            • 18. Re: query tuning
              Aman....
              Alok,
              I guess you should check whom you are replying.I guess your reply was meant for OP.
              Aman....
              • 19. Re: query tuning
                Maran Viswarayar
                Its been Long time since Alok has relied so i think he has confused..

                Alok

                It looks like nowadays your are stuck with DBA responsibility
                • 20. Re: query tuning
                  599921
                  Hi In the above query we have "CASE" . It's a VIEW. It displays
                  Item,Retailer, Country_indicator,Supp_indicatore,case_size columns.
                  I have done :

                  Explain plan for
                  select <stmt>

                  Then how to check the performance.Pls give me idea.
                  • 21. Re: query tuning
                    Aman....
                    Its been Long time since Alok has relied so i think he has confused..
                    No comments :)
                    Aman....
                    • 22. Re: query tuning
                      Aman....
                      What was the message that you got when you run
                      explain plan for
                      select <your statement>?
                      Aman....
                      • 23. Re: query tuning
                        AlokKumar
                        Its been Long time since Alok has relied so i think he has confused..
                        confuse on what ??

                        hare krishna
                        Alok
                        • 24. Re: query tuning
                          599921
                          Hi Aman ,
                          I got the folowing output.
                          Execution Plan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15085 Card=1139512 B
                                    ytes=134462416)

                             1    0   HASH JOIN (Cost=15085 Card=1139512 Bytes=134462416)
                             2    1     HASH JOIN (Cost=580 Card=30772 Bytes=1569372)
                             3    2       TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=130
                                    6 Bytes=18284)

                             4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ITEM' (Co
                                    st=2 Card=105311 Bytes=3369952)

                             5    4         NESTED LOOPS (Cost=86 Card=4296685 Bytes=158977345)
                             6    5           TABLE ACCESS (FULL) OF 'WAREHOUSE' (Cost=4 Card=41 Byt
                                    es=205)

                             7    5           INDEX (RANGE SCAN) OF 'PK_LOCATION' (UNIQUE) (Cost
                                    =1 Card=210622)

                             8    1     VIEW (Cost=14271 Card=48399 Bytes=3242733)
                             9    8       SORT (UNIQUE) (Cost=14271 Card=48399 Bytes=6098274)
                            10    9         HASH JOIN (Cost=992 Card=187614 Bytes=23639364)
                            11   10           HASH JOIN (Cost=186 Card=7449 Bytes=581022)
                            12   11             TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Ca
                                    rd=10451 Bytes=156765)

                            13   11             HASH JOIN (Cost=105 Card=12489 Bytes=786807)
                            14   13               MERGE JOIN (CARTESIAN) (Cost=40 Card=12489 Byt
                                    es=549516)

                            15   14                 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=
                                    13)

                            16   15                   TABLE ACCESS (FULL) OF 'SYSTEM' (C
                                    ost=3 Card=1 Bytes=3)

                            17   15                   BUFFER (SORT) (Cost=3 Card=1 Bytes=10)
                            18   17                     TABLE ACCESS (FULL) OF 'SYSTEM'
                                    (Cost=3 Card=1 Bytes=10)

                            19   14                 BUFFER (SORT) (Cost=37 Card=12489 Bytes=3871
                                    59)

                            20   19                   TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY'
                                     (Cost=34 Card=12489 Bytes=387159)

                            21   13               TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=2
                                    8 Card=24989 Bytes=474791)

                            22   10           VIEW (Cost=536 Card=172449 Bytes=8277552)
                            23   22             UNION-ALL
                            24   23               INDEX (FAST FULL SCAN) OF 'PK_ITEM_SUPPLIER_COUNTR
                                    Y' (UNIQUE) (Cost=11 Card=24978 Bytes=324714)

                            25   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Co
                                    st=34 Card=24978 Bytes=399648)

                            26   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Co
                                    st=34 Card=24978 Bytes=374670)

                            27   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Co
                                    st=34 Card=24978 Bytes=499560)

                            28   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                            29   28                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507
                                    759)

                            30   29                   INDEX (FAST FULL SCAN) OF 'PK_CASE_U
                                    PDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)

                            31   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                            32   31                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507
                                    759)

                            33   32                   INDEX (FAST FULL SCAN) OF 'PK_CASE_U
                                    PDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)

                            34   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                            35   34                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507
                                    759)

                            36   35                   INDEX (FAST FULL SCAN) OF 'PK_CASE_U
                                    PDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
                          Thank you
                          • 25. Re: query tuning
                            Aman....
                            Alok,
                            You are still confused.Read your reply above from here.You replied to me rather than OP.That's what Maran mentioned.
                            Aman....
                            • 26. Re: query tuning
                              599921
                              Hi all ,
                              I have posted the Explain plan. Please give me idea what to do next...

                              Thank you.
                              • 27. Re: query tuning
                                orawarebyte
                                You are still confused.Read your reply above from
                                here.
                                Stop Aman ,as you already confused to alok :P
                                You replied to me rather than OP.That's what
                                Maran mentioned.
                                Now you are going to confuse maran ;)

                                Khurram
                                • 28. Re: query tuning
                                  Charles Hooper
                                  Comparing the query with the explan plan:
                                  SELECT
                                    IM.LOCATION,
                                    IM.ITEM,
                                    CS.CASE_SIZE,
                                    IM.ONDATE,
                                    IM.OFFDATE,
                                    MAS.STATUS
                                  FROM
                                    CASE_SIZE CS,
                                    ITEM IM,
                                    MASTER MAS,
                                    WAREHOUSE WH
                                  WHERE
                                    MAS.PACK_IND = 'N'
                                    AND MAS.ITEM_LEVEL = MAS.TRNS_LEVEL
                                    AND MAS.STATUS = 'A'
                                    AND MAS.FOREIND = 'Y'
                                    AND MAS.ITEM = IM.ITEM
                                    AND IM.LOCATION_TYPE = 'S'
                                    AND MAS.ITEM = CS.ITEM
                                    AND CS.SUPPLY_INDICATOR = 'Y'
                                    AND CS.COUNTRY_INDICATOR = 'Y'
                                    AND IM.LOCATION =WH.WAREHOSE_NO
                                    AND NVL(WH.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&VERSDATE}, 'YYYYMMDD')

                                  Execution Plan
                                  ----------------------------------------------------------
                                     0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15085 Card=1139512 Bytes=134462416)
                                     1    0   HASH JOIN (Cost=15085 Card=1139512 Bytes=134462416)
                                     2    1     HASH JOIN (Cost=580 Card=30772 Bytes=1569372)
                                     3    2       TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=1306 Bytes=18284)
                                     4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ITEM' (Cost=2 Card=105311 Bytes=3369952)
                                     5    4         NESTED LOOPS (Cost=86 Card=4296685 Bytes=158977345)
                                     6    5           TABLE ACCESS (FULL) OF 'WAREHOUSE' (Cost=4 Card=41 Bytes=205)
                                     7    5           INDEX (RANGE SCAN) OF 'PK_LOCATION' (UNIQUE) (Cost=1 Card=210622)
                                     8    1     VIEW (Cost=14271 Card=48399 Bytes=3242733)
                                     9    8       SORT (UNIQUE) (Cost=14271 Card=48399 Bytes=6098274)
                                    10    9         HASH JOIN (Cost=992 Card=187614 Bytes=23639364)
                                    11   10           HASH JOIN (Cost=186 Card=7449 Bytes=581022)
                                    12   11             TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=10451 Bytes=156765)
                                    13   11             HASH JOIN (Cost=105 Card=12489 Bytes=786807)
                                    14   13               MERGE JOIN (CARTESIAN) (Cost=40 Card=12489 Bytes=549516)
                                    15   14                 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=13)
                                    16   15                   TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=3)
                                    17   15                   BUFFER (SORT) (Cost=3 Card=1 Bytes=10)
                                    18   17                     TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=10)
                                    19   14                 BUFFER (SORT) (Cost=37 Card=12489 Bytes=387159)
                                    20   19                   TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=12489 Bytes=387159)
                                    21   13               TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=28 Card=24989 Bytes=474791)
                                    22   10           VIEW (Cost=536 Card=172449 Bytes=8277552)
                                    23   22             UNION-ALL
                                    24   23               INDEX (FAST FULL SCAN) OF 'PK_ITEM_SUPPLIER_COUNTRY' (UNIQUE) (Cost=11 Card=24978 Bytes=324714)
                                    25   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=399648)
                                    26   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=374670)
                                    27   23               TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=499560)
                                    28   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                                    29   28                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
                                    30   29                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
                                    31   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                                    32   31                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
                                    33   32                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
                                    34   23               VIEW (Cost=141 Card=24179 Bytes=1039697)
                                    35   34                 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
                                    36   35                   INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
                                  It appears that the query refers to three tables and one view (CASE_SIZE) - this one view contains multiple other views. The one view contains several MERGE JOIN (CARTESIAN) operations (may be causing repeated access to the temp tablespace if the cardinality estimates are incorrect), and is performing several full table scans of ITEM_SUPPLIER_COUNTRY, and several fast full scans of the PK_CASE_UPDATES index. There are several SORT (UNIQUE) operations in the view, which might mean SELECT DISTINCT was used, and the view may be causing repeated access to the temp tablespace.

                                  Please perform a 10046 trace at level 8 for this query. Look closely at the wait events. If the temp tablespace is being used, you will likely see waits for "direct path read" and "direct path write". Please post the STAT lines for the query from the 10046 trace file, and if possible list the different wait events found in the trace file and the approximate sum of the ela= values for each wait event. For example, you might find something like this:
                                  WAIT #2: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6792072897
                                  WAIT #2: nam='db file sequential read' ela= 445 file#=4 block#=716024 blocks=1 obj#=35205 tim=6792130453
                                  WAIT #2: nam='db file sequential read' ela= 6870 file#=4 block#=716054 blocks=1 obj#=35205 tim=6792138515
                                  WAIT #2: nam='db file sequential read' ela= 1187 file#=4 block#=716069 blocks=1 obj#=35205 tim=6792140128
                                  WAIT #2: nam='db file sequential read' ela= 882 file#=4 block#=716039 blocks=1 obj#=35205 tim=6792141564
                                  WAIT #2: nam='db file sequential read' ela= 827 file#=4 block#=716084 blocks=1 obj#=35205 tim=6792143502
                                  WAIT #2: nam='db file sequential read' ela= 7436 file#=4 block#=716009 blocks=1 obj#=35205 tim=6792152248
                                  WAIT #2: nam='db file sequential read' ela= 2765 file#=4 block#=716099 blocks=1 obj#=35205 tim=6792156190
                                  WAIT #2: nam='db file sequential read' ela= 4721 file#=4 block#=715964 blocks=1 obj#=35205 tim=6792161456
                                  WAIT #2: nam='db file sequential read' ela= 888 file#=4 block#=715994 blocks=1 obj#=35205 tim=6792164250
                                  WAIT #2: nam='db file sequential read' ela= 958 file#=4 block#=715979 blocks=1 obj#=35205 tim=6792166727
                                  FETCH #2:c=93750,e=105718,p=10,cr=1832,cu=0,mis=0,r=1,dep=0,og=1,tim=6792178706
                                  STAT #2 id=1 cnt=1781 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1832 pr=10 pw=0 time=112866 us)'
                                  STAT #2 id=2 cnt=1781 pid=1 pos=1 obj=0 op='FILTER  (cr=1832 pr=10 pw=0 time=95423 us)'
                                  STAT #2 id=3 cnt=1781 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=1748 pr=10 pw=0 time=87875 us)'
                                  STAT #2 id=4 cnt=41 pid=3 pos=1 obj=0 op='HASH JOIN OUTER (cr=1605 pr=0 pw=0 time=56728 us)'
                                  STAT #2 id=5 cnt=41 pid=4 pos=1 obj=35204 op='TABLE ACCESS BY INDEX ROWID EDI830 (cr=3 pr=0 pw=0 time=527 us)'
                                  STAT #2 id=6 cnt=49 pid=5 pos=1 obj=35897 op='INDEX RANGE SCAN IND_EDI830 (cr=2 pr=0 pw=0 time=294 us)'
                                  STAT #2 id=7 cnt=198 pid=4 pos=2 obj=0 op='VIEW  (cr=1602 pr=0 pw=0 time=55308 us)'
                                  STAT #2 id=8 cnt=198 pid=7 pos=1 obj=0 op='HASH GROUP BY (cr=1602 pr=0 pw=0 time=54308 us)'
                                  STAT #2 id=9 cnt=2845 pid=8 pos=1 obj=0 op='HASH JOIN  (cr=1602 pr=0 pw=0 time=43118 us)'
                                  STAT #2 id=10 cnt=211 pid=9 pos=1 obj=34806 op='TABLE ACCESS FULL CUSTOMER_ORDER (cr=219 pr=0 pw=0 time=75265 us)'
                                  STAT #2 id=11 cnt=3118 pid=9 pos=2 obj=34818 op='TABLE ACCESS FULL CUST_ORDER_LINE (cr=1383 pr=0 pw=0 time=99845 us)'
                                  STAT #2 id=12 cnt=1781 pid=3 pos=2 obj=35205 op='TABLE ACCESS BY INDEX ROWID EDI830_LINES (cr=143 pr=10 pw=0 time=47302 us)'
                                  STAT #2 id=13 cnt=1781 pid=12 pos=1 obj=35899 op='INDEX RANGE SCAN PK001 (cr=93 pr=0 pw=0 time=6428 us)'
                                  STAT #2 id=14 cnt=41 pid=2 pos=2 obj=0 op='SORT AGGREGATE (cr=84 pr=0 pw=0 time=2039 us)'
                                  STAT #2 id=15 cnt=41 pid=14 pos=1 obj=0 op='FIRST ROW  (cr=84 pr=0 pw=0 time=1423 us)'
                                  STAT #2 id=16 cnt=41 pid=15 pos=1 obj=35898 op='INDEX RANGE SCAN (MIN/MAX) PRIMEKEY (cr=84 pr=0 pw=0 time=1004 us)'
                                  The above tells the story of why a query is slow. With the STAT lines it is possible to compare the cardinality estimates from your explain plan with the actual number of rows returned by each step. It is also possible to determine the number of consistent reads, and in this case the amount of time required at each step of the explain plan. The FETCH line shows the CPU time consumed and the elapsed time (c=93750,e=105718), and the wait events indicate that Oracle was performing single block reads of object 35205, which is likely an index. The tim= values help to give an idea what Oracle was doing at a specific time while the query was executing - this can be translated into seconds since the query started, unless the tim= values wrap around to 0.

                                  Charles Hooper
                                  IT Manager/Oracle DBA
                                  K&M Machine-Fabricating, Inc.
                                  • 29. Re: query tuning
                                    599921
                                    Charles,
                                    Thanks for giving great explanation. Then what is the next step to resolve this issue.Please let me know.

                                    Thank you
                                    1 2 3 4 Previous Next