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?
>
> 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?
>
> Maybe I'm missing something here, but I find that with the defaults I
> see all sorts of dumb hash joins where a nested loops (ie, RBO) path
> is much better. What numbers do you find work well and push the CBO
> into the NL route much more?
>
> Thanks in advance,
>
> Domenic

Best discussion of that I've seen is in Chapter 7 of Jonathan Lewis' "Practical Oracle8i".

I wonder is we could cajole Jonathan to give an update for 9i or 10g? /Hans Received on Tue Oct 26 2004 - 09:11:09 CDT