Re: Large Tables, Bad Indexes and Fake Statistics

I would like to publicly commend Don on his quest for root cause.
Many people would have tried setting things like
optimizer_index_cost_adj, etc. to get the optimizer to pick up the
index.  While some of these things may result in the desired outcome,
they really are not the solution.  Out-of-range low/high values is a
common cause in this type of scenario and probably goes misdiagnosed
9/10 times.  This behavior often shows up in transient or similar
tables where data is continuously rolled in/off and stats become non
representative.

In this case the 10053 trace was used, but if one is aware of this
issue and knows their data well enough, the low/high values columns in
user_tab_col_statistics can also be used as a first level of triage.
I would like to emphasize again, and this case is a prime example, if
the optimizer has representative stats on the data, the optimal plan
is generally chosen.

Again, kudos Don.

On 8/17/07, Don Seiler <don@xxxxxxxxx> wrote:
> I just wanted to follow-up on this issue.  Thanks to Wolfgang
> Breitling, I've got the issue resolved.  I wrote a summary on my blog,
> http://ora.seiler.us/2007/08/dr-statslove-or-how-i-learned-to-stop.html
>
> Hopefully it makes sense.  Try not to laugh too hard at my expense.
>
> Thanks again to Wolfgang!
>
> Don.
>
-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Other related posts: