Oracle-索引分裂研究
索引分裂（Index Block Split），就是索引块的分裂。当一次DML操作修改了索引块上的数据，但是旧有的索引块没有足够的空间去容纳新修改的数据时，将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上去，这个过程就称为索引块的分裂，简称索引分裂。
在分裂的过程中，前台进程需要等待分裂完成之后才能继续操作。如果此时其它会话也要修改这个索引块的数据，那么将会出现索引块的竞争，等待以“enq: TX – index contention”的形式体现，该事件是一个与索引分裂直接相关的等待事件。一般索引块的分裂持有资源和释放非常迅速，并不会对数据库造成严重的性能影响，但是在表操作并发量很大的情况下可能导致严重的竞争。当索引分裂发生时，负责实施分裂的进程会持有相关的队列锁，直到该进程完成分裂操作才会释放该队列锁。在这个过程中负责分裂的进程需要找到合适的新块并将对应的数据移动到该新块中。若在此分裂的过程中，有其它进程INSERT数据到该索引块中，则将进入enq: TX – index contention等待，直到分裂结束锁被释放。
分类
索引分裂有如下几种情况：
（1）按照分裂对象分：
叶子节点分裂：**叶子节点上没有足够的空间容纳新插入的数据。叶子节点分裂的情况最频繁发生，对性能影响最直接。
枝节点分裂：**其下层的节点分裂，会导致在该节点上增加一条记录指向新加的节点，当该节点空间不足时，会发生分裂。
根节点分裂：**特殊的枝节点分裂，分裂需要两个新的数据块，将原有数据转移至两个新节点，原有节点上生成两条记录分别指向新增的数据块。
（2）按照分裂数据块比例分：
9-1分裂：**当事务向索引的最右侧的叶节点上插入一条大于或等于现有索引块上最大值的数据，且该索引块上不存在其它未提交的事务，如果没有足够的空间，那么就会发生9-1分裂。绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。
5-5分裂：**当发生5-5分裂时，有一半索引记录仍存在当前块，而另一半数据移动到新的节点中，旧节点和新节点上的数据比例几乎是持平的。5-5分裂发生的条件：
当左侧节点发生新值插入时（插入到叶子节点中的索引键值小于该块中的最大值）。
当发生DML操作时，索引块上没有足够空间分配新的ITL槽。
当新插入数据大于或等于索引中最大值时，但是数据块上还存在其它未提交的事务。
对性能来说，无论是9-1分裂，还是5-5分裂，都会影响系统的性能。
通过10224事件可以生成索引块分裂及删除了的。
索引分裂实验
基础环境准备
--创建用户表空间
create tablespace zsdba_data datafile '+DATA' size 200M;
create user autoidx identified by autoidx default tablespace zsdba_data;
grant dba to autoidx;
--创建表
create table zsdba_idx(id number(20) not null,name varchar(20));
alter table zsdba_idx add constraint pri_id primary key (id);
基础信息统计–之前
--基础信息统计
col owner for a15
col segment_name for a15
col segment_type for a15
set linesize 200
set pagesize 999
select t.owner,t.segment_name,t.segment_type,t.header_file,t.header_block from dba_segments t where t.segment_name in ('ZSDBA_IDX','PRI_ID');
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
--------------- --------------- --------------- ----------- ------------
AUTOIDX PRI_ID INDEX 2 138
AUTOIDX ZSDBA_IDX TABLE 2 130
select t.owner,t.segment_name,t.segment_type,t.file_id,t.block_id,t.blocks from dba_extents t where t.segment_name in ('ZSDBA_IDX','PRI_ID');
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
--------------- --------------- --------------- ---------- ---------- ----------
AUTOIDX PRI_ID INDEX 2 136 8
AUTOIDX ZSDBA_IDX TABLE 2 128 8
col name for a25
select t.inst_id,t.name,t.value from gv$sysstat t where t.NAME like '%splits%' order by t.inst_id,t.name;
INST_ID NAME VALUE
---------- ------------------------- ----------
1 branch node splits 34
1 leaf node 90-10 splits 2208
1 leaf node splits 12525
1 queue splits 0
1 root node splits 6
2 branch node splits 32
2 leaf node 90-10 splits 887
2 leaf node splits 7273
2 queue splits 0
2 root node splits 8
数据插入
通过10224事件可以生成索引块分裂及删除的trace
alter session set events '10224 TRACE NAME CONTEXT FOREVER,LEVEL 10';
alter session set tracefile_identifier="STACK_10224";
insert into zsdba_idx select level,'11' from dual connect by level<50000;
commit;
alter session set events '10224 TRACE NAME CONTEXT OFF';
基础信息统计–之后
col owner for a15
col segment_name for a15
col segment_type for a15
set linesize 200
set pagesize 999
select t.owner,t.segment_name,t.segment_type,t.header_file,t.header_block,t.blocks,t.bytes
from dba_segments t where t.segment_name in ('T_IBS_LHR','PRI_ID');
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BLOCKS BYTES
--------------- --------------- --------------- ----------- ------------ ---------- ----------
AUTOIDX PRI_ID INDEX 2 138 112 917504
select t.owner,t.segment_name,t.segment_type,t.file_id,t.block_id,t.blocks from dba_extents t where t.segment_name in ('ZSDBA_IDX','PRI_ID');
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
--------------- --------------- --------------- ---------- ---------- ----------
AUTOIDX PRI_ID INDEX 2 136 8
AUTOIDX PRI_ID INDEX 2 144 8
AUTOIDX PRI_ID INDEX 2 160 8
AUTOIDX PRI_ID INDEX 2 176 8
AUTOIDX PRI_ID INDEX 2 192 8
AUTOIDX PRI_ID INDEX 2 200 8
AUTOIDX PRI_ID INDEX 2 216 8
AUTOIDX PRI_ID INDEX 2 232 8
AUTOIDX PRI_ID INDEX 2 248 8
AUTOIDX PRI_ID INDEX 2 264 8
AUTOIDX PRI_ID INDEX 2 280 8
AUTOIDX PRI_ID INDEX 2 296 8
AUTOIDX PRI_ID INDEX 2 312 8
AUTOIDX PRI_ID INDEX 2 328 8
AUTOIDX ZSDBA_IDX TABLE 2 128 8
AUTOIDX ZSDBA_IDX TABLE 2 152 8
AUTOIDX ZSDBA_IDX TABLE 2 168 8
AUTOIDX ZSDBA_IDX TABLE 2 184 8
AUTOIDX ZSDBA_IDX TABLE 2 208 8
AUTOIDX ZSDBA_IDX TABLE 2 224 8
AUTOIDX ZSDBA_IDX TABLE 2 240 8
AUTOIDX ZSDBA_IDX TABLE 2 256 8
AUTOIDX ZSDBA_IDX TABLE 2 272 8
AUTOIDX ZSDBA_IDX TABLE 2 288 8
AUTOIDX ZSDBA_IDX TABLE 2 304 8
AUTOIDX ZSDBA_IDX TABLE 2 320 8
AUTOIDX ZSDBA_IDX TABLE 2 336 8
27 rows selected.
col name for a25
select t.inst_id,t.name,t.value from gv$sysstat t where t.NAME like '%splits%' order by t.inst_id,t.name;
INST_ID NAME VALUE
---------- ------------------------- ----------
1 branch node splits 34
1 leaf node 90-10 splits 2300
1 leaf node splits 12713
1 queue splits 0
1 root node splits 6
2 branch node splits 32
2 leaf node 90-10 splits 887
2 leaf node splits 7351
2 queue splits 0
2 root node splits 8
Trace 数据统计
col value for a80
select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_48064_STACK_10224.trc
[oracle@19db1:/home/oracle]$ grep 'splitting' orcl1_ora_48064_STACK_10224.trc
splitting leaf,dba 0x0080008b,time 16:59:43.374
splitting leaf,dba 0x0080008c,time 16:59:43.381
.....
[oracle@19db1:/home/oracle]$ grep 'splitting' orcl1_ora_48064_STACK_10224.trc|awk -F '[ |,]' '{print $4}'
0x0080008b
............
0x0080014f
[oracle@19db1:/u01/app/oracle/diag/rdbms/orcl/orcl1/trace]$ grep 'splitting' orcl1_ora_48064_STACK_10224.trc|awk -F '[ |,]' '{print $4}'|uniq|wc -l
92 <=========索引分裂次数
数据分析
索引PRI_ID之dba_extents视图
从索引pri_id的现有块数看，由1个extent扩展至14个extent，目前1个extent有8个block，索引pri_id有112个块，和dba_segments视图统计一致。
数据为有序插入，会产生类型为leaf node 90-10 splits的分裂，即分裂块次数最多有111次。
索引PRI_ID之gv$sysstat视图
从视图gv$sysstat的leaf node 90-10 splits统计值看，插入前后差值为92，小于111次，且等于trace文件中统计到的92次。
INST_ID NAME 插入前 插入后 差集
---------- ------------------------- ---------- ---------- ----------
1 branch node splits 34 34 0
1 leaf node 90-10 splits 2208 2300 92 <======索引分裂
1 leaf node splits 12525 12713 188
1 queue splits 0 0 0
1 root node splits 6 6 0
2 branch node splits 32 32 0
2 leaf node 90-10 splits 887 887 0
2 leaf node splits 7273 7351 78
2 queue splits 0 0
2 root node splits 8 8
索引分裂衍生-enq:TX-index contention
enq:TX-index contention是一个很常见的等待事件，其专指由于索引分裂产生的竞争等待。最常见的索引竞争一般发生在主键索引上，主键值从序列（sequence）中获取，每个事务都会生成一条新的记录，每条记录都要获得一个新的序列号，因为从sequence中取出的值是单向递增的，当索引中插入数据，并且维护索引结构的时候，不得不一直走向索引的最右侧分支，对于每一个操作，都会想要维护索引中最右边的叶节点，那么所有的操作都会关注同一个内存块，希望能够维护这块内存，这就是一种典型的竞争形式。但在同一时间，只有一个人能够修改这块内存，因此当有一个人在修改时，其他所有想修改的人只能处于等待状态。
下面通过创建正常序列和18C的扩展序列作为索引，验证18C扩展序列的优势。
正常序列索引演示
数据准备
conn autoidx/autoidx
drop table zsdba_idx_seq_normal purge;
-- create table
create table zsdba_idx_seq_normal(id number(20) not null,name varchar(20));
-- create/recreate primary, unique and foreign key constraints
alter table zsdba_idx_seq_normal add constraint pri_id_normal primary key (id);
drop sequence test_seq_normal;
create sequence test_seq_normal
minvalue 1
maxvalue 9999999999999999999
start with 1
increment by 1
cache 2;
create or replace procedure p_task_idx_seq_normal is
begin
for i in 1 .. 50000 loop
insert into zsdba_idx_seq_normal values(test_seq_normal.nextval,i);
end loop;
end;
/
enq: TX - index contention事件统计–之前
col inst_id for 999
col event for a35
col total_waits for 999999999
col time_waited_micro for 99999999
set linesize 200
select t.inst_id,t.event,t.total_waits,t.time_waited_micro from gv$system_event t where t.event = 'enq: TX - index contention';
INST_ID EVENT TOTAL_WAITS TIME_WAITED_MICRO
------- ----------------------------------- ----------- -----------------
2 enq: TX - index contention 14167 23440717
1 enq: TX - index contention 22364 42026275
测试数据运行
declare
v_job_no number;
begin
for v_parallel in 1 .. 10 loop
dbms_job.submit(job=>v_job_no,what=>'p_task_idx_seq_normal;');
commit;
end loop;
end;
/
enq: TX - index contention事件统计–之后
col inst_id for 999
col event for a35
col total_waits for 999999999
col time_waited_micro for 99999999
set linesize 200
select t.inst_id,t.event,t.total_waits,t.time_waited_micro from gv$system_event t where t.event = 'enq: TX - index contention';
INST_ID EVENT TOTAL_WAITS TIME_WAITED_MICRO
------- ----------------------------------- ----------- -----------------
1 enq: TX - index contention 25883 51053826
2 enq: TX - index contention 17838 32869104
测试分析
统计测试前后的enq: TX - index contention差集
INST_ID EVENT 插入前 插入后 插入前 插入后
等待数 等待数 等待时间 等待时间
------- --------------------------- -------- ------------------- ------------
2 enq: TX - index contention 14167 17838 23440717 32869104
1 enq: TX - index contention 22364 25883 42026275 51053826
enq: TX - index contention等待次数（25883+17838）-（22364+14167）=7190
enq: TX - index contention等待时间（51053826+32869104）-（42026275+23440717）=18455938
扩展序列索引演示
数据准备
conn autoidx/autoidx
drop table zsdba_idx_seq_scale purge;
-- create table
create table zsdba_idx_seq_scale(id number(20) not null,name varchar(20));
-- create/recreate primary, unique and foreign key constraints
alter table zsdba_idx_seq_scale add constraint pri_id_scale primary key (id);
drop sequence test_seq_scale;
create sequence test_seq_scale
minvalue 1
maxvalue 9999999999999999999
start with 1
increment by 1
cache 2
scale;
create or replace procedure p_task_idx_seq_scale is
begin
for i in 1 .. 50000 loop
insert into zsdba_idx_seq_scale values(test_seq_scale.nextval,i);
end loop;
end;
/
enq: TX - index contention事件统计–之前
col inst_id for 999
col event for a35
col total_waits for 999999999
col time_waited_micro for 99999999
set linesize 200
select t.inst_id,t.event,t.total_waits,t.time_waited_micro from gv$system_event t where t.event = 'enq: TX - index contention';
INST_ID EVENT TOTAL_WAITS TIME_WAITED_MICRO
------- ----------------------------------- ----------- -----------------
2 enq: TX - index contention 17966 32976462
1 enq: TX - index contention 25920 51084690
测试数据运行
declare
v_job_no number;
begin
for v_parallel in 1 .. 10 loop
dbms_job.submit(job=>v_job_no,what=>'p_task_idx_seq_scale;');
commit;
end loop;
end;
/
enq: TX - index contention事件统计–之后
col inst_id for 999
col event for a35
col total_waits for 999999999
col time_waited_micro for 99999999
set linesize 200
select t.inst_id,t.event,t.total_waits,t.time_waited_micro from gv$system_event t where t.event = 'enq: TX - index contention';
INST_ID EVENT TOTAL_WAITS TIME_WAITED_MICRO
------- ----------------------------------- ----------- -----------------
2 enq: TX - index contention 18374 34443728
1 enq: TX - index contention 26050 53756689
测试分析
统计测试前后的enq: TX - index contention差集
INST_ID EVENT 插入前 插入后 插入前 插入后
等待数 等待数 等待时间 等待时间
------- ----------------------------------- --------------- ------- ---------
2 enq: TX - index contention 17966 18374 32976462 34443728
1 enq: TX - index contention 25920 26050 51084690 53756689
enq: TX - index contention等待次数（26050+18374）-（25920+17966）=538
enq: TX - index contention等待时间（53756689+34443728）-（51084690+32976462）=4139265
总 结
从测试数据分析，正常序列作为索引，在高并发的场景下，enq: TX - index contention等待次数7190，等待时间18.5s，扩展序列作为索引，在高并发的场景下，enq: TX - index contention等待次数538，等待时间4s，无论是从等待次数和等待时间都有大幅度的提升，而且随着并发的增大，扩展序列的优势会更加扩大化。由此可见，在18C的新特性中，Oracle真的用心良苦。