溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“怎么理解oracle索引塊分裂”，在日常操作中，相信很多人在怎么理解oracle索引塊分裂問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么理解oracle索引塊分裂”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
B*tree索引是包括oracle在內(nèi)的數(shù)據(jù)庫中最常見的索引結(jié)構(gòu)。它是一種類似樹狀結(jié)構(gòu)的索引，最底層的塊叫做葉塊（leaf block）。葉節(jié)點（leaf node）這一層實際上是一個雙向鏈表。如果是一個高度（height）為3的B*tree索引，那么葉塊上一層叫做分支塊（branch block）。最頂層叫做根塊（root block）。根塊是索引的入口。但實際上，根塊也可以看做是分支塊的一種，所以B*tree索引實際只包含兩種塊：分支塊和葉塊。分支塊用來search，葉塊用來存儲鍵值。
什么是索引塊分裂呢？
簡單來說，索引塊分裂就是當事務的增刪改操作需要去維護索引，如果索引塊沒有足夠的空間容納新數(shù)據(jù)，就要新增一個索引塊，舊的索引塊數(shù)據(jù)一分為二，將其中一部分數(shù)據(jù)搬遷到新增的索引塊上面去。這里的一分為二并不一定是平分，有可能是50-50的平分，也可能是90-10的分裂。這里足夠的空間也不一定就是指該塊的空閑空間，還有可能是ITL事務槽不夠用了。
與索引塊分裂有關(guān)的等待事件是enq: TX – index contention，同時可能會伴隨row lock waits。
索引塊分裂分為下面四種情況：
l 葉節(jié)點90-10分裂
l 葉節(jié)點50-50分裂
l 分支節(jié)點分裂
l 根節(jié)點分裂
其中，根節(jié)點分裂可以看做特殊的分支節(jié)點分裂。
當葉節(jié)點空間不夠發(fā)生分裂時，會多一個葉塊出來；葉塊持續(xù)增加，當分支塊的空間也不夠用時，分支塊也要發(fā)生分裂；當分支塊持續(xù)增加，根塊空間不夠時，根塊也要發(fā)生分裂。而根塊的分裂會使得B*tree索引的高度（height）增加。如果這種增加是不必要的（高度與索引規(guī)模不匹配），那么這會增加不必要的IO，造成索引性能衰退。這種情況是要避免的，此時可以通過重建索引來減小高度。
發(fā)生90-10分裂時，表的事務并發(fā)量較低，索引的鍵值是單調(diào)遞增的，這種情況下，只會把較大的那部分索引鍵值搬遷到新塊上，這樣新塊上空閑空間較多，滿足后續(xù)遞增檢查插入。而50-50分裂是在高并發(fā)的情況下發(fā)生的，而且對索引鍵值的操控是無序的，但是插入的值還是會比塊上的最大值要小，這樣每個分裂的塊上都會有一半的空閑空間，這樣高并發(fā)無序的維護索引的時候才會成本較低。
想象一下，如果高并發(fā)無序維護索引時，索引分裂時按照90-10的方式，那么新塊的空閑空間是足夠大了，但是舊塊上幾乎還是滿的，無法進行維護，這樣會拖累整個索引維護行為。而如果低并發(fā)有序操縱索引的行為是按照50-50的方式分裂，那么舊塊上因為鍵值的有序性不會再插入數(shù)據(jù)，會造成空間浪費，索引塊數(shù)增加造成索引效率下降，這也是不可取的。
下面模擬葉節(jié)點90-10分裂：
創(chuàng)建測試表：
SQL> create table tab91 (id number,name varchar2(100),created date); |
創(chuàng)建索引,這里指定pctfree參數(shù)為0,即在一個塊上不保留空間：
SQL> create index idx_tab91_01 on tab91(id) pctfree 0; |
這里先簡單介紹一個oralce的診斷事件：10224。該事件可以轉(zhuǎn)儲索引的分隔與刪除。用法：
開啟: ALTER SESSION SET EVENTS '10224 trace name context forever , level 1'; ALTER SYSTEM SET EVENTS '10224 trace name context forever , level 1'; 關(guān)閉: ALTER SESSION SET EVENTS '10224 trace name context off'; ALTER SYSTEM SET EVENTS '10224 trace name context off'; |
再簡單介紹一個轉(zhuǎn)儲索引結(jié)構(gòu)的語句：
alter session set events ‘immediate trace name treedump level n’; n:索引的object_id select object_id from dba_objects where object_name=upper(‘idx_tab91_01’); |
低并發(fā)，正常插入數(shù)據(jù)時，維護索引的行為：
SQL> ALTER SESSION SET EVENTS '10224 trace name context forever , level 1';
declare begin for i in 1 .. 10000 loop insert into tab91 values (i,'mingshuo',sysdate); end loop; commit; end; /
SQL> ALTER SESSION SET EVENTS '10224 trace name context off'; |
檢查索引信息：
SQL> analyze index idx_tab91_01 validate structure; Index analyzed.
SQL> set line 200 SQL> col NAME for a20 SQL> select height,blocks,name,lf_rows,lf_blks,pct_used from index_stats;
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED ---------- ---------- -------------------- ---------- ---------- ---------- 2 24 IDX_TAB91_01 10000 19 94 |
可以看到葉塊的個數(shù)為19.
10224事件生成的trace文件：
splitting leaf,dba 0x01c0059b,time 03:03:15.462
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c0059b,time 03:03:15.559
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059b,time 03:03:15.559
kdisnew_bseg_srch_cbk using block,dba 0x01c0059f,time 03:03:15.560
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c0059f,time 03:03:15.560
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059f,time 03:03:15.560
kdisnew_bseg_srch_cbk using block,dba 0x01c0059c,time 03:03:15.560
splitting leaf,dba 0x01c0059c,time 03:03:15.597
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c0059c,time 03:03:15.597
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059c,time 03:03:15.597
kdisnew_bseg_srch_cbk using block,dba 0x01c0059d,time 03:03:15.597
splitting leaf,dba 0x01c0059d,time 03:03:15.622
kdisnew_bseg_srch_cbk using block,dba 0x01c0059e,time 03:03:15.622
splitting leaf,dba 0x01c0059e,time 03:03:15.633
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c0059e,time 03:03:15.633
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059e,time 03:03:15.633
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c0059d,time 03:03:15.633
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059d,time 03:03:15.633
kdisnew_bseg_srch_cbk using block,dba 0x01c005ab,time 03:03:15.633
splitting leaf,dba 0x01c005ab,time 03:03:15.644
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ab,time 03:03:15.644
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ab,time 03:03:15.644
kdisnew_bseg_srch_cbk using block,dba 0x01c005ac,time 03:03:15.644
splitting leaf,dba 0x01c005ac,time 03:03:15.654
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ac,time 03:03:15.669
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ac,time 03:03:15.669
kdisnew_bseg_srch_cbk using block,dba 0x01c005ad,time 03:03:15.669
splitting leaf,dba 0x01c005ad,time 03:03:15.696
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ad,time 03:03:15.696
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ad,time 03:03:15.696
kdisnew_bseg_srch_cbk using block,dba 0x01c005ae,time 03:03:15.696
splitting leaf,dba 0x01c005ae,time 03:03:15.706
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ae,time 03:03:15.706
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ae,time 03:03:15.706
kdisnew_bseg_srch_cbk using block,dba 0x01c005af,time 03:03:15.706
splitting leaf,dba 0x01c005af,time 03:03:15.715
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005af,time 03:03:15.715
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005af,time 03:03:15.715
kdisnew_bseg_srch_cbk using block,dba 0x01c005a8,time 03:03:15.715
splitting leaf,dba 0x01c005a8,time 03:03:15.727
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005a8,time 03:03:15.727
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005a8,time 03:03:15.727
kdisnew_bseg_srch_cbk using block,dba 0x01c005a9,time 03:03:15.727
splitting leaf,dba 0x01c005a9,time 03:03:15.738
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005a9,time 03:03:15.738
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005a9,time 03:03:15.738
kdisnew_bseg_srch_cbk using block,dba 0x01c005aa,time 03:03:15.738
splitting leaf,dba 0x01c005aa,time 03:03:15.747
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005aa,time 03:03:15.747
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005aa,time 03:03:15.747
kdisnew_bseg_srch_cbk using block,dba 0x01c005c6,time 03:03:15.747
splitting leaf,dba 0x01c005c6,time 03:03:15.757
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005c6,time 03:03:15.757
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c6,time 03:03:15.757
kdisnew_bseg_srch_cbk using block,dba 0x01c005c7,time 03:03:15.757
splitting leaf,dba 0x01c005c7,time 03:03:15.768
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005c7,time 03:03:15.768
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c7,time 03:03:15.768
kdisnew_bseg_srch_cbk using block,dba 0x01c005c1,time 03:03:15.768
splitting leaf,dba 0x01c005c1,time 03:03:15.778
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005c1,time 03:03:15.778
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c1,time 03:03:15.778
kdisnew_bseg_srch_cbk using block,dba 0x01c005c2,time 03:03:15.778
splitting leaf,dba 0x01c005c2,time 03:03:15.789
kdisnew_bseg_srch_cbk using block,dba 0x01c005c3,time 03:03:15.789
splitting leaf,dba 0x01c005c3,time 03:03:15.799
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005c3,time 03:03:15.799
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c3,time 03:03:15.799
kdisnew_bseg_srch_cbk using block,dba 0x01c005c4,time 03:03:15.799
splitting leaf,dba 0x01c005c4,time 03:03:15.807
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005c4,time 03:03:15.807
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c4,time 03:03:15.807
kdisnew_bseg_srch_cbk using block,dba 0x01c005c5,time 03:03:15.807
查詢：
SQL> select s.SID, n.NAME, s.VALUE 2 from v$sesstat s, v$statname n 3 where s.STATISTIC# = n.STATISTIC# 4 and sid in (select userenv('SID') from dual) 5 and n.NAME like '%split%';
SID NAME VALUE ---------- ------------------------------ ---------- 1 leaf node splits 19 1 leaf node 90-10 splits 18 1 branch node splits 0 1 root node splits 0 1 queue splits 0 |
可以看到發(fā)生了18次葉節(jié)點90-10分裂，所以葉塊正好是19個。
轉(zhuǎn)儲索引結(jié)構(gòu)：
SQL> select object_id from dba_objects where object_name=upper('idx_tab91_01') and owner='SCOTT';
OBJECT_ID ---------- 77956 SQL> alter session set events 'immediate trace name treedump level 77956'; |
Dump信息：
----- begin tree dump
branch: 0x1c0059b 29361563 (0: nrow: 19, level: 1)
leaf: 0x1c0059f 29361567 (-1: nrow: 540 rrow: 540)
leaf: 0x1c0059c 29361564 (0: nrow: 533 rrow: 533)
leaf: 0x1c0059d 29361565 (1: nrow: 533 rrow: 533)
leaf: 0x1c0059e 29361566 (2: nrow: 533 rrow: 533)
leaf: 0x1c005ab 29361579 (3: nrow: 533 rrow: 533)
leaf: 0x1c005ac 29361580 (4: nrow: 533 rrow: 533)
leaf: 0x1c005ad 29361581 (5: nrow: 533 rrow: 533)
leaf: 0x1c005ae 29361582 (6: nrow: 533 rrow: 533)
leaf: 0x1c005af 29361583 (7: nrow: 533 rrow: 533)
leaf: 0x1c005a8 29361576 (8: nrow: 533 rrow: 533)
leaf: 0x1c005a9 29361577 (9: nrow: 533 rrow: 533)
leaf: 0x1c005aa 29361578 (10: nrow: 533 rrow: 533)
leaf: 0x1c005c6 29361606 (11: nrow: 533 rrow: 533)
leaf: 0x1c005c7 29361607 (12: nrow: 533 rrow: 533)
leaf: 0x1c005c1 29361601 (13: nrow: 533 rrow: 533)
leaf: 0x1c005c2 29361602 (14: nrow: 533 rrow: 533)
leaf: 0x1c005c3 29361603 (15: nrow: 533 rrow: 533)
leaf: 0x1c005c4 29361604 (16: nrow: 533 rrow: 533)
leaf: 0x1c005c5 29361605 (17: nrow: 399 rrow: 399)
----- end tree dump
因為之前設(shè)置的pctfree為0，所以一個葉塊可認為能夠存儲533行左右。
下面模擬葉節(jié)點50-50分裂：
創(chuàng)建測試表：
SQL> create table tab55 (id number,name varchar2(100),created date); |
創(chuàng)建索引,這里指定pctfree參數(shù)為0,即在一個塊上不保留空間：
SQL> create index idx_tab55_01 on tab55(id) pctfree 0; |
倒序插入數(shù)據(jù)時，維護索引的行為：
SQL> ALTER SESSION SET EVENTS '10224 trace name context forever , level 1';
declare begin for i in 1 .. 10000 loop insert into tab55 values (10001-i,'mingshuo',sysdate); end loop; commit; end; /
SQL> ALTER SESSION SET EVENTS '10224 trace name context off'; |
檢查索引信息：
SQL> analyze index idx_tab55_01 validate structure; Index analyzed.
SQL> set line 200 SQL> col NAME for a20 SQL> select height,blocks,name,lf_rows,lf_blks,pct_used from index_stats;
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED ---------- ---------- -------------------- ---------- ---------- ---------- 2 48 IDX_TAB55_01 10000 38 49 |
可以看到葉塊的個數(shù)為38.
10224事件生成的trace文件：
splitting leaf,dba 0x01c005e3,time 03:36:47.553
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e3,time 03:36:47.701
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e3,time 03:36:47.701
kdisnew_bseg_srch_cbk using block,dba 0x01c005e7,time 03:36:47.703
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e7,time 03:36:47.703
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e7,time 03:36:47.703
kdisnew_bseg_srch_cbk using block,dba 0x01c005e4,time 03:36:47.703
splitting leaf,dba 0x01c005e7,time 03:36:47.723
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e4,time 03:36:47.723
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e4,time 03:36:47.723
kdisnew_bseg_srch_cbk using block,dba 0x01c005e5,time 03:36:47.723
splitting leaf,dba 0x01c005e7,time 03:36:47.734
kdisnew_bseg_srch_cbk using block,dba 0x01c005e6,time 03:36:47.734
splitting leaf,dba 0x01c005e7,time 03:36:47.744
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e6,time 03:36:47.745
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e6,time 03:36:47.745
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e5,time 03:36:47.745
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e5,time 03:36:47.745
kdisnew_bseg_srch_cbk using block,dba 0x01c005ef,time 03:36:47.746
splitting leaf,dba 0x01c005e7,time 03:36:47.757
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ef,time 03:36:47.757
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ef,time 03:36:47.757
kdisnew_bseg_srch_cbk using block,dba 0x01c005e8,time 03:36:47.757
splitting leaf,dba 0x01c005e7,time 03:36:47.766
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e8,time 03:36:47.766
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e8,time 03:36:47.766
kdisnew_bseg_srch_cbk using block,dba 0x01c005e9,time 03:36:47.766
splitting leaf,dba 0x01c005e7,time 03:36:47.774
kdisnew_bseg_srch_cbk using block,dba 0x01c005ec,time 03:36:47.784
splitting leaf,dba 0x01c005e7,time 03:36:47.789
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ec,time 03:36:47.790
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ec,time 03:36:47.790
kdisnew_bseg_srch_cbk using block,dba 0x01c005ed,time 03:36:47.790
splitting leaf,dba 0x01c005e7,time 03:36:47.796
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ed,time 03:36:47.796
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ed,time 03:36:47.796
kdisnew_bseg_srch_cbk using block,dba 0x01c005ee,time 03:36:47.796
splitting leaf,dba 0x01c005e7,time 03:36:47.801
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ee,time 03:36:47.801
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ee,time 03:36:47.802
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005e9,time 03:36:47.802
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005e9,time 03:36:47.802
kdisnew_bseg_srch_cbk using block,dba 0x01c005ea,time 03:36:47.802
splitting leaf,dba 0x01c005e7,time 03:36:47.806
kdisnew_bseg_srch_cbk using block,dba 0x01c005eb,time 03:36:47.806
splitting leaf,dba 0x01c005e7,time 03:36:47.813
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005eb,time 03:36:47.813
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005eb,time 03:36:47.813
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ea,time 03:36:47.813
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ea,time 03:36:47.813
kdisnew_bseg_srch_cbk using block,dba 0x01c005fc,time 03:36:47.814
splitting leaf,dba 0x01c005e7,time 03:36:47.819
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005fc,time 03:36:47.819
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005fc,time 03:36:47.819
kdisnew_bseg_srch_cbk using block,dba 0x01c005fd,time 03:36:47.819
splitting leaf,dba 0x01c005e7,time 03:36:47.903
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005fd,time 03:36:47.903
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005fd,time 03:36:47.903
kdisnew_bseg_srch_cbk using block,dba 0x01c005fe,time 03:36:47.903
splitting leaf,dba 0x01c005e7,time 03:36:47.908
splitting leaf,dba 0x01c005e7,time 03:36:47.909
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005fe,time 03:36:47.909
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005fe,time 03:36:47.909
kdisnew_bseg_srch_cbk using block,dba 0x01c005ff,time 03:36:47.909
splitting leaf,dba 0x01c005e7,time 03:36:47.914
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005ff,time 03:36:47.914
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005ff,time 03:36:47.914
kdisnew_bseg_srch_cbk using block,dba 0x01c005f9,time 03:36:47.914
splitting leaf,dba 0x01c005e7,time 03:36:47.920
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005f9,time 03:36:47.920
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005f9,time 03:36:47.920
kdisnew_bseg_srch_cbk using block,dba 0x01c005fa,time 03:36:47.920
splitting leaf,dba 0x01c005e7,time 03:36:47.926
kdisnew_bseg_srch_cbk using block,dba 0x01c005fb,time 03:36:47.926
splitting leaf,dba 0x01c005e7,time 03:36:47.932
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005fb,time 03:36:47.932
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005fb,time 03:36:47.932
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01c005fa,time 03:36:47.932
kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005fa,time 03:36:47.932
kdisnew_bseg_srch_cbk using block,dba 0x0300008f,time 03:36:47.932
splitting leaf,dba 0x01c005e7,time 03:36:47.938
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008f,time 03:36:47.938
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008f,time 03:36:47.938
kdisnew_bseg_srch_cbk using block,dba 0x03000088,time 03:36:47.938
splitting leaf,dba 0x01c005e7,time 03:36:47.943
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03000088,time 03:36:47.943
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03000088,time 03:36:47.943
kdisnew_bseg_srch_cbk using block,dba 0x03000089,time 03:36:47.943
splitting leaf,dba 0x01c005e7,time 03:36:47.948
kdisnew_bseg_srch_cbk using block,dba 0x0300008c,time 03:36:47.949
splitting leaf,dba 0x01c005e7,time 03:36:47.954
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008c,time 03:36:47.954
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008c,time 03:36:47.954
kdisnew_bseg_srch_cbk using block,dba 0x0300008d,time 03:36:47.954
splitting leaf,dba 0x01c005e7,time 03:36:47.960
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008d,time 03:36:47.960
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008d,time 03:36:47.960
kdisnew_bseg_srch_cbk using block,dba 0x0300008e,time 03:36:47.960
splitting leaf,dba 0x01c005e7,time 03:36:47.965
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008e,time 03:36:47.965
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008e,time 03:36:47.965
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03000089,time 03:36:47.965
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03000089,time 03:36:47.965
kdisnew_bseg_srch_cbk using block,dba 0x0300008a,time 03:36:47.965
splitting leaf,dba 0x01c005e7,time 03:36:47.972
kdisnew_bseg_srch_cbk using block,dba 0x0300008b,time 03:36:47.973
splitting leaf,dba 0x01c005e7,time 03:36:47.981
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008b,time 03:36:47.981
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008b,time 03:36:47.981
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300008a,time 03:36:47.981
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300008a,time 03:36:47.981
kdisnew_bseg_srch_cbk using block,dba 0x0300009c,time 03:36:47.982
splitting leaf,dba 0x01c005e7,time 03:36:47.987
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009c,time 03:36:47.987
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009c,time 03:36:47.987
kdisnew_bseg_srch_cbk using block,dba 0x0300009d,time 03:36:47.987
splitting leaf,dba 0x01c005e7,time 03:36:47.993
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009d,time 03:36:47.993
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009d,time 03:36:47.993
kdisnew_bseg_srch_cbk using block,dba 0x0300009e,time 03:36:47.993
splitting leaf,dba 0x01c005e7,time 03:36:47.999
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009e,time 03:36:47.999
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009e,time 03:36:47.999
kdisnew_bseg_srch_cbk using block,dba 0x0300009f,time 03:36:47.999
splitting leaf,dba 0x01c005e7,time 03:36:48.06
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009f,time 03:36:48.06
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009f,time 03:36:48.06
kdisnew_bseg_srch_cbk using block,dba 0x03000099,time 03:36:48.06
splitting leaf,dba 0x01c005e7,time 03:36:48.13
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03000099,time 03:36:48.13
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03000099,time 03:36:48.13
kdisnew_bseg_srch_cbk using block,dba 0x0300009a,time 03:36:48.13
splitting leaf,dba 0x01c005e7,time 03:36:48.20
kdisnew_bseg_srch_cbk using block,dba 0x0300009b,time 03:36:48.20
splitting leaf,dba 0x01c005e7,time 03:36:48.25
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009b,time 03:36:48.25
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009b,time 03:36:48.25
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x0300009a,time 03:36:48.25
kdisnew_bseg_srch_cbk rejecting block ,dba 0x0300009a,time 03:36:48.25
kdisnew_bseg_srch_cbk using block,dba 0x030000af,time 03:36:48.26
splitting leaf,dba 0x01c005e7,time 03:36:48.31
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x030000af,time 03:36:48.31
kdisnew_bseg_srch_cbk rejecting block ,dba 0x030000af,time 03:36:48.31
kdisnew_bseg_srch_cbk using block,dba 0x030000a8,time 03:36:48.31
splitting leaf,dba 0x01c005e7,time 03:36:48.36
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x030000a8,time 03:36:48.36
kdisnew_bseg_srch_cbk rejecting block ,dba 0x030000a8,time 03:36:48.36
kdisnew_bseg_srch_cbk using block,dba 0x030000a9,time 03:36:48.36
splitting leaf,dba 0x01c005e7,time 03:36:48.43
kdisnew_bseg_srch_cbk using block,dba 0x030000ac,time 03:36:48.43
查詢：
SQL> select s.SID, n.NAME, s.VALUE 2 from v$sesstat s, v$statname n 3 where s.STATISTIC# = n.STATISTIC# 4 and sid in (select sid from v$mystat) 5 and n.NAME like '%split%' 6 ;
SID NAME VALUE ---------- ------------------------------ ---------- 35 leaf node splits 37 35 leaf node 90-10 splits 0 35 branch node splits 0 35 root node splits 0 35 queue splits 0 |
可以看到發(fā)生了37次葉節(jié)點50-50分裂，所以葉塊正好是38個。
轉(zhuǎn)儲索引結(jié)構(gòu)：
SQL> select object_id from dba_objects where object_name=upper('idx_tab55_01') and owner='SCOTT'; OBJECT_ID ---------- 77958 SQL> alter session set events 'immediate trace name treedump level 77958'; |
Dump信息：
----- begin tree dump
branch: 0x1c005e3 29361635 (0: nrow: 38, level: 1)
leaf: 0x1c005e7 29361639 (-1: nrow: 289 rrow: 289)
leaf: 0x30000ac 50331820 (0: nrow: 262 rrow: 262)
leaf: 0x30000a9 50331817 (1: nrow: 262 rrow: 262)
leaf: 0x30000a8 50331816 (2: nrow: 262 rrow: 262)
leaf: 0x30000af 50331823 (3: nrow: 262 rrow: 262)
leaf: 0x300009b 50331803 (4: nrow: 262 rrow: 262)
leaf: 0x300009a 50331802 (5: nrow: 266 rrow: 266)
leaf: 0x3000099 50331801 (6: nrow: 262 rrow: 262)
leaf: 0x300009f 50331807 (7: nrow: 262 rrow: 262)
leaf: 0x300009e 50331806 (8: nrow: 262 rrow: 262)
leaf: 0x300009d 50331805 (9: nrow: 262 rrow: 262)
leaf: 0x300009c 50331804 (10: nrow: 262 rrow: 262)
leaf: 0x300008b 50331787 (11: nrow: 262 rrow: 262)
leaf: 0x300008a 50331786 (12: nrow: 262 rrow: 262)
leaf: 0x300008e 50331790 (13: nrow: 266 rrow: 266)
leaf: 0x300008d 50331789 (14: nrow: 262 rrow: 262)
leaf: 0x300008c 50331788 (15: nrow: 262 rrow: 262)
leaf: 0x3000089 50331785 (16: nrow: 262 rrow: 262)
leaf: 0x3000088 50331784 (17: nrow: 262 rrow: 262)
leaf: 0x300008f 50331791 (18: nrow: 262 rrow: 262)
leaf: 0x1c005fb 29361659 (19: nrow: 262 rrow: 262)
leaf: 0x1c005fa 29361658 (20: nrow: 262 rrow: 262)
leaf: 0x1c005f9 29361657 (21: nrow: 266 rrow: 266)
leaf: 0x1c005ff 29361663 (22: nrow: 262 rrow: 262)
leaf: 0x1c005fe 29361662 (23: nrow: 262 rrow: 262)
leaf: 0x1c005fd 29361661 (24: nrow: 262 rrow: 262)
leaf: 0x1c005fc 29361660 (25: nrow: 262 rrow: 262)
leaf: 0x1c005eb 29361643 (26: nrow: 262 rrow: 262)
leaf: 0x1c005ea 29361642 (27: nrow: 262 rrow: 262)
leaf: 0x1c005ee 29361646 (28: nrow: 262 rrow: 262)
leaf: 0x1c005ed 29361645 (29: nrow: 267 rrow: 267)
leaf: 0x1c005ec 29361644 (30: nrow: 262 rrow: 262)
leaf: 0x1c005e9 29361641 (31: nrow: 262 rrow: 262)
leaf: 0x1c005e8 29361640 (32: nrow: 262 rrow: 262)
leaf: 0x1c005ef 29361647 (33: nrow: 262 rrow: 262)
leaf: 0x1c005e6 29361638 (34: nrow: 262 rrow: 262)
leaf: 0x1c005e5 29361637 (35: nrow: 262 rrow: 262)
leaf: 0x1c005e4 29361636 (36: nrow: 262 rrow: 262)
----- end tree dump
可以看到一個50%的索引塊大概存儲262行左右數(shù)據(jù)。
現(xiàn)在對比一下兩個索引：
索引名稱 | 塊的個數(shù) | 葉塊的個數(shù) | 索引大小/M |
idx_tab91_01 | 24 | 19 | 192 |
idx_tab55_01 | 48 | 38 | 384 |
解決措施：
l重建索引，哈希分區(qū)索引或者建反向鍵索引（reverse key index）。
重建索引可以降低索引樹的高度，反向鍵索引可以杜絕插入操作導致的索引熱塊競爭，但是有利就有弊，反向鍵索引會影響索引范圍掃描的效率。
哈希分區(qū)索引的思路與反向鍵索引是一致的，都是分散索引熱塊。在一些OLTP的環(huán)境中，存在單調(diào)遞增的某些列，在插入這些列的時候，只會向索引塊的右側(cè)插入，這時因為一些索引頁，buffer，latch等爭用以及一些附加的索引維護活動，索引右側(cè)反而變成了hotspot。哈希分區(qū)索引打散索引塊，避免了上述情況下的性能衰退。
l更改序列cache值
有一下單調(diào)遞增的列值是用序列值填充的。設(shè)置大的cache值，不同的實例會采用不同范圍的（noorder）序列值，這樣不同實例插入數(shù)據(jù)的時候插入到同一個塊上的概率就會小的多了。避免了熱塊的出現(xiàn)。用物理上的極限思想來考慮一下，如果cache小到1，那么實例1插入1和實例2插入2大概率在同一個塊上。如果cache值為1億，那么實例1插入1億1和實例2插入1億2不大坑在一個塊上。
l大量數(shù)據(jù)刪除之后rebuild或者shrink索引
l增加索引的PCT_FREE值
高的PCT_FREE值能夠防止ITL槽不夠用。這個參數(shù)主要對update的系統(tǒng)有用。
到此，關(guān)于“怎么理解oracle索引塊分裂”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
