Re: OICA and Oracle choosing the wrong index
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 11 May 2004 09:45:21 -0600
I thought that Jonathan explained it very well in that article. Or maybe I
am reading more into it because I was in his keynote session at the Hotsos
seminar this March where he presented the same example.
The crux is that the CBO uses the index name sort order to brake a tie in
index access costs, so if the costs are equal, t1_bad is chosen over
t1_good. With the default OICA, the t1_good index access cost is lower than
t1_bad (because of the lower cluster_factor).
When you lower OICA, eventually the index access costs of the two indexes
(after multiplying with OICA/100 and rounding to the next integer) become
identical and t1_bad is chosen.
Gathering system statistics fixes that. Not because of cpu_costing per se
in this case, but because the new cost formula not only includes
cpu_costing but gathers different values for single block reads and multi
block reads. It achieves the same (or very similar) goal as OICA, but not
by artificially reducing the single block IO cost, but by applying a
realistic, higher cost to multi block IO. Thus you do not get the same
rounding effect, the costs of the two index accesses remain different and
the CBO chooses the "good" index by its lower cost, not the "bad" index by
its lower name.
At 06:47 AM 5/11/2004, you wrote:
>This is in reference to a new article by Jonathan Lewis about the OICA:
>
>http://www.dbazine.com/jlewis18.shtml
>
>It states that if you give Oracle a bad OICA it will choose the wrong
>index. Does anyone know why? I have run across this. I have cases where
>95% of the time Oracle chooses the right index with a given OICA and for
>say 5% or less of queries Oracle chooses the wrong index.
>
>I've noticed Oracle picking bad join orders depending on how I set there.
>Especially with joins of 3 tables or more.
>
>Anyone have any comments or experiences? How much does CPU_COSTING
>eliminate this? We are still in development and have only delivered a
>small quantity of our application, so its not prudent to start messing
>with CPU_COSTING until I get a larger user sample.
regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
