Re: CBO choose full table scan, not index

On Nov 27, 10:03 am, lsllcm <lsl..._at_gmail.com> wrote:
> Hi All,
>
> I have one query
>
> SELECT * FROM GASSET_MASTER
> WHERE SERV_PROV_CODE = 'TEST' AND G1_ASSET_GROUP = 'LINE'
> AND ROWNUM < 101
> ;
>
> ROWCNT is 1071973
> INDEX:
> GASSET_MASTER_UIX (SERV_PROV_CODE, G1_ASSET_GROUP, G1_ASSET_TYPE)
>
> The query chooses table scan and not use index.
>
> The following are 10053 trace output, I don't want to add hint of
> index, and how to make the query use index scan access path.
>
> /oracle/admin/pftest/udump/pftest_ora_11554.trc
> Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.8.0 - Production
> ORACLE_HOME = /oracle/ora92
> System name: Linux
> Node name: localhost.localdomain
> Release: 2.6.9-5.ELsmp
> Version: #1 SMP Wed Jan 5 19:30:39 EST 2005
> Machine: i686
> Instance name: pftest
> Redo thread mounted by this instance: 1
> Oracle process number: 15
> Unix process pid: 11554, image: ora..._at_localhost.localdomain (TNS V1-
> V3)
>
> *** 2007-11-27 22:37:51.589
> *** SESSION ID:(67.17772) 2007-11-27 22:37:51.588
> QUERY
> SELECT * FROM GASSET_MASTER
> WHERE SERV_PROV_CODE = 'PETALUMA' AND G1_ASSET_GROUP = 'LINE'
> AND ROWNUM < 101
> *** SESSION ID:(67.17772) 2007-11-27 22:37:56.440
> QUERY
> DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
> *************************
> First K Rows: Setup begin
> ***************************************
> SINGLE TABLE ACCESS PATH
> Column: SERV_PROV_ Col#: 1 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 3 NULLS: 0 DENS: 3.3333e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_G Col#: 4 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 12 NULLS: 0 DENS: 8.3333e-02
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> TABLE: GASSET_MASTER ORIG CDN: 1071973 ROUNDED CDN: 29777
> CMPTD CDN: 297
> 77
> Access path: tsc Resc: 1291 Resp: 1291
> Skip scan: ss-sel 0 andv 357324
> ss cost 357324
> index io scan cost 1387
> Access path: index (scan)
> Index: GASSETMASTER_PK
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 18794
> IX_SEL: 3.3333e-01 TB_SEL: 3.3333e-01
> Skip scan: ss-sel 0 andv 29777
> ss cost 29777
> index io scan cost 186
> Access path: index (scan)
> Index: GASSET_MASTER_ASSETID_UIX
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 28084
> IX_SEL: 2.7778e-02 TB_SEL: 2.7778e-02
> BEST_CST: 1291.00 PATH: 2 Degree: 1
> ***********************
> Join order[1]: GASSET_MASTER[GASSET_MASTER]#0
> Best so far: TABLE#: 0 CST: 1291 CDN: 29777 BYTES:
> 2560822
> Final - All Rows Plan:
> JOIN ORDER: 1
> CST: 1291 CDN: 29777 RSC: 1291 RSP: 1291 BYTES: 2560822
> IO-RSC: 1291 IO-RSP: 1291 CPU-RSC: 0 CPU-RSP: 0
> First K Rows: K/N ratio = 0.003358296671928, qbc=0x9782e794
> First K Rows: Setup end
> ***********************
> ***************************************
> PARAMETERS USED BY THE OPTIMIZER
> ********************************
> OPTIMIZER_FEATURES_ENABLE = 9.2.0
> OPTIMIZER_MODE/GOAL = First_Rows(100)
> _OPTIMIZER_PERCENT_PARALLEL = 101

