Re: OICA and Oracle choosing the wrong index

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 11 May 2004 09:45:21 -0600
Message-Id: <6.1.0.6.2.20040511093320.02aaf198@pop.centrexcc.com>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 11 2004 - 10:46:03 CDT