Re: optimizer_index_cost_adj and optimizer_index_caching

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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: