当前位置: 首页 > systemstate > 正文

systemstate分析enq:TX-row lock contention

下面trace文件来至于一个完整分析方案中的一部分,只写出了TX的东西,环境为11.2.0.4的一个环境

 

欢迎大家加入ORACLE超级群:17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

 

1SESSION等待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,ID110008id2629

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,前一个ENQUEUESO

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     

本文固定链接: http://www.htz.pw/2014/07/16/systemstate%e5%88%86%e6%9e%90enqtx-row-lock-contention.html | 认真就输

该日志由 huangtingzhong 于2014年07月16日发表在 systemstate 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: systemstate分析enq:TX-row lock contention | 认真就输
关键字: ,