SQL Profiles – 2

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

   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     

After:

   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.

[Back to part 1]