【Oracle】收集统计信息和找出统计信息过期的表
参考文档：
目录
一、收集统计信息
--查询统计信息状态是否被锁
--oracle 11g进行MOVE TABLE/ALTER INDEX REBUILD/MOVE TABLESPACE后，会导致统计信息被锁，无法进行统计信息收集
select owner, table_name, stattype_locked from dba_tab_statistics a
where table_name='SDB_TB_WIP_SPLIT_MERGE'
and a.stattype_locked in ('ALL','DATA','CACHE')
and owner = 'tableowner';
--解锁统计信息
BEGIN
dbms_stats.unlock_table_stats(ownname => 'tableowner',
tabname => 'table1');
END;
/
--锁定统计信息
BEGIN
dbms_stats.lock_table_stats(ownname => 'tableowner',
tabname => 'table1');
END;
/
--收集统计信息
--在sqlplus执行
BEGIN
dbms_stats.gather_table_stats(ownname => 'tableowner',
tabname => 'table1');
END;
/
二、如何找出统计信息过期的表
1. 方式一： 通过DBA_TAB_STATISTICS查看表统计信息是否过期
数据库判断统计信息是否过期（stale）的依据是当前表的DML操作的记录数是否超过表数据量的10%，如果超过10%这个阈值，统计信息就被认为是过期了。
Oracle就认为这些表应该需要重新收集一次统计信息了。
--step1. 刷新数据库监控信息：
--出于性能原因，当实际修改发生时，Oracle数据库不会立即填充这些视图。刷新监控来填充这些视图有最新的信息
BEGIN
dbms_stats.flush_database_monitoring_info;
END;
/
--查看哪些表的统计信息过期
SELECT S.OWNER
, S.TABLE_NAME
, S.PARTITION_NAME
, S.OBJECT_TYPE
, S.STALE_STATS
, S.LAST_ANALYZED
FROM DBA_TAB_STATISTICS S
INNER JOIN DBA_TABLES T ON S.OWNER= T.OWNER AND S.TABLE_NAME =T.TABLE_NAME
WHERE (S.STALE_STATS = 'YES' OR S.LAST_ANALYZED IS NULL)
-- STALE_STATS = 'YES' 表示统计信息过期：当对象有超过10%的ROWS被修改时
-- LAST_ANALYZED IS NULL 表示该对象从未进行过统计信息收集
AND T.TEMPORARY ='N' --排除临时表
AND S.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 S.TABLE_NAME NOT LIKE 'BIN%'
-- 回收站中的表不做统计
ORDER BY OWNER,TABLE_NAME;
2. 方式二： 通过DBA_TABLES的LAST_ANALYZED查看表统计信息是否过期（仅供参考）
--主要是通过上一次的收集统计信息的时间来判断。其实根据收集统计信息的时间来判断统计信息是否过时，其实有一定的局限性和不合理性。
--时间维度来判断太过粗糙。例如对于维表，或者一些数据长期没有DML操作的表来说，以LAST_ANALYZED来判断的话，就有失公允。
SELECT OWNER, TABLE_NAME, LAST_ANALYZED
FROM DBA_TABLES
WHERE LAST_ANALYZED < SYSDATE - 7;
3. 方式三： 通过DBMS_STATS.GATHER_SCHEMA_STATS查看表统计信息是否过期
--找出某个SCHEMA下统计信息过期的对象
set serveroutput on;
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname => '&owner',
options => 'LIST STALE',
objlist => mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).ownname || '.' || mystaleobjs(i).objname);
end loop;
end;
/
--找出整个数据库中统计信息过期的对象
set serveroutput on;
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist => ObjList,
options => 'LIST STALE');
FOR i in ObjList.FIRST .. ObjList.LAST LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
--找出没有统计信息的表
set serveroutput on;
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname => '&owner',
options => 'LIST EMPTY',
objlist => mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).ownname || '.' || mystaleobjs(i).objname);
end loop;
end;
/
4. 方式四： 通过DBA_TAB_MODIFICATIONS来判断表统计信息是否过期
--DBA_TAB_MODIFICATIONS使用来记录表的DML操作，依靠里面的信息确定统计信息是否陈旧。
SELECT M.TABLE_OWNER,
'NO' AS IS_PARTITION,
M.TABLE_NAME AS NAME,
M.INSERTS,
M.UPDATES,
M.DELETES,
M.TRUNCATED,
M.TIMESTAMP AS LAST_MODIFIED,
ROUND((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(T.NUM_ROWS,0),2) AS EST_PCT_MODIFIED,
T.NUM_ROWS AS LAST_KNOWN_ROWS_NUMBER,
T.LAST_ANALYZED
FROM DBA_TAB_MODIFICATIONS M,
DBA_TABLES T
WHERE M.TABLE_OWNER=T.OWNER
AND M.TABLE_NAME=T.TABLE_NAME
AND M.TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND ((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(T.NUM_ROWS,0) > 10 OR T.LAST_ANALYZED IS NULL)
UNION
SELECT M.TABLE_OWNER,
'YES' AS IS_PARTITION,
M.PARTITION_NAME AS NAME,
M.INSERTS,
M.UPDATES,
M.DELETES,
M.TRUNCATED,
M.TIMESTAMP AS LAST_MODIFIED,
ROUND((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(P.NUM_ROWS,0),2) AS EST_PCT_MODIFIED,
P.NUM_ROWS AS LAST_KNOWN_ROWS_NUMBER,
P.LAST_ANALYZED
FROM DBA_TAB_MODIFICATIONS M,
DBA_TAB_PARTITIONS P
WHERE M.TABLE_OWNER=P.TABLE_OWNER
AND M.TABLE_NAME=P.TABLE_NAME
AND M.PARTITION_NAME = P.PARTITION_NAME
AND M.TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND ((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(P.NUM_ROWS,0) > 10 OR P.LAST_ANALYZED IS NULL)
ORDER BY 8 DESC;
5. 方式五： 通过其他手工方式来判断表统计信息是否过期
--1). 检查表的行记录信息
SELECT OWNER, TABLE_NAME, OBJECT_TYPE, NUM_ROWS
FROM DBA_TAB_STATISTICS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP';
SELECT COUNT(*) FROM SCOTT.EMP;
--2). 检查DBA_TAB_COL_STATISTICS中表的列的NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,NUM_NULLS值，然后将其与实际值对比，从而判断统计信息
--这种方法虽然最准确，但是比较繁琐耗时，而且有些表的统计信息的采样比例可能不是100%，所以实际操作实施起来也比较麻烦
SELECT COUNT(DISTINCT COLUMN_NAME), MIN(COLUMN_NAME), MAX(COLUMN_NAME) FROM OWNER_NAME.TABLE_NAME;
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE OWNER='&OWNER' AND TABLE_NAME='&TABLE_NAME';
浙公网安备 33010602011771号