Tag Archives: Stale statistics

Useful scripts: Table list with stale statistics

This script is intended for daily use to get tables where percentage of changed records is above 10%:

SELECT DT.OWNER,
       DT.TABLE_NAME,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM   dba_tables dt, ALL_TAB_MODIFICATIONS atm
WHERE      DT.OWNER = ATM.TABLE_OWNER
       AND DT.TABLE_NAME = ATM.TABLE_NAME
       AND NUM_ROWS > 0
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 desc

Also you can avoid system schemas by adding:

AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OSMMON','PERFSTAT')
%d такие блоггеры, как: