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

mccmx_at_hotmail.com wrote:
> It looks as though Oracle's built in Stats gather process doesn't work.
>
> The table change information that should be written to
> USER_TAB_MODIFICATIONS isn't being updated correctly. So when the
> nightly job comes along to analyze the tables, it will miss some tables
> because they don't appear in this view. Beware.
>
> Check out Bug 5557078 on Metalink.
>
> It has been filed against 10.1 but we are having the same problem on
> 10.2.
>
> How the hell did this test this functionality....!
>
> Matt
>

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.

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