如何理解enq: TX - index contention等待的探讨与测试

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> select count(*) from test;

COUNT(*)
———-
10001

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

1 row created.

SQL> commit;

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

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;

注意：UPDATE 也会造成索引块的分割，对于索引来说 UPDATE 实际上是一条DELETE 加上一条 INSERT语句。

4.并发引发 enq: TX – index contention
无论何时，只要索引块中没有空间容纳新来的数据时，就会发生索引块的分割。 如果在分割的过程中，其他进程也同时要操作相应的索引块，那么其他进程就会处于 enq:TX – index contention等待中。

为了演示的方便，重建创建一个稍微大一点的表
SQL> DROP TABLE TEST;

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

—接下来同时在session 1和session 2向表中插入记录，且在插入数据的同时再开一个窗口监控等待事件

BEGIN
FOR I IN 0 .. 100000 LOOP
INSERT INTO TEST VALUES (I, TO_CHAR(I), SYSDATE, RPAD(‘X’, 2000, ‘X’));
END LOOP;
END;
/

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 /

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 fro免费主机域名m gv$session where event like ‘enq: TX – index contention’;

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>

关于这个等待事件描述如下：
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.
注意：如果索引块中没有空间分配事务槽还会引发enq: TX – allocate ITL entry 的竞争。

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> /

查看结果看到了大量的分裂

enq: TX – index contention

Most probable reasons are
o Indexes on the tables which are being accessed heavily from the application. o Indexes on table columns which are monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
o Large data purge has been performed, followed by high concurrent insert（大批量并发的insert）

When running an OLTP systems, it is possible to see high TX enqueue contention on index associated with tables, which are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and DELETEs concurrently. For RAC system, the concurrent INSERTs and DELETEs could happen from all the instances .

The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for TX lock in mode 4, until the session that is doing the block splits completes the operations.（索引块的分裂）
A session will initiate a index block split, when it can’??t find space in an index block where it needs to insert a new row. Before starting the split, it would clean out all the keys in the block to check whether there is enough sufficient space in the block.deleted

Splitter has to do the following activities:

o Allocate a new block.
o Copy a percentage of rows to the new buffer.
o Add the new buffer to the index structure and commit the operation.

In RAC environments, this could be an expensive operation, due to the global cache operations included. The impact will be more if the split is happening at a branch or root block level.