<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
   
   
   
        
           SQL
        
        
          >
        
        
        
        
          create
        
        
        
        
          or
        
        
        
        
          replace
        
        
        
        
          procedure
        
        
           show_space  ( p_segname 
        
        
          in
        
        
        
        
          varchar2
        
        
          ,  p_owner 
        
        
          in
        
        
        
        
          varchar2
        
        
        
        
          default
        
        
        
        
          user
        
        
          ,  p_type 
        
        
          in
        
        
        
        
          varchar2
        
        
        
        
          default
        
        
        
        
          '
        
        
          TABLE
        
        
          '
        
        
          ,  p_partition 
        
        
          in
        
        
        
        
          varchar2
        
        
        
        
          default
        
        
        
        
          NULL
        
        
           ) 
        
        
          as
        
        
            l_free_blks 
        
        
          number
        
        
          ;  l_total_blocks 
        
        
          number
        
        
          ;  l_total_bytes 
        
        
          number
        
        
          ;  l_unused_blocks 
        
        
          number
        
        
          ;  l_unused_bytes 
        
        
          number
        
        
          ;  l_LastUsedExtFileId 
        
        
          number
        
        
          ;  l_LastUsedExtBlockId 
        
        
          number
        
        
          ;  l_last_used_block 
        
        
          number
        
        
          ; 
        
        
          procedure
        
        
           p( p_label 
        
        
          in
        
        
        
        
          varchar2
        
        
          , p_num 
        
        
          in
        
        
        
        
          number
        
        
           ) 
        
        
          is
        
        
        
        
          begin
        
        
            dbms_output.put_line( rpad(p_label,
        
        
          40
        
        
          ,
        
        
          '
        
        
          .
        
        
          '
        
        
          ) 
        
        
          ||
        
        
           p_num ); 
        
        
          end
        
        
          ; 
        
        
          begin
        
        
            dbms_space.free_blocks  ( segment_owner 
        
        
          =>
        
        
           p_owner,  segment_name 
        
        
          =>
        
        
           p_segname,  segment_type 
        
        
          =>
        
        
           p_type,  partition_name 
        
        
          =>
        
        
           p_partition,  freelist_group_id 
        
        
          =>
        
        
        
        
          0
        
        
          ,  free_blks 
        
        
          =>
        
        
           l_free_blks );  dbms_space.unused_space  ( segment_owner 
        
        
          =>
        
        
           p_owner,  segment_name 
        
        
          =>
        
        
           p_segname,  segment_type 
        
        
          =>
        
        
           p_type,  partition_name 
        
        
          =>
        
        
           p_partition,  total_blocks 
        
        
          =>
        
        
           l_total_blocks,  total_bytes 
        
        
          =>
        
        
           l_total_bytes,  unused_blocks 
        
        
          =>
        
        
           l_unused_blocks,  unused_bytes 
        
        
          =>
        
        
           l_unused_bytes,  last_used_extent_file_id 
        
        
          =>
        
        
           l_LastUsedExtFileId,  last_used_extent_block_id 
        
        
          =>
        
        
           l_LastUsedExtBlockId,  last_used_block 
        
        
          =>
        
        
           l_last_used_block );  p( 
        
        
          '
        
        
          Free Blocks
        
        
          '
        
        
          , l_free_blks );  p( 
        
        
          '
        
        
          Total Blocks
        
        
          '
        
        
          , l_total_blocks );  p( 
        
        
          '
        
        
          Total Bytes
        
        
          '
        
        
          , l_total_bytes );  p( 
        
        
          '
        
        
          Unused Blocks
        
        
          '
        
        
          , l_unused_blocks );  p( 
        
        
          '
        
        
          Unused Bytes
        
        
          '
        
        
          , l_unused_bytes );  p( 
        
        
          '
        
        
          Last Used Ext FileId
        
        
          '
        
        
          , l_LastUsedExtFileId );  p( 
        
        
          '
        
        
          Last Used Ext BlockId
        
        
          '
        
        
          , l_LastUsedExtBlockId );  p( 
        
        
          '
        
        
          Last Used Block
        
        
          '
        
        
          , l_last_used_block ); 
        
        
          end
        
        
          ;  過程已創(chuàng)建。
        
      
    

3.2.1 HWM位置

HWM位置按下面的公式計算:

HWM = useed byte = Total Bytes - Unused Blocks

Total Bytes和Unused Blocks都可以用show_space提取。

還可以通過ANALYZE tables得到HWM信息. DBA_TABLES視圖中包含了可用于各表空間分析的列。其中blocks代表已使用過的塊即HWM，empty_blocks代表未使用的空間。

3.2.1 HWM下空間利用信息
要比較有數(shù)據(jù)行的塊的塊數(shù)和HIGH WATER MARK下總塊數(shù)，可以用下面的公式來展示HWM下未用空間的比例。

p = 1- r/h

r:有數(shù)據(jù)行的塊的塊數(shù)

h:HWM下的塊數(shù).

r可以通過如下方法獲得：

Oracle7:

SELECT count(distinct substr(rowid, 15,4) || substr(rowid, 1,8) ) FROM schema.table;

