Re: Question about stale statistics
Date: Wed, 20 Apr 2011 05:49:28 -0700 (PDT)
Message-ID: <5b5164b7-a810-46b1-877d-340867f25211_at_dr5g2000vbb.googlegroups.com>
On Apr 20, 1:53 am, Mick <mjms..._at_gmail.com> wrote:
> 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.
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 reanalyzed 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 reanalyzed 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 -- Received on Wed Apr 20 2011 - 07:49:28 CDT