Re: Question about stale statistics

From: joel garry <joel-garry_at_home.com>
Date: Wed, 20 Apr 2011 09:28:42 -0700 (PDT)
Message-ID: <ed0daff7-9c31-4035-9726-ac2098dee620_at_r4g2000prm.googlegroups.com>

On Apr 19, 10:53 pm, 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.

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/B19306_01/server.102/b14211/stats.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

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

Received on Wed Apr 20 2011 - 11:28:42 CDT