Re: Default optimizer_index_caching value DUMB ...

Jonathan Lewis wrote:
> 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).

A relevant metalink note (62286.1) would suggest that Oracle intend the value to be considered for in-list as well as NL.

> 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.

I'm not sure that I said that it was directly a measure of caching, but of the probability that any given index block is cached rather than not.

> 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.
<snip>
> "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.
> >

I've never really liked the idea of favouring NL over other access methods *at a system-wide level*, but if that is what you are going to do using the *system-wide* BCHR to set your weighting doesn't seem entirely unreasonable to me. In other words I'd consider the BCHR as a possible proxy for what optimizer_index_caching actually means.

I'm actually a little surprised by O_I_C as the intention is clearly to affect specific queries that do or don't use specific indexes. Given the existence of the INDEX hint I am a little puzzled at O_I_C which is a broad generalisation and not focussed specifically.

<non-serious>
of course if you were to religiously keep your indexes in their own separate cache then the bchr *for that cache* would be an even better proxy for the correct setting to mangle O_I_C with. :( </non-serious>
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Fri Nov 05 2004 - 09:21:03 CST