Now, I'm not desperately experienced at reading 10053 trace files, and I'm wading through Jonathan Lewis' CBO book, so a 'RTFM' reply wouldn't be unwarranted (provided somebody points me to which part of TFM I should R).
We have a table IMMD_NEEDS, PK on (WHSE, IMMD_NEED_ID). Table has 31407 rows. The WHSE column is skewed: two distinct values with 3 and 31404 rows. Histogram reflects this.
8K block size
DBFMRC=8. OPTIMIZER_MODE=FIRST_ROWS.
I'm having problems understanding why the optimizer not picking a full table scan access path for a particular query:
explain plan for
SELECT /*+FULL(IMMD_NEEDS)*/ IMMD_NEEDS.WHSE, IMMD_NEEDS.IMMD_NEED_ID, IMMD_NEEDS.SHPMT_NBR, IMMD_NEEDS.CASE_NBR, IMMD_NEEDS.PO_NBR, IMMD_NEEDS.SKU_ID
, IMMD_NEEDS.INVN_TYPE, IMMD_NEEDS.PROD_STAT, IMMD_NEEDS.BATCH_NBR, IMMD_NEEDS.SKU_ATTR_1, IMMD_NEEDS.SKU_ATTR_2, IMMD_NEEDS.SKU_ATTR_3, IMMD_NEEDS.SKU_ATTR_4, IMMD_NEEDS.SKU_ATTR_5, IMMD_NEEDS.CNTRY_OF_ORGN, IMMD_NEEDS.IMMD_NEED_PRTY, IMMD_NEEDS.REC_TYPE, IMMD_NEEDS.TMPL_ID, IMMD_NEEDS.SNGL_CASE_REQD, IMMD_NEEDS.PCNT_XCESS_NEED, IMMD_NEEDS.PERM_NEED_FLAG, IMMD_NEEDS.SNGL_SKU_CASE,
IMMD_NEEDS.CASE_PROC, IMMD_NEEDS.IMMD_NEED_TYPE, IMMD_NEEDS.PUTWY_TYPE, IMMD_NEEDS.QTY_TYPE, IMMD_NEEDS.QTY_REQD, IMMD_NEEDS.QTY_FULFLD, IMMD_NEEDS.CMNT, IMMD_NEEDS.STAT_CODE, IMMD_NEEDS.SRC_OF_NEED, IMMD_NEEDS.CREATE_DATE_TIME, IMMD_NEEDS.MOD_DATE_TIME,
IMMD_NEEDS.USER_ID, IMMD_NEEDS.STAT_CODE_UPDATE, IMMD_NEEDS.REMOVE_LOCK_FLAG, IMMD_NEEDS.REMOVE_LOCK_DTL_FLAG, IMMD_NEEDS.RELEASE_DTL_FLAG, IMMD_NEEDS.ORIG_WHSE, IMMD_NEEDS.ORIG_IMMD_NEED_ID FROM IMMD_NEEDS WHERE ( ( ( ( ( ( IMMD_NEEDS.WHSE = :1 ) AND ( IMMD_NEEDS.SHPMT_NBR = :2 ) ) AND ( IMMD_NEEDS.SKU_ID = :3 ) ) AND ( IMMD_NEEDS.STAT_CODE = :4 ) ) AND ( ( IMMD_NEEDS.CASE_NBR = :5 ) OR ( IMMD_NEEDS.CASE_NBR IS NULL ) ) ) AND ( ( IMMD_NEEDS.PO_NBR = :6 ) OR ( IMMD_NEEDS.PO_NBR IS NULL ) ) ) ORDER BY IMMD_NEEDS.IMMD_NEED_PRTY ASC, IMMD_NEEDS.TMPL_ID DESC, IMMD_NEEDS.QTY_REQD DESC
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 657 |
| 1 | SORT ORDER BY | | 1 | 100 | 657 |
|* 2 | TABLE ACCESS BY INDEX ROWID| IMMD_NEEDS | 1 | 100 | 653 |
|* 3 | INDEX RANGE SCAN | PK_IMMD_NEEDS | 15725 | | 43 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IMMD_NEEDS"."SHPMT_NBR"=:Z AND "IMMD_NEEDS"."SKU_ID"=:Z AND
"IMMD_NEEDS"."STAT_CODE"=TO_NUMBER(:Z) AND ("IMMD_NEEDS"."CASE_NBR"=:Z OR
"IMMD_NEEDS"."CASE_NBR" IS NULL) AND ("IMMD_NEEDS"."PO_NBR"=:Z OR
"IMMD_NEEDS"."PO_NBR" IS NULL))
3 - access("IMMD_NEEDS"."WHSE"=:Z)
Note: cpu costing is off
Same query with hint: /*+FULL(IMMD_NEEDS)*/
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 85 |
| 1 | SORT ORDER BY | | 1 | 100 | 85 |
|* 2 | TABLE ACCESS FULL | IMMD_NEEDS | 1 | 100 | 81 |
--------------------------------------------------------------------
A 10053 of the EXPLAIN PLAN without the hint gives:
***************************************
SINGLE TABLE ACCESS PATH
Column: WHSE Col#: 1 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 2 NULLS: 0 DENS: 1.5898e-05
FREQUENCY HISTOGRAM: #BKT: 31450 #VAL: 2
Column: SHPMT_NBR Col#: 3 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 1 NULLS: 6 DENS: 1.0000e+00
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: SKU_ID Col#: 6 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 14950 NULLS: 3 DENS: 6.6890e-05
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: STAT_CODE Col#: 30 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 3 NULLS: 0 DENS: 3.3333e-01 LO: 0 HI: 99
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: CASE_NBR Col#: 4 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 5823 NULLS: 6 DENS: 3.1803e-05
HEIGHT BALANCED HISTOGRAM: #BKT: 200 #VAL: 39
Column: PO_NBR Col#: 5 Table: IMMD_NEEDS Alias: IMMD_NEEDS
NDV: 1 NULLS: 21 DENS: 1.0000e+00
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: IMMD_NEEDS ORIG CDN: 31450 ROUNDED CDN: 1 CMPTD CDN: 0
Access path: tsc Resc: 81 Resp: 81
Skip scan: ss-sel 0 andv 15725
Access path: tsc Resc: 81 Resp: 81
Skip scan: ss-sel 0 andv 15725
ss cost 15725
index io scan cost 0
Access path: index (scan)
Index: PK_IMMD_NEEDS
TABLE: IMMD_NEEDS
RSC_CPU: 0 RSC_IO: 653
IX_SEL: 5.0000e-01 TB_SEL: 5.0000e-01
BEST_CST: 653.00 PATH: 4 Degree: 1
***************************************
GENERAL PLANS
***********************
Join order[1]: IMMD_NEEDS[IMMD_NEEDS]#0
ORDER BY sort
SORT resource Sort statistics
Sort width: 27 Area size: 131072 Max Area size: 5032960
Degree: 1
Blocks to Sort: 1 Row size: 120 Rows: 1
Initial runs: 1 Merge passes: 1 IO Cost / pass: 6
Total IO sort cost: 4
Total CPU sort cost: 0
Total Temp space used: 0
Total CPU sort cost: 0
Total Temp space used: 0
Best so far: TABLE#: 0 CST: 657 CDN: 1 BYTES: 100
SORT resource Sort statistics
Sort width: 27 Area size: 131072 Max Area size: 5032960
Degree: 1
Blocks to Sort: 1 Row size: 120 Rows: 1
Initial runs: 1 Merge passes: 1 IO Cost / pass: 6
Total IO sort cost: 4
Total CPU sort cost: 0
Total Temp space used: 0
prefetching is on for PK_IMMD_NEEDS
Final - First Rows Plan:
JOIN ORDER: 1
CST: 657 CDN: 1 RSC: 656 RSP: 656 BYTES: 100
IO-RSC: 656 IO-RSP: 656 CPU-RSC: 0 CPU-RSP: 0
First Rows Plan
=====================
So, it looks to me like the FTS is cheaper, and the optimizer knows it. So why doesn't it choose it unless I ask it to?
Stuart
