2021年6月1日，一客户反馈接口数据同步缓慢，有大量积压。登录数据库主机查看负载情况，发现有比较严重的阻塞与等待现象。主要等待事件为enq: TX - index contention，gc buffer busy acquire等。
在此次故障中，还存在很严重的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.
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;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-2774912/，如需转载，请注明出处，否则将追究法律责任。