平台 hpux 11.31 for oracle 10.2.0.4 rac出现严重的enq: TX - row lock contention等待事件,拖得前台业务操作变得很慢,曾经怀疑是BUG造成的,但这个等待事件全部来自于其中的一个业务模块用户,其他的业务模块都没有出现这个等待事件,在alert日志里也没有出现任何错误代码。摘录ASH报告片段如下:
Top User Events
Event Event Class % Activity Avg Active Sessions
enq: TX - row lock contention Application 97.89 47.07
CPU + Wait for CPU CPU 1.26 0.61 Top SQL Statements
SQL ID Planhash % Activity Event % Event SQL Text
1duqy3ymwjmk2 527214071 25.56 enq: TX - row lock contention 25.56 update CC20 set AAC001=:1, AA...
ag6xavbchc83y 1532225287 16.54 enq: TX - row lock contention 16.54 UPDATE AC01 SET AAC027=DECODE(...
gpmh882kc4wcm 1539173121 13.97 enq: TX - row lock contention 13.96 UPDATE CB21 SET ACB219 = NVL(:...
00acgbca50xk1 1532225287 12.30 enq: TX - row lock contention 12.29 update AC01 set AAB001=:1, AA...
cx8xw0upms9x2 587156927 6.16 enq: TX - row lock contention 6.16 update AB01 set SB_AAB001=:1, ... Top Sessions
Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
454, 1290 2.05 enq: TX - row lock contention 2.05 user1 JDBC Thin Client 740/750 [ 99%] 0
495, 8051 2.05 enq: TX - row lock contention 2.05 users1 JDBC Thin Client 740/750 [ 99%] 1
502, 5460 2.05 enq: TX - row lock contention 2.05 user1 740/750 [ 99%] 1
844, 746 2.05 enq: TX - row lock contention 2.05 user1 740/750 [ 99%] 1
857, 1329 2.05 enq: TX - row lock contention 2.05 user1 JDBC Thin Client 740/750 [ 99%] 0 怀疑是程序方面的原因,让开发去查,开发方面查完后说SQL没有问题
Top User Events
Event Event Class % Activity Avg Active Sessions
enq: TX - row lock contention Application 97.89 47.07
CPU + Wait for CPU CPU 1.26 0.61 Top SQL Statements
SQL ID Planhash % Activity Event % Event SQL Text
1duqy3ymwjmk2 527214071 25.56 enq: TX - row lock contention 25.56 update CC20 set AAC001=:1, AA...
ag6xavbchc83y 1532225287 16.54 enq: TX - row lock contention 16.54 UPDATE AC01 SET AAC027=DECODE(...
gpmh882kc4wcm 1539173121 13.97 enq: TX - row lock contention 13.96 UPDATE CB21 SET ACB219 = NVL(:...
00acgbca50xk1 1532225287 12.30 enq: TX - row lock contention 12.29 update AC01 set AAB001=:1, AA...
cx8xw0upms9x2 587156927 6.16 enq: TX - row lock contention 6.16 update AB01 set SB_AAB001=:1, ... Top Sessions
Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
454, 1290 2.05 enq: TX - row lock contention 2.05 user1 JDBC Thin Client 740/750 [ 99%] 0
495, 8051 2.05 enq: TX - row lock contention 2.05 users1 JDBC Thin Client 740/750 [ 99%] 1
502, 5460 2.05 enq: TX - row lock contention 2.05 user1 740/750 [ 99%] 1
844, 746 2.05 enq: TX - row lock contention 2.05 user1 740/750 [ 99%] 1
857, 1329 2.05 enq: TX - row lock contention 2.05 user1 JDBC Thin Client 740/750 [ 99%] 0 怀疑是程序方面的原因,让开发去查,开发方面查完后说SQL没有问题
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货