Re: Default optimizer_index_caching value DUMB ...

A point that is often overlooked with optimizer_index_caching is that it supposed to apply only for index costs during nested loops (although it does seem to be applied for inlist iterators).

Consequently it is not really a measure of what fraction of an index is cached, it is a measure of how many rows I select from a driving table that require me to collect the same data from the inner table repeatedly.

For example:

    If I select 5 rows from table A and then     join to table B, it is possible that I will do a     physical read for the index blocks for tableB     after reading the first row from table A - but     then use the same index blocks for the join to     the next 4 rows from table A.

    In this case, an optimizer_index_caching value     of 80% would be the representative value.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message 
news:1098972594.359149.185370_at_f14g2000cwb.googlegroups.com...



>                I believe you want the proportion of index blocks that

> are in memory out of the total number of index blocks. i.e it isn't the

> probability that a given block within the buffer cache is an index

> block, but the probability of finding an index block in memory as

> opposed to on disk. So here is a possible use for the BCHR.

>

>