oracle索引頁塊碎片分析

155次閱讀

共計 8173 個字符，預計需要花費 21 分鐘才能閱讀完成。

這篇文章主要介紹“oracle 索引頁塊碎片分析”，在日常操作中，相信很多人在 oracle 索引頁塊碎片分析問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”oracle 索引頁塊碎片分析”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

oracle 的標準索引結構是 B×tree 結構，一個 B×tree 結構由三種 block 組成
根塊 (root block): 在 B×tree 里有且只有一個 block，所有訪問索引都從這開始，root block 下有很多 child blocks。
分支塊（Branch blocks）: 這是中間層，branch block 是沒有什么限制的，它是隨著 leaf block 的增加而增加的，branch block 一般是 4 層，如果多于 4 層，就影響性能了。在我們刪除行時，branch block 是不被刪除的。
葉塊（leaf block）：葉塊是最底層，上面存儲著索引條目和 rowid

索引和表數據是級聯關系的，當刪除表數據的時候，索引條目也會被自動刪除，這樣在 index leaf
block 就會產生碎片，這也就是在 OLTP 系統上有大量更新的表上不建議創建大量的索引，很影響性能
有的人說刪除過的索引條目空間不會被再用，因為在應用中不會再有 insert 相同的數據。其實這個
說法不完全對的，除了半空葉塊外，其他的刪除的索引空間是可被再利用的。

eg：
本文的所有實驗都是在如下平臺測試：
SQL select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
SQL create table test_idx as select seq_test.nextval id,2000 syear, a.* from dba_objects a;
表已創建。
SQL insert into test_idx select seq_test.nextval id,2001 syear, a.* from dba_objects a;
已創建 50780 行。
SQL insert into test_idx select seq_test.nextval id,2002 syear, a.* from dba_objects a;
已創建 50780 行。
SQL commit;
提交完成。
SQL desc test_idx
SQL create unique index idx_test on test_idx(syear,id) ;
索引已創建。

SQL select segment_name , bytes/1024/1024 , blocks, tablespace_name , extents
from dba_segments
where segment_name = IDX_TEST

SQL select object_name, object_id, data_object_id
From dba_objects
where object_NAME= IDX_TEST

OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
———- ———- ————–
IDX_TEST 59545 59545

———Used to join X$BH table（從 x$bh 查詢緩存 blocks，要用 DATA_OBJECT_ID）

SQL

查看系統現在緩存多少，這個要用 sysdba 用戶執行
SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
17

查看執行計劃：
SQL set autot trace exp
SQL select syear,id from test_idx where syear =2000 and syear =2002;
SQL
執行一次查詢，讓 oracle 緩存相應的索引 block
SQL set autot trace statis
SQL select syear,id from test_idx where syear =2000 and syear =2002;
已選擇 152340 行。
SQL
這個時候再看看 oracle 緩存了多少

SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
438

由原來的 17 增加到 438

SQL analyze index idx_test validate structure;
索引已分析
SQL select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows From index_stats;

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
———- ———- ———- ———- ———- ———- ———–
2 512 418 152340 1 417 0

SQL
這個索引 idx_test 共有 418 個葉塊都已經被緩存里了，和預期的是一樣的，下面刪除三分之一的數據

SQL delete from test_idx where syear=2001;
SQL commit;

清空數據緩存
SQL alter system flush buffer_cache;
SQL alter system flush buffer_cache;
SQL alter system flush buffer_cache;

再次查詢，發現緩存數有所下降了，從 438 到 396

SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
396
再次執行查詢，讓其緩存索引塊
SQL set autot trace stat
SQL select syear,id from test_idx where syear =2000 and syear =2002;
已選擇 101560 行。

統計信息
———————————————————-
0 recursive calls
0 db block gets
7186 consistent gets
425 physical reads
0 redo size
1976416 bytes sent via SQL*Net to client
74870 bytes received via SQL*Net from client
6772 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101560 rows processed

SQL
這次查詢緩存的數量發現突然增加很多，從 438 增加到 774
SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
774

突然增加這么多，推測是因為刪除的那些空索引塊需要重新從磁盤加載到 buffer cache 中，所以
緩存的會突然增加，用 alter system flush buffer_cache 不能完全清除 data cache，下面我 reboot
數據庫，再來查看下

重啟數據庫是為了完全清空緩存的索引

SQL shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。

SQL startup
ORACLE 例程已經啟動。
Total System Global Area 574619648 bytes
Fixed Size 1297944 bytes
Variable Size 192938472 bytes
Database Buffers 373293056 bytes
Redo Buffers 7090176 bytes
數據庫裝載完畢。
數據庫已經打開。

執行查詢，使索引緩存
SQL select syear,id from test_idx where syear =2000 and syear =2002;
已選擇 101560 行。

再來看緩存的多少
SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
425

我可以從查詢結果中看到，緩存結果 425 和刪除前的 438，沒有太大的變化，而我刪除了三分之一的
數據，按理論說應該緩存的表很少了啊，我們在查看現在的葉塊是多少

SQL analyze index idx_test validate structure;
索引已分析

SQL select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows from index_stats;

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
———- ———- ———- ———- ———- ———- ———–
2 512 418 152340 1 417 50780
從結果來看，葉塊和刪除前一樣 418 沒有變化，這就進一步證明索引葉 block 雖然被刪除了，但是并沒有
釋放空間，而查詢語句并不會跳過這些刪除的索引塊，所以這些碎片對性能產生很多的影響。

