oracle資料庫中索引空間的重用

bfc99發表於2015-04-03
Oracle資料庫索引

以下轉自：http://www.itpub.net/thread-1801436-1-1.html 作者：wei-xh

不知道你是否對這些有過疑問？


我隨機的刪除了表裡的一些記錄，我不知道我的索引空間是不是會被重用？

我刪除了表裡大部分記錄，我不知道表上一個自增的索引的空間會不會被重用？

我列舉了三種場景下索引空間的重用，希望對你有一點點的幫助和提高。


【場景一】

測試的思路：

1）刪除一個索引塊裡的某幾個entry，但是不要刪除完。之所以不刪除完，是考慮到，如果索引塊內的entry被完全刪除，那麼這個索引塊會被加入到索引段頭的空閒bitmap裡，這個場景是我們測試的第二個場景。

2）新開一個事物，往表裡新插入一條記錄，觀察新插記錄會不會導致ORACLE清除索引塊裡的deleted entry。


我們先準備下實驗的環境：建立測試表，共9條資料，在id列上建立索引

drop table test;

create table test as select rownum id,object_name from dba_objects where rownum<10;

create index i on test(id);


我們用ANALYZE對索引做一下分析，然後看一下我們本次實驗比較care的幾個統計值。

ANALYZE INDEX i VALIDATE STRUCTURE;

select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_ROWS DEL_LF_ROWS

---------- -----------

         9           0

LF_ROWS：代表索引上總共的entry數（包含deleted的entry）。

DEL_LF_ROWS：代表索引上總共的deleted的entry，這些entry還沒有被cleanout。


採用ANALYZE分析索引後，我們可以知道這個索引一共9個entry，還沒有被刪除的entry：DEL_LF_ROWS的值為0。


我們繼續實驗，刪除4個entry：

DELETE test WHERE id in (2,4,6,8);

commit;

ANALYZE INDEX i VALIDATE STRUCTURE;

select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_ROWS DEL_LF_ROWS

---------- -----------

         9           4

查詢的結果跟我們的預期相符。索引塊內共9個entry，其中4個是被刪除的。


我們可以通過ORACLE提供的索引tree dump工具來觀察下這個時候索引entry的樣子：

tree dump:

col object_name for a30

select object_name,object_id from dba_objects where object_name in ('I');

OBJECT_NAME                     OBJECT_ID

------------------------------ ----------

I                                  119015

alter session set events 'immediate trace name treedump level 119023';

tree dump 裡的內容：

----- begin tree dump

leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)

----- end tree dump

從tree dump可以看到索引只有一個塊，這是由於實驗的資料量非常少，這個塊既充當了根塊也充當了分支塊還充當了葉子塊。

根據leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我們知道這個索引塊的地址18454860，共有9個entry（nrow: 9）,實際存在的entry有5個（rrow: 5），說明有4個是delete的。


我們再來看看此時索引塊內的entry長什麼樣子：

根據索引塊地址18454860 ，我們來通過工具包轉換成我們需要的格式，然後dump這個索引塊。

SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)

---------------------------------------------- -----------------------------------------------

                                             4                                         1677644

alter system dump datafile 4 block 1677644;

dump的結果：


row#0[8020] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 02

col 1; len 6; (6):  01 19 99 44 00 00

row#1[8008] flag: ---D--, lock: 2, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  01 19 99 44 00 01

row#2[7996] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  01 19 99 44 00 02

row#3[7984] flag: ---D--, lock: 2, len=12

col 0; len 2; (2):  c1 05

col 1; len 6; (6):  01 19 99 44 00 03

row#4[7972] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 06

col 1; len 6; (6):  01 19 99 44 00 04

row#5[7960] flag: ---D--, lock: 2, len=12

col 0; len 2; (2):  c1 07

col 1; len 6; (6):  01 19 99 44 00 05

row#6[7948] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 08

col 1; len 6; (6):  01 19 99 44 00 06

row#7[7936] flag: ---D--, lock: 2, len=12

col 0; len 2; (2):  c1 09

col 1; len 6; (6):  01 19 99 44 00 07

row#8[7924] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 0a

col 1; len 6; (6):  01 19 99 44 00 08


標紅部分---D--，代表這個entry已經被刪除，ORACLE只是做了刪除標記，並未物理的釋放這個entry佔有的空間。


下面的步驟就比較重要了，我們往表裡插入一條記錄：

insert into test values(10,'weixh');

1 row created.

commit;

