Re: Optimizer 10053 trace file, strange choises made by Oracle??
From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Fri, 11 Dec 2009 00:10:47 -0800 (PST)
Message-ID: <8b522c24-60a9-4d8d-9661-6d6adf7b76c0_at_r5g2000yqb.googlegroups.com>
On 10 dec, 23:15, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "HansP" <Hans-Peter.Sl..._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 Lewishttp://jonathanlewis.wordpress.com
Date: Fri, 11 Dec 2009 00:10:47 -0800 (PST)
Message-ID: <8b522c24-60a9-4d8d-9661-6d6adf7b76c0_at_r5g2000yqb.googlegroups.com>
On 10 dec, 23:15, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "HansP" <Hans-Peter.Sl..._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 Lewishttp://jonathanlewis.wordpress.com
Thanks for your answer Jonathan,
Well the optimizer_mode of the database is all_rows. But Siebel sets the optimizer_mode for the sessions to first_rows_10. That is really messing up the query plan. It is a 17 table join with a lot of outer joins.
For every table involved it calculates with the cardinality of 11.
Best so far: Table#: 1 cost: 2.0006 card: 11.0000 bytes: 539
Table#: 0 cost: 3.1010 card: 11.0000 bytes: 2783
Table#: 2 cost: 5.1010 card: 11.0000 bytes: 7458
Table#: 3 cost: 7.3020 card: 11.0000 bytes: 7711
Table#: 4 cost: 10.6033 card: 11.0000 bytes: 8382
Table#: 5 cost: 12.8046 card: 10.8637 bytes: 20592
Table#: 6 cost: 13.8046 card: 10.8637 bytes: 20790
Table#: 7 cost: 14.8046 card: 10.8637 bytes: 20988
Table#: 8 cost: 18.1059 card: 10.8637 bytes: 21351
Table#: 9 cost: 20.3068 card: 9.8761 bytes: 19610
Table#:10 cost: 21.3073 card: 9.8761 bytes: 19720
Table#:11 cost: 23.3082 card: 9.8761 bytes: 20500
Table#:12 cost: 24.3091 card: 9.8761 bytes: 21280
Table#:13 cost: 27.3102 card: 9.8761 bytes: 21590
Table#:14 cost: 30.3114 card: 9.8761 bytes: 21880
Table#:15 cost: 31.3119 card: 9.8761 bytes: 22010
Table#:16 cost: 32.3123 card: 9.8761 bytes: 22120
But wanting to have the first 10 rows does not mean that you only need
10 rows from every table involved.
Really anoying.
In this case the query takes about 20 hours with the first_rows_10
optimizer mode and is sub second
without.
Thanks Received on Fri Dec 11 2009 - 02:10:47 CST