Scalable Sequences减少索引分裂及TX – index contention

索引分裂介绍

索引分裂（Index Block Split），就是索引块的分裂。当一次DML操作修改了索引块上的数据，但是旧有的索引块没有足够的空间去容纳新修改的数据时，将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上去，这个过程就称为索引块的分裂，简称索引分裂。

在分裂的过程中前台进程需要等待分裂完成之后才能继续操作。如果此时其它会话也要修改这个索引块的数据，那么将会出现索引块的竞争，等待以“enq: TX – index contention”的形式体现，该事件是一个与索引分裂直接相关的等待事件。一般索引块的分裂持有资源和释放非常迅速，并不会对数据库造成严重的性能影响，但是对表操作并发量很大的情况下可能导致严重的竞争。当索引分裂发生时，负责实施分裂的进程会持有相关的队列锁，直到该进程完成分裂操作才会释放该队列锁。在这个过程中负责分裂的进程需要找到合适的新块并将对应的数据移动到该新块中。若在此分裂的过程中，有其它进程INSERT数据到该索引块中，则将进入enq: TX – index contention等待，直到分裂结束锁被释放。

分类

索引分裂有如下几种情况

（1）按照分裂对象分：

l 叶子节点分裂：叶子节点上没有足够的空间容纳新插入的数据。叶子节点分裂的情况最频繁发生，对性能影响最直接。

l 枝节点分裂：其下层的节点分裂，会导致在该节点上增加一条记录指向新加的节点，当该节点空间不足时，会发生分裂。

l 根节点分裂：特殊的枝节点分裂，分裂需要两个新的数据块，将原有数据转移至两个新节点，原有节点上生成两条记录分别指向新增的数据块。

（2）按照分裂数据块比例分：

l 9-1分裂：当事务向索引的最右侧的叶节点上插入一条大于或等于现有索引块上最大值的数据，且该索引块上不存在其它未提交的事务，如果没有足够的空间，那么就会发生9-1分裂。绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。

l 5-5分裂：当发生5-5分裂时，有一半索引记录仍存在当前块，而另一半数据移动到新的节点中，旧节点和新节点上的数据比例几乎是持平的。5-5分裂发生的条件：

1、当左侧节点发生新值插入时（插入到叶子节点中的索引键值小于该块中的最大值）。

2、当发生DML操作时，索引块上没有足够空间分配新的ITL槽。

3、当新插入数据大于或等于索引中最大值时，但是数据块上还存在其它未提交的事务。

对性能来说，无论是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

测试分析

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的新特性中，开发商真的用心良苦。