Oracle中shrink space命令詳解[轉]－－還示測試

我們接下來討論一下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不需要使用額外的空閒空間


source: