Oracle SQL Script to Show Objects That are Missing Statistics

This script shows which tables and indexes are either missing statistics or have stale statistics, and the statistics are not locked (meaning that a DBA has not intentionally blocked the object from getting statistics). The script filters out schemas that are installed with the database to show only user schemas.

This script helps a DBA find objects that need to be analyzed in case they are not using the AUTO method for DBMS_STATS.

The script

The SQL script is as follows.

SELECT 'TABLE' object_type,owner, table_name object_name, last_analyzed, stattype_locked, stale_stats
FROM all_tab_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
and owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
UNION ALL
SELECT 'INDEX' object_type,owner, index_name object_name,  last_analyzed, stattype_locked, stale_stats
FROM all_ind_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
ORDER BY object_type desc, owner, object_name
/

Sample Output

The script produces the below sample output:

OBJECT_TYPE OWNER                          OBJECT_NAME                    LAST_ANALYZED             STATTYPE_LOCKED STALE_STATS
----------- ------------------------------ ------------------------------ ------------------------- --------------- -----------
TABLE       SH                             DR$SUP_TEXT_IDX$I
TABLE       SH                             DR$SUP_TEXT_IDX$K
TABLE       SH                             DR$SUP_TEXT_IDX$N
TABLE       SH                             DR$SUP_TEXT_IDX$R

INDEX       HR                             DEPT_ID_PK                     03-SEP-09                                 YES
INDEX       HR                             DEPT_LOCATION_IX               03-SEP-09                                 YES