由於回收站存在大量物件導致查詢表空間使用率較慢

gycixput發表於2017-04-07
原文網址 : http://blog.itpub.net/24585765/viewspace-2136793/
物件
日常監控免不了監控表空間使用情況，經常查詢的檢視是dba_free_space 和dba_data_files,  類似以下語句：
set pagesize 9999 
set pagesize 9999 
set linesize 132 
select
f.tablespace_name,
a.Total_mb,
f.Free_mb,
round(a.total_MB-f.free_mb,2) Used_mb,
round((f.free_MB/a.total_MB)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/

但是有時發現查詢他非常緩慢，這是因為dba_free_space 裡面條目過多，所以查詢起來非常緩慢，為啥這個view裡面條目如此 
之多呢，有時候會發現裡面的extent居然還是連續的， 其實這是因為drop 物件造成的，下面的例子可以展示一下這個場景： 
create table t1 as select * from dba_tables where rownum<1000; 
create table t2 as select * from dba_tables where rownum<1000;  
分別新建兩個表，然後查詢他們的extent情況 
select * from dba_extents where segment_NAME IN ('T1','T2') 

MAOB T1  TABLE USERS 0 4 520 65536 8 4
MAOB T1  TABLE USERS 1 4 528 65536 8 4
MAOB T1  TABLE USERS 2 4 536 65536 8 4
MAOB T1  TABLE USERS 3 4 7184 65536 8 4
MAOB T1  TABLE USERS 4 4 7192 65536 8 4
MAOB T1  TABLE USERS 5 4 7200 65536 8 4


MAOB T2  TABLE USERS 0 4 7208 65536 8 4   <<T2 是接著T1的最後一個extent分配的
MAOB T2  TABLE USERS 1 4 7216 65536 8 4
MAOB T2  TABLE USERS 2 4 7224 65536 8 4
MAOB T2  TABLE USERS 3 4 7232 65536 8 4
MAOB T2  TABLE USERS 4 4 7240 65536 8 4
MAOB T2  TABLE USERS 5 4 7248 65536 8 4

對兩個表進行drop 之後，我們查詢  dba_free_space情況 
select * from dba_free_space where file_id=4 order by block_id

USERS 4 520 65536 8 4
USERS 4 528 65536 8 4
USERS 4 536 65536 8 4  <<<以上3個是T1的前三個extent 都是連續的，並沒有進行合併
USERS 4 640 41943040 5120 4  
USERS 4 7184 65536 8 4
USERS 4 7192 65536 8 4
USERS 4 7200 65536 8 4 <<<以上3個是T1的後三個extent 都是連續的，並沒有進行合併 

USERS 4 7208 65536 8 4
USERS 4 7216 65536 8 4
USERS 4 7224 65536 8 4
USERS 4 7232 65536 8 4
USERS 4 7240 65536 8 4
USERS 4 7248 65536 8 4      <<以上6個是T2的extent，都是連續的，並沒有進行合併
USERS 4 7256 524288 64 4   <<這個和T2的extents是連續的，但是也沒有合併

 這些extents都是保持原樣的出現在了dba_free_space，所以造成條目增多，我們接下來進行purge  
 purge recyclebin;  
我們再次查詢  dba_free_space情況 select * from dba_free_space where file_id=4 order by block_id

USERS 4 520 196608 24 4           <<3個extent 已經合併成一個24blocks的新extent
USERS 4 640 41943040 5120 4
USERS 4 7184 1114112 136 4    <<6個extent 共計72個block連同原有的相鄰extent 裡面的64 個block合併成為了一個新的136block的extent 
dba_free_space條目也大幅減少，所以對於recyclebin裡面有著大量drop的表，尤其是這些表曾經分配了大量的extent，就會出現此類問題。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24585765/viewspace-2136793/，如需轉載，請註明出處，否則將追究法律責任。

相關文章