OLTP系统的数据库出现严重：enq: TX - index contention 等待事件

foxhuntwang

系统中有个表 ：CHANGE_NOTIF  ，表中只有一个主键索引： PK_CHANGE_NOTIF.这个表的特点就是 频繁的有数据插入，然后每天对其中已经处理过的数据进行一次删除。
平时每天的新增的数据量在 100W 左右， 但是在月初 也就是联通出账后，的 1号-- 3号  每天会有 1000W 的数据量。

出现问题的情况是5月1号 发现执行查询这个表数据的SQL 变的很慢：
SELECT MIN("A1"."CHANGE_NOTIF_ID"), MAX("A1"."CHANGE_NOTIF_ID"), COUNT(*), DECODE(UPPER("A1"."TABLE_NAME"), 'ACCT', 1, 'SUBS', 2, 'SUBS_UPP_INST', 3, 'PROD', 4, 'PROD_ATTR_VALUE', 5, 'CUST', 6, 'SUBS_ACCT', 7, 'FELLOW_NBR', 8, 'CALLING_CIRCLE_MEM', 9, 'SUBS_UPP_INST_VALUE', 10, 'SUBS_HOME_ZONE', 11, 'DEPEND_PROD_SPEC', 12, 'BUNDLE_SUBS_INST', 13, 'SUBS_RELA', 14, 'BAL_ACCT_ITEM_TYPE', 15, 'BAL_SHARE', 16, 'CUST_ATTR_VALUE', 17, 'ACCT_ATTR_VALUE', 18, 'NP_DB', 19, 'CHG_PRM_MSC', 20, 'CHG_PRM_MSC_LACCELL', 21, 'CHG_PRM_MSC_TRUCK', 22, 'CHG_PRM_HLR', 23, 'ROUTING_TABLE_SEG', 25, 'SUBS_ACCNBR_RELA', 26, 'SUBS_ADVICE_INST', 27, 'GEO_NET_ZONE', 28, 0) FROM "CHANGE_NOTIF" "A1" WHERE "A1"."CHANGE_NOTIF_ID">=:P_CHANGE_NOTIF_ID_LAST AND "A1"."CHANGE_NOTIF_ID"<=:P_CHANGE_NOTIF_ID_NEXT GROUP BY "A1"."TABLE_NAME"

当时处理的手段就是删除掉了 CHANGE_NOTIF   中的 已经处理过的历史数据（本来是每天晚上删除 当天历史数据的），删除后上述SQL 执行速度提高了 。每天删除数据 也没有定期对 CHANGE_NOTIF   表做碎片整理。

观察了当天的AWR 报告 ，发现其中有 enq: TX - index contention  严重的等待，并且在AWR 报告的
Segments by ITL Wait 部分 排在第一的是 ：

Owner        Tablespace Name        Object Name        Subobject Name        Obj. Type        ITL Waits        % of Capture
CC                     IDX_CC        PK_CHANGE_NOTIF                 INDEX                                     391        85.1

有几点没搞清楚：

1：为啥删了数据 SQL执行效率就提高了，当时看了执行计划， 索引 PK_CHANGE_NOTIF 走了 range scan ，而且SQL 中的查询条件 已经将数据扫描的范围 固定住了 不会去查到那些无效数据，删除了数据 索引又没重建  按道理不会起到效果才对。

2： 有什么方法可以降低 enq: TX - index contention 等待，我查了下 有的地方说 修改 索引的 initrans 值，默认是2  可以增大一些 不知道 对于我这个系统 是否有效果 。

附上AWR 报告

05-7-8.rar (36.27 KB, 下载次数: 424)

2013-5-9 23:25 上传

点击文件名下载附件

dla001

使用awrsqrpt.sql，看一下是出故障期间SQL的详细信息再做判断。