Re: FBI? MAX? INDEX FULL SCAN (MIN/MAX)?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Mar 2007 15:08:49 -0700
Message-ID: <1173737328.948248.91480@64g2000cwx.googlegroups.com>

On Mar 12, 1:09 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Mar 12, 7:18 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > I suggest that you compare the output of DBMS Xplan with the SET
> > AUTOTRACE TRACEONLY output to see if the plans are the same.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Charles,
>
> In 10gR2 at least, autotrace uses dbms_xplan to display the explain
> plan.
> A 10046 trace would reveal that autotrace is calling the following
> sql to display the explain plan:
> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :
> 1))

I must be confusing SQL*Plus directives. Wasn't there an explain plan directive that does not actually execute the SQL statement, but only returns the expected plan for the SQL statement?

> I checked out this issue in 10.2.0.2 and 10.2.0.3 and on first glance
> (using 10053 trace), it appears that CBO is handling it differently
> in the two versions.
> In 10.2.0.2, it appears that CBO is applying optimizer_index_cost_adj
> to FFS while in 10.2.0.3 it is not. So in 10.2.0.3 test I did, it
> appears to have picked up the FBI since the cost after applying
> o_i_c_a appeared to be less.
>
> However, I read Bug 4483286 and it states that o_i_c_a should not
> be applied to FFS.
>
> not sure whats going on here...

Here is what I see for 10053 traces that indicate why Oracle selected not to use the function based index in my example: optimizer_index_cost_adj = 100
optimizer_index_caching = 0

The unhinted execution, note the clustering factor and CPU cost of the function based index compared to the index for the primary key index (SYS_C0022498):
Table Stats::
  Table: T1 Alias: T1
    #Rows: 7200 #Blks: 20 AvgRowLen: 23.00 Index Stats::
  Index: IND_T1 Col#: 3

    LVLS: 1  #LB: 18  #DK: 7162  LB/K: 1.00  DB/K: 1.00  CLUF: 5222.00
  Index: IND_T1_2  Col#: 4
    LVLS: 1  #LB: 18  #DK: 7162  LB/K: 1.00  DB/K: 1.00  CLUF: 5333.00

Index: SYS_C0022498 Col#: 1
LVLS: 1 #LB: 13 #DK: 7200 LB/K: 1.00 DB/K: 1.00 CLUF: 16.00

SINGLE TABLE ACCESS PATH
  Table: T1 Alias: T1
    Card: Original: 7200 Rounded: 7200 Computed: 7200.00 Non Adjusted: 7200.00
  Access Path: TableScan
    Cost: 6.10 Resp: 6.10 Degree: 0

      Cost_io: 6.00  Cost_cpu: 1222429
      Resp_io: 6.00  Resp_cpu: 1222429
  Access Path: index (index (FFS))

    Index: IND_T1
    resc_io: 5.00 resc_cpu: 992186
    ix_sel: 0.0000e+000 ix_sel_with_filters: 1   Access Path: index (FFS)
    Cost: 5.08 Resp: 5.08 Degree: 1

      Cost_io: 5.00  Cost_cpu: 992186
      Resp_io: 5.00  Resp_cpu: 992186
  Access Path: index (index (FFS))

    Index: SYS_C0022498
    resc_io: 4.00 resc_cpu: 956579
    ix_sel: 0.0000e+000 ix_sel_with_filters: 1   Access Path: index (FFS)
    Cost: 4.08 Resp: 4.08 Degree: 1

      Cost_io: 4.00  Cost_cpu: 956579
      Resp_io: 4.00  Resp_cpu: 956579
  Access Path: index (FullScan)

    Index: IND_T1
    resc_io: 19.00 resc_cpu: 1575307
    ix_sel: 1 ix_sel_with_filters: 1
    Cost: 19.12 Resp: 19.12 Degree: 1
  Access Path: index (FullScan)
    Index: SYS_C0022498
    resc_io: 14.00 resc_cpu: 1539700
    ix_sel: 1 ix_sel_with_filters: 1

    Cost: 14.12  Resp: 14.12  Degree: 1
  Best:: AccessPath: IndexFFS  Index: SYS_C0022498
         Cost: 4.08  Degree: 1  Resp: 4.08  Card: 7200.00  Bytes: 0

The cost of returning the data with an Index Fast Full Scan of the SYS_C0022498 index is 4.08. The cost of a fast full scan of the function based index IND_T1 is 5.08, while a full scan of the same index had a cost of 19.12.

The hinted execution to use an index full scan (/*+ INDEX(T1 IND_T1) */):
Table Stats::
  Table: T1 Alias: T1
    #Rows: 7200 #Blks: 20 AvgRowLen: 23.00 Index Stats::
  Index: IND_T1 Col#: 3
    LVLS: 1 #LB: 18 #DK: 7162 LB/K: 1.00 DB/K: 1.00 CLUF: 5222.00     User hint to use this index
  Index: IND_T1_2 Col#: 4
    LVLS: 1 #LB: 18 #DK: 7162 LB/K: 1.00 DB/K: 1.00 CLUF: 5333.00   Index: SYS_C0022498 Col#: 1
    LVLS: 1 #LB: 13 #DK: 7200 LB/K: 1.00 DB/K: 1.00 CLUF: 16.00

SINGLE TABLE ACCESS PATH
  Table: T1 Alias: T1
    Card: Original: 7200 Rounded: 7200 Computed: 7200.00 Non Adjusted: 7200.00
  Access Path: index (FullScan)
    Index: IND_T1
    resc_io: 19.00 resc_cpu: 1575307
    ix_sel: 1 ix_sel_with_filters: 1
    Cost: 19.12 Resp: 19.12 Degree: 1
  Best:: AccessPath: IndexRange Index: IND_T1

Cost: 19.12 Degree: 1 Resp: 19.12 Card: 7200.00 Bytes: 0

Cost is almost 5 times greater, yet it finishes much faster.

With an index fast full scan hint (/*+ INDEX_FFS(T1 IND_T1) */) Table Stats::
  Table: T1 Alias: T1
    #Rows: 7200 #Blks: 20 AvgRowLen: 23.00 Index Stats::
  Index: IND_T1 Col#: 3
    LVLS: 1 #LB: 18 #DK: 7162 LB/K: 1.00 DB/K: 1.00 CLUF: 5222.00     User hint to use this index
  Index: IND_T1_2 Col#: 4
    LVLS: 1 #LB: 18 #DK: 7162 LB/K: 1.00 DB/K: 1.00 CLUF: 5333.00   Index: SYS_C0022498 Col#: 1
    LVLS: 1 #LB: 13 #DK: 7200 LB/K: 1.00 DB/K: 1.00 CLUF: 16.00

SINGLE TABLE ACCESS PATH
  Table: T1 Alias: T1
    Card: Original: 7200 Rounded: 7200 Computed: 7200.00 Non Adjusted: 7200.00
  Access Path: index (index (FFS))
    Index: IND_T1
    resc_io: 5.00 resc_cpu: 992186
    ix_sel: 0.0000e+000 ix_sel_with_filters: 1   Access Path: index (FFS)
    Cost: 5.08 Resp: 5.08 Degree: 1

      Cost_io: 5.00  Cost_cpu: 992186
      Resp_io: 5.00  Resp_cpu: 992186
  Best:: AccessPath: IndexFFS  Index: IND_T1
         Cost: 5.08  Degree: 1  Resp: 5.08  Card: 7200.00  Bytes: 0

The cost based optimizer makes decisions on the access path based on the cost of the access path. The cost of the access path for the system generated index for the primary key was expected to be less expensive than the access path using the function based index.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 12 2007 - 17:08:49 CDT