找出需要分析的表以及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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL如何通過分析binlog日誌找出操作頻繁的表MySql
- 設定SAP標準報表顯示介面預設值的方式
- antdesign的表單中的下拉框設定預設值BUG處理
- echarts調整圖表和標題的距離，以及設定高度Echarts
- 圖表外掛Highcharts的動態化賦值，實現圖表資料的動態化設定顯示賦值
- 為什麼你的企業需要線上工時表？
- 表屬性設定
- 透過Github同步你的VScode設定GithubVSCode
- 前端程式設計師能力不足？表現在哪幾點，你需要加強的地方！前端程式設計師
- 如何根據MLOG$表的CHANGE_VECTOR$$找出被更新的列
- QSpinBox樣式表設定
- Python資料分析入門（十六）：設定視覺化圖表的資訊Python視覺化
- MySQL不支援DELETE使用表別名?MySqldelete
- HBase的表結構你設計得不對！
- 檢查及設定合理的undo表空間
- openGauss核心分析（九）：資料庫表的建立過程資料庫
- Go 通過反射的reflect設定實際變數的值Go反射變數
- 面世超過一年，英特爾傲騰的表現如何?
- PowerApps 的表單中人員下拉選單怎麼設預設值APP
- 如何找出被鎖定的行
- MySQL表名不區分大小寫的設定方法MySql
- PostgreSQL的表檔案以及TOAST表檔案對應關係SQLAST
- 單表的更新UPDATE和刪除記錄DELETE（二十六）delete
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- 艾偉也談專案管理，找出軟體開發過程中的BUG，你需要火眼金睛專案管理
- 對於Redis中設定了過期時間的Key，你需要知道這些內容Redis
- MySQL設定表自增步長MySql
- 積木報表設定時間
- Linux 設定靜態路由表Linux路由
- mssql sqlserver update delete表別名用法簡介SQLServerdelete
- mysql支援跨表delete刪除多表記錄MySqldelete
- dubbo 超時設定和原始碼分析原始碼
- Sqlserver update\delete用inner join關聯，會update\delete關鍵字後面的表關聯到的行SQLServerdelete
- 資料庫表的基本操作和編碼格式設定資料庫
- python之matplotlib製作基礎圖表以及圖例，標註，marker，中文設定Python
- MySQL單表最大記錄數不能超過多少？MySql
- jquery 透過顯示值 設定 select boxjQuery
- 原生js設計表單驗證外掛的思路分析JS
- 基於 Formily 的表單設計器實現原理分析 ORM