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

you probably have stale stats in production

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

well the prod ones are wrong then arent they

It does look like that. WHat would you suggest I should do?

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

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

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

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

and how do you generate the stats

Do you have an index on effective_date col?

Change this line
AND TRUNC (flum.effective_date) = TO_DATE ('01/30/2006', 'MM/DD/YYYY')
to

AND flum.effective_date between
to_date('01/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and
to_date('01/30/2006 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
Tamil

No there is no index on effective_date. Should I add one?

Try it out. You know your data very well and its distribution.

Tamil