LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUND

資料庫過程無法除錯，看了一下等待事件，發現是library cache pin。

--檢視等待的的會話
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';

       SID       SEQ# EVENT                                                                    P1 P1RAW                    P2 P2RAW                    P3 P3RAW            STATE
---------- ---------- ---------------------------------------------------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- -------------------
        39        647 library cache pin                                                5.04403172 07000003587A4D78 5.04403173 070000037DC33240        200 00000000000000C8 WAITING
       522      40434 library cache lock                                               5.04403173 07000003712ED5F8 5.04403172 070000034E34E590       1301 0000000000000515 WAITING

--等待的物件
SQL> select sw.sid,xb.kglhdadr as addr,xb.kglhdpar as parent_addr ,xb.kglnaown as owner,xb.kglnaobj
  2  from x$kglob xb,v$session_wait sw
  3  where xb.kglhdadr = sw.p1raw
  4  and sw.event like 'library%' ;

       SID ADDR             PARENT_ADDR      OWNER                                                            KGLNAOBJ
---------- ---------------- ---------------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
        39 07000003587A4D78 07000003584CF198                                                                  begin   --sys.dbms_session.reset_package;   sys.dbms_debug.probe_version(major =
       522 07000003712ED5F8 07000003712ED5F8 SYS                                                              DBMS_DEBUG

--鎖定情況
SQL> SELECT a.SID, a.username,
  2         case b.kglpnmod when 2 then 'S' when 3 then 'X' end "Mode",
  3         case b.kglpnreq when 2 then 'S' when 3 then 'X' end "Req" ,
  4         a.program, b.addr, b.kglpnadr, b.kglpnuse,b.kglpnses, b.kglpnhdl, b.kglpnlck
  5  FROM v$session a, x$kglpn b
  6  WHERE a.saddr = b.kglpnuse
  7  --AND b.kglpnmod <> 0
  8  AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') ;

       SID USERNAME                       Mode                             Req                              PROGRAM                                          ADDR             KGLPNADR         KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK
---------- ------------------------------ -------------------------------- -------------------------------- ------------------------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
        39 MAIN                                                         S                                plsqldev.exe                                     0000000110381F78 070000037DC33240 070000032B417910 070000032B417910 07000003587A4D78 070000034E4A2300
       522 MAIN                        X                                                                 plsqldev.exe                                     0000000110381FD0 07000003691C1980 070000032D45E4B0 070000032D45E4B0 07000003587A4D78 070000034E27CEF0

會話39是在等待pin 07000003587A4D78 ，而07000003587A4D78 被會話522以模式X lock住，所以一直等待。
但是522已經執行了幾天了，它又在等待07000003712ED5F8（dbms_debug）上加X鎖，它又是被誰阻塞了呢？

做了個systemstate trace，但是也找不到holder
[/oracle]$sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jul 27 20:30:05 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.8.0 - Production
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
Session altered.
SQL> ALTER SYSTEM SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
System altered.

開SR後，得到答覆是：

There is a bug that informs that on a rac environment the blocker was absent from both the nodes. Bug 3936673 : 9I-GSI - HIGH LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUND (Base Bug 3087362) ----------------------------- The base bug 3087362 is fixed on 10.1. Both the bugs are unpublished. We have the below options: As the bug is fixed on 10g, you can upgrade to this version. Or, Reboot the database and verify the result.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10867315/viewspace-670642/，如需轉載，請註明出處，否則將追究法律責任。