Why is the full table scan not being chosen?

From: Stuart Clowes <stuart.clowes_at_gmail.com>
Date: Wed, 5 Jul 2006 12:38:20 +0100
Message-ID: <8cb0040f0607050438t493823acs35afb1b8ecf1d3e9@mail.gmail.com>

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

| 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

| 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 05 2006 - 06:38:20 CDT