Oracle索引分裂（Index Block Split）
索引分裂：index block split : 就是索引块的分裂，当一次DML 事务操作修改了索引块上的数据，但是旧有的索引块没有足够的空间去容纳新修改的数据，那么将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上去.
分裂的类型：根节点分裂，分支节点分裂，叶节点分裂（最频繁发生，对性能影响最直接）
按照数据迁移量的比例，将索引分裂分为两种类型：9-1分裂和5-5分裂.
9-1分裂：绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。
5-5分裂：旧节点和新节点上的数据比例几乎是持平的。
5-5 分裂发生的条件：
1.左侧节点发生新值插入时（新值小于索引中的最大值）。
2.发生DML 操作，索引块上没有足够空间分配新的ITL槽。
3.新值待插入的索引上存在其他未提交的事务。
5-5分裂:
9-1分裂：通常是索引的键值是递增的，表上的事务并发量比较低，可以保证新的数据块上有较大的空闲空间插入新值。
5-5分裂：表上的事务并发度较高，操作的数据是无序的，需保证分裂的新旧数据块上有相对较大的空闲空间容纳新事务的操作。
9-1分裂如下:
对性能来说，无论9-1还是5-5 都不是什么好事。
索引结构如下:
--9-1 分裂分析
create table t_index(id number,name varchar2(100));
create index i_index_01 on t_index(id);
alter session set events '10224 trace name context forever,level 1';
declare
begin
for i in 1 .. 3000 loop
insert into t_index values (i, 'ls');
end loop;
commit;
end;
alter session set events '10224 trace name context off';
splitting leaf,dba 0x02015f04,time 15:19:09.639
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f04,time 15:19:09.640
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f04,time 15:19:09.640
kdisnew_bseg_srch_cbk using block,dba 0x02015f1e,time 15:19:09.640
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f1e,time 15:19:09.640
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f1e,time 15:19:09.640
kdisnew_bseg_srch_cbk using block,dba 0x02015f22,time 15:19:09.640
splitting leaf,dba 0x02015f22,time 15:19:09.676
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f22,time 15:19:09.676
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f22,time 15:19:09.676
kdisnew_bseg_srch_cbk using block,dba 0x02015f26,time 15:19:09.676
splitting leaf,dba 0x02015f26,time 15:19:09.711
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f26,time 15:19:09.711
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f26,time 15:19:09.711
kdisnew_bseg_srch_cbk using block,dba 0x02015f2a,time 15:19:09.711
splitting leaf,dba 0x02015f2a,time 15:19:09.748
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f2a,time 15:19:09.748
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f2a,time 15:19:09.748
kdisnew_bseg_srch_cbk using block,dba 0x02015f2e,time 15:19:09.748
splitting leaf,dba 0x02015f2e,time 15:19:09.784
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x02015f2e,time 15:19:09.784
kdisnew_bseg_srch_cbk rejecting block ,dba 0x02015f2e,time 15:19:09.784
kdisnew_bseg_srch_cbk using block,dba 0x02015f32,time 15:19:09.784
--验证是否发生了9-1分裂：
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 value > 0
6 and n.NAME like '%split%'
7 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
776 leaf node splits 5
776 leaf node 90-10 splits 5
记录了5次索引叶子节点数据块的分裂，也就是说整个过程发生了5次索引分裂，因为表和索引都是新建的，所以次索引树结构应该有6个叶节点数据块。
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 159572';
Session altered.
*** 2015-12-22 16:19:30.295
branch: 0x2015f04 33644292 (0: nrow: 6, level: 1)
leaf: 0x2015f1e 33644318 (-1: nrow: 540 rrow: 540)
leaf: 0x2015f22 33644322 (0: nrow: 533 rrow: 533)
leaf: 0x2015f26 33644326 (1: nrow: 533 rrow: 533)
leaf: 0x2015f2a 33644330 (2: nrow: 533 rrow: 533)
leaf: 0x2015f2e 33644334 (3: nrow: 533 rrow: 533)
leaf: 0x2015f32 33644338 (4: nrow: 328 rrow: 328)
----- end tree dump
ANALYZE INDEX I_INDEX_01 VALIDATE STRUCTURE;
SQL> SELECT HEIGHT,
2 ROUND((DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100, 2) || '%' RATIO,
3 PCT_USED
4 FROM INDEX_STATS
5 WHERE NAME = 'I_INDEX_01';
HEIGHT RATIO PCT_USED
---------- ----------------------------------------- ----------
2 0% 81
SQL> SELECT PCT_FREE FROM USER_INDEXES WHERE INDEX_NAME = 'I_INDEX_01';
PCT_FREE
----------
10
--分析i_index_01 结构： 索引空间，使用率大约81% 左右，从dump文件看出，每个叶子块大约存储530 个条目，
简单的换算：81% 充盈的叶块可以存储 533 行记录，那么一个100% 充盈的块就可以存储 533/0.81 =658行记录
同时我们看到索引的PCT_FREE=10%，也就是说索引的叶块的利用率可达到90% ，单块可以存储记录行数为658 * 0.9=592
但是实际情况是单块存储记录的行数还没到达592（才530）就开始分裂了。PCT_FREE 参数的设置被忽略了。
--5-5分裂分析：
create table t_index_1(id number,name varchar2(100));
create index i_index_02 on t_index_1(id);
alter session set events '10224 trace name context forever,level 1';
--反序插入3000条记录
declare
begin
for i in 1 .. 3000 loop
insert into t_index_1 values (3001-i, 'ls');
end loop;
commit;
end;
--5-5分裂会导致索引叶子节点的数据块使用率不高--（使用率不高的时候就发生了分裂）
SQL> select s.SID, n.NAME, s.VALUE
from v$sesstat s, v$statname n
where s.STATISTIC# = n.STATISTIC#
and sid in (select sid from v$mystat)
and value > 0
and n.NAME like '%split%';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
776 leaf node splits 10
ANALYZE INDEX I_INDEX_02 VALIDATE STRUCTURE;
SQL> SELECT HEIGHT,
2 ROUND((DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100, 2) || '%' RATIO,
3 PCT_USED
4 FROM INDEX_STATS
5 WHERE NAME = 'I_INDEX_02';
HEIGHT RATIO PCT_USED
---------- ----------------------------------------- ----------
2 0% 47
--此时索引空间总体的使用率由之前的81% 下降到47%
select object_id, object_name
from dba_objects
where owner = user
and object_name = 'I_INDEX_01'
--
alter session set events 'immediate trace name treedump level 88317'
*** 2015-12-24 13:31:00.102
*** SESSION ID:(776.93) 2015-12-24 13:31:00.102
*** CLIENT ID:() 2015-12-24 13:31:00.102
*** SERVICE NAME:(SYS$USERS) 2015-12-24 13:31:00.102
*** MODULE NAME:(SQL*Plus) 2015-12-24 13:31:00.102
*** ACTION NAME:() 2015-12-24 13:31:00.102
----- begin tree dump
branch: 0x2016004 33644548 (0: nrow: 11, level: 1)
leaf: 0x201601e 33644574 (-1: nrow: 375 rrow: 375)
leaf: 0x201600b 33644555 (0: nrow: 262 rrow: 262)
leaf: 0x2016007 33644551 (1: nrow: 262 rrow: 262)
leaf: 0x201603e 33644606 (2: nrow: 267 rrow: 267)
leaf: 0x201603a 33644602 (3: nrow: 262 rrow: 262)
leaf: 0x2016036 33644598 (4: nrow: 262 rrow: 262)
leaf: 0x2016032 33644594 (5: nrow: 262 rrow: 262)
leaf: 0x201602e 33644590 (6: nrow: 262 rrow: 262)
leaf: 0x201602a 33644586 (7: nrow: 262 rrow: 262)
leaf: 0x2016026 33644582 (8: nrow: 262 rrow: 262)
leaf: 0x2016022 33644578 (9: nrow: 262 rrow: 262)
----- end tree dump
--单块存储的记录行数由570行下降到262行。说明此时索引有点"虚胖"
导致问题：在一次简单的查询或者dml操作中，会扫描非常多的索引块，直接导致I/O次数的增加，特别是在并发度很高的表上。
5-5分裂中，PCT_FREE参数再一次被忽视。参数PCT_FREE在索引创建时起作用，而在使用时往往被忽略。
SQL> SELECT PCT_FREE FROM USER_INDEXES WHERE INDEX_NAME = 'I_INDEX_02';
PCT_FREE
----------
10
--在索引块上，以下两种情况会触发'enq: TX-allocate ITL entry' 等待：
1.达到数据块上最大事务数限制
2.递归事务ITL 争用
总结：
索引的争用源自于索引的分裂，而触发索引的分裂的契机就是索引上的高并发事务操作。
如何去解决高并发导致索引分裂的争用：
1.增加ITL槽来增加并发处理能力-->修改索引initrans 参数
其实修改initrans 参数并没有真正解决问题，随着并发度的不断提升，ITL 槽的争用也越发激烈。
2.反向建索引。利：入库高效了，几乎完全消除了enq:TX-index contention 弊：数据读取低效，本来访问一个索引块即可，现在需要访问多个索引块了。增加了额外I/O开销。
About Me
...............................................................................................................................● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。