严重的row lock contention分析

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

背景:

近期接手客户某系统的监控维护任务。该系统整体上比较稳定,业务也比较流畅。

但是临近一年的中业务高峰时期,还需要解决隐患问题。 因为处在业务量,数据量极速膨胀的时代,谁也不知道未来的业务高峰期数据量,并发量有多大。某一个小问题可能在高并发的情况下,带来严重后果。为了迎接即将到来的业务高峰,我们需要大量的调研,核查,巡检,监控等工作。 也需要在蛛丝马迹中寻找到隐患问题,并且做出对应的解决方案。本文就是发现该系统中存在的一个隐藏问题,把分析思路,解决方案详细说明。

现象:

拿某典型AWR报告数据说明, 早上8点到9点enq: TX – row lock contention等待事件比较严重


很显然行锁在榜首其DBtime 竟然集中到 83%。

分析:

查询这个时间段阻塞的session:


竟然全都是被sid:2638阻塞的,那么它在干什么呢?查询sid:2638具体干的事情。


从结果中发现,sid:2638从凌晨3点半执行SQL:cc47v9d20wur4,一直执行到9点半。 也就是这个SQL执行了6小时+才执行完。 查询该SQL:

另外也查询了AWR报告中其他被阻塞的SQl


SQL_ID: 7wm6nmr3t7a2c (时间主要消耗在等待上面)

SQl毕竟也是更新同一张表。经过排查这个SQl是被阻塞了,如果没有被阻塞那么这个SQl走id索引 0.0秒/次。

解决问题:

分析阻塞源头SQl cc47v9d20wur4

执行计划:

执行计划中显示其都是全表扫描,SQl是关联跟新, 关联跟新效率是比较差的。

建议:改成merge into

测试效率:14S/次


引起大量row lock contention 的SQl,从6小时/次优化到14S/次。原先SQl一直持续到早上9点多,一般每天9:30左右也是业务高峰期,类似于SQL:7wm6nmr3t7a2c通过ID更新数据的业务会大量执行,引起大量阻塞。因此也影响系统的稳定性。 优化后SQl仅仅执行了14S/次,也是在业务高峰期之前执行完毕,避免了大量的阻塞。从而为系统的稳定性提供进一步保证。

总结:

这个SQL是从凌晨3点开始执行,虽然执行的时间较长。但是和业务高峰时间重合时间段不长,问题SQl也比较隐蔽,而且是通过等待事件指标值异常推算得出。如果通过系统CPU使用率告警,或者业务感受缓慢再去分析诊断,往往比较延迟。因为已经影响了整个系统,或者整体业务才能收到告警。这些问题需要在迎接更大的业务高峰期之前发现,并且解决。这个是对一线运维人员不小的挑战,而我们迎接这个挑战已经准备好了。

未经允许不得转载:Oracle一体机用户组 » 严重的row lock contention分析

相关推荐