這篇文章主要介紹“oracle索引頁(yè)塊碎片分析”，在日常操作中，相信很多人在oracle索引頁(yè)塊碎片分析問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”oracle索引頁(yè)塊碎片分析”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

成都網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)建站！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)公司、微信開(kāi)發(fā)、小程序制作、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。核心團(tuán)隊(duì)均擁有互聯(lián)網(wǎng)行業(yè)多年經(jīng)驗(yàn)，服務(wù)眾多知名企業(yè)客戶；涵蓋的客戶類(lèi)型包括：三輪攪拌車(chē)等眾多領(lǐng)域，積累了大量豐富的經(jīng)驗(yàn)，同時(shí)也獲得了客戶的一致贊美！

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

索引和表數(shù)據(jù)是級(jí)聯(lián)關(guān)系的，當(dāng)刪除表數(shù)據(jù)的時(shí)候，索引條目也會(huì)被自動(dòng)刪除，這樣在index leaf
block就會(huì)產(chǎn)生碎片，這也就是在OLTP系統(tǒng)上有大量更新的表上不建議創(chuàng)建大量的索引，很影響性能
有的人說(shuō)刪除過(guò)的索引條目空間不會(huì)被再用，因?yàn)樵趹?yīng)用中不會(huì)再有insert相同的數(shù)據(jù)。其實(shí)這個(gè)
說(shuō)法不完全對(duì)的，除了半空葉塊外，其他的刪除的索引空間是可被再利用的。

eg：
本文的所有實(shí)驗(yàn)都是在如下平臺(tái)測(cè)試：
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;
表已創(chuàng)建。
SQL> insert into test_idx  select seq_test.nextval id,2001 syear, a.* from dba_objects a;
已創(chuàng)建50780行。
SQL> insert into test_idx  select seq_test.nextval id,2002 syear, a.* from dba_objects a;
已創(chuàng)建50780行。
SQL> commit;
提交完成。
SQL> desc test_idx
SQL> create unique index idx_test on test_idx(syear,id) ;
索引已創(chuàng)建。

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查詢(xún)緩存blocks，要用DATA_OBJECT_ID）

SQL>
查看系統(tǒng)現(xiàn)在緩存多少，這個(gè)要用sysdba用戶執(zhí)行
SQL> select count(*) from x$bh where obj=59545 ;
  COUNT(*)
----------
        17

 

查看執(zhí)行計(jì)劃：
SQL> set autot trace exp
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
SQL>
執(zhí)行一次查詢(xún)，讓oracle緩存相應(yīng)的索引block
SQL> set autot trace statis
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已選擇152340行。
SQL>
這個(gè)時(shí)候再看看oracle緩存了多少

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

由原來(lái)的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>
這個(gè)索引idx_test共有418個(gè)葉塊都已經(jīng)被緩存里了，和預(yù)期的是一樣的，下面刪除三分之一的數(shù)據(jù)

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

清空數(shù)據(jù)緩存
SQL> alter system flush buffer_cache;
SQL> alter system flush buffer_cache;
SQL> alter system flush buffer_cache;

再次查詢(xún)，發(fā)現(xiàn)緩存數(shù)有所下降了，從438到396

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

統(tǒng)計(jì)信息
----------------------------------------------------------
          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>
這次查詢(xún)緩存的數(shù)量發(fā)現(xiàn)突然增加很多，從438增加到774
SQL> select count(*) from x$bh where obj=59545 ;
  COUNT(*)
----------
       774

突然增加這么多，推測(cè)是因?yàn)閯h除的那些空索引塊需要重新從磁盤(pán)加載到buffer cache中，所以
緩存的會(huì)突然增加，用alter system flush buffer_cache不能完全清除data cache，下面我reboot
數(shù)據(jù)庫(kù)，再來(lái)查看下

重啟數(shù)據(jù)庫(kù)是為了完全清空緩存的索引

