This discussion is archived
1 2 3 4 8 Previous Next 117 Replies Latest reply: May 5, 2008 10:19 AM by 181444 Go to original post RSS
  • 15. Re: query tuning
    Aman.... Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    Its been Long time since Alok has relied so i think he has confused..
    No comments :)
    Aman....
  • 22. Re: query tuning
    Aman.... Oracle ACE
    Currently Being Moderated
    What was the message that you got when you run
    explain plan for
    select <your statement>?
    Aman....
  • 23. Re: query tuning
    AlokKumar Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi all ,
    I have posted the Explain plan. Please give me idea what to do next...

    Thank you.
  • 27. Re: query tuning
    316993 Pro
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 8 Previous Next