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
> 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

Hi,

One thing that jumps out at me is the columns in the predicate only have 12 and three distinct values, respectively. It expects almost 30,000 rows out of a little over 1 million total. Is that number reasonable/correct?

What columns comprise the PK?

HTH, Steve Received on Tue Nov 27 2007 - 10:18:35 CST