显示缺少统计信息的对象的脚本

Oracle统计信息缺失查询

最新推荐文章于 2025-09-07 22:30:42 发布

翻译最新推荐文章于 2025-09-07 22:30:42 发布 · 966 阅读

Oracle DB 同时被 2 个专栏收录

772 篇文章

订阅专栏

MOS文章翻译

86 篇文章

订阅专栏

本文提供了一种查询Oracle中缺少统计信息的表和索引的方法。通过SQL查询从all_tab_statistics及all_ind_statistics视图中筛选出未分析或统计信息过期的对象。排除了系统保留的schema，并对结果按对象类型、所有者和对象名称进行了排序。

来源于：
Script to Show Objects That are Missing Statistics (文档 ID 957993.1)

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

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