ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 诊断和解决 "Library Cache Pin" Waits一例

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

原创 Linux操作系统 作者:xhailiang 时间:2006-11-09 00:00:00 0 删除 编辑
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/308563/viewspace-171934/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 查看SQL查询进度
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    415309