Re: CBO choose full table scan, not index

On Nov 27, 9: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
> HASH_AREA_SIZE = 2097152
> HASH_JOIN_ENABLED = TRUE
> HASH_MULTIBLOCK_IO_COUNT = 0
> SORT_AREA_SIZE = 1048576
> OPTIMIZER_SEARCH_LIMIT = 5
> PARTITION_VIEW_ENABLED = FALSE
> _ALWAYS_STAR_TRANSFORMATION = FALSE
> _B_TREE_BITMAP_PLANS = TRUE
> STAR_TRANSFORMATION_ENABLED = FALSE
> _COMPLEX_VIEW_MERGING = TRUE
> _PUSH_JOIN_PREDICATE = TRUE
> PARALLEL_BROADCAST_ENABLED = TRUE
> OPTIMIZER_MAX_PERMUTATIONS = 1000
> OPTIMIZER_INDEX_CACHING = 90
> _SYSTEM_INDEX_CACHING = 0
> OPTIMIZER_INDEX_COST_ADJ = 10
> OPTIMIZER_DYNAMIC_SAMPLING = 1
> _OPTIMIZER_DYN_SMP_BLKS = 32
> QUERY_REWRITE_ENABLED = FALSE
> QUERY_REWRITE_INTEGRITY = ENFORCED
> _INDEX_JOIN_ENABLED = TRUE
> _SORT_ELIMINATION_COST_RATIO = 0
> _OR_EXPAND_NVL_PREDICATE = TRUE
> _NEW_INITIAL_JOIN_ORDERS = TRUE
> ALWAYS_ANTI_JOIN = CHOOSE
> ALWAYS_SEMI_JOIN = CHOOSE
> _OPTIMIZER_MODE_FORCE = TRUE
> _OPTIMIZER_UNDO_CHANGES = FALSE
> _UNNEST_SUBQUERY = TRUE
> _PUSH_JOIN_UNION_VIEW = TRUE
> _FAST_FULL_SCAN_ENABLED = TRUE
> _OPTIM_ENHANCE_NNULL_DETECTION = TRUE
> _ORDERED_NESTED_LOOP = TRUE
> _NESTED_LOOP_FUDGE = 100
> _NO_OR_EXPANSION = FALSE
> _QUERY_COST_REWRITE = TRUE
> QUERY_REWRITE_EXPRESSION = TRUE
> _IMPROVED_ROW_LENGTH_ENABLED = TRUE
> _USE_NOSEGMENT_INDEXES = FALSE
> _ENABLE_TYPE_DEP_SELECTIVITY = TRUE
> _IMPROVED_OUTERJOIN_CARD = TRUE
> _OPTIMIZER_ADJUST_FOR_NULLS = TRUE
> _OPTIMIZER_CHOOSE_PERMUTATION = 0
> _USE_COLUMN_STATS_FOR_FUNCTION = TRUE
> _SUBQUERY_PRUNING_ENABLED = TRUE
> _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
> _SUBQUERY_PRUNING_COST_FACTOR = 20
> _LIKE_WITH_BIND_AS_EQUALITY = FALSE
> _TABLE_SCAN_COST_PLUS_ONE = TRUE
> _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
> _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
> _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
> _OPTIMIZER_COST_MODEL = CHOOSE
> _GSETS_ALWAYS_USE_TEMPTABLES = FALSE
> 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

Your data may be skewed, as Oracle believes a table scan to be the lowest cost for this query. Have you determined the number of rows containing these indexed values and calculated the percentage of the table this would involve? I expect it's far greater than 30%, or, at the very least, Oracle has decided that is the case. Histograms may shift this plan from a table scan to an index scan, however as it is at the moment Oracle has calculated three separate ways to select this data (including index scans) and has found a full table scan to be preferable since your index selectivity is no better than your table selectivity. You also might wish to change the value for optimizer_features_enable to match the exact release you're using (currently it's set to 9.2.0, and setting it to 9.2.0.8 may help matters). The optimizer_mode setting is first_rows(100); setting this to all_rows may make a difference as well.

I would suggest you purchase a copy of "Cost-Based Oracle Fundamentals", by Jonathan Lewis, and start reading. Much is covered there that can help you with this query.

David Fitzjarrell Received on Tue Nov 27 2007 - 10:18:27 CST