alter system checkpoint;(寫髒資料）


我們再次dump之前的索引塊：

alter system dump datafile 4 block 1677644;

row#0[8020] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 02

col 1; len 6; (6):  01 19 99 44 00 00

row#1[7996] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  01 19 99 44 00 02

row#2[7972] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 06

col 1; len 6; (6):  01 19 99 44 00 04

row#3[7948] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 08

col 1; len 6; (6):  01 19 99 44 00 06

row#4[7924] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 0a

col 1; len 6; (6):  01 19 99 44 00 08

row#5[7912] flag: ------, lock: 2, len=12------------新插入的entry

col 0; len 2; (2):  c1 0b

col 1; len 6; (6):  01 19 99 46 00 00

----- end of leaf block dump -----

我們從dump的顯示中可以知道，之前的索引entry已經被cleanout，也就是說空間已經被重用了，索引的最後是我們新插入的資料。


通過分析索引的統計資訊，也說明了這一點：一共6個索引entry，不存在被刪除的entry。

ANALYZE INDEX i VALIDATE STRUCTURE;

select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_ROWS DEL_LF_ROWS

---------- -----------

         6           0


場景一總結：

1）任何一個新事務，往葉子塊插入一條記錄，ORACLE都會移除/釋放這個索引塊內所有的已被刪除的entry。

2）在一個隨機插入的索引列上，被刪除空間的重用往往不會存在什麼問題，就像我們看到的，只要一有新插入，空間就會釋放。


【場景二】

很多人對於這個場景有誤解：

一張表的欄位id是自增的，如果我們按照id的某個範圍，把記錄刪除。那麼這些記錄佔用的索引空間將不會被重用。

測試思路：

1）新建一張表，表上有一個自增的索引欄位，一萬記錄數，然後清空表。

2）往表裡再插入一萬記錄，但是這一萬記錄的值要比上面步驟一里的大。

3）觀察索引的葉子塊數有沒有變化



我們來試驗驗證一把。

drop table test;

create table test as select rownum id,object_name from dba_objects where rownum<10000;

create index i on test(id);

ANALYZE INDEX i VALIDATE STRUCTURE;

Index analyzed.

select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21       9999           0

經過上面的步驟，我們建立了一張9999條記錄的一張表，id的值從1到9999。id上有一個普通的索引。

我們然後把表清空。

delete from test;

commit;

ANALYZE INDEX i VALIDATE STRUCTURE;

Index analyzed.

select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21       9999        9999

表清空後，我們分析了索引，查詢INDEX_STATS檢視，也印證了表裡有9999條記錄，而且全部是已經被刪除的，這些記錄一共佔取了21個索引塊。


然後我們往表裡插入10000條記錄，id的值從20001開始遞增，保證不會跟上面的id有重複。如果說這些葉子塊不會被重用的話，我們插入資料後，葉子塊的數目應該是要變多的。


INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;

commit;

ANALYZE INDEX i VALIDATE STRUCTURE;

select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21      10000           0

你發現了什麼？

索引葉子塊的數目沒發生任何的變化！而且，DEL_LF_ROWS的值為0，說明這些被刪除的entry，已經被cleanout，被oracle重用了。


場景二總結：

索引的葉子塊被清空後，會把這個葉子塊做為空閒塊加入到索引段的空閒bitmap裡（如果你使用的是ASSM），這樣後面的事務就可以重新使用這些空閒的索引塊了。需要指出的是，這些被清空的索引塊雖然被加入到了索引的空閒列表中，但是他們仍然存在在索引的邏輯結構中，被分支節點所參照(link)，直到被重新使用，才會被重新的relink。

參照索引大師：http://richardfoote.wordpress.com/2008/07/01/deleted-index-entries-part-v-trouble/

可能需要fan qiang才能看，我把裡面重要的文字摘錄：

Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.


【場景三】

這個場景知道的人比較少，可以在Richard Foote部落格裡找到類似的博文。

延遲塊清除會對索引葉子塊做cleanout，釋放索引的空間。


測試思路：

1）新建一張一萬記錄的表，表上存在一個普通的索引

2）清空表，不要提交

3）發生檢查點

4）提交步驟2的事物

5）分析索引（分析索引會導致物理讀，發生延遲塊清除），檢視索引統計值



drop table test;

create table test as select rownum id,object_name from dba_objects where rownum<10000;

create index i on test(id);

ANALYZE INDEX i VALIDATE STRUCTURE;

select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21       9999           0

我們經過上面的步驟，建立了一個有9999條記錄的表。在id上存在一個普通索引。


delete from test;

另開一個session 執行：alter system flush buffer_cache;

然後對delete的會話進行commit;

上面的步驟，在清空表資料後，把髒資料全部重新整理磁碟，這樣就能保證，ORACLE不能做任何的塊清除。


經過上面兩個場景的鍛鍊，我想你一定能知道，現在索引裡有9999個entry，其中9999個是被刪除的entry。


ANALYZE INDEX i VALIDATE STRUCTURE;

我們通過ANALYZE來分析一下索引，注意哦，這個命令會真實的去讀索引塊，讀取索引塊的過程中，發生延遲塊清除。並且這裡ORACLE會在延遲塊清除的過程中，把索引的被刪除的entry做cleanout，空間也會被釋放了。


select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;     

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21          0           0

可以看到DEL_LF_ROWS，LF_ROWS的值都為0。索引空間已經在ANALYZE後完全釋放了。（並不是ANALYZE的功勞，而是這個操作遞迴導致的延遲塊清楚產生的效果）


來讓我們具體dump下，索引葉子塊現在長什麼樣子。如果像INDEX_STATS裡顯示的，那麼此時索引的葉子塊應該是沒有entry的。


col object_name for a30

select object_name,object_id from dba_objects where object_name in ('I');

OBJECT_NAME                     OBJECT_ID

------------------------------ ----------

I                                  119023

alter session set events 'immediate trace name treedump level 119025';

Session altered.

branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)

   leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)

   leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)

   leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)

   leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)

   leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)

   leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)

   leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)

   leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)

   leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)

   leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)

   leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)

   leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)

   leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)

   leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)

   leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)

   leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)

   leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)

   leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)

   leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)

   leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)

   leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)

