一次大量enq:TX - row lock contention锁等待的问题(四)
35]7pdp5zfbc7bh5 4 0 100 0 update INS__104 set STATE = :1 ,DONE_DATE = :2 where ROWID = :3
[16:17:35]024cfqtnjr1vb 3 0 100 0 update INS__103 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,OP_ID = :3 ,STATE = :4 ,COUNTY_CODE =
[16:17:35]24m4b9jfqn2zm 2 0 100 0 update INS__101 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,OP_ID = :3 ,STATE = :4 ,COUNTY_CODE =
[16:17:35]8vm3vv2t6ymv6 2 0 100 0 update INS__102 set STATE = :1 ,OP_ID = :2 ,DONE_DATE = :3 where ROWID = :4
[16:17:35]cnq4y75v772af 2 0 100 0 update INS__103 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,STATE = :3 ,COUNTY_CODE = :4 ,DONE_DA
5，查看HANGANALYZE log的内容，339 sessions被sid=8863的进程阻塞
[plain]
.......
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'db file scattered read'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x13c8fc65
[b] Chain 2 Signature: 'db file scattered read'<='enq: TX - row lock contention'
Chain 2 Signature Hash: 0x13c8fc65
[c] Chain 3 Signature: 'db file scattered read'<='enq: TX - row lock contention'
Chain 3 Signature Hash: 0x13c8fc65
===============================================================================
Non-intersecting chains: www.2cto.com
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (yydb.yydb1)
os id: 46072062
process id: 320, oracle@scyydb1
session id: 1
session serial #: 59191
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x14e0011
p3: 'sequence'=0xc359
time in wait: 73 min 18 sec
timeout after: never www.2cto.com
wait id: 5488
blocking: 0 sessions
current sql: update INS_ set STATE = :1 ,DONE_DATE = :2 where ROWID = :3
short stack: ksedsts()+644<-ksdxfstk()+44<-ksdxcb()+3384<-sspuser()+116<-47dc<-sskgpwwait()+32<-skgpwwait()+180<-ksliwat()+11032<-kslwaitctx()+1
80<-kjusuc()+3652<-ksipgetctxi()+1892<-ksqcmi()+21888<-ksqgtlctx()+2904<-ksqgelctx()+660<-ktuGetTxForXid()+184<-ktcwit1()+456<-IPRA.$kdddgb()+6860<-kdddgb()+
96<-kdusru()+8516<-updrowFastPath()+1192<-qerupFetch()+2596<-updaul()+1100<-updThreePhaseExe()+336<-updexe()+344<-opiexe()+14476<-kpoal8()+4616<-opiodr()+720
<-ttcpip()+1028<-opitsk()+1508<-opiino()+940<-opiodr()+720<-opidrv()+1132<
wait history: www.2cto.com
* time between current wait and wait #1: 0.000401 sec
1. event: 'SQL*Net message from client'
time waited: 0.000596 sec
wait id: 5487 p1: 'driver id'=0x28444553
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000008 sec
2. event: 'SQL*Net message to client'
time waited: 0.000002 sec
wait id: 5486 p1: 'driver id'=0x28444553
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000117 sec
3. event: 'db file sequential read'
time waited: 0.007607 sec
wait id: 5485 p1: 'file#'=0xa2
p2: 'block#'=0x112360
p3: 'blocks'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (yydb.yydb1)
os id: 16319376
process id: 1567, oracle@scyydb1
session id: 8863
session serial #: 53455
} www.2cto.com
which is waiting for 'db file scattered read' with wait info:
{
p1: 'file#'=0xc4
p2: 'block#'=0x26d37c
p3: 'blocks'=0x10
time in wait: 0.000