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