找出需要分析的表以及delete超過閥值(你設定)的表
自己編寫的一個小指令碼，找出沒有被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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16978544/viewspace-705698/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle SYSAUX表空間使用率超過警戒閥值OracleUX
- Oracle 找出需要建立索引的表Oracle索引
- Oracle 找出需要回收高水位的表Oracle
- Disk_monitor_超過閥值就報警
- 察看linux系統閥值設定Linux
- MySQL如何通過分析binlog日誌找出操作頻繁的表MySql
- 利用Oracle threshold(度量閥值)監控表空間Oracle
- “閥值”與“閾值”的區別
- 設定InfoObject的過濾值Object
- 設定SAP標準報表顯示介面預設值的方式
- ORACLE—DELETE表後的恢復Oracledelete
- antdesign的表單中的下拉框設定預設值BUG處理
- echarts調整圖表和標題的距離，以及設定高度Echarts
- 超過 130 個你需要了解的 vim 命令
- 超過130個你需要了解的vim命令
- delete與delete[]需要注意的地方delete
- 透過圖表分析oracle的parallel效能OracleParallel
- 通過圖表分析oracle的parallel效能OracleParallel
- （轉）超過 130 個你需要了解的 vim 命令
- 管理員能否revoke表擁有者delete表的許可權delete
- 【COLUMN】設定表欄位預設值僅對未來生效
- 設定為disabled不可用的表單元素的value值無法傳送
- memcached原始碼分析-----雜湊表基本操作以及擴容過程原始碼
- RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表delete
- 表屬性設定
- Oracle Delete表恢復(ODU)Oracledelete
- delete 與全表掃描delete
- 前端程式設計師能力不足？表現在哪幾點，你需要加強的地方！前端程式設計師
- oracle的表空間、分割槽表、以及索引的總結Oracle索引
- 通過POWERDESIGER指令碼批量設定表主鍵指令碼
- 圖表外掛Highcharts的動態化賦值，實現圖表資料的動態化設定顯示賦值
- 度量閥值預警總結
- 分割槽表中的maxvalue引數設定
- oracle恢復表delete/truncate/drop的方法總結Oracledelete
- 臨時表空間的建立、刪除，設定預設臨時表空間
- lenovothinkpadt460sopensuselinux保護電池設定電池充電閥值ThinkPadLinux
- HBase的表結構你設計得不對！
- sql server 儲存過程中使用變數表,臨時表的分析(續)SQLServer儲存過程變數