Oracle8 and Oracle9:

SELECT count(distinct substr(rowid, 7,3) || substr(rowid, 10,6) ) FROM schema.table;

如果公式計算的結(jié)果 p是0，就不需要對表進行重建。如果結(jié)果p大于0,應(yīng)該考慮系統(tǒng)狀況和應(yīng)用需要來決定是否需要總組表。

4.優(yōu)化

4.1手工回收存儲空間

在HIGH WATER MARK以上的塊對性能沒有影響，但是會耗費空間。如何空間大小是一個考慮的問題，就可以決定回收空塊。 假設(shè)表T1的存儲示意圖如圖2.5所示，使用ALTER TABLE ... DEALLOCATE UNUSED語句可以回收HWM以上的空間。比如：

alter table t1 deallocate unused;

回收后T1的存儲示意如圖4.1.1

如果在ALTER TABLE ... DEALLOCATE UNUSED語句中使用了KEEP關(guān)鍵字，則可以在HWM之后保留指定大小的空閑空間，比如：

alter table t1 deallocate unused keep 10K;

4.2刪減表

根據(jù)3.2.1可以得到HWM以下塊的使用情況。如何p大于時，對全表掃描性能會產(chǎn)生影響，同時也會耗用空間。

如果能夠確認(rèn)應(yīng)用有良好的索引幾乎不會用到全表掃描，那么HIGH WATER MARK以下的空塊，盡管耗費了空間，不會對訪問產(chǎn)生影響。如果不能確定，那么就需要考慮刪減表。

刪減表的操作將刪除表中所有的記錄，并且重置HWM標(biāo)記。表在刪減之后將成為一個空表。

在Oracle中刪減表只有如下的兩種辦法：

1.使用drop語句

先使用drop語句刪除整個表，然后再重建這個表。在刪除-重建的過程中，與表相關(guān)的所有索引、完整性約束以及觸發(fā)器都會丟失，并且所有依賴于該表的對象都會變?yōu)镮NVALID狀態(tài)，同時原來爭對表的授權(quán)也會失效。因此采用這種方式刪除表中的記錄代價太大。

2．使用TRUNCATE語句

TRUNCATE語句屬于DDL語句，不會產(chǎn)生任何回退信息，并且被立即自動提交。在執(zhí)行TRUNCATE語句時不會影響到與被刪減表相關(guān)的任何數(shù)據(jù)庫對象與授權(quán)，也不會觸發(fā)表中所定義的觸發(fā)器。此外，在對標(biāo)進行刪減時，HWM將重置，已經(jīng)為表分配的存儲空間將被回收。

在執(zhí)行TRUNCATE語句時，可以通過drop storage子句和reuse storage子句來控制被釋放的區(qū)是否回收到表空間中。如何作在線系統(tǒng)的TRUNCATE，不希望表長時間鎖住，那么可以使用reuse storage子句，僅將HWM重置。

4.3 free list優(yōu)化

free list 競爭出現(xiàn)在多個進程使用同一個free list并試圖同時修改free list頭部數(shù)據(jù)塊時。可以通過查詢視圖v$waitsate的class類型為data block 的記錄來檢查競爭情況。

產(chǎn)生data block類型競爭的主要原因是多個進程試圖同時修改free list頭部數(shù)據(jù)塊。 然而，它也會出現(xiàn)在當(dāng)進程準(zhǔn)備將塊讀入buffer cathe時，另一個進程需要訪問同一個塊。如果能在V$SESSION_WAIT中正好捕獲buffer busy waits，就可以通過查詢V$SESSION_WAIT中的P3來判定是那一類。A 0 或 1014代表讀類型，其他的值為修改競爭的類型。

下一步需要確定競爭涉及那些段。 如果能夠在V$SESSION_WAIT捕獲waits，就可以用P1和P2的值 (對應(yīng)file 和 block) 在DBA_EXTENTS中找到段名。 如何是一個表，就很可能需要重建表來創(chuàng)建更多的process freelists。 一種計算需要創(chuàng)建多少個freelist的方法是dump一些段中接近HWM的塊，檢查interested transaction list的個數(shù)，具體方法可參見3.1。interested transactions個數(shù)的峰值加1 就是需要的最小process freelists的值。

從2.3和2.4可以看出，使用多個free list可能導(dǎo)致更多的空塊未被使用， 也可能導(dǎo)致段更快地擴展。如果性能是當(dāng)前所關(guān)心的重點，那么多free lists 可以用來提高并發(fā)訪問能力，當(dāng)然會增加一些額外空間的耗用。然而，如果空間使用大小是首先考慮的因素，那么推薦使用single freelist，使參數(shù)FREELISTS=1, 當(dāng)然就不能提升并發(fā)事務(wù)的性能了。

V$WAITSTAT 也可顯示其他類型class的競爭，包括segment header 和free list。 出現(xiàn)在同一個free list group中多個事務(wù)需要同時更新它們的free list header記錄時。 有多種方法來解決這個問題如重建表采用更多的free list groups,或者增加 _bump_highwater_mark_count大小，或者調(diào)整應(yīng)用本身。