概述：enq: TX - index contention
这个等待事件解释如下：
Waits for TX in mode 4 also occur when a transaction inserting a row in an
index has to wait for the end of an index block split being done by another
transaction. This type of TX enqueue wait corresponds to the wait event enq: TX
- index contention.
可以认为一个session在向一个索引块中执行插入时产生了索引块的split，而其它的session也要往该索引块中插入数据，此时，其它session必须要等待split完成，由此引发了该等待事件。
当事务修改索引中的数据时,而相关索引块没有足够的空间的时候,就会发生索引块的分裂,在分裂的过程中前台进程需要等待分裂完毕才能继续操作。
如果这个时候其他会话也要修改这个索引块的数据,那么将会出现索引块的竞争。.一般索引块的分裂持有资源和释放非常短,并不会对数据库造成严重的影响。但是对表操作并发量很大的情况下可能导致严重的竞争。
原因是在将新行插入索引时，索引块会分裂。操作将不得不等待模式4的TX锁，直到进行索引分裂的会话完成操作。
当在索引块中找不到需要插入新行的空间时，会话将启动索引分裂。开始分裂之前，它将清除块中的所有键，以检查在被删除的块中是否有足够的空间。
分裂操作必须做以下活动：
o分配一个新的块。
o将一定百分比的行复制到新索引块。
o将新索引块添加到索引结构并提交操作。
在RAC环境中，由于包括全局缓存操作，这可能是一项昂贵的操作。如果分裂发生在分支或根块级别，则影响将更大。
原因：
最可能的原因是：
o从应用程序大量访问的表的索引。
o单向生长的表列索引。也就是说，大多数索引插入仅在索引的右边缘出现。
o已执行大量数据清除，并紧接着又发生了高并发插入。
B_tree索引结构
索引块分裂
索引块分裂index block split 发生在 当某一个 索引块(root branch leaf) 的空闲空间不足以容纳新加入的记录时， 一般来说INSERT是引起索引块分裂的主要操作。
按照 leaf Block Split 分裂时的行为 又可以分为：
leaf node 90-10 splits 插入到索引leaf block叶子块中的索引键是该块中最大的键值(包括块中已删除的索引键值)。 在此种情况下实施 90-10 split( 实际是 99-1 )，原叶子块仍保持99%的full，
而到另一个空的叶子块中插入该条新的最大键值记录。如图：
leaf
node 50-50 splits 当插入到索引叶子块中的索引键值不是该块中的最大值时(包括块中已删除的索引键值)， 将发生 50/50 split分裂， 这意味着有一半索引记录仍存在当前块，而另一半数据移动到新的叶子块中。
Branch Block 50-50 Split 由于不断的索引叶块分裂需要将新的leaf block的信息加入到branch block中，当branch block没有足够空间容纳新的记录时，又会引发branch block的 Split 。 branch block的split 是50-50的，即将一半记录移动到新的branch block中。
Root Block 50-50 Split root block 根块实际是一种特殊的branch block, 当root block 50-50 split分裂发生时将分配2个新的数据块， 分裂将一半的数据移动到一个新块中，另一半数据移动到另一个新块中。 并更新原来的root block，使之指向那2个新的数据块，实际上是2个branch block了。
但是root block的数据块位置本身没有变，仍旧是原来的那个数据块。 当root
block split发生时 会导致索引的高度上升。
索引的高度 height 视乎 索引中记录的多少 、Leaf Block的数量而定，一般Height 为3~4； 举例来说当一个索引的高度为4，但是其中包含大量删除的记录，那么通过索引rebuild 往往可以降低其高度，例如从4降低到3。 假设该索引再次插入了大量的数据，造成leaf block不断分裂，最终导致root block 再次分裂， 索引高度从3再次上升到4，在这个root block split 的过程中可能短期内阻塞索引的DML维护，导致进程等待” enqueue TX: index contention” ， 这在OLTP环境中是很常见的问题，
RAC中的索引块分裂
在RAC环境中 由于全局缓冲块的争用 以及 全局队列的争用， 在RAC private network 心跳网络传输存在性能瓶颈的情况下可能导致 负责split工作的进程在分裂过程中遇到 例如 gc buffer busy、gc current block busy、gc current split等等待事件，这将极大拖慢 split的速度，导致大量INSERT进程长时间等待enq: TX – index contention。
实验1：理解insert操作时的索引分裂情况
1.创建测试表
SQL>
CREATE TABLE TEST(ID INT,NAME VARCHAR2(50),CREATED DATE);
Table created.
SQL> BEGIN
2 FOR
I IN 10000 .. 20000 LOOP
3 INSERT INTO TEST VALUES (I, RPAD(I, 50, 'X'),
SYSDATE);
4 END
LOOP;
5 END;
6 /
PL/SQL procedure successfully
completed.
SQL> commit;
Commit complete.
SQL> CREATE INDEX
IDX_TEST_01 ON TEST(ID,NAME) PCTFREE 0;
Index
created.
首先创建了一个测试表TEST，并向里面插入了10001 条记录，ID 最小是10000，最大是20000。然后再TEST 的ID,NAME 列上创建了升序索引。此时索引中的数据将会先按照ID 排序，再按照NAME 列排序。注意我将PCTFREE 设置为0。这将会导致叶子节点块的空间都填满了，当然B 树索引的最右边的叶子块除外(可能填满也可能没填满)。准备工作完成。
2.索引的信息
首先我们分析一下这个索引的情况。
SQL>
ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;
Index analyzed.
SQL> set lines 200
QL> SELECT
HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM
INDEX_STATS;
HEIGHT BLOCKS
NAME
PARTITION_NAME
LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED
---------- ----------
-------------------------------------------- ---------- ----------- ----------
----------
2 88 IDX_TEST_01 10001
0 85 98
可以看到，这个索引有的二元高度为2,BLOCKS数为88(包括根块，枝块，叶子块及其一些开销块) ,叶子块记录数为10001，叶子块数为85，由于最后一个叶子块空间没有用完，因此 PCT_USED 显示的并不是100%，而是98%。
3.新增记录对索引的影响
SQL>
INSERT INTO TEST VALUES(20001,RPAD(20001,50,'X'),SYSDATE);
1 row created.
SQL> commit;
Commit complete.
--由于20001 比表中的最大值20000 还大，因此数据将会插入到索引数的最右边的叶子节点。由于索引树的最后一个叶子节点还有空闲空间容纳这条记录，因此数据能顺利插入。 索引的叶子块数也不会发生改变。
SQL> ANALYZE INDEX
IDX_TEST_01 VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT
HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS;
HEIGHT BLOCKS
NAME
PARTITION_NAME LF_ROWS DEL_LF_ROWS
LF_BLKS PCT_USED
---------- ----------
--------------- --------------- ---------- ----------- ---------- ----------
2 88
IDX_TEST_01 10002 0
85 98
可以看到索引的叶子块中的记录数已经为10002 增加了1，但是叶子块数却还是85，没有改变。
--如果我们执行如下的SQL： INSERT INTO TEST
VALUES(9999,RPAD(9999,50,'X'),SYSDATE);
由于9999 比表中的ID 最小值10000 还小，因此数据将会插入到索引数的最左边的叶子节点。 而此时索引数的最左边的叶子节点已经没有空闲空间容纳这条记录，数据无法插入。ORACLE 将会在后台进行索引块的5-5 分割，将大约一半的数据放到新的索引块中，原来的数据继续留在索引的块中。然后将9999 的记录插入到相应的块中。
SQL>
INSERT INTO TEST VALUES(9999,RPAD(9999,50,'X'),SYSDATE);
1
row created.
SQL>
commit;
Commit
complete.
SQL>
ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;
Index
analyzed.
SQL>
SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED
FROM INDEX_STATS;
HEIGHT
BLOCKS NAME
PARTITION_NAME LF_ROWS DEL_LF_ROWS
LF_BLKS PCT_USED
---------- ---------- ---------------
--------------- ---------- ----------- ---------- ----------
2 88
IDX_TEST_01 10003
0 86
97
可以看到，索引的叶子块中的记录数已经为10003 增加了1，并且叶子块数已经增加到了86，这就是索引块的分割导致一个数据块一分为二。
--如果此时继续插入下面的SQL 语句,将会发生什么呢？ INSERT INTO
TEST VALUES(9998,RPAD(9998,50,'X'),SYSDATE);
由于最左边的块刚刚已经发生过分割，1 个块一分为二。因此现在左边的2个块大约还有一半的空闲空间。因此容纳记录9998 有足够的空间了。
如下所示：
SQL>
INSERT INTO TEST VALUES(9998,RPAD(9998,50,'X'),SYSDATE);
1
row created.
SQL>
commit;
Commit complete
SQL>
ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;
Index
analyzed.
SQL>
SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED
FROM INDEX_STATS;
HEIGHT
BLOCKS NAME
PARTITION_NAME LF_ROWS DEL_LF_ROWS
LF_BLKS PCT_USED
---------- ---------- ---------------
--------------- ---------- ----------- ---------- ----------
2 88 IDX_TEST_01 10004
0
86 97
可以看到，记录增加，叶子块却没有增加。
--如果插入下面的SQL：INSERT INTO TEST
VALUES(14998,RPAD(14998,50,'X'),SYSDATE);
根据前面的分析，及其目前索引块的空闲情况，此时也会进行索引块的分割。
新开一个会话：
SQL>
select sid from v$mystat where rownum<2;
SID
----------
30
SQL> INSERT INTO TEST
VALUES(14998,RPAD(14998,50,'X'),SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX
IDX_TEST_01 VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT
HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM
INDEX_STATS;
HEIGHT BLOCKS
NAME
PARTITION_NAME LF_ROWS DEL_LF_ROWS
LF_BLKS PCT_USED
---------- ----------
-------------------- --------------- ---------- ----------- ----------
----------
2 96
IDX_TEST_01 10005
0 87
96
可以看到索引块又发生分割了。
也可以通过下面的SQL 语句查询索引块的分裂数。
SQL>
SELECT B.NAME, A.VALUE
FROM v$SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME LIKE '%split%'
AND A.SID = 30;
NAME
VALUE
----------------------------------------------------------------
----------
leaf node
splits
1
leaf node 90-10
splits
0
branch node
splits
0
root node
splits
0
queue
splits
0
实验2：模拟并发引发 enq: TX - index contention
为了演示的方便，重建创建一个稍微大一点的表
SQL> DROP TABLE TEST;
Table dropped.
SQL> CREATE TABLE
TEST(ID NUMBER,NAME CHAR(10), CREATED DATE,CONTENTS VARCHAR2(4000));
Table created.
SQL> CREATE INDEX IDX_TEST_01 ON TEST(CREATED,CONTENTS);
Index created.
--分两个窗口进行：session 1：26 session：33
先统计一下这2 个会话有关索引分割的统计信息如下：
SELECT A.SID, B.NAME,
A.VALUE
FROM v$SESSTAT A,
V$STATNAME B
WHERE A.STATISTIC# =
B.STATISTIC#
AND B.NAME
LIKE '%split%'
AND A.SID IN
(26,33)
ORDER BY 1, 2;
SID
NAME
VALUE
----------
---------------------------------------------------------------- ----------
26 branch node
splits
0
26 leaf node 90-10
splits
0
26 leaf node
splits
0
26 queue
splits
0
26 root node
splits
0
33 branch node
splits
0
33 leaf node 90-10
splits
0
33 leaf node
splits
0
33 queue
splits
0
33 root node
splits
0
10 rows selected.
--接下来同时在session 1和session 2向表中插入记录，且在插入数据的同时再开一个窗口监控等待事件
session 1： 26
SQL> SELECT
USERENV('SID') FROM DUAL;
USERENV('SID')
--------------
26
SQL> BEGINSQL> BEGIN
2
FOR I IN 0 .. 60000 LOOP
3 INSERT INTO TEST VALUES (I, TO_CHAR(I),
SYSDATE, RPAD('X', 2000, 'X'));
4
END LOOP;
5 END;
6 /
session 2：33
SQL> SELECT
USERENV('SID') FROM DUAL;
USERENV('SID')
--------------
33
SQL> BEGIN
2
FOR I IN 0 .. 60000 LOOP
3 INSERT INTO TEST VALUES (I, TO_CHAR(I),
SYSDATE, RPAD('X', 2000, 'X'));
4
END LOOP;
5 END;
6 /
--插入前查询等待事件如下：
SQL> set lines 200
SQL> col event for
a30
SQL> col machine
for a15
SQL> select
inst_id,sid,sql_id,status,machine,event,blocking_session,wait_time,state,seconds_in_wait
from gv$session where event like 'enq: TX - index contention';
no rows selected
--插入期间查询等待事件：
SQL> set lines 200
SQL>
col event for a30
SQL>
col machine for a15
SQL>
select
inst_id,sid,sql_id,status,machine,event,blocking_session,wait_time,state,seconds_in_wait
from gv$session where event like 'enq: TX - index contention';
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
INST_ID SID
SQL_ID STATUS
MACHINE
EVENT
BLOCKING_SESSION WAIT_TIME
STATE
SECONDS_IN_WAIT -------------------------------- ---------------- ----------
------------------- -------------------------------------------------------------------------------
1 33 41vqxgnub01q1
ACTIVE
wang enq: TX
- index contention
26 0
WAITING
0
SQL> select sql_text from v$sql where sql_id='41vqxgnub01q1';
SQL_TEXT
-------------------------------------------------------------------------------------------------------
INSERT INTO TEST VALUES (:B1 , TO_CHAR(:B1 ), SYSDATE, RPAD('X', 2000, 'X'))
SQL> l
1 SELECT A.SID, B.NAME,
A.VALUE
2 FROM
v$SESSTAT A, V$STATNAME B
3 WHERE A.STATISTIC#
= B.STATISTIC#
4 AND
B.NAME LIKE '%split%'
5 AND
A.SID IN (26,33)
6* ORDER BY 1, 2
SQL> /
SID
NAME
VALUE
----------
---------------------------------------------------------------- ----------
26 branch node
splits
7334
26 leaf node 90-10
splits
19705
26 leaf node
splits
20142
26 queue
splits
0
26 root node
splits
6
33 branch node
splits
7414
33 leaf node 90-10
splits
19918
33 leaf node
splits
20149
33 queue
splits
0
33 root node
splits
2
10 rows selected.
查看结果看到了大量的分裂 ….
排障
方法1：生成awr根据mos 文档故障排除："enq: TX - index contention" (文档 ID
2331575.1)
识别热索引：有争用的索引可以从问题时段的AWR报告中确定。
再结合如下：
基本上就是top前几位的sql锁引起的问题
方法2：
select a.sid hold_sid, b.sid wait_sid, a.id1, a.id2, a.type,a.lmode,a.request,a.ctime
from gv$lock a, gv$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1
and b.block = 0;
关注hold_sid窗口的a.ctime，分析排行时间最长的ctime。
方法三：
利用hanganalyze分析
10g的hang trace文件：如下：
11g的hang trace文件：
处理
根据MOS文档Troubleshooting 'enq: TX - index contention' Waits (文档 ID 873243.1)
将 AWR 报告的'Segments by Row Lock Waits'中列出的索引重建为反向键索引或散列分区
CREATE INDEX <index name> ON <column> REVERSE;
ALTER INDEX <index name> rebuild reverse;
反向键索引旨在消除插入应用程序上的索引热点,但它的缺点是它可能影响索引范围扫描的性能.
考虑增加 sequence 的 CACHE 大小
alter sequence <owner>.<seq name> cache <required
value>;
当我们使用单调增加的sequence来填充列值时，具有高序列值的叶子索引块将随着每个插入而改变，这使得它成为块分裂的热块和潜在候选。
在大量数据清除后，重建或 shrink 相关联索引
如果有大量的数据清除（删除）操作，通过alterindexrebuild或alterindexshrink命令重建或缩小关联的索引有助于减少等待。
增加索引的 PCT_FREE
高的PCT_FREE有助于避免索引块的ITL争用。当一个区块内的所有可用ITL当前正在使用中，而且Oracle的PCT_FREE区域没有足够的空间来动态分配新的ITL插槽，那么将发生ITL争用.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2149635/ ，如需转载，请注明出处，否则将追究法律责任。
