Re: Default optimizer_index_caching value DUMB ...

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 28 Oct 2004 18:02:41 -0700
Message-ID: <1099011701.34775@yasure>

Richard Foote wrote:

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

While I can't disagree with anything you have said ... I also know that I have never set them to 50:50 and not seen an improvement. And that when I got done tuning they were neither 0:100 nor 50:50. Still all-in-all I think it was Tom Kyte that suggested going to 50:50 and tuning from there and to me that is a better default. Something dynamic would, of course, be better.

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

Received on Thu Oct 28 2004 - 20:02:41 CDT