那如何完全刪除索引葉塊呢？
SQL alter index idx_test rebuild nologging online;
索引已更改。

SQL analyze index idx_test validate structure;
索引已分析

SQL select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr
om index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
———- ———- ———- ———- ———- ———- ———–
2 384 276 101560 1 275 0
SQL

SQL select count(*) from x$bh where obj=59545 ;
COUNT(*)
———-
139

SQL
通過以上結果可以看到刪除的索引葉塊的空間被釋放了
在刪除了 2001 年后在 insert2003 年的

SQL insert into test_idx select seq_test.nextval id,2003 syear, a.* from dba_objects a;
已創建 50781 行。

SQL commit;
提交完成。

SQL select segment_name , bytes/1024/1024 ,
2 blocks, tablespace_name , extents
3 from dba_segments
4 where segment_name = IDX_TEST
——————————————————————————–
SEGMENT_NAME BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
————— ———- —————————— ———-
IDX_TEST 4 512 USERS 19

SQL analyze index idx_test validate structure;
索引已分析

SQL select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
———- ———- ———- ———- ———- ———- ———–
2 512 403 152341 1 402 0
SQL

從查詢結果來看，索引的總的塊數為 512，在 delete 和 insert 后沒有增長，說明索引刪除的空間
被重用了啊
什么是半空葉塊（Half Empty Leaf Blocks）

一個葉塊（Leaf Block）是用索引鍵值初始化的，當某些鍵值被刪除后，這個葉塊即包含刪除的
索引鍵值，也包含未刪除的索引鍵值，這時這個塊就被稱為”Half Empty Leaf Blocks“。

下面還是以 test_idx 為例

SQL insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_objects a ;
已創建 50781 行。

SQL commit;
提交完成。
SQL select segment_name , bytes/1024/1024 ,
blocks, tablespace_name , extents
from dba_segments
where segment_name = IDX_TEST
——————————————————————————–
SEGMENT_NAME BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
————— ———- —————————— ———-
IDX_TEST 1152 USERS 24
SQL delete from test_idx where syear=2005 and mod(id,2)=0;
已刪除 101562 行。

SQL commit;
提交完成。

在重新插入 101562 行數據

SQL insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_objects a ;
已創建 50781 行。

SQL select segment_name , bytes/1024/1024 ,
blocks, tablespace_name , extents
from dba_segments
where segment_name = IDX_TEST

SEGMENT_NAME
——————————————————————————–

BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
————— ———- —————————— ———-
IDX_TEST
11 1408 USERS 26

SQL
刪除了 101562 行數據，再重新添加 101562 行數據，可索引塊卻增加了 1408-1152=256 個數據塊，所以說半空塊
索引并沒有被重用。從下面的 trace 也可以看出

SQL select object_id from dba_objects where object_name= IDX_TEST
OBJECT_ID
———-
59545

得到 tree 的 dump 的命令如下

SQL alter session set events immediate trace name treedump level 59545
會話已更改。

然后查看對應的 trace 文件，如下所示：

branch: 0x100972c 16815916 (0: nrow: 3, level: 2)
branch: 0x1007fe5 16809957 (-1: nrow: 511, level: 1)
leaf: 0x100972d 16815917 (-1: nrow: 378 rrow: 378)
leaf: 0x100972e 16815918 (0: nrow: 378 rrow: 378)
.
.
.
leaf: 0x1007ff2 16809970 (14: nrow: 400 rrow: 400)
leaf: 0x1007ff6 16809974 (15: nrow: 400 rrow: 332)
leaf: 0x1007ffa 16809978 (16: nrow: 400 rrow: 200)
leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)—————– Half empty blocks
leaf: 0x1008002 16809986 (18: nrow: 400 rrow: 200)
.
.
.
leaf: 0x1009f86 16818054 (19: nrow: 400 rrow: 200)
leaf: 0x1009f4b 16817995 (20: nrow: 400 rrow: 400)
.
.
.
leaf: 0x1009f4f 16817999 (21: nrow: 400 rrow: 400)
leaf: 0x100a15f 16818527 (274: nrow: 56 rrow: 56)

leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)
解釋：leaf block 包含 400 行，這個塊已經刪除了 200 行的鍵值
識別索引是否有碎片

獲得關于索引的信息，用下面的命令
analyze index index_name validate structure 或 validate index index_name
analyze index index1 validate structure：是用來分析索引的數據塊是否有壞塊，以及根據分析得到的數據（存放在 index_stats）來判斷索引是否需要重新建立。

運行命令后，然后在視圖 index_stats 查詢，這個視圖記錄索引的一些信息，這個視圖只有一個記錄，所以在同一時間只能分析一個索引。

1. 刪除的行數如占總的行數的 30%，即 del_lf_rows / lf_rows 0.3，那就考慮索引碎片整理
2. 如果”hight“大于 4，可以考慮碎片整理
3. 如果索引的行數（LF_rows）遠遠小于‘LF_BLKS’，那就說明有了一個大的刪除動作，需要整理碎片

索引碎片整理方法
1. recreate index
2. alter index skate.idx_test rebuild nologging parallel 4 online ;
3. 如果葉塊是 half-empty or near empty，可以運行“alter index coalesce”來合并

到此，關于“oracle 索引頁塊碎片分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完