Re: Question about stale statistics

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 20 Apr 2011 10:30:23 -0700 (PDT)
Message-ID: <fd3e9363-c456-4368-9752-787d296bcccb_at_hd10g2000vbb.googlegroups.com>

On 20 Apr., 07:53, Mick <mjms..._at_gmail.com> wrote:
> I am doing some investigation into stale statistics in our database.

You mention then that you manually re-gather statistics on the table - do you mean to indexes or why do you refer then to the table rather then the indexes shown as stale?

Just for clarification: The indexes show STALE_STATS = 'YES', but the date shown in LAST_ANALYZED corresponds to the date you expected the automated statistics job to have run?

Then you should check the STALE_STATS entry of the corresponding table. In principle there is no separate monitoring for stale indexes, the staleness of indexes is determined by the modification to the corresponding table.

However, there is this expression in the 10.2 version of the USER/ALL/ DBA_IND_STATISTICS view for the STALE_STATS column:

.
.
.
when (i.analyzetime < t.analyzetime or
               (((m.inserts + m.deletes + m.updates) > t.rowcnt * 0.1
or
                bitand(m.flags,1) = 1))) then 'YES'

Which means: If the date shown in the LAST_ANALYZED column of the corresponding table is later than that of the index then the index is automatically shown as stale... Which can be confirmed by simply doing a GATHER_TABLE_STATS on a table with indexes once with CASCADE=>TRUE which will show all tables and indexes as not stale, and then immediately repeat the same with CASCADE=>FALSE which will show the same indexes as STALE although they just have been gathered.

Nevertheless the automated job should take care of both tables and indexes that are shown as STALE and therefore *right after the job completed* you shouldn't see what you've described except for the job couldn't process all state objects within the given timeframe.

If the index and the corresponding table show both as STALE then crosscheck  DBA_TAB_MODIFICATIONS for those tables (you might need to flush the monitoring info to get the latest info shown using dbms_stats.flush_database_monitoring_info)

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Apr 20 2011 - 12:30:23 CDT