Oracle 统计信息过期的表重新收集统计信息

查看当前库中统计信息过期的表：

set linesize 150
set pagesize 1000
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT OWNER, TABLE_NAME, PARTITION_NAME, 
       OBJECT_TYPE, STALE_STATS, LAST_ANALYZED 
  FROM DBA_TAB_STATISTICS
 WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL)
   -- STALE_STATS = 'YES' 表示统计信息过期：当对象有超过10%的rows被修改时
   -- LAST_ANALYZED IS NULL 表示该对象从未进行过收集统计信息
   AND OWNER NOT IN ('MDDATA', 'MDSYS', 'ORDSYS', 'CTXSYS', 
                     'ANONYMOUS', 'EXFSYS', 'OUTLN', 'DIP', 
                     'DMSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 
                     'TSMSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                     'OLAPSYS', 'SYSTEM', 'SYS', 'SYSMAN',
                     'DBSNMP', 'SCOTT', 'PERFSTAT', 'PUBLIC',
                     'MGMT_VIEW', 'WK_TEST', 'WKPROXY', 'WKSYS')
   -- 系统用户表的统计信息状态不做统计，根据需求打开或关闭
   AND TABLE_NAME NOT LIKE 'BIN%'
   -- 回收站中的表不做统计
  order by 1,2;

生成统计信息重新收集的SQL：

set linesize 150
set pagesize 1000
SELECT 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || OWNER ||
       ''',tabname=>''' || TABLE_NAME || ''',degree=>4);'
  FROM DBA_TAB_STATISTICS
 WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL)
   AND OWNER NOT IN ('MDDATA', 'MDSYS', 'ORDSYS', 'CTXSYS', 
                     'ANONYMOUS', 'EXFSYS', 'OUTLN', 'DIP', 
                     'DMSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 
                     'TSMSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                     'OLAPSYS', 'SYSTEM', 'SYS', 'SYSMAN',
                     'DBSNMP', 'SCOTT', 'PERFSTAT', 'PUBLIC',
                     'MGMT_VIEW', 'WK_TEST', 'WKPROXY', 'WKSYS')
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND PARTITION_NAME IS NULL
   -- 非分区表统计信息收集
-- AND OWNER='XXXXXX'
-- 可以单独生成某个用户的统计信息过期表重新收集SQL
union all
SELECT 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || OWNER ||
       ''',tabname=>''' || TABLE_NAME || ''',partname=>''' ||
       PARTITION_NAME || ''',degree=>4);'
  FROM DBA_TAB_STATISTICS
 WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL)
   AND OWNER NOT IN ('MDDATA', 'MDSYS', 'ORDSYS', 'CTXSYS', 
                     'ANONYMOUS', 'EXFSYS', 'OUTLN', 'DIP', 
                     'DMSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 
                     'TSMSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                     'OLAPSYS', 'SYSTEM', 'SYS', 'SYSMAN',
                     'DBSNMP', 'SCOTT', 'PERFSTAT', 'PUBLIC',
                     'MGMT_VIEW', 'WK_TEST', 'WKPROXY', 'WKSYS')
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND PARTITION_NAME IS NOT NULL
   -- 分区表统计信息收集
--AND OWNER='XXXXXX'
-- 可以单独生成某个用户的统计信息过期表重新收集SQL
 order by 1;

几种级别的统计信息收集SQL样例：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'EMP',degree=>4);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'EMP',partname=>'p2018',degree=>4);
-- 表统计信息收集和分区表分区统计信息收集
exec DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT',indname=>'IND1',degree=4);
exec DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT',indname=>'IND1'，partname=>'p2018',degree=4);
-- 索引统计信息收集和分区索引统计信息收集
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',degree=>4);
-- 用户统计信息收集
exec DBMS_STATS.GATHER_DATABASE_STATS(degree=>4);
-- 全库统计信息收集

-- 增加degree参数增加并行度，加快收集速度
-- 需要考虑当前的主机负载和CPU空闲程度

[TOC]