Re: Warning: Built in 10g Stats gather is broken

mccmx_at_hotmail.com wrote:
>> This is precisely why I have a scheduled job to check the stats to see
>> how far off target they are. If they are off target, I recalculate
>> stats. For me, I compare the number of BLOCKS from DBA_TABLES to the
>> number of BLOCKS from DBA_SEGMENTS. I realize this does not address all
>> issues where the stats could have diverged too far from reality, but it
>> works in my system. To see the script I use, visit my web site (URL
>> below). You will find "fix_stats.sql" in the Scripts section.
>>

> 
> Did you implement that solution because you found that the built in job
> wasn't working properly or because you prefer to use your own method..?
> 
> Matt
> 

Because the built-in job wasn't working properly. More accurately, Oracle was not sensing that the table had been modified "significantly". My tables would be over 100GB in size, but the stats showed the table was only 1 block, not many, many blocks. This forced a full table scan of a 100GB+ table. Talk about a performance killer when an index scan is much more efficient!

On a side note, Oracle 10.2.0.2 seems to be acting better as I do not see this discrepancy nearly as much as I did in 10.1.0.x or 10.2.0.1.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown