Optimizer 10053 trace file, strange choises made by Oracle??
in [Oracle]
|
From: HansP on 10 Dec 2009 15:21 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
From: Jonathan Lewis on 10 Dec 2009 17:15 "HansP" <Hans-Peter.Sloot(a)atosorigin.com> wrote in message news:b4ec3efd-18cd-4fa5-afbc-e97d127c856f(a)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
From: HansP on 11 Dec 2009 03:10 On 10 dec, 23:15, "Jonathan Lewis" <jonat...(a)jlcomp.demon.co.uk> wrote: > "HansP" <Hans-Peter.Sl...(a)atosorigin.com> wrote in message > > news:b4ec3efd-18cd-4fa5-afbc-e97d127c856f(a)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
|
Pages: 1 Prev: Ocopy and Archived Logs Next: update multiple rows continue past exceptions |