轉自：http://blog.csdn.net/tianlesoftware/archive/2009/11/04/4764254.aspx
從10g開始，oracle開始提供Shrink的命令，假如我們的表空間中支持自動段空間管理 (ASSM),就可以使用這個特性縮小段，即降低HWM。這里需要強調一點，10g的這個新特性，僅對ASSM表空間有效，否則會報 ORA-10635: Invalid segment or tablespace type。
有關ASSM的詳細信息，請參考我的Blog：Oracle 自動段空間管理
http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx
如果經常在表上執行DML操作，會造成數據庫塊中數據分布稀疏，浪費大量空間。同時也會影響全表掃描的性能，因為全表掃描需要訪問更多的數據塊。從oracle10g開始，表可以通過shrink來重組數據使數據分布更緊密，同時降低HWM釋放空閑數據塊。
segment shrink分為兩個階段：
1、數據重組(compact):通過一系列insert、delete操作，將數據盡量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。由于涉及到rowid的改變，需要enable row movement.同時要disable基于rowid的trigger.這一過程對業務影響比較小。
2、HWM調整：第二階段是調整HWM位置，釋放空閑數據塊。此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。
shrink space語句兩個階段都執行。
shrink space compact只執行第一個階段。
如果系統業務比較繁忙，可以先執行shrink space compact重組數據,然后在業務不忙的時候再執行shrink space降低HWM釋放空閑數據塊。
shrink必須開啟行遷移功能。
alter table table_name enable row movement ;
注意：alter table XXX enable row movement語句會造成引用表XXX的對象(如存儲過程、包、視圖等)變為無效。執行完成后，最好執行一下utlrp.sql來編譯無效的對象。
語法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
收縮表，相當于把塊中數據打結實了，但會保持 high water mark;
alter table <tablespace_name> shrink space;
收縮表，降低 high water mark;
alter table <tablespace_name> shrink space cascade;
收縮表，降低 high water mark，并且相關索引也要收縮一下下。
alter index idxname shrink space;
回縮索引
1：普通表
Sql腳本，改腳本會生成相應的語句
select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;
select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
2：分區表的處理
進行shrink space時 發生ORA-10631錯誤.shrink space有一些限制.
在表上建有函數索引（包括全文索引）會失敗。
Sql腳本，改腳本會生成相應的語句
select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;
select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;
select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';
詳細測試：
我們用系統視圖all_objects來在上個測試的tablespace ASSM上創建測試表my_objects
/* Formatted on 2009-12-7 20:42:45 (QP5 v5.115.810.9015) */
CREATE TABLESPACE ASSM DATAFILE 'd:\ASSM01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
/* Formatted on 2009-12-7 20:39:26 (QP5 v5.115.810.9015) */
SELECT TABLESPACE_NAME,
BLOCK_SIZE,
EXTENT_MANAGEMENT,
ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
FROM dba_tablespaces
WHERE TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
--------------------- ---------- ---------- --------- ------
ASSM 8192 LOCAL SYSTEM AUTO
1 row selected.
/* Formatted on 2009-12-7 20:44:15 (QP5 v5.115.810.9015) */
CREATE TABLE my_objects
TABLESPACE assm
AS
SELECT * FROM all_objects;
然后我們隨機地從table MY_OBJECTS中刪除一部分數據：
SQL> SELECT COUNT ( * ) FROM my_objects;
COUNT(*)
----------
49477
SQL> delete from my_objects where object_name like '%C%';
SQL> delete from my_objects where object_name like '%U%';
SQL> delete from my_objects where object_name like '%A%';
現在我們使用show_space()來看看my_objects的數據存儲狀況：
注： show_space() 存儲過程代碼參看一下連接的附件
http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx
SQL>exec show_space('my_objects','auto','T','Y');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................68
Unused Bytes............................557056
Last Used Ext FileId....................8
Last Used Ext BlockId...................649
Last Used Block.........................60
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............41
0% -- 25% free space bytes..............335872
25% -- 50% free space blocks............209
25% -- 50% free space bytes.............1712128
50% -- 75% free space blocks............190
50% -- 75% free space bytes.............1556480
75% -- 100% free space blocks...........229
75% -- 100% free space bytes............1875968
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................11
Total bytes.............................90112
PL/SQL 過程已成功完成。
這里，table my_objects的HWM下有767個block，其中，free space為25-50%的block有209個，free space為50-75%的block有190個，free space為75-100%的block有229個. Total blocks 11個。
這種情況下，我們需要對這個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','auto','T','Y');
Total Blocks............................272
Total Bytes.............................2228224
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................8
Last Used Ext BlockId...................265
Last Used Block.........................16
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................257
Total bytes.............................2105344
在執行玩shrink命令后，我們可以看到，table my_objects的HWM現在降到了271的位置，而且HWM下的block的空間使用狀況，Total blocks 的block有257個，free space 為25-50% Block只有0個。
Shrink 的實現機制：
我們接下來討論一下shrink的實現機制，我們同樣使用討論move機制的那個實驗來觀察。
/* Formatted on 2009-12-7 20:58:40 (QP5 v5.115.810.9015) */
CREATE TABLE TEST_HWM (id INT, name CHAR (2000))
TABLESPACE ASSM;
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和信息：
/* Formatted on 2009-12-7 21:00:02 (QP5 v5.115.810.9015) */
SQL>SELECT ROWID, id, name FROM TEST_HWM;ROWID ID NAME
ROWID ID NAME
------------------------------------- ---------- --------
AAANMEAAIAAAAEcAAA 3 dd
AAANMEAAIAAAAEcAAB 4 ds
AAANMEAAIAAAAEcAAC 5 dss
AAANMEAAIAAAAEdAAA 6 dss
AAANMEAAIAAAAEdAAB 7 ess
AAANMEAAIAAAAEdAAC 8 es
AAANMEAAIAAAAEeAAA 9 es
AAANMEAAIAAAAEeAAB 10 es
AAANMEAAIAAAAEgAAA 1 aa
AAANMEAAIAAAAEgAAB 2 bb
AAANMEAAIAAAAEgAAC 2 cc
/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */
SQL>SELECT EXTENT_ID,
FILE_ID,
RELATIVE_FNO,
BLOCK_ID,
BLOCKS
FROM dba_extents
WHERE segment_name = 'TEST_HWM';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 8 8 281 8
1 row selected.
然后從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
------------------------------------------ ---------- ---------
AAANMEAAIAAAAEcAAC 5 dss
AAANMEAAIAAAAEdAAA 6 dss
AAANMEAAIAAAAEeAAA 9 es
AAANMEAAIAAAAEeAAB 10 es
AAANMEAAIAAAAEgAAA 1 aa
/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */
SQL>SELECT EXTENT_ID,
FILE_ID,
RELATIVE_FNO,
BLOCK_ID,
BLOCKS
FROM dba_extents
WHERE segment_name = 'TEST_HWM';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 8 8 281 8
1 row selected.
從以上的信息，我們可以看到，在table test_hwm中，剩下的數據是分布在AAAAEc，AAAAEd，AAAAEf，AAAAEg這樣四個連續的block中。
SQL> exec show_space('TEST_HWM','auto','T','Y');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................8
Last Used Ext BlockId...................281
Last Used Block.........................8
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............1
25% -- 50% free space bytes.............8192
50% -- 75% free space blocks............3
50% -- 75% free space bytes.............24576
75% -- 100% free space blocks...........1
75% -- 100% free space bytes............8192
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0
我們可以看到目前這四個block的空間使用狀況，AAAAEc，AAAAEd，AAAAEf，AAAAEg上各有一行數據，我們猜測free space為50-75%的3個block是這三個block，那么free space為25-50%的1個block就是AAAAEg了，剩下free space為 75-100% 的3個block，是HWM下已格式化的尚未使用的block。（在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
------------------ ---------- ------------
AAANMEAAIAAAAEcAAA 10 es
AAANMEAAIAAAAEcAAC 5 dss
AAANMEAAIAAAAEcAAD 1 aa
AAANMEAAIAAAAEcAAE 9 es
AAANMEAAIAAAAEdAAA 6 dss
/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */
SQL>SELECT EXTENT_ID,
FILE_ID,
RELATIVE_FNO,
BLOCK_ID,
BLOCKS
FROM dba_extents
WHERE segment_name = 'TEST_HWM';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 8 8 281 8
1 row selected.
當執行了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 AAAAEc上，寫到（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時，才能使用。
Move 和 Shrink 產生日志的對比
我們對比了同樣數據量和分布狀況的兩張table，在move和shrink下生成的redo size（table上沒有index的情況下）：
/* Formatted on 2009-12-7 21:20:43 (QP5 v5.115.810.9015) */
SQL>SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT
FROM dba_tablespaces
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
/* Formatted on 2009-12-7 21:21:48 (QP5 v5.115.810.9015) */
SQL>SELECT VALUE
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
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
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = 'redo size';
VALUE
----------
32579712
SQL> alter table my_objects1 move;
Table altered
SQL>SELECT VALUE
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
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的幾點問題：
1. 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的成本也會比較高。
2. 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操作的。
3． shrink對空間的要求
我們在前面討論了shrink的數據的移動機制，既然oracle是從后向前移動行數據，那么，shrink的操作就不會像move一樣，shrink不需要使用額外的空閑空間。
本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/tianlesoftware/archive/2009/11/04/4764254.aspx
posted on 2011-04-15 10:57
xzc 閱讀(9357)
評論(0) 編輯 收藏 所屬分類:
Oracle