诊断和解决 "Library Cache Pin" Waits一例 (转)

===========================================================

作者: xhailiang(http://xhailiang.itpub.net)
发表于:2006.11.09 20:20
分类: oracle
出处：http://xhailiang.itpub.net/post/6051/228105
---------------------------------------------------------------

Oracle uses library cache pins to manage library cache concurrency. This tip outlines a method to deal with "library cache pin" wait events that are blocking other users

The first step is to see who is waiting for Library Cache Pins:

SQL> COL event FORMAT a20 TRUNC
SQL>
SQL> tti "Users Waiting for Library Cache Pins"
SQL> SELECT sid, event, p1raw, seconds_in_wait, wait_time
  2    FROM sys.v_$session_wait
  3   WHERE event = 'library cache pin'
  4     AND state = 'WAITING'
  5  /

Wed Aug 11                                                             page    1
                      Users Waiting for Library Cache Pins

       SID EVENT                P1RAW            SECONDS_IN_WAIT
---------- -------------------- ---------------- ---------------
       374 library cache pin    000000051862E5F0            1531
       944 library cache pin    000000051862E5F0           10383
      1057 library cache pin    000000051862E5F0           10554
       776 library cache pin    000000051862E5F0            2405

4 rows selected.

P1raw is the "Handle Address" of the object that is blocking. Execute the following query to get the object's owner and name:

SQL> tti "Object that is Blocking"
SQL> COL owner format a8
SQL> COL object format a70
SQL> SELECT kglnaown AS owner, kglnaobj as Object
  2    FROM sys.x$kglob
  3   WHERE kglhdadr='&P1RAW'
  4  /
Enter value for p1raw: 000000051862E5F0
old   3:  WHERE kglhdadr='&P1RAW'
new   3:  WHERE kglhdadr='000000051862E5F0'

Wed Aug 11                                                             page    1
                            Object that is Blocking

OWNER    OBJECT
-------- -------------------------------------------------- --------------------
         begin SP_EMP.PROC1@orcl(:a,:b,:c); end;

Identify the users that are waiting/ blocking:

SQL> tti "Blocking/Waiting Users"
SQL> col SID_SERIAL format a12
SQL> SELECT s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
  2    FROM sys.x$kglpn p, sys.v_$session s
  3   WHERE p.kglpnuse = s.saddr
  4     AND kglpnhdl   = '&P1RAW'
  5  /
Enter value for p1raw: 000000051862E5F0
old   4:    AND kglpnhdl   = '&P1RAW'
new   4:    AND kglpnhdl   = '000000051862E5F0'

Wed Aug 11                                                             page    1
                             Blocking/Waiting Users

SID_SERIAL    Mode Held    Request
------------ ---------- ----------
374,1390              0          2
776,2906              0          2
944,2193              0          2
991,59496             3          0
1057,1966             0          2

5 rows selected.

In the above example, session 991 is blocking the other sessions listed. Killing session 911 should solve the problem. However, before killing the session, you may want to collect evidence of the problem so you can log a TAR. To do so, collect 3 SYSTEMSTATE dumps at 30 seconds intervals, then submit them to Oracle support for further analysis

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6456/viewspace-112238/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6456/viewspace-112238/