--9-1 分裂分析
--leaf node 90-10 splits 插入到索引leaf block叶子块中的索引键是该块中最大的键值(包括块中已删除的索引键值)。
--在此种情况下实施 90-10 split( 实际是 99-1 )，原叶子块仍保持99%的full， 而到另一个空的叶子块中插入该条新的最大键值记录
--通常是索引的键值是递增的，表上的事务并发量比较低，可以保证新的数据块上有较大的空闲空间插入新值
create table t1(id number,name varchar2(100));
create index idx1_t1 on t1(id);
alter session set events '10224 trace name context forever,level 1';
declare
begin
for i in 1 .. 3000 loop
insert into t1 values (i, 'ls');
end loop;
commit;
end;
/
alter session set events '10224 trace name context off';
--trc可以看到 splitting 信息：
splitting leaf,dba 0x00418101,time 08:32:01.765
kdisnew first,dba 0x00418102,time 08:32:01.766
kdisnew using block,dba 0x00418102,time 08:32:01.766
kdisnew first,dba 0x00418102,time 08:32:01.766
kdisnew reject block unlink,dba 0x00418102,time 08:32:01.766
kdisnew loop trying,dba 0x00418103,time 08:32:01.766
kdisnew using block,dba 0x00418103,time 08:32:01.766
splitting leaf,dba 0x00418103,time 08:32:01.792
kdisnew first,dba 0x00418103,time 08:32:01.792
kdisnew reject block unlink,dba 0x00418103,time 08:32:01.792
kdisnew loop trying,dba 0x00418104,time 08:32:01.792
kdisnew using block,dba 0x00418104,time 08:32:01.792
splitting leaf,dba 0x00418104,time 08:32:01.816
kdisnew first,dba 0x00418104,time 08:32:01.816
kdisnew reject block unlink,dba 0x00418104,time 08:32:01.816
kdisnew loop trying,dba 0x00418105,time 08:32:01.816
kdisnew using block,dba 0x00418105,time 08:32:01.816
splitting leaf,dba 0x00418105,time 08:32:01.844
--验证是否发生了9-1分裂：
set linesize 1000
col name for a30
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
---------- ------------------------------ ----------
72 leaf node splits 5
72 leaf node 90-10 splits 5
select object_id from dba_objects where object_name='IDX1_T1';
OBJECT_ID
----------
88197
ALTER SESSION SET EVENTS 'immediate trace name treedump level 88197';
--trc
----- begin tree dump
branch: 0x418101 4292865 (0: nrow: 6, level: 1)
leaf: 0x418102 4292866 (-1: nrow: 540 rrow: 540)
leaf: 0x418103 4292867 (0: nrow: 533 rrow: 533)
leaf: 0x418104 4292868 (1: nrow: 533 rrow: 533)
leaf: 0x418105 4292869 (2: nrow: 533 rrow: 533)
leaf: 0x418106 4292870 (3: nrow: 533 rrow: 533)
leaf: 0x418107 4292871 (4: nrow: 328 rrow: 328)
----- end tree dump
--INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement
ANALYZE INDEX idx1_t1 VALIDATE STRUCTURE;
SELECT HEIGHT,
ROUND((DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100, 2) || '%' RATIO,
PCT_USED
FROM INDEX_STATS
WHERE NAME = 'IDX1_T1';
HEIGHT RATIO PCT_USED
---------- ---------------------------------------------------------------------------------- ----------
2 0% 81
--PCT_USED：Percent of space allocated in the B-Tree that is being used
--解析：
索引 IDX1_T1 使用率大概81%，从dump文件看出，每个叶子块大约存储533个条目，简单的换算：81%充盈的叶子块可以存储533行记录
那么100%充盈的叶子块可以存储 533/0.81 =658 行记录；
同时我们看到索引的PCT_FREE =10%，也就是说索引的叶块的利用率可达到90%，单块可以存储记录数为658 *0.9= 592，
但是实际上单块叶子块存储的记录数还没达到592（才533）就开始分裂了，PCT_FREE参数的设置是被忽略的。有次可以证明如果索引分裂修改PCT_FREE是无效的。
--5-5分裂分析：
--leaf node 50-50 splits 当插入到索引叶子块中的索引键值不是该块中的最大值时(包括块中已删除的索引键值)，
-- 将发生 50/50 split分裂， 这意味着有一半索引记录仍存在当前块，而另一半数据移动到新的叶子块中
--表上的事务并发度较高，操作的数据是无序的，需保证分裂的新旧数据块上有相对较大的空闲空间容纳新事务的操作
create table t2(id number,name varchar2(100));
create index idx1_t2 on t2(id);
--反序插入3000条记录
declare
begin
for i in 1 .. 3000 loop
insert into t2 values (3001-i, 'ls');
end loop;
commit;
end;
/
--5-5分裂会导致索引叶子节点的数据块使用率不高--（使用率不高的时候就发生了分裂）
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
---------- -------------------- ----------
10 leaf node splits 10
ANALYZE INDEX idx1_t2 VALIDATE STRUCTURE;
SELECT HEIGHT,
ROUND((DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100, 2) || '%' RATIO,
PCT_USED
FROM INDEX_STATS
WHERE NAME = 'IDX1_T2';
HEIGHT RATIO PCT_USED
---------- ---------------------------------------------------------------------------------- ----------
2 0% 47
--此时索引空间总体的使用率由之前的81% 下降到47%
select object_id from dba_objects where object_name='IDX1_T2';
OBJECT_ID
----------
88201
ALTER SESSION SET EVENTS 'immediate trace name treedump level 88201';
----- begin tree dump
branch: 0x418111 4292881 (0: nrow: 11, level: 1)
leaf: 0x418112 4292882 (-1: nrow: 375 rrow: 375)
leaf: 0x41811c 4292892 (0: nrow: 262 rrow: 262)
leaf: 0x41811b 4292891 (1: nrow: 262 rrow: 262)
leaf: 0x41811a 4292890 (2: nrow: 267 rrow: 267)
leaf: 0x418119 4292889 (3: nrow: 262 rrow: 262)
leaf: 0x418118 4292888 (4: nrow: 262 rrow: 262)
leaf: 0x418117 4292887 (5: nrow: 262 rrow: 262)
leaf: 0x418116 4292886 (6: nrow: 262 rrow: 262)
leaf: 0x418115 4292885 (7: nrow: 262 rrow: 262)
leaf: 0x418114 4292884 (8: nrow: 262 rrow: 262)
leaf: 0x418113 4292883 (9: nrow: 262 rrow: 262)
----- end tree dump
单块存储的记录行数由533行下降到262行。说明此时索引有点"虚胖"
导致问题：在一次简单的查询或者dml操作中，会扫描非常多的索引块，直接导致I/O次数的增加，特别是在并发度很高的表上。
--反向索引测试
create table t3(id number,name varchar2(100));
create index idx1_t3 on t3 (id) reverse;
declare
begin
for i in 1 .. 3000 loop
insert into t3 values (i, 'ls');
end loop;
commit;
end;
/
ANALYZE INDEX idx1_t3 VALIDATE STRUCTURE;
SELECT HEIGHT,
ROUND((DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100, 2) || '%' RATIO,
PCT_USED
FROM INDEX_STATS
WHERE NAME = 'IDX1_T3';
HEIGHT RATIO PCT_USED
---------- ---------------------------------------------------------------------------------- ----------
2 0% 63
set linesize 1000
col name for a30
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
---------- ------------------------------ ----------
130 leaf node splits 7
select object_id from dba_objects where object_name='IDX1_T3';
OBJECT_ID
----------
88203
ALTER SESSION SET EVENTS 'immediate trace name treedump level 88203';
----- begin tree dump
branch: 0x418101 4292865 (0: nrow: 8, level: 1)
leaf: 0x418102 4292866 (-1: nrow: 340 rrow: 340)
leaf: 0x418120 4292896 (0: nrow: 340 rrow: 340)
leaf: 0x418105 4292869 (1: nrow: 353 rrow: 353)
leaf: 0x418121 4292897 (2: nrow: 347 rrow: 347)
leaf: 0x418103 4292867 (3: nrow: 402 rrow: 402)
leaf: 0x418106 4292870 (4: nrow: 408 rrow: 408)
leaf: 0x418104 4292868 (5: nrow: 402 rrow: 402)
leaf: 0x418107 4292871 (6: nrow: 408 rrow: 408)
----- end tree dump
反向索引的使用率是63%，9-1分裂的使用率是81%，5-5分裂的使用率是47%。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
