Re: Default optimizer_index_caching value DUMB ...

Domenic wrote:
> Why does Oracle use such silly default values for
> optimizer_index_caching and optimizer_index_cost_adj?
>
> With optimizer_index_caching set to ZERO, we're to assume no index
> blocks are ever in memory? This is simply untrue. Why can't Oracle
> dynamically adjust this setting based on the ratio/percentage of
> index:data blocks in the cache -- maybe via v$bh?

How often do you want to invalidate execution plans for your SQL? In addition I don't believe the ratio you are suggesting is actually the one you want. 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.

> And, with optimier_index_cost_adj set to 100, an index scan is
> supposed to be as expensive as a FTS? -- this just doesn't make sense
> either. Can't Oracle look at the ratio between
> db_file_scattered_reads and db_file_sequential_reads to figure out
the
> correct value?

Because as of 9 it already has a mechanism to do this. Its called system statistics. I believe these get collected automatically in 10g so Mark has already at least half answered Daniel's request!

Jonathan Lewis has an article on system statistics being potentially unfortunate for you on his site that in passing deals with the problem that O_I_C_A and O_I_C are both system wide measures and , typically, it is process level problems that we wish to deal with. Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Thu Oct 28 2004 - 09:09:54 CDT