当前位置:数据库 > SQLServer >>

彻底搞清楚library cache lock的成因和解决方法(二)

答案:
上一个例子中我们主要借助于X$KGLLK基表和event systemstate解决问题,那么如果你不了解X$KGLLK基表,或者忘记了如何使用它,那也不要紧张,这里再介绍一种常规的方法。

从system state 的转储信息中,我们已经注意到PROCESS 28当前正在等待'library cache lock'。
'handle address'表示的就是正持有 PROCESS 28 进程所等待的library cache中的地址。

现在我们继续在跟踪文件中查找包含 'handle=c000000122e2a6d8' 字符串的ORACLE PROCESS,也就是查找blocking session的信息,发现信息如下:

PROCESS 26: ----------------阻塞其他会话的Oracle进程,这里PROCESS 26对应了V$PROCESS中的PID的值
  ----------------------------------------
  SO: c000000109c831e0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
  (process) Oracle pid=26, calls cur/top: c00000010b2774d0/c00000010b2774d0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 17 24 6
              last post received-location: ksusig
              last process to post me: c000000109c840f8 25 0
              last post sent: 751404 0 15
              last post sent-location: ksasnd
              last process posted by me: c000000109c836e8 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: c000000109eefda0
    O/S info: user: ora9i, term: UNKNOWN, ospid: 20552
    OSD pid info: Unix process pid: 20552, image: oracle@cs_dc02 (TNS V1-V3)
    ----------------------------------------
    SO: c0000001180b9510, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=162e1b48 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: c0000001180b9458, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=162deb18 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: c0000001180b8230, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=162de848 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: c0000001180b7b00, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=162de578 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: c000000108c99e28, type: 4, owner: c000000109c831e0, flag: INIT/-/-/0x00
c000000108c99e28 对应的就是V$SESSION 中的SADDR的值,通过这个信息就可以找到blocking session的SID等信息
    (session) trans: c0000001169403c0, creator: c000000109c831e0, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0002-001A-0000007D, short-term DID: 0000-0000-00000000
              txn branch: c00000011b825e18
              oct: 0, prv: 0, sql: 800003fb0005f7b0, psql: c00000011fbe3f98, user: 50/PUBUSER
    O/S info: user: report16, term: , ospid: 20550, machine: cs_dc02
              program: sqlplus@cs_dc02 (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
    waiting for 'SQL*Net message from dblink' blocking sess=0x0 seq=3319 wait_time=0
                driver id=28444553, #bytes=1, =0
                -------------------
这里,

 #bytes 表示个server process通过database link发送给另一个server process的字节数(bytes)
driver id 是一个10进制数,我们需要把它转化为16进制数,然后就会发现它对应于我们通过event 10046中的相应的信息:
*** 2005-01-10 11:44:48.200
WAIT #1: nam='SQL*Net message from dblink' ela= 104397696 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0

SQL> select to_char(675562835,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TO_CHAR(675562835,'XXXXXXXXXXXXXX
---------------------------------
                         28444553

SQL>
               
    temporary object counter: 0
      ----------------------------------------
      SO: c00000011a4496b0, type: 51, owner: c000000108c99e28, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000011a4496b0 handle=c00000012029f968 mode=N
      call pin=0000000000000000 session pin=c00000011a44ad70
      htl=c00000011a449720[c00000011a4baa78,c00000011a4baa78] htb=c00000011a4baa78
      user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=173
      LIBRARY OBJECT HANDLE: handle=c00000012029f968
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=3
      lwt=c00000012029f998[c00000012029f998,c00000012029f998] ltm=c00000012029f9a8[c00000012029f9a8,c00000012029f9a8]
      pwt=c00000012029f9c8[c00000012029f9c8,c00000012029f9c8] ptm=c00000012029fa58[c00000012029fa58,c00000012029fa58]
      ref=c00000012029f978[c0000001202a0068, c0000001202a0068] lnd=c00000012029fa70[c00000012029fa70,c00000012029fa70]
        LIBRARY OBJECT: object=c00000012029f5c8
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        DEPENDENCIES: count=1 size=16
        AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
        ACCESSES: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c00000012029f8a8 c00000012029f288 I/P/A     0 NONE 
            6 c00000012029f6e8 c00000012029e7c8 I/-/A     0 NONE 
      ----------------------------------------

。。。 。。。


          ----------------------------------------
          SO: c00000011a44a150, type: 51, owner: c0000001169403c0, flag: INIT/-/-/0x00
      ////////////// X$KGLLK.KGLLKADR 对应于SO(SO: c00000011a44a150 //////////////
      ////////////// X$KGLLK.KGLLKUSE 和 x$kgllk.KGLLKSES

上一个:彻底搞清楚library cache lock的成因和解决方法(三)
下一个:彻底搞清楚library cache lock的成因和解决方法(一)

Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,