Re: Default optimizer_index_caching value DUMB ...

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1098930221.253078_at_yasure...
> 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?
> --

Hi Daniel,

Although I agree that the defaults are perhaps not great, there really isn't any default that is going to be right most of the time. The thing I like about the defaults is that it's relatively easy to calculate and determine why the optimizer has come up with a given cost and take action as appropriate.

A couple of points with requesting a change to the defaults:

1. I would say many (most ?) sites have these values at the default values, for better or for worse, it's just the nature of a default value in Oracle. If Oracle were to suddenly change the default values to "a better guess", it could have a significant impact, for better or for worse, for all those sites currently happily sitting on the default values. A dangerous proposition.
2. Note there's really no equivalent "table" optimizer_table_caching parameter, which means Oracle is perhaps overstating the cost of FTS when/if significant portions of a table may be cached as well. This point is often forgotten by those who try desperately to ensure the optimizer gets the index costs as accurate as possible. I kinda feel sorry for the poor FTS when people neglect them and assume that all FTS are performed on non-cached blocks. I mean, we do have the KEEP pool for a reason right ? Then again, cached blocks can actually hurt FTS performance as it can impact the effective multiblock read. We live in an imperfect world...
3. Interesting how the caching characteristics of all indexes and tables differ and yet we only have a couple of "global" parameters to influence the optimizer. Not surprising when the caching characteristic of any given segment can change over time or during different periods of the day.

Rather than Oracle changing the defaults, I would recommend that "we" set them to as appropriate value as we can in our differing environments. Heck, there's go to be something left for us DBAs to do in these days of self tuning databases :)

Cheers

Richard Received on Thu Oct 28 2004 - 09:06:11 CDT