ITPub博客

首页 > 数据库 > Oracle > enq: TX - index contention故障修复一例

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

原创 Oracle 作者:abstractcyj 时间:2021-06-01 15:54:46 0 删除 编辑

       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 i€™s 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;


重建之后,问题得到了解决


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-2774912/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
曾从事java方向开发多年。近年已经转入数据库方向。主要擅长SQL优化,Oracle数据库问题诊断,Oracle备份与恢复等。服务于医药物流,医院等行业

注册时间:2010-01-26

  • 博文量
    582
  • 访问量
    948817