Re: optimizer_index_cost_adj and optimizer_index

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 05 Mar 2004 16:50:09 -0700
Message-Id: <6.0.0.22.2.20040305163944.030d74b0@pop.centrexcc.com>

Notes in-line.

At 03:29 PM 3/5/2004, you wrote:

>Notes in-line.
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>
>I share your opinion about the knee-jerk suggestions for
>a value for the two optimizer_index adjust parameters,
>but I think they can be a highly appropriate consideration.
>
>Ideally, you should calibrate your hardware before running
>the database to get a baseline value for o_i_c_a (It looks as
>if the pacakge on www.iozone.com might be the ideal tool)
>and then design your database perfectly. Tim's interpretation
>of using the o_i_c_a as a way of improving Oracle's understanding
>of the relative time for single and multiple block reads seems to
>be a very reasonable step forward in correcting the optimizer,
>so I am in favour of using it, rather than trying to fiddle lots
>of separate SQL statements after the event. After all, it's doing
>exactly what system stats are trying to do.

That is how I interpret it too, which is why I prefer using system_stats over changing o_i_c_a. There is a subtle difference between the way o_i_c_a and system_stats achieve the apparently same goal:

o_i_c_a lowers the cost of index accesses compared to the baseline whereas

system_stats increase the cost of full scans compared to the baseline

I have not figured out yet if that can lead to differences in plan compositions or if it is guaranteed that the relative costs of all plan components remain the same when comparing a plan stemming from having o_i_c_a=25 (i.e. single reads cost are 1/4 of multi read costs) vs. having system statistics where mreadtim = 4*sreadtim.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://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 Sat Mar 06 2004 - 00:43:15 CST

Re: optimizer_index_cost_adj and optimizer_index_caching