Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to identify non-system and sys tables that have out of date statistics.

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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 21 Feb 2006 09:01:05 -0800
Message-ID: <1140541265.651460.281150@g14g2000cwa.googlegroups.com>


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 -- Received on Tue Feb 21 2006 - 11:01:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US