下面trace文件来至于一个完整分析方案中的一部分，只写出了TX的东西，环境为11.2.0.4的一个环境
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
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=n |
3，查看enqueue的信息
以enqueue*10008*629，其中这里我们也可以通过enqueue*TX或者是其它的如果enqueue*CF来搜索，得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 |
systemstate分析enq:TX-row lock contention：等您坐沙发呢！