SQL> shutdown immediate;
數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫(kù)。
ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup
ORACLE 例程已經(jīng)啟動(dòng)。
Total System Global Area  574619648 bytes
Fixed Size                  1297944 bytes
Variable Size             192938472 bytes
Database Buffers          373293056 bytes
Redo Buffers                7090176 bytes
數(shù)據(jù)庫(kù)裝載完畢。
數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。

執(zhí)行查詢(xún)，使索引緩存
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已選擇101560行。

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

我可以從查詢(xún)結(jié)果中看到，緩存結(jié)果425和刪除前的438，沒(méi)有太大的變化，而我刪除了三分之一的
數(shù)據(jù)，按理論說(shuō)應(yīng)該緩存的表很少了啊，我們?cè)诓榭船F(xiàn)在的葉塊是多少

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 從結(jié)果來(lái)看，葉塊和刪除前一樣418沒(méi)有變化，這就進(jìn)一步證明索引葉block雖然被刪除了，但是并沒(méi)有
釋放空間，而查詢(xún)語(yǔ)句并不會(huì)跳過(guò)這些刪除的索引塊，所以這些碎片對(duì)性能產(chǎn)生很多的影響。

那如何完全刪除索引葉塊呢？
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>
通過(guò)以上結(jié)果可以看到刪除的索引葉塊的空間被釋放了
在刪除了2001年后 在insert2003年的

SQL>  insert into test_idx  select seq_test.nextval id,2003 syear, a.* from dba_objects a;
已創(chuàng)建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>

從查詢(xún)結(jié)果來(lái)看，索引的總的塊數(shù)為512，在delete和insert后沒(méi)有增長(zhǎng)，說(shuō)明索引刪除的空間
被重用了啊
什么是半空葉塊（Half Empty Leaf Blocks）

一個(gè)葉塊（ Leaf Block）是用索引鍵值初始化的，當(dāng)某些鍵值被刪除后，這個(gè)葉塊即包含刪除的
索引鍵值，也包含未刪除的索引鍵值，這時(shí)這個(gè)塊就被稱(chēng)為”Half Empty Leaf Blocks“。

下面還是以test_idx為例

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

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

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

SQL>  insert into test_idx  select seq_test.nextval id,2005 syear, a.* from dba_objects a ;
已創(chuàng)建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行數(shù)據(jù)

SQL>  insert into test_idx  select seq_test.nextval id,2006 syear, a.* from dba_objects a ;
已創(chuàng)建50781行。

SQL>  insert into test_idx  select seq_test.nextval id,2006 syear, a.* from dba_objects a ;
已創(chuàng)建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行數(shù)據(jù)，再重新添加101562行數(shù)據(jù)，可索引塊卻增加了1408-1152=256個(gè)數(shù)據(jù)塊，所以說(shuō)半空塊
索引并沒(méi)有被重用。從下面的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';
會(huì)話已更改。

然后查看對(duì)應(yīng)的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)
解釋?zhuān)?leaf block包含400行，這個(gè)塊已經(jīng)刪除了200行的鍵值
識(shí)別索引是否有碎片

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

運(yùn)行命令后，然后在視圖 index_stats查詢(xún)，這個(gè)視圖記錄索引的一些信息，這個(gè)視圖只有一個(gè)記錄，所以在同一時(shí)間只能分析一個(gè)索引。

1.刪除的行數(shù)如占總的行數(shù)的30%，即del_lf_rows / lf_rows > 0.3，那就考慮索引碎片整理
2.如果”hight“大于4，可以考慮碎片整理
3.如果索引的行數(shù)（LF_rows）遠(yuǎn)遠(yuǎn)小于‘LF_BLKS’ ，那就說(shuō)明有了一個(gè)大的刪除動(dòng)作，需要整理碎片

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

到此，關(guān)于“oracle索引頁(yè)塊碎片分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！

文章名稱(chēng)：oracle索引頁(yè)塊碎片分析
文章網(wǎng)址：http://www.rwnh.cn/article30/jddpso.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、網(wǎng)站內(nèi)鏈、Google、服務(wù)器托管、App設(shè)計(jì)、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)