Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Large Tables, Bad Indexes and Fake Statistics

Re: Large Tables, Bad Indexes and Fake Statistics

From: Greg Rahn <greg_at_structureddata.org>
Date: Mon, 20 Aug 2007 17:53:30 +0300
Message-ID: <a9c093440708200753u40d68e25q8d9725ec5111306a@mail.gmail.com>


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
Received on Mon Aug 20 2007 - 09:53:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US