oracle索引頁塊碎片分析

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

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>  insert into test_idx  select seq_test.nextval id,2005 syear, a.* from dba_objects a ;
已創建50781行。

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

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>  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索引頁塊碎片分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！