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_at_seiler.us> 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