Oracle資料庫表碎片整理

一 建立測試表t1,id列建立索引in_t1_id

select  default_tablespace from  dba_users where  username = 'CHEN' ;

create table t1 as select level as id from dual connect by level<=300000;
create index in_t1_id on t1(id);
analyze table t1 compute statistics;
select count(*) from t1;

二 檢視錶T1段4M，佔用473個資料塊，39個空塊；索引IN_T1_ID段6M；

select   sum ( bytes )/ 1024 / 1024   from  dba_segments where  segment_name = 'T1' ;

select   sum ( bytes )/ 1024 / 1024   from  dba_segments where  segment_name = 'IN_T1_ID' ;

SELECT  blocks ,  empty_blocks ,  num_rows FROM  user_tables WHERE  table_name = 'T1' ;

三 檢視沒有資料的塊佔用的空間

DBMS_STATS包無法獲取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊

估算表在高水位線下還有多少空間可用 ，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少!

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  FROM USER_TABLES
 WHERE table_name = 'T1';

四 檢視全表掃描佔用CPU為133

explain plan for select * from t1;
select * from table(dbms_xplan.display);

五 刪除大部分資料，並收集統計資訊，檢視T1佔用資料塊和空塊都沒有減少

delete   from  t1 where   id > 10 ;

analyze table t1 compute statistics;
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

六 檢視全表掃描佔用CPU為130，CPU使用幾乎沒有下降

explain plan for select * from t1; 
select * from table(dbms_xplan.display);

七 檢視沒有資料的塊佔用的空間

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  FROM USER_TABLES
 WHERE table_name = 'T1';

八 整理表碎片

alter table t1 enable row movement; 
alter table t1 shrink space cascade;
alter table t1 disable row movement;
select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';

select   sum ( bytes )/ 1024 / 1024   from  dba_segments where  segment_name = 'IN_T1_ID' ;

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  FROM USER_TABLES
 WHERE table_name = 'T1';

九 收集統計資訊

analyze   table  t1 compute   statistics ;

十 佔用資料塊及空閒資料塊下降，並且CPU使用也下降了

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  FROM USER_TABLES
 WHERE table_name = 'T1';

select  blocks , empty_blocks , num_rows from  user_tables where  table_name = 'T1' ;

explain plan for select * from t1; 
select * from table(dbms_xplan.display);

其他

1.再用alter table table_name move時，表相關的索引會失效，

所以之後還要執行 alter index index_name rebuild online; 

最後重新編譯資料庫所有失效的物件.

2. 在用alter table table_name shrink space cascade時，

他相當於alter table table_name move和alter index index_name rebuild online. 所以只要編譯資料庫失效的物件就可以 ；

1. Move會移動高水位，但不會釋放申請的空間，是在高水位以下(below HWM)的操作。
2. shrink space 同樣會移動高水位，但也會釋放申請的空間，是在高水位上下(below and above HWM)都有的操作。

原理不一樣，move是以block為單位重組資料，行的rowid都會跟著變化，

而shrink是以 ” 行 “ 為單位重組資料，他是根據複雜的演算法從邏輯+物理重組資料

move 速度快於 shrink.
Move 相當於 從segment 底部 move到 頭。
Shrink 相當於先delete，然後再insert這樣產生很多undo,redo
通常首 選 MOVE

語法：

alter   table   <table_name>   shrink   space   [   <null>   |   compact   |   cascade   ];  

alter   table   <table_name>   shrink   space   compcat;  

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必須開啟行遷移功能。

###chenjuchao 2016-03-10###