enq: TX - index contention故障修復一例

       2021年6月1日，一客戶反饋介面資料同步緩慢，有大量積壓。登入資料庫主機檢視負載情況，發現有比較嚴重的阻塞與等待現象。主要等待事件為enq: TX - index contention，gc buffer busy acquire等。

調取AWR報告，發現dbtime相當高。

主要等待事件如下:

更新與插入操作極為緩慢。簡單的insert消耗時間都達到了30秒之多。

可以看到對某個秒的DML操作極為頻繁。檢視AWR報告中的 Segments by Row Lock Waits部分，其中一個索引等待尤其嚴重。

在此次故障中，還存在很嚴重的buffer busy waits

至此可以基本確認是由於這個索引訪問與更新過於頻繁導致。

MOS文件 Troubleshooting 'enq: TX - index contention' Waits (Doc ID 873243.1)如下：

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.

Causes:

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

最終的解決方案為將索引重建，反向鍵索引

CREATE INDEX <index name> ON <column> REVERSE;

重建之後，問題得到了解決