关于统计信息两个脚本（OVER THE Change RATE TABLES/the unanalyzed table）

本文提供了一种使用PL/SQL过程来监控Oracle数据库中未分析表及表变更率的方法。通过设置阈值为10%，该过程能有效识别出频繁变动且未进行分析的表，并建议收集这些表的统计信息以提高数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

可以使用如下查看：
for heap tables

set serveroutput on
declare

-----select OVER THE Change RATE TABLES---------------
cursor overchangerate is
select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows
from dba_tab_modifications a, dba_tables b
where a.table_name = b.table_name
and table_owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',
'OUTLN', 'TSMSYS', 'MDSYS')
and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1
or a.table_name = b.table_name
and table_owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',
'OUTLN', 'TSMSYS', 'MDSYS')
and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1
or a.table_name = b.table_name
and table_owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',
'OUTLN', 'TSMSYS', 'MDSYS')
and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ;

----select the unanalyzed table---------------
cursor nullmonitor is
select owner, table_name
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',
'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',
'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')
and last_analyzed is null;

begin
dbms_output.enable(1000000);

----flush the monitorring information into the dba_tab_modifications
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

----display the unanalyzed table--------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

dbms_output.put_line('Unalalyzed tables:');
for v_null in nullmonitor loop
dbms_output.put_line(v_null.owner || '.' || v_null.table_name ||
' has not been analyzed, consider gathering statistics');
end loop;

----display the information-------------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );
dbms_output.put_line('Over the Change_Rate 10%:');
for v_topinsert in overchangerate loop
dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||
'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||
' rows. consider gathering statistics');
end loop;
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
end;
/