高旭

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

關(guān)鍵詞：空間碎片；統(tǒng)計信息；段指導(dǎo)；shrink

1 概述

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

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

2 技術(shù)原理介紹

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

segment shrink分為兩個階段：

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

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

3 shrink的特性

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

②shrink表同時可以維護(hù)該表上的索引。

③shrink表只在本表內(nèi)移動數(shù)據(jù)，不需要多余的空間。

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

4.1 使用段指導(dǎo)

em管理器和OEM12c中都提供的段指導(dǎo)功能，通過向?qū)Э梢砸员砜臻g或者schema為單位進(jìn)行段分析，通過生成的指導(dǎo)方案進(jìn)行空間的收縮，由于該向?qū)ο到y(tǒng)資源尤其是I/O影響比較大，建議在業(yè)務(wù)不忙時進(jìn)行（如下圖1）。

4.2 手動sql查詢

①更新統(tǒng)計信息。

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

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

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'；

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

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 收縮段空間

非分區(qū)表

alter table "table_name" enable row movement；

alter table "table_name" shrink space COMPACT；

alter table "table_name" shrink space；

分區(qū)表

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表插入大量數(shù)據(jù)，導(dǎo)致表空間ZHYU不斷增長，空間空閑率非常低，然后對test1做頻繁刪除，插入工作，從而查處test1的段占用空間和實際數(shù)據(jù)量存在很大差距，通過段收縮，將浪費的空間釋放到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.

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

6.3 反復(fù)進(jìn)行刪除，插入操作

執(zhí)行多次刪除操作：

Delete from test where rownum<3000000；

執(zhí)行插入操作：

begin

for i in 1..1000000

loop

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

end loop；

end；

然后更新統(tǒng)計信息。

6.4 查詢到test1的實際使用量

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

TABLE_NAME Tablespace Name TOTAL_MB USED_MB ANALYZED

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

TEST1 ZHYU 208 28 201503161105

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

6.5 對test1進(jìn)行段收縮

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

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

6.6 表空間使用率降低

通過OEM12c查看當(dāng)前表空間使用率：

7 總結(jié)

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

參考文獻(xiàn)：

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

[2]如何執(zhí)行對數(shù)據(jù)庫的健康狀況檢查 （文檔 ID 1548891.1）.