ORACLE數據庫段空間收縮的研究

高旭

摘 要：在基于oracle的大型業務系統中，空間碎片是不可避免的。隨著ASSM表空間的出現，數據庫中的碎片問題主要集中在heap表上，即段的空間管理。嚴重的碎片問題不僅影響sql執行效率，降低數據庫的性能；還持續消耗物理空間，造成存儲資源的浪費，作為DBA，應該對碎片處理給予高度重視，作為日常運維的基本工作。重建碎片化嚴重的表是段空間收縮的主要方法，也可以使用shrink命令進行空間收縮，oracle的EM管理器或者OEM12C云管理器中提供了段指導方案進行向導式操作，也可以通過sql查詢進行手動操作。

關鍵詞：空間碎片；統計信息；段指導；shrink

1 概述

隨著IT信息化的快速發展，企業的業務系統越來越龐大，信息結構越來越復雜，在數據庫某些特定的業務表中，不可避免的要進行大批量的、相當頻繁的delete，insert操作，以至于數據庫的物理存儲結構形成大量細小的，分散的空間，這些空間碎片如果沒有得到及時的收縮，會直接導致業務表不斷申請新的空間，形成超大表。其所在的表空間使用率直線攀升，造成存儲資源緊缺。另外，這些碎片化嚴重的表也會影響數據庫的性能，尤其是需要進行全表掃描的SQL語句。

處理碎片化嚴重的表，通用方法是重建該對象或者用EXP/IMP導出導入，也可以使用move table的方法，從ORACLE 10G開始，更好的方法是利用segment advisor和shrink命令進行收縮操作。本文主要研究ORACLE 數據庫空間收縮的原理與優勢。

2 技術原理介紹

自動段空間管理（ASSM），它首次出現在Oracle 9i數據庫里。有了ASSM，空閑空間列表freelist被位圖取代，它是一個二進制的數組，能夠迅速、有效地管理存儲擴展和剩余區塊（free block），因此能夠改善分段存儲本質，ASSM表空間上創建的段還有另外一個稱呼叫Bitmap Managed Segments（BMB段），帶有ASSM的本地管理表空間會略掉任何為PCTUSED、NEXT和FREELISTS所指定的值。注：shrink命令只適用于ASSM的表空間。

segment shrink分為兩個階段：

①數據重組（compact）：通過一系列insert、delete操作，將數據盡量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。由于涉及rowid的改變，需要enable row movement，同時要disable基于rowid的trigger，這一過程對業務影響比較小。

②HWM調整：第二階段是調整HWM位置，釋放空閑數據塊。此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。

3 shrink的特性

①shrink只適用于ASSM的表空間，需要表打開row movement功能。

②shrink表同時可以維護該表上的索引。

③shrink表只在本表內移動數據，不需要多余的空間。

4 查詢碎片表可以釋放的空間

4.1 使用段指導

em管理器和OEM12c中都提供的段指導功能，通過向導可以以表空間或者schema為單位進行段分析，通過生成的指導方案進行空間的收縮，由于該向導對系統資源尤其是I/O影響比較大，建議在業務不忙時進行（如下圖1）。

4.2 手動sql查詢

①更新統計信息。

EXEC DBMS_STATS.GATHER_TABLE_STATS （'schema_name'， 'table_name'）；

②查詢某用戶下非分區表段碎片信息。

select d.table_name，

d.tablespace_name，

trunc（（b.blocks-d.EMPTY_BLOCKS）*8/1024） total_MB，

trunc（d.NUM_ROWS*AVG_ROW_LEN/1024/1024） used_MB，

to_char（d.LAST_ANALYZED，'YYYYMMDDHH24MI'） analyzed

from

（select owner，segment_name，blocks from dba_segments

where owner='schema_name'）b，

dba_tables d

where d.OWNER='schema_name'

and d.table_name=b.SEGMENT_NAME

and PARTITIONED='NO'；

③查詢某用戶下分區表段碎片信息。

select d.table_name，

b.PARTITION_NAME，

trunc（（b.locks-d.EMPTY_BLOCKS）*8/1024） Total_MB，

trunc（d.NUM_ROWS*AVG_ROW_LEN/1024/1024） Used_MB，

to_char（d.LAST_ANALYZED，'YYYYMMDDHH24MI'） analyzed

from

（select owner，segment_name，PARTITION_NAME，blocks from dba_segments

where owner='schema_name'）b，

dba_tab_partitions d

where d.table_owner='schema_name'

and b.OWNER=d.table_owner

and d.table_name=b.SEGMENT_NAME

and b.PARTITION_NAME=d.PARTITION_NAME；

5 收縮段空間

非分區表

alter table "table_name" enable row movement；

alter table "table_name" shrink space COMPACT；

alter table "table_name" shrink space；

分區表

alter table "table_name" enable row movement；

alter table "table_name" modify partition "parttion_name" shrink space COMPACT；

alter table "table_name" shrink space；

6 案例說明

測試中對test1表插入大量數據，導致表空間ZHYU不斷增長，空間空閑率非常低，然后對test1做頻繁刪除，插入工作，從而查處test1的段占用空間和實際數據量存在很大差距，通過段收縮，將浪費的空間釋放到ZHYU，提高了ZHYU的空閑率。

6.1 插入前的表空間實用情況

6.2 建測試表，插入記錄

create table test1（id number，name varchar2（30））；

begin

for i in 1..10000000

loop

insert into test1 values（i，'test'）；

end loop；

end；

/

PL/SQL procedure successfully completed.

用上面的語句創建一張大表test1，生成1千萬條記錄，用于測試碎片收縮原理。

6.3 反復進行刪除，插入操作

執行多次刪除操作：

Delete from test where rownum<3000000；

執行插入操作：

begin

for i in 1..1000000

loop

insert into test1 values（i，'test'）；

end loop；

end；

然后更新統計信息。

6.4 查詢到test1的實際使用量

利用上面提到的“查詢某用戶下非分區表段碎片信息”語句檢查當前使用率：

TABLE_NAME Tablespace Name TOTAL_MB USED_MB ANALYZED

———————————————————————————————————

TEST1 ZHYU 208 28 201503161105

可見實際只有28M數據，卻占用了208M的空間。

6.5 對test1進行段收縮

SQL> alter table test1 enable row movement；

SQL> alter table test1 shrink space compact；

SQL> alter table test1 shrink space；

TABLE_NAME Tablespace Name TOTAL_MB USED_MB ANALYZED

———————————————————————————————————

TEST1 ZHYU 45 28 201503161105

經過shrink方法進行段空間收縮后，占用空間降低到45M，比之前的200多M大大降低。

6.6 表空間使用率降低

通過OEM12c查看當前表空間使用率：

7 總結

在大業務系統中，對大表頻繁刪除，更新操作必然造成空間的浪費，可以使用段收縮的方式壓縮表的占用空間并提高sql效率，收縮操作比較費時，做好在業務不忙時或者特定的維護窗口下進行。

參考文獻：

[1]Overview of Database Fragmentation in Oracle 7 （文檔 ID 1012431.6）.

[2]如何執行對數據庫的健康狀況檢查 （文檔 ID 1548891.1）.