Re: Optimizer 10053 trace file, strange choises made by Oracle??

"HansP" <Hans-Peter.Sloot_at_atosorigin.com> wrote in message news:b4ec3efd-18cd-4fa5-afbc-e97d127c856f_at_d20g2000yqh.googlegroups.com...
> Hi all,
>
> I had a 10053 trace file generated of a Siebel query and I do not
> understand the SINGLE TABLE ACCESS PATH part.
> Below is a snippet of the tracefile.
>
> It seems to calculate the costs of a full tablescan as 1988.76.
> Then the different index access paths are calculated which are
> calculated as 215.78 and 236.68 respectively.
> At the end to my surprise it says "Best:: AccessPath: TableScan" with
> cost 1988.76.
>
> Can someone shed some light on this. Why is the TableScan chosen
> whereas the index accesses have a lower cost.
>
> Regards HansP
>
> (Oracle version 10.2.0.4, optimizer_index_cost_adj = 10)
>
> SINGLE TABLE ACCESS PATH
> -----------------------------------------
> BEGIN Single Table Cardinality Estimation
> -----------------------------------------
> Table: S_ORG_EXT_LSX Alias: T13
> Card: Original: 784404 Rounded: 784404 Computed: 784404.00 Non
> Adjusted: 784404.00
> -----------------------------------------
> END Single Table Cardinality Estimation
> -----------------------------------------
> Access Path: TableScan
> Cost: 1988.76 Resp: 1988.76 Degree: 0
> Cost_io: 1947.00 Cost_cpu: 817088980
> Resp_io: 1947.00 Resp_cpu: 817088980
> ****** trying bitmap/domain indexes ******
> Access Path: index (FullScan)
> Index: S_ORG_EXT_LSX_P1
> resc_io: 2149.00 resc_cpu: 171334375
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 215.78 Resp: 215.78 Degree: 0
> Access Path: index (FullScan)
> Index: S_ORG_EXT_LSX_U1
> resc_io: 2358.00 resc_cpu: 172822756
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 236.68 Resp: 236.68 Degree: 0
> ****** finished trying bitmap/domain indexes ******
> Best:: AccessPath: TableScan
> Cost: 1988.76 Degree: 1 Resp: 1988.76 Card: 784404.00
> Bytes: 0

It's often necessary to investigate any odd case in detail, version by version of Oracle.

In this case, you have optimizer_index_cost_adj = 10, and you can see that your final index costs are 10% (approx) of the initial resc_io for the index; and the tablescan cost is less than the initial index costs. On top of this, the index uses are both FULL SCAN. The selectivity, in the index and at the table, is 1 - i.e. all the data. (And since it's Siebel, I wouldn't be surprised if you've also got the optimizer mode set to first_rows_10 .... and the first_rows_n optimsations always add confusion).

Possibilities -

    first_rows_n may have messed things up

    full scans may be paths where Oracle ignores the     optimizer_index_cost_adj when making the final decision.

    the selectivities of 1 throughout may cause Oracle     to ignore the optimizer_index_cost_adj when making     the final decision.

    you've found a bug

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com