Oracle索引片段分析、空間重用和整理

對索引頻繁的update，delete操作會產生index Frag，影響索引效率，增加索引IO。

1、索引片段分析
產生測試索引片段：
SCOTT @devcedb>select count(*) from obj;

COUNT(*)
----------
124256

SCOTT @devcedb>create index ind_obj_id on obj(OBJECT_ID);

Index created.

SCOTT @devcedb>delete obj where rownum<50000;

49999 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

索引片段分析：
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

--注意一點，就是該命令有一個壞處，就是在運行過程中，會鎖定整個表，從而阻塞其他session對錶進行插入、更新和刪除等操作。這是因為該命令的主要目的並不是用來填充index_stats視圖的，其主要作用在於校正索引中的每個有效索引條目都對應到表裡的一行，同時表裡的每一行資料在索引中都存在一個對應的索引條目。為了完成該目的，所以在運行過程中要鎖定整個表，同時對於很大的表來說，運行該命令需要耗費非常多的時間。

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062

索引片段比率：(del_lf_rows_len/lf_rows_len)*100，如果百分比超過20%就說明索引片段比率很高了。需要整理片段。
索引和表資料是級聯關係的，當刪除表資料的時候，索引條目不會被自動刪除，而是在該條目上打上一個刪除(D)的標識位，具體後面會說明，索引的block數量是不會改變的，空葉塊不會被刪除。所以當INDEX FAST FULL SCAN和INDEX FULL SCAN的時候，這些空索引塊也會被載入到記憶體中，增加了IO。索引空葉塊多，極大影響了索引掃描的效率。否則索引片段對效率的影響不是很大。如下：

SCOTT @devcedb>select object_id from obj where object_id < 9000;

41377 rows selected.

Elapsed: 00:00:01.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2777403740

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55341 | 702K| 79 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IND_OBJ_ID | 55341 | 702K| 79 (2)| 00:00:01 |
-----------------------------------------------------------------------------------

SYS AS SYSDBA@devcedb>select count(*) from x$bh where obj='102822';

COUNT(*)
---------- ---從x$bh查詢快取blocks，要用DBA_OBJECTS.DATA_OBJECT_ID
268 ---該索引載入到buffer pool的資料區塊數，該索引分配有384 blocks，266 LEAF_BLOCKS

那麼索引空塊會不會被重用呢？下面測試說明：
SCOTT @devcedb>select count(*) from obj2;

COUNT(*)
----------
62144

SCOTT @devcedb>create unique index uni_ind_obj2_id on obj2(id);

Index created. --該索引分配有256 blocks，130 LEAF_BLOCKS

SCOTT @devcedb>delete obj2 where rownum<15537;

15536 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 240063 938727 25.5732497 25.5732626

SCOTT @devcedb>insert into obj2 select seq_obj2.nextval id,a.* from dba_objects a;

15537 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 7180 938727 .764865611 .764882473

SYS AS SYSDBA@devcedb>select INDEX_NAME,LEAF_BLOCKS,BLEVEL from dba_indexes where INDEX_NAME=upper('uni_ind_obj2_id');

INDEX_NAME LEAF_BLOCKS BLEVEL
------------------------------ ----------- ----------
UNI_IND_OBJ2_ID 130 1

這裡我們看到，索引的片段降低了，而且LEAF_BLOCKS的數量沒有增加，說明空葉塊被重用了。

當刪除表裡的一條記錄時，其對應於索引裡的索引條目並不會被物理的刪除，只是做了一個刪除標記（這可以通過dump 索引資料區塊alter system dump datafile # block #;可以看到類似”row#0[443] flag: ---D-, lock: 2“。)當一個空葉塊被重用的時候，當第一條資料插入該索引葉塊之前，Oracle清空該空葉塊上所有打上D標識位的索引條目，然後重用該索引塊。如下：
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 307878 835601 36.8450971 36.8625788 837792 1279392 66

SCOTT @devcedb>insert into obj select a.* from dba_objects a where rownum<5; --該索引存在大量空索引塊，我們插入4條記錄

4 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 306312 834091 36.7240505 36.7303962 836282 1279392 66

我們關注下USED_SPACE和BTREE_SPACE
USED_SPACE--Total space that is currently being used in the B-Tree
BTREE_SPACE --Total space currently allocated in the B-Tree
重新分析該索引後我們注意到USED_SPACE反而降低了，BTREE_SPACE不變（當然我們也可以看到LF_ROWS減少了），在這4條資料重用一個空索引塊後，釋放的空間大於使用的空間，該空葉塊被重用了。

半空葉塊是如何重用的呢？
我們構想一下，一個有兩個葉塊的index，第一個葉塊包含1到10(不包含6），第二個葉塊11到20，這時候我們刪除表中2,4,11的資料，分析下索引後，分三種情況：1）插入鍵之前刪除的索引值，插入2，索引葉塊1標識為D的兩條索引記錄會被清空，重新插入索引值為2的記錄，索引空間被重用，BTREE_SPACE不變，USED_SPACE降低,LF_ROWS減1。2）插入一個屬於原來被刪除索引值範圍內的值，插入6，我們會發現和情況1相同。3）插入比之前索引值更大的值，假定葉塊2空間滿了，會新增一個葉塊。

所以說經常被刪除或更新index索引值，以後幾乎不再會被插入時，空間的重用率很低，片段產生的就越快。

1 2 下一頁