Prev: need 9.2.0.5 windows client
Next: SGA
From: lsllcm on 27 Nov 2007 10:03
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: oracle(a)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
From: fitzjarrell on 27 Nov 2007 11:18
On Nov 27, 9:03 am, lsllcm <lsl...(a)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...(a)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


From: Steve Howard on 27 Nov 2007 11:18
On Nov 27, 10:03 am, lsllcm <lsl...(a)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...(a)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
From: Steve Howard on 27 Nov 2007 11:22
....it also occurred to me that the cardinality calculated by the
optimizer is exactly the total number of rows /
(column_1_distinct_values * column2_distinct_values)

1071973 / ( 12 * 3) = 29777

It may not help, but it at least pushes you in a direction for the
numbers the optimizer is using.
From: Charles Hooper on 27 Nov 2007 13:12
On Nov 27, 10:03 am, lsllcm <lsl...(a)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...(a)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.
 |  Next  |  Last
Pages: 1 2
Prev: need 9.2.0.5 windows client
Next: SGA