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

I agree ... at least as far as to say that these defaults are ridiculous and Mark and his team need to change them in the next release.

How about it Mark?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)