怎么理解oracle索引块分裂
这篇文章主要介绍"怎么理解oracle索引块分裂"，在日常操作中，相信很多人在怎么理解oracle索引块分裂问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"怎么理解oracle索引块分裂"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
B*tree索引是包括oracle在内的数据库中最常见的索引结构。它是一种类似树状结构的索引，最底层的块叫做叶块（leaf block）。叶节点（leaf node）这一层实际上是一个双向链表。如果是一个高度（height）为3的B*tree索引，那么叶块上一层叫做分支块（branch block）。最顶层叫做根块（root block）。根块是索引的入口。但实际上，根块也可以看做是分支块的一种，所以B*tree索引实际只包含两种块：分支块和叶块。分支块用来search，叶块用来存储键值。
什么是索引块分裂呢？
简单来说，索引块分裂就是当事务的增删改操作需要去维护索引，如果索引块没有足够的空间容纳新数据，就要新增一个索引块，旧的索引块数据一分为二，将其中一部分数据搬迁到新增的索引块上面去。这里的一分为二并不一定是平分，有可能是50-50的平分，也可能是90-10的分裂。这里足够的空间也不一定就是指该块的空闲空间，还有可能是ITL事务槽不够用了。
与索引块分裂有关的等待事件是enq: TX - index contention，同时可能会伴随row lock waits。
索引块分裂分为下面四种情况：
l 叶节点90-10分裂
l 叶节点50-50分裂
l 分支节点分裂
l 根节点分裂
其中，根节点分裂可以看做特殊的分支节点分裂。
当叶节点空间不够发生分裂时，会多一个叶块出来；叶块持续增加，当分支块的空间也不够用时，分支块也要发生分裂；当分支块持续增加，根块空间不够时，根块也要发生分裂。而根块的分裂会使得B*tree索引的高度（height）增加。如果这种增加是不必要的（高度与索引规模不匹配），那么这会增加不必要的IO，造成索引性能衰退。这种情况是要避免的，此时可以通过重建索引来减小高度。
发生90-10分裂时，表的事务并发量较低，索引的键值是单调递增的，这种情况下，只会把较大的那部分索引键值搬迁到新块上，这样新块上空闲空间较多，满足后续递增检查插入。而50-50分裂是在高并发的情况下发生的，而且对索引键值的操控是无序的，但是插入的值还是会比块上的最大值要小，这样每个分裂的块上都会有一半的空闲空间，这样高并发无序的维护索引的时候才会成本较低。
想象一下，如果高并发无序维护索引时，索引分裂时按照90-10的方式，那么新块的空闲空间是足够大了，但是旧块上几乎还是满的，无法进行维护，这样会拖累整个索引维护行为。而如果低并发有序操纵索引的行为是按照50-50的方式分裂，那么旧块上因为键值的有序性不会再插入数据，会造成空间浪费，索引块数增加造成索引效率下降，这也是不可取的。
下面模拟叶节点90-10分裂：
创建测试表：
SQL> create table tab91 (id number,name varchar2(100),created date); |
创建索引,这里指定pctfree参数为0,即在一个块上不保留空间：
SQL> create index idx_tab91_01 on tab91(id) pctfree 0; |
这里先简单介绍一个oralce的诊断事件：10224。该事件可以转储索引的分隔与删除。用法：
开启: ALTER SESSION SET EVENTS '10224 trace name context forever , level 1'; ALTER SYSTEM SET EVENTS '10224 trace name context forever , level 1'; 关闭: ALTER SESSION SET EVENTS '10224 trace name context off'; ALTER SYSTEM SET EVENTS '10224 trace name context off'; |
再简单介绍一个转储索引结构的语句：
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'); |
低并发，正常插入数据时，维护索引的行为：
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 |
可以看到叶块的个数为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 |
可以看到发生了18次叶节点90-10分裂，所以叶块正好是19个。
转储索引结构：
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
因为之前设置的pctfree为0，所以一个叶块可认为能够存储533行左右。
下面模拟叶节点50-50分裂：
创建测试表：
SQL> create table tab55 (id number,name varchar2(100),created date); |
创建索引,这里指定pctfree参数为0,即在一个块上不保留空间：
SQL> create index idx_tab55_01 on tab55(id) pctfree 0; |
倒序插入数据时，维护索引的行为：
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 |
可以看到叶块的个数为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 |
可以看到发生了37次叶节点50-50分裂，所以叶块正好是38个。
转储索引结构：
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行左右数据。
现在对比一下两个索引：
索引名称 | 块的个数 | 叶块的个数 | 索引大小/M |
idx_tab91_01 | 24 | 19 | 192 |
idx_tab55_01 | 48 | 38 | 384 |
解决措施：
l重建索引，哈希分区索引或者建反向键索引（reverse key index）。
重建索引可以降低索引树的高度，反向键索引可以杜绝插入操作导致的索引热块竞争，但是有利就有弊，反向键索引会影响索引范围扫描的效率。
哈希分区索引的思路与反向键索引是一致的，都是分散索引热块。在一些OLTP的环境中，存在单调递增的某些列，在插入这些列的时候，只会向索引块的右侧插入，这时因为一些索引页，buffer，latch等争用以及一些附加的索引维护活动，索引右侧反而变成了hotspot。哈希分区索引打散索引块，避免了上述情况下的性能衰退。
l更改序列cache值
有一下单调递增的列值是用序列值填充的。设置大的cache值，不同的实例会采用不同范围的（noorder）序列值，这样不同实例插入数据的时候插入到同一个块上的概率就会小的多了。避免了热块的出现。用物理上的极限思想来考虑一下，如果cache小到1，那么实例1插入1和实例2插入2大概率在同一个块上。如果cache值为1亿，那么实例1插入1亿1和实例2插入1亿2不大坑在一个块上。
l大量数据删除之后rebuild或者shrink索引
l增加索引的PCT_FREE值
高的PCT_FREE值能够防止ITL槽不够用。这个参数主要对update的系统有用。
到此，关于"怎么理解oracle索引块分裂"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！