(Snip)
> DB_FILE_MULTIBLOCK_READ_COUNT = 16
> _NEW_SORT_COST_ESTIMATE = TRUE
> _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
> _CPU_TO_IO = 0
> _PRED_MOVE_AROUND = TRUE
> ***************************************
> BASE STATISTICAL INFORMATION
> ***********************
> Table stats Table: GASSET_MASTER Alias: GASSET_MASTER
> TOTAL :: CDN: 1071973 NBLKS: 13409 AVG_ROW_LEN: 86
> Column: ASSET_ID_E Col#: 24 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 8 NULLS: 1071963 DENS: 1.2500e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: ASSET_ID_S Col#: 23 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 10 NULLS: 1071963 DENS: 1.0000e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_CLASS_T Col#: 22 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 5 NULLS: 9188 DENS: 2.0000e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DEPRECIATI Col#: 21 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 4 NULLS: 1071716 DENS: 2.5000e-01 LO: 0 HI: 8004
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DEPRECIATI Col#: 20 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 4 NULLS: 1071716 DENS: 2.5000e-01 LO: 0 HI: 9980
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DEPRECIATI Col#: 19 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 9 NULLS: 1071951 DENS: 1.1111e-01 LO: 2454296 HI:
> 2455396
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DEPRECIATI Col#: 18 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 12 NULLS: 1071948 DENS: 8.3333e-02 LO: 2452470 HI:
> 2454432
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: CURRENT_VA Col#: 17 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 8 NULLS: 1071715 DENS: 1.2500e-01 LO: 0 HI: 50000
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: SALVAGE_VA Col#: 16 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 7 NULLS: 1071710 DENS: 1.4286e-01 LO: 0 HI: 100
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: USEFUL_LIF Col#: 15 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 9 NULLS: 1071707 DENS: 1.1111e-01 LO: 0 HI: 50
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DATE_OF_SE Col#: 14 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 62 NULLS: 1071762 DENS: 1.6129e-02 LO: 2453931 HI:
> 2454431
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: START_VALU Col#: 13 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 14 NULLS: 1071707 DENS: 7.1429e-02 LO: 0 HI:
> 1000000000
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: REC_STATUS Col#: 12 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 2 NULLS: 45 DENS: 5.0000e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: REC_FUL_NA Col#: 11 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 15 NULLS: 0 DENS: 6.6667e-02
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: REC_DATE Col#: 10 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 3639 NULLS: 0 DENS: 2.7480e-04 LO: 2452977 HI:
> 2454432
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_COMMENT Col#: 9 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 377 NULLS: 1071434 DENS: 2.6525e-03
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_S Col#: 8 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 4599 NULLS: 6608 DENS: 2.1744e-04 LO: 2452977 HI:
> 2454432
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_S Col#: 7 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 8 NULLS: 1062903 DENS: 1.2500e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_DESCRIP Col#: 6 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 389 NULLS: 1071184 DENS: 2.5707e-03
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_T Col#: 5 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 192 NULLS: 0 DENS: 5.2083e-03
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_G Col#: 4 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 12 NULLS: 0 DENS: 8.3333e-02
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_I Col#: 3 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 125251 NULLS: 0 DENS: 7.9840e-06
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_S Col#: 2 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 1067469 NULLS: 0 DENS: 9.3680e-07 LO: 1 HI:
> 1363276
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: SERV_PROV_ Col#: 1 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 3 NULLS: 0 DENS: 3.3333e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: SERV_PROV_ Col#: 1 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 3 NULLS: 0 DENS: 3.3333e-01
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: G1_ASSET_G Col#: 4 Table: GASSET_MASTER Alias:
> GASSET_MASTER
> NDV: 12 NULLS: 0 DENS: 8.3333e-02
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> -- Index stats
> INDEX NAME: GASSETMASTER_PK COL#: 1 2
> TOTAL :: LVLS: 2 #LB: 4160 #DK: 1071973 LB/K: 1 DB/K: 1
> CLUF: 52215
> INDEX NAME: GASSET_MASTER_ASSETID_UIX COL#: 1 4 5 3
> TOTAL :: LVLS: 2 #LB: 6661 #DK: 1071973 LB/K: 1 DB/K: 1
> CLUF: 1004225
> _OPTIMIZER_PERCENT_PARALLEL = 0
> ***************************************
> SINGLE TABLE ACCESS PATH
> TABLE: GASSET_MASTER ORIG CDN: 1071973 ROUNDED CDN: 29777
> CMPTD CDN: 297
> 77
> Access path: tsc Resc: 1291 Resp: 1291
> Skip scan: ss-sel 0 andv 357324
> ss cost 357324
> index io scan cost 1387
> Access path: index (scan)
> Index: GASSETMASTER_PK
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 18794
> IX_SEL: 3.3333e-01 TB_SEL: 3.3333e-01
> Skip scan: ss-sel 0 andv 29777
> ss cost 29777
> index io scan cost 186
> Access path: index (scan)
> Index: GASSET_MASTER_ASSETID_UIX
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 28084
> IX_SEL: 2.7778e-02 TB_SEL: 2.7778e-02
> ****** trying bitmap/domain indexes ******
> ****** finished trying bitmap/domain indexes ******
> BEST_CST: 1291.00 PATH: 2 Degree: 1
> ***************************************
> OPTIMIZER STATISTICS AND COMPUTATIONS
> ***************************************
> GENERAL PLANS
> ***********************
> Join order[1]: GASSET_MASTER[GASSET_MASTER]#0
> Best so far: TABLE#: 0 CST: 1291 CDN: 29777 BYTES:
> 2560822
> ***************************************
> SINGLE TABLE ACCESS PATH (First K Rows)
> TABLE: GASSET_MASTER ORIG CDN: 3601 ROUNDED CDN: 100 CMPTD
> CDN: 100
> Access path: tsc Resc: 6 Resp: 6
> Skip scan: ss-sel 0 andv 1200
> ss cost 1200
> index io scan cost 5
> Access path: index (scan)
> Index: GASSETMASTER_PK
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 66
> IX_SEL: 3.3333e-01 TB_SEL: 3.3333e-01
> Skip scan: ss-sel 0 andv 192
> ss cost 192
> index io scan cost 1
> Access path: index (scan)
> Index: GASSET_MASTER_ASSETID_UIX
> TABLE: GASSET_MASTER
> RSC_CPU: 0 RSC_IO: 97
> IX_SEL: 2.7778e-02 TB_SEL: 2.7778e-02
> ****** trying bitmap/domain indexes ******
> ****** finished trying bitmap/domain indexes ******
> BEST_CST: 6.00 PATH: 2 Degree: 1
> ***********************
> Join order[1]: GASSET_MASTER[GASSET_MASTER]#0
> Best so far: TABLE#: 0 CST: 6 CDN: 100 BYTES:
> 8600
> Final - First K Rows Plan:
> JOIN ORDER: 1
> CST: 6 CDN: 100 RSC: 6 RSP: 6 BYTES: 8600
> IO-RSC: 6 IO-RSP: 6 CPU-RSC: 0 CPU-RSP: 0
> First K Rows Plan
> QUERY
> EXPLAIN PLAN SET STATEMENT_ID='PLUS86933' FOR SELECT * FROM
> GASSET_MASTER
> WHERE SERV_PROV_CODE = 'PETALUMA' AND G1_ASSET_GROUP = 'LINE'
> AND ROWNUM < 101
> PLAN
> Cost of plan: 6
> Operation...........Object name.....Options.........Id...Pid..
> SELECT STATEMENT 0
> COUNT STOPKEY 1
> TABLE ACCESS GASSET_MASTER FULL 2 1
> QUERY
> SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',
> 2*(LEVEL-1))||OPERAT
> ION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'',' ('||
> OPTIONS||')')||
> DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')||
> DECODE(OBJECT_TYPE,NULL
> ,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,DECODE(OPTIMIZER,NULL,'','
> Optimizer='|
> |OPTIMIZER))||DECODE(COST,NULL,'',' (Cost='||COST||
> DECODE(CARDINALITY,NULL,'','
> Card='||CARDINALITY)||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')')
> PLAN_PLUS_EXP
> ,OBJECT_NODE OBJECT_NODE_PLUS_EXP FROM PLAN_TABLE START WITH ID=0 AND
> STATEMENT_
> ID=:1 CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID=:1 ORDER BY
> ID,POSITION
> QUERY
> SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER
> OTHER_PLUS_EXP FROM PLA
> N_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID
> QUERY
> DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
>
> Thanks

SQL statement:
SELECT * FROM GASSET_MASTER
WHERE SERV_PROV_CODE = 'TEST' AND G1_ASSET_GROUP = 'LINE' AND ROWNUM < 101

You are limiting the results to the first 100 rows. Oracle will stop the in-process full tablescan once the 100th match is found. The cost calculations:

Full table scan:
  TABLE: GASSET_MASTER ORIG CDN: 3601 ROUNDED CDN: 100 CMPTD CDN: 100
  Access path: tsc Resc: 6 Resp: 6
The above has a cost of 6, which is roughly 100 (the estimated number of rows to be returned) divided by 16 (the value of DB_FILE_MULTIBLOCK_READ_COUNT). Partial index match, index skip scan using the GASSETMASTER_PK index:   Skip scan: ss-sel 0 andv 1200
    ss cost 1200
    index io scan cost 5
  Access path: index (scan)

      Index: GASSETMASTER_PK
  TABLE: GASSET_MASTER
      RSC_CPU: 0 RSC_IO: 66
  IX_SEL: 3.3333e-01 TB_SEL: 3.3333e-01 The above has a higher cost than the full tablescan.

Partial index match using the GASSET_MASTER_ASSETID_UIX index:   Skip scan: ss-sel 0 andv 192
    ss cost 192
    index io scan cost 1
  Access path: index (scan)

      Index: GASSET_MASTER_ASSETID_UIX
  TABLE: GASSET_MASTER
      RSC_CPU: 0 RSC_IO: 97
  IX_SEL: 2.7778e-02 TB_SEL: 2.7778e-02 The above has a higher cost than the full tablescan, so the full tablescan wins.

Looking at the column detail:
Column: SERV_PROV_ Col#: 1 Table: GASSET_MASTER Alias: GASSET_MASTER
    NDV: 3 NULLS: 0 DENS: 3.3333e-01     NO HISTOGRAM: #BKT: 1 #VAL: 2
There are only 3 distinct (unique) values in this column. The index will likely be ineffective with only three distinct values - if this was the only column included in the index. There are no histograms.

Column: G1_ASSET_G Col#: 4 Table: GASSET_MASTER Alias: GASSET_MASTER
    NDV: 12 NULLS: 0 DENS: 8.3333e-02     NO HISTOGRAM: #BKT: 1 #VAL: 2
There are 12 distinct (unique) values in this column. The index might be helpful, but because of columns 5 and 3 also being included in the GASSET_MASTER_ASSETID_UIX index, the clustering factor is driven up to 1004225, making it look like an expensive data access method. There are no histograms.

The indexes
  INDEX NAME: GASSETMASTER_PK COL#: 1 2     TOTAL :: LVLS: 2 #LB: 4160 #DK: 1071973 LB/K: 1 DB/K: 1 CLUF: 52215
Note that column #2 is not included in the WHERE clause, so an index skip scan would be required to use this index.

  INDEX NAME: GASSET_MASTER_ASSETID_UIX COL#: 1 4 5 3     TOTAL :: LVLS: 2 #LB: 6661 #DK: 1071973 LB/K: 1 DB/K: 1 CLUF: 1004225
Note that columns 5 and 3 are not included in the WHERE clause, so an index skip scan would be required to use this index.

The table's average row length is 86 bytes, and assuming an 8KB block size and a DB_FILE_MULTIBLOCK_READ_COUNT of 16, the database will be able to read roughly 1,520 (16*FLOOR(8192/86)) rows in a single read operation, assuming that most blocks are full. It is possible that Oracle determines that the query can be resolved using between 1 and 6 operating system IO requests, while each index lookup (there would be at least 100) might take 3 or 4 IO requests each.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Nov 27 2007 - 12:12:43 CST