Re: how to identify non-system and sys tables that have out of date statistics.

Mark D Powell wrote:
> The default behavior of dbms_stats is however version dependent since a
> couple minor changes exist between the 8.1, 9.2, and the 10g versions.
> With 10g table monitoring is a default database behavior. On 9.2 it
> isn't, however the 9.2 Supplied packages manual states that
>
> >>
> GATHER STALE: Gathers statistics on stale objects as determined by
> looking at the *_tab_modifications views. Also, return a list of
> objects found to be stale.
> <<
>
> Manually you can find tables that might be stale by comparing the
> actual row count to the dba_tables.num_rows value. A large percentage
> difference could mean the statistics need updating. You can also
> compare dba_tab_columns statistics against actual counts of num rows,
> distinct values, etc.. to look for tables that might need to have their
> statistics updated.
>
> You need to check the manual for your version of Oracle and make the
> necessary database parameter and/or package parameter choices for your
> application needs.
>
> HTH -- Mark D Powell --

With 9.2 I have set the desired tables to MONITORING (using 'alter table .... monitoring;') and, using dbms.stats.gather_schema_stats and the 'GATHER STALE' option the statistics are updated when Oracle deems it necessary. I do not rely upon what the documentation states as the behaviour for dbms_stats.gather_schema_stats (my two cents).

Setting tables to MONITORING takes very little time, and the time saved using the 'GATHER STALE' option to dbms_stats.gather_schema_stats is worth the effort (again, in my opinion).

David Fitzjarrell Received on Tue Feb 21 2006 - 14:31:56 CST