ASSM下的Shrink命令應(yīng)用
??? 從10g開(kāi)始，oracle開(kāi)始提供Shrink的命令，假如我們的表空間中支持自動(dòng)段空間管理(ASSM)，就可以使用這個(gè)特性縮小段，即降低HWM。這里需要強(qiáng)調(diào)一點(diǎn)，10g的這個(gè)新特性，僅對(duì)ASSM表空間有效，否則會(huì)報(bào) ORA-10635: Invalid segment or tablespace type。在這里，我們來(lái)討論如何對(duì)一個(gè)ASSM的segment回收浪費(fèi)的空間。
?
??? 同樣，我們用系統(tǒng)視圖all_objects來(lái)在tablespace ASSM上創(chuàng)建測(cè)試表my_objects，這一小節(jié)的內(nèi)容，實(shí)驗(yàn)環(huán)境為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
?
?
??? 然后我們隨機(jī)地從table MY_OBJECTS中刪除一部分?jǐn)?shù)據(jù)：
?
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
?
?
??? 現(xiàn)在我們使用show_space和show_space_assm來(lái)看看my_objects的數(shù)據(jù)存儲(chǔ)狀況：
?
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 過(guò)程已成功完成。
?
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 過(guò)程已成功完成。
?
?
??? 這里table my_objects的HWM下有679個(gè)block。其中free space為25-50%的block有205個(gè)，free space為50-75%的block有180個(gè)，free space為75-100%的block有229個(gè)，full space的block只有45個(gè)，這種情況下，我們需要對(duì)這個(gè)table的現(xiàn)有數(shù)據(jù)行進(jìn)行重組。
?
??? 要使用assm上的shink，首先我們需要使該表支持行移動(dòng)，可以用這樣的命令來(lái)完成：
?
alter table my_objects enable row movement;
?
?
??? 現(xiàn)在，就可以來(lái)降低my_objects的HWM，回收空間了，使用命令：
?
alter table bookings shrink space;
?
?
??? 我們具體的看一下實(shí)驗(yàn)的結(jié)果：
?
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 過(guò)程已成功完成。
?
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 過(guò)程已成功完成。
?
?
??? 在執(zhí)行玩shrink命令后，我們可以看到，table my_objects的HWM現(xiàn)在降到了264的位置，而且HWM下的block的空間使用狀況，full space的block有249個(gè)，free space 為25-50% Block只有1個(gè)。
?
??? 我們接下來(lái)討論一下shrink的實(shí)現(xiàn)機(jī)制，我們同樣使用討論move機(jī)制的那個(gè)實(shí)驗(yàn)來(lái)觀察。
?
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
?
Table created
?
?
??? 往table test_hwm中插入如下的數(shù)據(jù)：
?
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');
?
??? 我們來(lái)看看這個(gè)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中刪除一些數(shù)據(jù)：
?
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中，剩下的數(shù)據(jù)是分布在AAAAFH，AAAAFI，AAAAFJ，AAAAFK這樣四個(gè)連續(xù)的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
?
?
??? 通過(guò)show_space_assm我們可以看到目前這四個(gè)block的空間使用狀況，AAAAFH，AAAAFI，AAAAFJ上各有一行數(shù)據(jù)，我們猜測(cè)free space為50-75%的3個(gè)block是這三個(gè)block，那么free space為25-50%的1個(gè)block就是AAAAFK了，剩下free space為 75-100% 的3個(gè)block，是HWM下已格式化的尚未使用的block。（關(guān)于assm下hwm的移動(dòng)我們前面已經(jīng)詳細(xì)地討論過(guò)了，在extent不大于于16個(gè)block時(shí)，是以一個(gè)extent為單位來(lái)移動(dòng)的）
?
??? 然后，我們對(duì)table my_objects執(zhí)行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
?
?
??? 當(dāng)執(zhí)行了shrink操作后，有意思的現(xiàn)象出現(xiàn)了。我們來(lái)看看oracle是如何移動(dòng)行數(shù)據(jù)的，這里的情況和move已經(jīng)不太一樣了。我們知道，在move操作的時(shí)候，所有行的rowid都發(fā)生了變化，table所位于的block的區(qū)域也發(fā)生了變化，但是所有行物理存儲(chǔ)的順序都沒(méi)有發(fā)生變化，所以我們得到的結(jié)論是，oracle以block為單位，進(jìn)行了block間的數(shù)據(jù)copy。那么shrink后，我們發(fā)現(xiàn)，部分行數(shù)據(jù)的rowid發(fā)生了變化，同時(shí)，部分行數(shù)據(jù)的物理存儲(chǔ)的順序也發(fā)生了變化，而table所位于的block的區(qū)域卻沒(méi)有變化，這就說(shuō)明，shrink只移動(dòng)了table其中一部分的行數(shù)據(jù)，來(lái)完成釋放空間，而且，這個(gè)過(guò)程是在table當(dāng)前所使用的block中完成的。
?
??? 那么Oracle具體移動(dòng)行數(shù)據(jù)的過(guò)程是怎樣的呢？我們根據(jù)這樣的實(shí)驗(yàn)結(jié)果，可以來(lái)猜測(cè)一下：
?
??? Oracle是以行為單位來(lái)移動(dòng)數(shù)據(jù)的。Oracle從當(dāng)前table存儲(chǔ)的最后一行數(shù)據(jù)開(kāi)始移動(dòng)，從當(dāng)前table最先使用的block開(kāi)始搜索空間，所以，shrink之前，rownum＝10的那行數(shù)據(jù)(10,es)，被移動(dòng)到block AAAAFH上，寫(xiě)到(1,aa)這行數(shù)據(jù)的后面，所以(10,es)的rownum和rowid同時(shí)發(fā)生改變。然后是(9,es)這行數(shù)據(jù)，重復(fù)上述過(guò)程。這是oracle從后向前移動(dòng)行數(shù)據(jù)的大致遵循的規(guī)則，那么具體移動(dòng)行數(shù)據(jù)的的算法是比較復(fù)雜的，包括向ASSM的table中insert數(shù)據(jù)使用block的順序的算法也是比較復(fù)雜的，大家有興趣的可以自己來(lái)研究，在這里我們不多做討論。
?
??? 我們還可以在shrink table的同時(shí)shrink這個(gè)table上的index：
?
alter table my_objects shrink space cascade;
?
??? 同樣地，這個(gè)操作只有當(dāng)table上的index也是ASSM時(shí)，才能使用。
?
?
??? 關(guān)于日志的問(wèn)題，我們對(duì)比了同樣數(shù)據(jù)量和分布狀況的兩張table，在move和shrink下生成的redo size（table上沒(méi)有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
?
??? 對(duì)于table my_objects，進(jìn)行shrink，產(chǎn)生了32579712 – 27808792=4770920，約4.5M的redo ；對(duì)table my_objects1進(jìn)行move，產(chǎn)生了32676784-32579712= 97072,約95K的redo size。那么，與move比較起來(lái)，shrink的日志寫(xiě)要大得多。
?
?
?
Shrink的幾點(diǎn)問(wèn)題：
?
a. shrink后index是否需要rebuild：
?
因?yàn)閟hrink的操作也會(huì)改變行數(shù)據(jù)的rowid，那么，如果table上有index時(shí)，shrink table后index會(huì)不會(huì)變?yōu)閁NUSABLE呢？我們來(lái)看這樣的實(shí)驗(yàn)，同樣構(gòu)建my_objects的測(cè)試表：
?
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%';
?
現(xiàn)在我們來(lái)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
?
我們發(fā)現(xiàn)，table my_objects上的index的狀態(tài)為VALID，估計(jì)shrink在移動(dòng)行數(shù)據(jù)時(shí)，也一起維護(hù)了index上相應(yīng)行的數(shù)據(jù)rowid的信息。我們認(rèn)為，這是對(duì)于move操作后需要rebuild index的改進(jìn)。但是如果一個(gè)table上的index數(shù)量較多，我們知道，維護(hù)index的成本是比較高的，shrink過(guò)程中用來(lái)維護(hù)index的成本也會(huì)比較高。
?
?
b. shrink時(shí)對(duì)table的lock
?
在對(duì)table進(jìn)行shrink時(shí)，會(huì)對(duì)table進(jìn)行怎樣的鎖定呢？當(dāng)我們對(duì)table MY_OBJECTS進(jìn)行shrink操作時(shí)，查詢(xún)v$locked_objects視圖可以發(fā)現(xiàn)，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
?
那么，當(dāng)table在進(jìn)行shrink時(shí)，我們對(duì)table是可以進(jìn)行DML操作的。
?
?
c. shrink對(duì)空間的要求
?
我們?cè)谇懊嬗懻摿藄hrink的數(shù)據(jù)的移動(dòng)機(jī)制，既然oracle是從后向前移動(dòng)行數(shù)據(jù)，那么，shrink的操作就不會(huì)像move一樣，shrink不需要使用額外的空閑空間。
?
?
?