深入探討Oracle數據庫10g的Shrink機制
從10g開始，oracle開始提供Shrink的命令，假如我們的表空間中支持自動段空間管理 (ASSM),就可以使用這個特性縮小段，即降低HWM。這里需要強調一點，10g的這個新特性，僅對ASSM表空間有效，否則會報 ORA-10635: Invalid segment or tablespace type。
在這里，我們來討論如和對一個ASSM的segment回收浪費的空間。
同樣，我們用系統視圖all_objects來在tablespace ASSM上創建測試表my_objects，這一小節的內容，實驗環境為oracle10.1.0.2：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 – Production
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,
2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT
3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
---------------- ---------- ----------------- --------------- ------------------------
ASSM 8192 LOCAL UNIFORM AUTO
SQL> create table my_objects tablespace assm
2 as select * from all_objects;
Table created
然后我們隨機地從table MY_OBJECTS中刪除一部分數據：
SQL> select count(*) from my_objects;
COUNT(*)
----------
47828
SQL> delete from my_objects where object_name like '%C%';
16950 rows deleted
SQL> delete from my_objects where object_name like '%U%';
4503 rows deleted
SQL> delete from my_objects where object_name like '%A%';
6739 rows deleted
現在我們使用show_space和show_space_assm來看看my_objects的數據存儲狀況：
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................680
Total Bytes.............................5570560
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................6
Last Used Ext BlockId...................793
Last Used Block.........................4
PL/SQL 過程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............205
free space 50-75% Blocks:...............180
free space 75-100% Blocks:..............229
Full Blocks:............................45
Unformatted blocks:.....................0
PL/SQL 過程已成功完成。
這里，table my_objects的HWM下有679個block，其中，free space為25-50%的block有205個，free space為50-75%的block有180個，free space為75-100%的block有229個，full space的block只有45個，這種情況下，我們需要對這個table的現有數據行進行重組。
要使用assm上的shink，首先我們需要使該表支持行移動，可以用這樣的命令來完成：
alter table my_objects enable row movement;
現在，就可以來降低my_objects的HWM，回收空間了，使用命令：
alter table bookings shrink space;
我們具體的看一下實驗的結果：
SQL> alter table my_objects enable row movement;
表已更改。
SQL> alter table my_objects shrink space;
表已更改。
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................265
Total Bytes.............................2170880
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................6
Last Used Ext BlockId...................308
Last Used Block.........................3
PL/SQL 過程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............1
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................249
Unformatted blocks:.....................0
PL/SQL 過程已成功完成。
在執行玩shrink命令后，我們可以看到，table my_objects的HWM現在降到了264的位置，而且HWM下的block的空間使用狀況，full space的block有249個，free space 為25-50% Block只有1個。
我們接下來討論一下shrink的實現機制，我們同樣使用討論move機制的那個實驗來觀察。
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
Table created
往table test_hwm中插入如下的數據：
insert into TEST_HWM values (1,'aa');
insert into TEST_HWM values (2,'bb');
insert into TEST_HWM values (2,'cc');
insert into TEST_HWM values (3,'dd');
insert into TEST_HWM values (4,'ds');
insert into TEST_HWM values (5,'dss');
insert into TEST_HWM values (6,'dss');
insert into TEST_HWM values (7,'ess');
insert into TEST_HWM values (8,'es');
insert into TEST_HWM values (9,'es');
insert into TEST_HWM values (10,'es');
我們來看看這個table的rowid和block的ID和信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- ---------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFHAAB 2 bb
AAANhqAAGAAAAFHAAC 2 cc
AAANhqAAGAAAAFIAAA 3 dd
AAANhqAAGAAAAFIAAB 4 ds
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFJAAB 7 ess
AAANhqAAGAAAAFJAAC 8 es
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
11 rows selected
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 324 5
1 6 6 329 5
然后從table test_hwm中刪除一些數據：
delete from TEST_HWM where id = 2;
delete from TEST_HWM where id = 4;
delete from TEST_HWM where id = 3;
delete from TEST_HWM where id = 7;
delete from TEST_HWM where id = 8;
觀察table test_hwm的rowid和blockid的信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- --------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 324 5
1 6 6 329 5
從以上的信息，我們可以看到，在table test_hwm中，剩下的數據是分布在AAAAFH，AAAAFI，AAAAFJ，AAAAFK這樣四個連續的block中。
SQL> exec show_space_assm('TEST_HWM','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............1
free space 50-75% Blocks:...............3
free space 75-100% Blocks:..............3
Full Blocks:............................0
Unformatted blocks:.....................0
通過show_space_assm我們可以看到目前這四個block的空間使用狀況，AAAAFH，AAAAFI，AAAAFJ上各有一行數據，我們猜測free space為50-75%的3個block是這三個block，那么free space為25-50%的1個block就是AAAAFK了，剩下free space為 75-100% 的3個block，是HWM下已格式化的尚未使用的block。（關于assm下hwm的移動我們前面已經詳細地討論過了，在extent不大于于16個block時，是以一個extent為單位來移動的）
然后，我們對table my_objects執行shtink的操作：
SQL> alter table test_hwm enable row movement;
Table altered
SQL> alter table test_hwm shrink space;
Table altered
SQL> select rowid ,id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ------ -----------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFHAAB 10 es
AAANhqAAGAAAAFHAAD 9 es
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 324 5
1 6 6 329 5
當執行了shrink操作后，有意思的現象出現了。我們來看看oracle是如何移動行數據的，這里的情況和move已經不太一樣了。我們知道，在move操作的時候，所有行的rowid都發生了變化，table所位于的block的區域也發生了變化，但是所有行物理存儲的順序都沒有發生變化，所以我們得到的結論是，oracle以block為單位，進行了block間的數據copy。那么shrink后，我們發現，部分行數據的rowid發生了變化，同時，部分行數據的物理存儲的順序也發生了變化，而table所位于的block的區域卻沒有變化，這就說明，shrink只移動了table其中一部分的行數據，來完成釋放空間，而且，這個過程是在table當前所使用的block中完成的。
那么Oracle具體移動行數據的過程是怎樣的呢？我們根據這樣的實驗結果，可以來猜測一下：
Oracle是以行為單位來移動數據的。Oracle從當前table存儲的最后一行數據開始移動，從當前table最先使用的block開始搜索空間，所以，shrink之前，rownum＝10的那行數據（10,es），被移動到block AAAAFH上，寫到（1,aa）這行數據的后面，所以（10,es）的rownum和rowid同時發生改變。然后是（9,es）這行數據，重復上述過程。這是oracle從后向前移動行數據的大致遵循的規則，那么具體移動行數據的的算法是比較復雜的，包括向ASSM的table中insert數據使用block的順序的算法也是比較復雜的，大家有興趣的可以自己來研究，在這里我們不多做討論。
我們還可以在shrink table的同時shrink這個table上的index：
alter table my_objects shrink space cascade;
同樣地，這個操作只有當table上的index也是ASSM時，才能使用。
關于日志的問題，我們對比了同樣數據量和分布狀況的兩張table，在move和shrink下生成的redo size（table上沒有index的情況下）：
SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
2 where tablespace_name in('ASSM','HWM');
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
------------------------------ ------------------------
ASSM AUTO
HWM MANUAL
SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
Table created
SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;
Table created
SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';
BYTES/1024/1024
---------------
2.1875
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects1 where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> delete from my_objects1 where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> alter table my_objects enable row movement;
Table altered
SQL> select value from v$mystat, v$statname
2 where v$mystat.statistic# = v$statname.statistic#
3 and v$statname.name = 'redo size';
VALUE
----------
27808792
SQL> alter table my_objects shrink space;
Table altered
SQL> select value from v$mystat, v$statname
2 where v$mystat.statistic# = v$statname.statistic#
3 and v$statname.name = 'redo size';
VALUE
----------
32579712
SQL> alter table my_objects1 move;
Table altered
SQL> select value from v$mystat, v$statname
2 where v$mystat.statistic# = v$statname.statistic#
3 and v$statname.name = 'redo size';
VALUE
----------
32676784
對于table my_objects，進行shrink，產生了32579712 – 27808792=4770920，約4.5M的redo ；對table my_objects1進行move，產生了32676784-32579712= 97072,約95K的redo size。那么，與move比較起來，shrink的日志寫要大得多。
Shrink的幾點問題：
a. shrink后index是否需要rebuild：
因為shrink的操作也會改變行數據的rowid，那么，如果table上有index時，shrink table后index會不會變為UNUSABLE呢？我們來看這樣的實驗，同樣構建my_objects的測試表：
create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
create index i_my_objects on my_objects (object_id);
delete from my_objects where object_name like '%C%';
delete from my_objects where object_name like '%U%';
現在我們來shrink table my_objects：
SQL> alter table my_objects enable row movement;
Table altered
SQL> alter table my_objects shrink space;
Table altered
SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
INDEX_NAME STATUS
------------------------------ --------
I_MY_OBJECTS VALID
我們發現，table my_objects上的index的狀態為VALID，估計shrink在移動行數據時，也一起維護了index上相應行的數據rowid的信息。我們認為，這是對于move操作后需要rebuild index的改進。但是如果一個table上的index數量較多，我們知道，維護index的成本是比較高的，shrink過程中用來維護index的成本也會比較高。
b. shrink時對table的lock
在對table進行shrink時，會對table進行怎樣的鎖定呢？當我們對table MY_OBJECTS進行shrink操作時，查詢v$locked_objects視圖可以發現，table MY_OBJECTS上加了row-X (SX) 的lock：
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------ -----------
55422 153 DLINGER 3
SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID
----------
55422
那么，當table在進行shrink時，我們對table是可以進行DML操作的。
c. shrink對空間的要求
我們在前面討論了shrink的數據的移動機制，既然oracle是從后向前移動行數據，那么，shrink的操作就不會像move一樣，shrink不需要使用額外的空閑空間。
網公網安備