Following yesterday’s note on SQL Profiles, someone asked how I detect that an opt_estimate hint had been used – with specific reference to the “index_scan” option. The reason for this particular choice is that other opt_estimate hints have a highly visible impact in the 10053 trace files, but this one doesn’t.
Here are a few extracts from the Single Table Access section of the trace:
a) Following: opt_estimate(@sel$1, table, t1@sel$1, scale_rows=10) (multiply row count by 10)
Table: T1 Alias: T1 Card: Original: 4000 >> Single Tab Card adjusted from: 10.00 to: 100.00 Rounded: 100 Computed: 100.00 Non Adjusted: 10.00
b) Following: opt_estimate(@sel$1, join, (t2@sel$1, t1@sel$1), scale_rows=225) (multiply join cardinality by 225)
>> Join Card adjusted from 0.00 to: 1.00, prelen=2 Adjusted Join Cards: adjRatio=2666.67 cardHjSmj=1.00 cardHjSmjNPF=1.00
cardNlj=1.00 cardNSQ=1.00 cardNSQ_na=0.00
As you can see, the impact of the opt_estimate() shows up very clearly in these two cases, but when we try: opt_estimate(@sel$1, index_scan, t1@sel$1, t1_i1, scale_rows=0.05) (divide index selectivity by 20), we have to check the before and after trace files (note particularly the values for ix_sel and ix_sel_with_filters:
Access Path: index (AllEqRange) Index: T1_I1 resc_io: 168.00 resc_cpu: 1278402 ix_sel: 0.05 ix_sel_with_filters: 0.05 Cost: 168.19 Resp: 168.19 Degree: 1 Best:: AccessPath: IndexRange Index: T1_I1 Cost: 168.19 Degree: 1 Resp: 168.19 Card: 10.00 Bytes: 0
Access Path: index (AllEqRange) Index: T1_I1 resc_io: 12.00 resc_cpu: 89967 ix_sel: 0.0025 ix_sel_with_filters: 0.0025 Cost: 12.01 Resp: 12.01 Degree: 1 Best:: AccessPath: IndexRange Index: T1_I1 Cost: 12.01 Degree: 1 Resp: 12.01 Card: 10.00 Bytes: 0
In this case, we don’t get any indication of “adjusted values” – we actually have to check the before and after to see what has changed – and infer from the drop in cost that we have used a reduced cardinality. This can be particularly awkward to interpret in cases where the targetted index is not a suitable candidate for a single table access path, but becomes available as an access path later in the join evolution.