Re: optimizer_index_cost_adj and optimizer_index_caching

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


: 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@xxxxxx) 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@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: