Statistics Freshness Check

Find Candidates for Statistics Gathering

Anything that has more than 10% changes (inserts+deletes+updates) since last analyze should probably be analyzed again. This can be checked Oracle's Table Monitoring feature. This query will show a list of database tables with the most changed first.

Verify Table Monitoring is On

Survey table monitoring

SELECT MONITORING, 
       COUNT(*)
  FROM USER_TABLES 
GROUP BY MONITORING;

Turn table monitoring on for 1 table

ALTER TABLE [TABLE_NAME] MONITORING;

Turn table monitoring on for all tables that need it

DECLARE
  my_sql VARCHAR2(256);
BEGIN
FOR c1 IN (SELECT TABLE_NAME 
           FROM USER_TABLES 
           WHERE MONITORING='NO')
LOOP
  my_sql := 'ALTER TABLE ' || c1.TABLE_NAME || ' MONITORING');
  execute immediate my_sql;
  
END LOOP;
END;
/

Updating Monitoring Information

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

View data about monitored tables

This query will give information about table monitoring since the table's last analyze.
-- Statistics Freshness
-- David Mann    http://ba6.us
-- Display Change % and details for monitored tables. These tables are
-- candidates for statistics gathering once they hit the STALE_PERCENT 
-- threshold defined in sys.optstat_hist_control$
SELECT ATM.TABLE_OWNER,  
       ATM.TABLE_NAME,  
       ATM.PARTITION_NAME as Part,
       ATM.SUBPARTITION_NAME as SubPart, 
       AT.NUM_ROWS as Analyzed_NumRows,  
       ATM.INSERTS,  
       ATM.UPDATES,  
       ATM.DELETES,  
      (ATM.INSERTS+ATM.UPDATES+ATM.DELETES) as Changes,  
      ROUND(((ATM.INSERTS+ATM.UPDATES+ATM.DELETES)/NUM_ROWS)*100,2) as STALE_PCT,  
      (SELECT LAST_ANALYZED FROM ALL_TABLES WHERE OWNER=ATM.TABLE_OWNER and TABLE_NAME=ATM.TABLE_NAME) as LAST_ANALYZED  
 FROM ALL_TAB_MODIFICATIONS ATM,   
      ALL_TABLES AT
WHERE ATM.TABLE_OWNER=AT.OWNER  
  AND ATM.TABLE_NAME=AT.TABLE_NAME  
  AND ATM.TABLE_NAME NOT LIKE 'BIN$%' 
  AND ATM.TABLE_OWNER NOT IN ('SYS','SYSTEM')  
  AND NUM_ROWS  0  
ORDER BY 10 DESC;
Note that there are some quirks with updates to _TAB_MODIFICATIONS: o Stats flush to _TAB_MODIFICATIONS are noted as every 3 hours in 762738.1 o If users modify an object in another schema, those DML counts do not show up in USER_TAB_MODIFICATIONS

References

Oracle 10.2 Documentation - Determining Stale Statistics