Re: index skip scan
Date: Tue, 6 Jan 2009 08:29:17 -0800 (PST)
Message-ID: <36405b48-7174-466a-840e-9a2f664e3f88_at_t39g2000prh.googlegroups.com>
On Jan 6, 10:44 am, "jerni..._at_nospam.kochind.com" <jerni..._at_kochind.com> wrote:
> Yes it is a DATE column. I get the same plan with to_date.
>
> I know it doesn't use bind variables. This is a simplified version
> of multiple queries that are in a packaged application. I can't
> change the queries in the app.
>
> I am trying to understand what impacts the cost of index skip scans.
> The 10053 trace shows the cost of the index skip scan as being
> lower.
>
> Are there any parameters that impact the cost of a skip scan?
>
> I hate to delete the index used by the skip scan, because it is
> probably needed by some other queries.
Did you capture the 10053 trace at level 1 or level 2?
Take a look at the contents of the 10053 trace files. Look for a section similar to the following:
PARAMETERS WITH ALTERED VALUES
optimizer_features_enable = 10.1.0.4 sort_area_size = 20971520 sort_area_retained_size = 262144 _pga_max_size = 409600 KB cursor_sharing = force optimizer_index_cost_adj = 20 optimizer_index_caching = 100 query_rewrite_enabled = false query_rewrite_integrity = trusted *********************************
Please post the section "PARAMETERS WITH ALTERED VALUES" from the trace files.
Further down in the trace files, look for a section that looks like this:
BASE STATISTICAL INFORMATION
Table Stats::
Table: PART Alias: PART
#Rows: 33651 #Blks: 1252 AvgRowLen: 233.00 Index Stats::
Index: SYS_C005496 Col#: 1
LVLS: 1 #LB: 93 #DK: 33651 LB/K: 1.00 DB/K: 1.00 CLUF: 23471.00
Index: X_PART_1 Col#: 35
LVLS: 1 #LB: 62 #DK: 2 LB/K: 31.00 DB/K: 837.00 CLUF: 1675.00
Index: X_PART_2 Col#: 36
LVLS: 1 #LB: 32 #DK: 2 LB/K: 16.00 DB/K: 506.00 CLUF: 1013.00
Index: X_PART_3 Col#: 15 16
LVLS: 1 #LB: 3 #DK: 623 LB/K: 1.00 DB/K: 1.00 CLUF: 493.00
Index: X_PART_4 Col#: 3
LVLS: 1 #LB: 67 #DK: 18 LB/K: 3.00 DB/K: 102.00 CLUF: 1842.00
Index: X_PART_5 Col#: 25
LVLS: 1 #LB: 9 #DK: 5 LB/K: 1.00 DB/K: 197.00 CLUF: 987.00 Index: X_PART_6 Col#: 28
LVLS: 1 #LB: 46 #DK: 571 LB/K: 1.00 DB/K: 11.00 CLUF: 6792.00
Please post the section "BASE STATISTICAL INFORMATION" from the trace files.
Further down in the trace file, you will find the calculations of each
access path, which looks something like this:
Access Path: index (UniqueScan)
Index: SYS_C005496
resc_io: 2.00 resc_cpu: 23374
ix_sel: 2.9717e-005 ix_sel_with_filters: 2.9717e-005
Cost: 1.00 Resp: 1.00 Degree: 1
Using prorated density: 2.9717e-005 of col #1 as selectivity of out-
of-range value pred
Access Path: index (AllEqUnique)
Index: SYS_C005496
resc_io: 2.00 resc_cpu: 23374
ix_sel: 2.9717e-005 ix_sel_with_filters: 2.9717e-005
Cost: 1.00 Resp: 1.00 Degree: 1
One row Card: 1.00
Best:: AccessPath: IndexUnique Index: SYS_C005496
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
Please post that section of the trace files.
Just below the plan table output, you will find something that looks
like this:
Predicate Information:
2 - access("ID"=:SYS_B_0)
Content of other_xml column
db_version : 10.2.0.2 parse_schema : ME plan_hash : 2694627966
Peeked Binds
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=178
char format=1
max length=32
value=AAAAAA
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('optimizer_index_cost_adj' 20)
OPT_PARAM('optimizer_index_caching' 100)
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$1")
INDEX(_at_"SEL$1" "PART"@"SEL$1" ("PART"."ID"))
END_OUTLINE_DATA
*/
Please post the section from the 10053 trace files from the "Predicate Information" line to the END_OUTLINE_DATA line.
You are headed in the right direction. A stored outline may be used to force a specific execution plan for queries which cannot be modified.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jan 06 2009 - 10:29:17 CST