故障分析：systemstate分析enq:TX-row lock contention
下面trace文件来至于一个完整分析方案中的一部分，只写出了TX的东西，环境为11.2.0.4的一个环境
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到
1，SESSION等待TX
SO: 0x9b4728e8, type: 4, owner: 0x9cde1780, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x9cde1780, name=session, file=ksu.h LINE:12624, pg=0
(session) sid: 795 ser: 15 trans: 0x951b5128, creator: 0x9cde1780
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x9) -/-/INC
DID: , short-term DID:
txn branch: (nil)
oct: 3, prv: 0, sql: 0x8e4f5860, psql: 0x8f112090, user: 0/SYS
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: oracle, term: pts/1, ospid: 6441
machine: 11rac1 program: sqlplus@11rac1 (TNS V1-V3)
application name: sqlplus@11rac1 (TNS V1-V3), hash value=985707405
Current Wait Stack:
0: waiting for ‘enq: TX – row lock contention’
name|mode=0x54580006, usn<<16 | slot=0x10008, sequence=0x629
wait_id=12 seq_num=13 snap_id=1
wait times: snap=326 min 43 sec, exc=326 min 43 sec, total=326 min 43 sec
wait times: max=infinite, heur=326 min 43 sec
wait counts: calls=39125 os=39125
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 793, ser: 5
Dumping final blocker:
inst: 1, sid: 793, ser: 5
There are 2 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 25, ser: 9从这个SESSION中，我们可以看到SESSION正在等等enq: TX – row lock contention,ID1为10008，id2为629
2，查询会话正在扫描的SQL语句
以libraryhandle*8e4f5860来搜索
SO: 0x8e5c25e0, type: 78, owner: 0x9b4728e8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x9cde1780, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0
LibraryObjectLock: Address=0x8e5c25e0 Handle=0x8e4f5860 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=1
User=0x9b4728e8 Session=0x9b4728e8 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=53c5d9d0
LibraryHandle: Address=0x8e4f5860 Hash=169e8b2b LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select scott.seq_test.nextval from dual
FullHashValue=2526c79f4aaf29918254aba9169e8b2b Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=379489067 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x8e4f5910(0, 7, 0, 0) Mutex=0x8e4f5990(0, 147, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x8e4f58f0[0x8e4f58f0,0x8e4f58f0]
Pin=0x8e4f58d0[0x8e4f58d0,0x8e4f58d0]
LoadLock=0x8e4f5948[0x8e4f5948,0x8e4f5948]
Timestamp: Current=07-16-2014 09:47:07
HandleReference: Address=0x8e4f5a20 Handle=(nil) Flags=[00]
LibraryObject: Address=0x8bb640b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size=’16’
Child: id=’0′ Table=0x8bb64f60 Reference=0x8bb649b8 Handle=0x8e576af0
NamespaceDump:
Parent Cursor: sql_id=84p5bp4b9x2tb parent=0x8bb64150 maxchild=1 plk=y ppn=n3，查看enqueue的信息
以enqueue10008629，其中这里我们也可以通过enqueueTX或者是其它的如果enqueueCF来搜索，得session最近的一行记录
得到下面的内容
49577 (enqueue) TX-00010008-00000629 DID: 0001-002F-00000009
54216 (enqueue) TX-00010008-00000629 DID: 0001-0031-00000006其实这里我们知道，所进更少的就代表的是持有者，如红色
—————————————-
SO: 0x9ac03e38, type: 8, owner: 0x9034c1b0, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x9cde1780, name=enqueue, file=ksq1.h LINE:380, pg=0
(enqueue) TX-00010008-00000629 DID: 0001-0031-00000006
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x7
req: X, lock_flag: 0x10, lock: 0x9ac03e90, res: 0x9b320c08
own: 0x9b4728e8, sess: 0x9b4728e8, proc: 0x9cde1780, prv: 0x9b320c28
slk: 0x9841d5a8
—————————————-req: X这个可以看到，请求的是X级别的锁
4，查看enqueue的持有者
—————————————-
SO: 0x950f50f8, type: 56, owner: 0x9b478838, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x9cddf620, name=transaction, file=ktccts.h LINE:410, pg=0
(trans) flg = 0x00001e03, flg2 = 0x000c0000, flg3 = 0x00000000, prx = (nil), ros = 2147483647, crtses=0x9b478838
flg = 0x00001e03: ALC TRN VUS VID CHG USN
flg2 = 0x000c0000: PGA NIP
flg3 = 0x00000000:
bsn = 0xfb0 bndsn = 0xfb1 spn = 0xfb2
efd = 4 rfd = 0 DID:
file:kta.c lineno:1662
parent xid: 0x0000.000.00000000
env [0x950f5510]: (scn: 0x0000.01d20b25 xid: 0x0001.008.00000629 uba: 0x00c00ae1.0175.01 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.01d20b26 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00000000 flg: 0x00000000)
cev: (spc = 8012 arsp = 0x94d2dc38 ubkds (ubk:tsn: 2 rdba: 0x00c00ae1 flag:0x4 hdl:(nil) addr:0x85d38014) useg tsn: 2 rdba: 0x00c00080
hwm uba: 0x00c00ae1.0175.01 col uba: 0x00000000.0000.00
num bl: 1 bk list: 0x954d54c0)
cr opc: 0x0 spc: 8012 uba: 0x00c00ae1.0175.01
Begin scn:0x0000.01d20b26 uba:0x00c00ae1.0175.01 ts:1405475262[07/16/2014 09:47:42]
Undo blks: 1 recs: 1
ccbstg: 0x00000000
(enqueue) TX-00010008-00000629 DID: 0001-002F-00000009
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x7
mode: X, lock_flag: 0x0, lock: 0x950f5170, res: 0x9b320c08
own: 0x9b478838, sess: 0x9b478838, proc: 0x9cddf620, prv: 0x9b320c18
slk: 0x9841ce68
xga: (nil), heap: UGA
tsnl:0x8f071d60 nent:1 nxt:(nil)
tsn:0 uba:0x00c00ae1.0175.01
arsp=0x94d2dc38 usn:1 link:0x94d2dcb8[94d2d2e0,94d2d2e0]
lat:1 siz:2220032 wrt:24080 get:131
wat:0 ext:4 nax:1 nbx:0
tsz:262360 opt:4294967295 hwm:2220032 flg:0100
nsh:0 nwp:0 nex:0 tsh:0
ash:0 imu:65535 bsz:8168 aae:0
ifl:3 tct:11 tsn:2 dba:0x00c00080
qualify: imu_ok: 1 imu_not_ok: 1
Trans IMU st: 0 Pool index 65535, Redo pool 0x950f58b8, Undo pool 0x950f59a0
Redo pool range [0x7f77a0c1c180 0x7f77a0c1c180 0x7f77a0c1e980]
Undo pool range [0x7f77a0c19980 0x7f77a0c19980 0x7f77a0c1c180]
chnf control flags 0x0 CHNF hwm uba uba: 0x00000000.0000.00这些得到了enqueue的持有等级，会话的SO,进程的SO,前一个ENQUEUE的SO
5，查看持者的会话
以so*9b478838*type*4
—————————————-
SO: 0x9b478838, type: 4, owner: 0x9cddf620, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x9cddf620, name=session, file=ksu.h LINE:12624, pg=0
(session) sid: 793 ser: 5 trans: 0x950f50f8, creator: 0x9cddf620
flags: (0x41) USR/- flags_idl: (0x0) -/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: (nil)
oct: 3, prv: 0, sql: 0x8e480a68, psql: 0x8e480a68, user: 0/SYS
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: oracle, term: pts/0, ospid: 6363
machine: 11rac1 program: sqlplus@11rac1 (TNS V1-V3)
application name: sqlplus@11rac1 (TNS V1-V3), hash value=985707405
Current Wait Stack:
0: waiting for ‘SQL*Net message from client’6，查看SQL语句
SO: 0x8d720750, type: 78, owner: 0x9b478838, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x9cddf620, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0
LibraryObjectLock: Address=0x8d720750 Handle=0x8e480a68 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=1
User=0x9b478838 Session=0x9b478838 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=53c5d9be
LibraryHandle: Address=0x8e480a68 Hash=df8da661 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from seq$ where obj#=:"SYS_B_0" for update
FullHashValue=d0606922ffe0518419476a39df8da661 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3750602337 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=2 ActiveLocks=1 TotalLockCount=5 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0
Concurrency: DependencyMutex=0x8e480b18(0, 9, 0, 0) Mutex=0x8e480b98(0, 87, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x8e480af8[0x8e480af8,0x8e480af8]
Pin=0x8e480ad8[0x8e480ad8,0x8e480ad8]
LoadLock=0x8e480b50[0x8e480b50,0x8e480b50]
Timestamp: Current=07-16-2014 09:46:06
HandleReference: Address=0x8e480c38 Handle=(nil) Flags=[00]
LibraryObject: Address=0x8bbc80b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size=’16’
Child: id=’0′ Table=0x8bbc8f60 Reference=0x8bbc89b8 Handle=0x8e557a68
NamespaceDump:
Parent Cursor: sql_id=1kjva77gsv9m1 parent=0x8bbc8150 maxchild=1 plk=y ppn=n------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
