Explian Plan explanation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Explian Plan explanation

  1. #1
    Join Date
    Jul 2005
    Posts
    31

    Explian Plan explanation

    I am running the same query on development and production. In development the query is running in 6 seconds whereas in production it is taking 12 minutes. I am posting the explain plan for both the environments, please take a look at it and tell me why is this happening?

    Production:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3723 Card=1 Bytes=161)

    1 0 SORT (ORDER BY) (Cost=3723 Card=1 Bytes=161)
    2 1 SORT (GROUP BY) (Cost=3723 Card=1 Bytes=161)
    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'FL3_WP_DET' (Cost=3627 Card=1 Bytes=126)

    4 3 NESTED LOOPS (Cost=3633 Card=1 Bytes=161)
    5 4 NESTED LOOPS (Cost=6 Card=1 Bytes=35)
    6 5 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=29)
    7 6 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=17)

    8 7 TABLE ACCESS (BY INDEX ROWID) OF 'FBIL_RANK' (Cost=2 Card=1 Bytes=9)

    9 8 INDEX (RANGE SCAN) OF 'FBIL_RANK_IDX1' (NON-UNIQUE) (Cost=1 Card=1)

    10 7 BUFFER (SORT) (Cost=2 Card=1 Bytes=8)
    11 10 TABLE ACCESS (BY INDEX ROWID) OF 'DIV_FOB_43' (Cost=2 Card=1 Bytes=8)

    12 11 INDEX (RANGE SCAN) OF 'DIV_FOB_43_UI1' (NON-UNIQUE) (Cost=1 Card=1)

    13 6 BUFFER (SORT) (Cost=4 Card=2 Bytes=24)
    14 13 TABLE ACCESS (FULL) OF 'WORK_TYPE_DESC' (Cost=2 Card=2 Bytes=24)

    15 5 INDEX (RANGE SCAN) OF 'LINE_RATE_43_UI1' (NON-UNIQUE)

    16 4 INDEX (RANGE SCAN) OF 'FL3_WP_DET_IDX1' (NON-UNIQUE) (Cost=29 Card=5956)

    Development:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (ORDER BY)
    2 1 SORT (GROUP BY)
    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_TYPE_DESC'
    4 3 NESTED LOOPS
    5 4 NESTED LOOPS
    6 5 NESTED LOOPS
    7 6 NESTED LOOPS
    8 7 TABLE ACCESS (BY INDEX ROWID) OF 'FL3_WP_DET'

    9 8 INDEX (RANGE SCAN) OF 'FL3_WP_DET_IDX1' (NON-UNIQUE)

    10 7 TABLE ACCESS (BY INDEX ROWID) OF 'FBIL_RANK'

    11 10 INDEX (RANGE SCAN) OF 'FBIL_RANK_IDX1' (NON-UNIQUE)

    12 6 TABLE ACCESS (BY INDEX ROWID) OF 'DIV_FOB_43'
    13 12 INDEX (RANGE SCAN) OF 'DIV_FOB_43_UI1' (NON-UNIQUE)

    14 5 INDEX (RANGE SCAN) OF 'LINE_RATE_43_UI1' (NON-UNIQUE)

    15 4 INDEX (RANGE SCAN) OF 'WORK_TYPE_DESC_UI1' (NON-UNIQUE)

    Your response in highly appreciated,
    DBA01

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you probably have stale stats in production

  3. #3
    Join Date
    Jul 2005
    Posts
    31
    No, as a matter of fact it is the other way around. The stats in production are current whereas they are stale in development.

    DBA01

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well the prod ones are wrong then arent they

  5. #5
    Join Date
    Jul 2005
    Posts
    31
    It does look like that. WHat would you suggest I should do?

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by dba01
    It does look like that. WHat would you suggest I should do?
    In prod I see "COST CARD etc..".
    But I don't see "COST CARD etc" in DEV.

    WHY?

    Tamil

  7. #7
    Join Date
    Jul 2005
    Posts
    31
    I think what is happening is that in development as there are no statistics the optimizer mode being used is rule whereas in production the statistics are current and the optimizer mode being used is cost.
    If I force the Query to use the RULE based optimizer I get the response in less than a minute.
    Why is this happening?

    DBA01

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    There are many reasons why you got different execution plans:
    1 Selectivity
    2 Cardinality
    3 Not generating histograms
    4 The way you anlayze tables and indexes
    etc
    Tamil

  9. #9
    Join Date
    Jul 2005
    Posts
    31
    If this helps. Here is the SQL:

    SELECT flum.vend_name vendorname, MAX (flum.gmm) gmm,
    MAX (flum.fob) fob, flum.dept dept, MAX (flum.TYPE) TYPE,
    MAX (DECODE (flum.reason,
    '0013', '0003',
    '0014', '0003',
    '0098', '0004',
    '0082', '0008',
    flum.reason
    )
    ) reason,
    SUM (flum.units) targetunits, flum.pid pid,
    MAX (flum.item_desc) item_desc,
    DECODE (MAX (DECODE (flum.reason,
    '0013', '0003',
    '0014', '0003',
    '0098', '0004',
    '0082', '0008',
    flum.reason
    )
    ),
    '0001', 1,
    '0012', 2,
    '0002', 3,
    '0003', 4,
    '0004', 5,
    '0005', 6,
    '0006', 7,
    '0008', 8,
    '0010', 9,
    999
    ) AS ordercol
    FROM fl3_wp_det flum,
    div_fob_43 div,
    line_rate_43 line,
    work_type_desc des,
    fbil_rank fbil
    WHERE div.div = flum.div
    AND div.fob = flum.fob
    AND des.TYPE = flum.TYPE
    AND des.work_type = line.work_type
    AND line.line = div.line
    AND flum.div = 20
    AND flum.div = fbil.div_nbr
    AND flum.STORE = fbil.store_nbr
    AND fbil.RANK <> 'F'
    AND flum.STORE = 10
    AND TRUNC (flum.effective_date) = TO_DATE ('01/30/2006', 'MM/DD/YYYY')
    AND flum.TYPE = 'PCS'
    AND flum.reason = '0001'
    AND DECODE (flum.TYPE, 'PCS', flum.reason, ' ') = des.reason
    AND flum.fob IN (81)
    GROUP BY flum.pid, flum.dept, flum.vend_name
    ORDER BY gmm, fob, flum.dept, ordercol, flum.vend_name, flum.pid;

    I appreciate your help.

    DBA01

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    and how do you generate the stats

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width