一次大量enq:TX - row lock contention锁等待的问题(二)
UEST TY
[16:16:03]------------------------------------------------- ---------- ---------- ---------- ---------- --
[16:16:03]Holder: 7205 21037088 51133 6 0 TX
[16:16:03]Waiter: 14692 21037088 51133 0 6 TX
[16:16:03]Waiter: 6246 21037088 51133 0 6 TX
[16:16:03]Waiter: 1190 21037088 51133 0 6 TX
[16:16:03]Holder: 12643 21299226 52395 6 0 TX
[16:16:03]Waiter: 3820 21299226 52395 0 6 TX
[16:16:03]Waiter: 10197 21299226 52395 0 6 TX
[16:16:03]Waiter: 1926 21299226 52395 0 6 TX
[16:16:03]Holder: 7169 21561365 54540 6 0 TX
[16:16:03]Waiter: 13929 21561365 54540 0 6 TX
[16:16:03]Waiter: 1562 21561365 54540 0 6 TX
[16:16:03] www.2cto.com
[16:16:03]SESS ID1 ID2 LMODE REQUEST TY
[16:16:03]------------------------------------------------- ---------- ---------- ---------- ---------- --
[16:16:03]Waiter: 9080 21561365 54540 0 6 TX
[16:16:03]Holder: 8863 21889041 50009 6 0 TX
[16:16:03]Waiter: 14914 21889041 50009 0 6 TX
[16:16:03]Waiter: 14911 21889041 50009 0 6 TX
[16:16:03]Waiter: 14902 21889041 50009 0 6 TX
[16:16:03]Waiter: 14882 21889041 50009 0 6 TX
[16:16:03]Waiter: 14872 21889041 50009 0 6 TX
[16:16:03]Waiter: 14865 21889041 50009 0 6 TX
[16:16:03]Waiter: 14721 21889041 50009 0 6 TX
3，由于是rac，做一个全局的HANGANALYZE
[sql] www.2cto.com
[16:16:57]SQL> ORADEBUG setmypid
[16:16:57]Statement processed.
[16:17:02]SQL> ORADEBUG setinst all
[16:17:03]Statement processed.
[16:17:07]SQL> ORADEBUG -g def hanganalyze 3
[16:17:26]Hang Analysis in /oracle/diag/rdbms/yydb/yydb1/trace/yydb1_diag_4260840.trc
4，此时被阻塞的语句是大量update
[sql]
16:17:35]SQL_ID PCTLOAD CPU OTHER IO SQL_TEXT
[16:17:35]------------- ------- ---- ----- ---- ----------------------------------------------------------------------------------------------------
[16:17:35]9bxujnufu8hfd 17 0 100 0 update INS__100 set STATE = :1 ,DONE_DATE = :2 where ROWID = :3
[16:17:35]8
vcrwd10dsd5w 15 0 100 0 update INS__101 set STATE = :1 ,DONE_DATE = :2 where ROWID = :3
[16:17:35]777xsjn66q3a3 10 0 100 0 update INS__102 set STATE = :1 ,DONE_DATE = :2 where ROWID = :3 www.2cto.com
[16:17:35]3xng1922jhqdx 10 0 100 0 update INS__103 set STATE = :1 ,DONE_DATE = :2 where ROWID = :3
[16:17:35]5g2gyxwn24cfj 8 0 100 0 update INS__100 set OP_ID = :1 ,STATE = :2 ,DONE_DATE = :3 where ROWID = :4
[16:17:35]ac17casyksfka 5 0 100 0 update INS__100 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,STATE = :3 ,COUNTY_CODE = :4 ,DONE_DA
[16:17: