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