I am doing some investigation into stale statistics in our database.

We are running Oracle 10.2.0.3.0

Using the sql below it returns several indexes :

select INDEX_NAME, TABLE_NAME, LAST_ANALYZED, STALE_STATS
from dba_ind_statistics
where TABLE_OWNER = 'BWGIS'
and STALE_STATS = 'YES'
order by LAST_ANALYZED

The job GATHER_STATS_JOB has been running on our system and this
is proven by the fact that the last_analyzed date corresponds with the
time that this job is scheduled to run.

If I manually gather statistics for a particular table using
dbms_stats.gather_table_stats then the stats for that table
no longer show as stale.

Does this mean that the GATHER_STATS_JOB does not do all the
work that you would expect?

As a result of this would it be better for me to run the statistics
manually
myself?

Thank You in advance.

On Apr 20, 1:53*am, Mick <mjms... (AT) gmail (DOT) com> wrote:
Note - I tried to post earlier but Google reported an error so if
duplicate posts show up, my apologies.

I ran your query and got three hits on one of my systems. The last
analyzed was just two days ago for two of the three indexes. I think
all this really means is that since the last analyze was done that
enough changes have taken place for the index to qualify to be re-
analyzed have taken place.

Now if I run the query again tomorrow and the same three indexes show
up with the same last_analyzed dates then I would wonder why the job
did not re-analyze the objects though I cannot remember if the indexes
will be done if the table does not qualify to have its statistics re-
analyzed yet. None of the tables for the indexes I found have stale
statistics. I will have to try to remember to check on their status
but I do not expect to see the indexes re-analyzed till the table is.

But unless you notice a performance issue related to these objects
there may be no need to take any manual action. The fact the
statistics are stale does not actually equate to the statistics being
bad. It just means the statistics might not be reflective of the
data.

HTH -- Mark D Powell --

On Apr 19, 10:53*pm, Mick <mjms... (AT) gmail (DOT) com> wrote:
Checked for stattype_locked in dba_tab_statistics. I see what you
described on the related indices where I've locked the table
statistics on the related table, though the last_analyzed was the
locking date.

I'm unsure what manual gather stats does with locked statistics, I
would expect it to err. You might want to look at a backup from
before the manual gather - I wonder if that statistics information is
in an export?

I know in the past I've stumbled over not deleting stats before
getting new ones: http://download.oracle.com/docs/cd/B...ats.htm#i41788
(and people have blogged about that issue).

Ohhhh, just noticed MOS Bug 10121689: LAST_ANALYZED IN
DBA_TAB_COL_STATISTICS NOT UPDATED FOR FUNCTION-BASED INDEX sounds
just like what you describe, if you have an FBI. Even in 10.2.0.5 and
some 11's.

jg
--
@home.com is bogus.
“I love my McJob” - in slideshow for McDonalds hiring day.

On 20 Apr., 07:53, Mick <mjms... (AT) gmail (DOT) com> wrote:
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 cross-
check 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-.../dp/1430226684