----- end tree dump

隨便找個葉子塊dump一下。

SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)

---------------------------------------------- -----------------------------------------------

                                             4                                         1683949

alter system dump datafile 4 block 1683949;

                                             

Leaf block dump

===============

header address 133640292=0x7f73064

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 0

kdxcofbo 36=0x24

kdxcofeo 8032=0x1f60

kdxcoavs 7996

kdxlespl 0

kdxlende 0

kdxlenxt 18461166=0x119b1ee

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

----- end of leaf block dump -----

發現索引葉子塊出了塊頭的一些資訊，沒有任何的entry，真的是空空如也！被刪除的索引entry都已經被ORACLE做了cleanout了。


上面的步驟裡，如果你省去了flush buffer cache這個步驟，查詢的INDEX_STATS的結果就會是下面這樣了。由於commit發生的時候資料都在記憶體中，因此ORACLE會對塊做快速塊清除，快速的塊清除不會去做索引entry的cleanout。：                                             

select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS; 

   LF_BLKS    LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

        21       9999        9999


場景三的總結：

1）一些大的事物由於髒塊較多，非常可能會導致事物提交的時候，一些髒塊已經被刷出磁碟了

2）當這些沒有清除的塊再次被訪問的時候，會發生延遲塊清除。

3）延遲塊清除會把這個塊上所有的被刪除的索引entry做cleanout。


後記：看了上面的三個場景，好幾個同學給我留言說，我們是不是不需要在意這些被標記為刪除的空間，早晚ORACLE會重用這些空間。

其實不能這麼說我給你舉一個場景。有一個業務表emp，上面存在一個索引id，此id列是依據序列生成，是自增的。如果你隨機的刪除了表裡的一些記錄。那麼此時，索引裡的情況會像我說的場景一，索引裡的entry，有刪除的，有沒被刪除的。但是你要知道的是，你的id是依序列遞增的，你後續的插入，只會去增長索引的右邊的葉子塊，不會再用到我們之前做過刪除的索引葉子塊。這個情況下，這些空間就不會被重用。

如果你的id值是隨機生成的，那就不一樣了，一旦你插入到了之前存在刪除entry的葉子塊上，這些空間就可以被重用。

還有就是，雖然ORACLE的索引空間回收機制做的比較好，但是如果你索引的空間一段時間內空出很多的“GAP"，可能對於範圍掃描，也會影響一些效能。因為掃描的索引葉子塊變得多了一些。至於資料做了刪除後，索引要不要重建，也實在是個很大話題，可以另外再討論。


實驗過程中，我們用到了analyze xxx validate structure，此命令使用的時候要小心，除非你已經充分認識到這個命令的風險，否則不要輕易的在生產環境使用。此命令執行過程中，會對錶加一個型別為4的TM鎖，阻塞表上的一切的DML操作。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1485497/，如需轉載，請註明出處，否則將追究法律責任。

相關文章