Re: optimizer_index_cost_adj and optimizer_index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Mar 2004 22:29:30 -0000
Message-ID: <02cb01c40301$53703650$7002a8c0@Primary>

Notes in-line.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com>

: I already said in an earlier post (possibly on a different subject) that I
: am biased against changing O_I_C and particularly O_I_C_A, especially in
: light that it has gained almost silver bullet status. Every post in a
: metalink forum or on c.d.o.s about performance of a sql gets at least one
: response suggesting lowering O_I_C_A as a solution. For me it is just
: another flavour of the folly that views all FTS as evil and index access
as
: good.

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.

Of course, the 'idle' figures for response times don't match the 'workload' figures for response times, so you need to worry about modifying o_i_c_a on the fly, just as you need to modify the system stats on the fly.

: OK, I step down from the soap box. I do not have experience with Oracle 9
: in a production environment, so I have not had a chance to test it for
: real, but Joze Senegacnik (joze.senegacnik_at_snt.si) seems to have had
: several clients with Oracle 9 and reports good experience with it.

: As for "blowing up in ones face", if you think system_stats may be
: dynamite, I would consider O_I_C_A<100 nitroglycerine.

I prefer system stats to o_i_c_a because system stats are intuitively more recognisable. However, once you get system stats working, you then realise that o_i_c_a can be interpreted differently - it's reasonably valid as a measure of the fraction of single block reads that will actually turn into real read requests. Unfortunately, there is no equivalent measure for multiblock reads.

In a similar vein, the optimizer_index_caching parameter does represent a valid fix to an error in the basic CBO assumptions; and it's one that is not addressed at all by system stats.

Roll on v10+.. one day the optimizer will use the recent cache statistics of every object in your query on a segment by segment basis to produce an execution plan - an may throw in a little dynamic sampling on the side, just in case; then we'll never be able to write a reproducible test case ever again.

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 - 01:29:10 CST

Re: optimizer_index_cost_adj and optimizer_index_caching