SQL查詢table或index異常增長問題 - space_usage (show_space)

前階段遇到的一個問題，一個Index在幾分鐘內忽然增長了6G左右，且增長的
這個block都是未格式化的塊，詳細情況見 ：
http://www.itpub.net/thread-1349570-1-3.html

 

SQL> show serveroutput
serveroutput OFF
SQL>
SQL> set serveroutput on;
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> 
declare
    l_fs1_bytes number;
    l_fs2_bytes number;
    l_fs3_bytes number;
    l_fs4_bytes number;
    l_fs1_blocks number;
    l_fs2_blocks number;
    l_fs3_blocks number;
    l_fs4_blocks number;
    l_full_bytes number;
    l_full_blocks number;
    l_unformatted_bytes number;
    l_unformatted_blocks number;
  begin
      dbms_space.space_usage('DFMS',
         'IDX1_WIP_D_WO_DETAIL_COMID',
         'INDEX',
         fs1_bytes=> l_fs1_bytes,
         fs1_blocks         => l_fs1_blocks,
         fs2_bytes          => l_fs2_bytes,
         fs2_blocks         => l_fs2_blocks,
         fs3_bytes          => l_fs3_bytes,
         fs3_blocks         => l_fs3_blocks,
         fs4_bytes          => l_fs4_bytes,
         fs4_blocks         => l_fs4_blocks,
         full_bytes         => l_full_bytes,
         full_blocks        => l_full_blocks,
         unformatted_blocks => l_unformatted_blocks,
         unformatted_bytes  => l_unformatted_bytes   );
      dbms_output.put_line(' FS1 Blocks (0~25%) = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
      dbms_output.put_line(' FS2 Blocks(25~50%) = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
      dbms_output.put_line(' FS3 Blocks(50~75%) = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
      dbms_output.put_line(' FS4 Blocks (75~100%)= '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
      dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes = '||l_unformatted_bytes);
   end;
   /

FS1 Blocks(0~25%)  = 0 and Bytes = 0
FS2 Blocks(25~50%)  = 1548 and Bytes = 25362432
FS3 Blocks(50~75%)  = 0 and Bytes = 0
FS4 Blocks(75~100%)  = 0 and Bytes = 0
Full Blocks = 3405 and Bytes = 55787520
Unformatted Blocks = 1137328  and Bytes = 18633981952  

 

 

目前為止，還沒有找到如何解決，為了及時發現類似bug導致的異常，我們需要定期檢視一些table, index，LOB等是否有異常增長的現象 。 我們可以設定如下SQL,
透過某個監控系統或schedule job定期查詢，一旦發現異常，及時回饋並處理。

 

select  a.owner, a.segment_name, b.num_rows, a.bytes/1024/1024/1024||' G' 
from   dba_segments a, dba_tables b 
where a.owner='DFMS' and a.segment_name=b.table_name
      and a.segment_type in ('TABLE') and bytes>=8*1024*1024*1024 
      order by bytes desc  ;

select  a.owner, a.segment_name,b.table_name, b.num_rows,
        a.bytes/1024/1024/1024||' G (Index Size)' 
from   dba_segments a, dba_indexes b 
where a.owner='DFMS' and a.segment_name=b.index_name
      and a.segment_type in ('INDEX') and a.bytes>=6*1024*1024*1024 
      order by a.bytes desc  ;
     
select  a.owner, a.segment_name,b.table_name, a.bytes/1024/1024/1024||' G (LOB Size)' 
from   dba_segments a, dba_lobs b 
where a.owner='DFMS' and a.segment_name=b.segment_name
      and a.segment_type in ('LOBSEGMENT') and a.bytes>=5*1024*1024*1024 
      order by a.bytes desc  ;      

 

相關的檢視： 
select * from dba_tables   ; 
select * from dba_indexes ;
select * from dba_lobs   ;
select * from dba_segments ;