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_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
Received on Tue Nov 27 2007 - 09:03:53 CST
