關於enq: TX - index contention 等待的探討與測試

最近生產庫上遭遇短時間的enq: TX - index contention 等待，導致資料庫hang住：
這個等待事件解釋如下：
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完成，由此引發了該等待事件。

當事務修改索引中的資料時,而相關索引塊沒有足夠的空間的時候,就會發生索引塊的分割,在分割的過程中前臺程式需要等待分割完畢才能繼續操作。  
   
如果這個時候其他會話也要修改這個索引塊的資料,那麼將會出現索引塊的競爭。(enq: TX- index contention).一般索引塊的分割持有資源和釋放非常短,並不會對資料庫造成嚴重的影響。但是對錶操作併發量很大的情況下可能導致嚴重的競爭。  
   
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> select count(*) from test;

  COUNT(*)
----------
     10001

SQL>  select min(id),max(id) from test;

   MIN(ID)    MAX(ID)
---------- ----------
     10000      20000


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%。  
   
PCT_USED   percent of space allocated in the b-tree that is being used   使用的空間百分比  
   
   
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>  COL NAME FORMAT A20  
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                                                          2
leaf node 90-10 splits                                                    0
branch node splits                                                        0
root node splits                                                          0
queue splits                                                              0


注意：UPDATE 也會造成索引塊的分割，對於索引來說 UPDATE 實際上是一條DELETE 加上一條 INSERT語句。  
   
4.併發引發 enq: TX - index contention  
無論何時，只要索引塊中沒有空間容納新來的資料時，就會發生索引塊的分割。 如果在分割的過程中，其他程式也同時要操作相應的索引塊，那麼其他程式就會處於 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向表中插入記錄，且在插入資料的同時再開一個視窗監控等待事件

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  /


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>


--插入期間查詢等待事件：
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> /

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>

   
關於這個等待事件描述如下：  
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> /

       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.

檢視結果看到了大量的分裂 

從抓取的ash報告來看，產生等待的是一條insert語句，而該sql要插入資料的表是一個每天需要進行頻繁delete的表，該等待事件的產生與頻繁的大批次delete是具有緊密聯絡的。廠商最後給出的建議是定期對該表進行rebuild，並加大索引的pctfree。

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.

Solutions:解決方法
a) Rebuild the index  as reverse key indexes or hash partition the indexes which are listed in the Segments by Row Lock Waits' of the AWR reports  重建索引
b) Consider increasing the CACHE size of the sequences  增大cache值
c) Rebuild or shrink associated index after huge amount of data purge   大批次的資料改動後 索引的收縮或重建
d) Increase PCT_FREE for the index 增大索引塊的PCT_FREE