自己编写的一个小脚本，找出没有被analyzed的表，插入条数top 5,删除条数 top 5的表，以及delete 超过阀值的表，该脚本对ORACLE性能没有多大影响，放心使用吧。
注意，每当我们对表搜集一次统计信息之后，如果该表没有insert,delete操作，此脚本将无法查询出任何条目
create or replace Function tablespace(segment_owner varchar2, segment_name varchar2)
return varchar2 as
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
luefi number;
luebi number;
lub number;
begin
dbms_space.unused_space(segment_name => segment_name,
segment_owner => segment_owner,
segment_type => 'TABLE',
total_blocks => total_blocks,
total_bytes => total_bytes,
unused_blocks => unused_blocks,
unused_bytes => unused_bytes,
last_used_extent_file_id => luefi,
last_used_extent_block_id => luebi,
last_used_block => lub);
return segment_owner || '.' || segment_name || ' has ' || total_blocks || ' blocks,' ||(total_blocks -
unused_blocks) || ' used,' || unused_blocks || ' unused.';
end tablespace;
运行下面脚本之前需要创建上面的函数
declare
top_n_inserts number :=5;
top_n_deletes number :=5;
top_n_updates number :=5;
delete_rate number :=20;
cursor topinsert is
select *
from (select a.table_owner, a.table_name, sum(a.inserts) inserts
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
group by a.table_owner, a.table_name
order by inserts desc)
where rownum <= top_n_inserts;
-----select top_n_updates---------------
cursor topupdate is
select *
from (select a.table_owner, a.table_name, sum(a.updates) updates
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 updates > 0
group by a.table_owner, a.table_name
order by updates desc)
where rownum <= top_n_updates;
----select top_n_deletes----------------
cursor topdelete is
select *
from (select a.table_owner, a.table_name, sum(a.deletes) deletes
from dba_tab_modifications a, dba_tables b
where a.table_name = b.table_name
and a.table_owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',
'OUTLN', 'TSMSYS', 'MDSYS')
and deletes > 0
group by a.table_owner, a.table_name
order by deletes desc)
where rownum <= top_n_deletes;
----select the detail information about the table------------
cursor monitor is
select b.owner, b.table_name, inserts, deletes, num_rows
from (select table_owner,
table_name,
sum(inserts) inserts,
sum(updates) updates,
sum(deletes) deletes
from dba_tab_modifications
group by table_owner, table_name) a,
dba_tables b
where a.table_owner = b.owner
and a.table_name = b.table_name
and b.owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',
'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',
'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')
and b.last_analyzed is not null;
----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 top_n_insert information-------------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );
dbms_output.put_line('Top ' || top_n_inserts || ' Inserts:');
for v_topinsert in topinsert loop
dbms_output.put_line(tablespace(v_topinsert.table_owner,
v_topinsert.table_name) ||
' Inserted ' || v_topinsert.inserts || ' rows,' ||
'consider gathering statistics');
end loop;
----display the top_n_update informaation----------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Top ' || top_n_updates || ' Updates:');
for v_topupdate in topupdate loop
dbms_output.put_line(tablespace(v_topupdate.table_owner,
v_topupdate.table_name) ||
' Updated ' || v_topupdate.updates || ' rows,' ||
'consider gathering statistics');
end loop;
---display the top_n_deletes information-----------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Top ' || top_n_deletes || ' Deletes:');
for v_topdelete in topdelete loop
dbms_output.put_line(tablespace(v_topdelete.table_owner,
v_topdelete.table_name) ||
' Deleted ' || v_topdelete.deletes || ' rows,' ||
'consider gathering statistics');
end loop;
---display the table which should be shrinked--------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Over the Delete_Rate ' || delete_rate || '%:');
for v_monitor in monitor loop
if (v_monitor.deletes - v_monitor.inserts) > 0 then
if (v_monitor.num_rows + v_monitor.inserts - v_monitor.deletes) = 0 then
dbms_output.put_line(tablespace(v_monitor.owner,
v_monitor.table_name) || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
(v_monitor.deletes - v_monitor.inserts) ||
' rows deleted.consider shirnking the table!!! ');
elsif (v_monitor.deletes - v_monitor.inserts) /
(v_monitor.num_rows + v_monitor.inserts - v_monitor.deletes) >=
delete_rate / 100 then
dbms_output.put_line(tablespace(v_monitor.owner,
v_monitor.table_name) || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
round((v_monitor.deletes -
v_monitor.inserts) /
(v_monitor.num_rows +
v_monitor.inserts -
v_monitor.deletes),
4) * 100 ||
'% of the table has been deleted' ||
',consider shirnking the table!!!');
end if;
else
if (v_monitor.deletes) / (v_monitor.inserts + v_monitor.num_rows) >=
(delete_rate / 100) then
dbms_output.put_line(tablespace(v_monitor.owner,
v_monitor.table_name) || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
round((v_monitor.deletes) /
(v_monitor.num_rows +
v_monitor.inserts),
4) * 100 ||
'% of the table has been deleted' ||
',consider shirnking the table!!!');
end if;
end if;
end loop;
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
end;
/
例子:
SQL> /
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Unalalyzed tables:
ROBINSON.CONS has not been analyzed,consider gathering statistics
ROBINSON.CONS1 has not been analyzed,consider gathering statistics
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Inserts:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Updates:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Deletes:
ROBINSON.TEST has 768 blocks,707 used,61 unused. Deleted 49970 rows,consider gathering statistics
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Over the Delete_Rate 20%:
ROBINSON.TEST has 768 blocks,707 used,61 unused. Has 0 rows now,49970 rows deleted.consider shirnking the table!!!
PL/SQL procedure successfully completed
可以刻使用下面的脚本，下面这个脚本不需要运行上面的函数
set serveroutput on
declare
top_n_inserts number :=5;
top_n_deletes number :=5;
top_n_updates number :=5;
delete_rate number :=20;
cursor topinsert is
select *
from (select a.table_owner, a.table_name, sum(a.inserts) inserts,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
group by a.table_owner, a.table_name,b.num_rows
order by inserts desc)
where rownum <= top_n_inserts;
-----select top_n_updates---------------
cursor topupdate is
select *
from (select a.table_owner, a.table_name, sum(a.updates) updates,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 updates > 0
group by a.table_owner, a.table_name,b.num_rows
order by updates desc)
where rownum <= top_n_updates;
----select top_n_deletes----------------
cursor topdelete is
select *
from (select a.table_owner, a.table_name, sum(a.deletes) deletes,b.num_rows
from dba_tab_modifications a, dba_tables b
where a.table_name = b.table_name
and a.table_owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',
'OUTLN', 'TSMSYS', 'MDSYS')
and deletes > 0
group by a.table_owner, a.table_name,b.num_rows
order by deletes desc)
where rownum <= top_n_deletes;
----select the detail information about the table------------
cursor monitor is
select b.owner, b.table_name, inserts, deletes, num_rows
from (select table_owner,
table_name,
sum(inserts) inserts,
sum(updates) updates,
sum(deletes) deletes
from dba_tab_modifications
group by table_owner, table_name) a,
dba_tables b
where a.table_owner = b.owner
and a.table_name = b.table_name
and b.owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',
'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',
'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')
and b.last_analyzed is not null;
----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 top_n_insert information-------------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );
dbms_output.put_line('Top ' || top_n_inserts || ' Inserts:');
for v_topinsert in topinsert 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,' ||
'consider gathering statistics');
end loop;
----display the top_n_update informaation----------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Top ' || top_n_updates || ' Updates:');
for v_topupdate in topupdate loop
dbms_output.put_line(v_topupdate.table_owner || '.' || v_topupdate.table_name || ' once has ' || v_topupdate.num_rows || ' rows, ' ||
'till now updated ' || v_topupdate.updates || ' rows,' ||
'consider gathering statistics');
end loop;
---display the top_n_deletes information-----------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Top ' || top_n_deletes || ' Deletes:');
for v_topdelete in topdelete loop
dbms_output.put_line(v_topdelete.table_owner || '.' || v_topdelete.table_name || ' once has ' || v_topdelete.num_rows || ' rows, ' ||
'till now deleted ' || v_topdelete.deletes || ' rows,' ||
'consider gathering statistics');
end loop;
---display the table which should be shrinked--------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line('Over the Delete_Rate ' || delete_rate || '%:');
for v_monitor in monitor loop
if (v_monitor.deletes - v_monitor.inserts) > 0 then
if (v_monitor.num_rows + v_monitor.inserts - v_monitor.deletes) = 0 then
dbms_output.put_line(v_monitor.owner || '.' ||
v_monitor.table_name || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
(v_monitor.deletes - v_monitor.inserts) ||
' rows deleted.consider shirnking the table!!! ');
elsif (v_monitor.deletes - v_monitor.inserts) /
(v_monitor.num_rows + v_monitor.inserts - v_monitor.deletes) >=
delete_rate / 100 then
dbms_output.put_line(v_monitor.owner || '.' ||
v_monitor.table_name || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
round((v_monitor.deletes -
v_monitor.inserts) /
(v_monitor.num_rows +
v_monitor.inserts -
v_monitor.deletes),
4) * 100 ||
'% of the table has been deleted' ||
',consider shirnking the table!!!');
end if;
else
if (v_monitor.deletes) / (v_monitor.inserts + v_monitor.num_rows) >=
(delete_rate / 100) then
dbms_output.put_line(v_monitor.owner || '.' ||
v_monitor.table_name || ' Has ' ||
(v_monitor.num_rows + v_monitor.inserts -
v_monitor.deletes) || ' rows now,' ||
round((v_monitor.deletes) /
(v_monitor.num_rows +
v_monitor.inserts),
4) * 100 ||
'% of the table has been deleted' ||
',consider shirnking the table!!!');
end if;
end if;
end loop;
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
end;
/
例子：
SQL> /
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Unalalyzed tables:
ROBINSON.CONS has not been analyzed,consider gathering statistics
ROBINSON.CONS1 has not been analyzed,consider gathering statistics
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Inserts:
ROBINSON.TEST once has 49970 rows, till now inserted 49969 rows,consider gathering statistics
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Updates:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Top 5 Deletes:
ROBINSON.TEST once has 49970 rows, till now deleted 49970 rows,consider gathering statistics
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Over the Delete_Rate 20%:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
PL/SQL procedure successfully completed