原帖地址   http://www.killdb.com/?p=447

关于library cache pin和library cache lock,是一个让人比较疑惑的问题。

我这里主要是指的event,首先来说下其原理:
lock主要有三种模式:Null,share(2),Exclusive(3).
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.

pin操作跟lock一样,也有三种模式,Null,shared(2)和exclusive(3).
只读模式时获得shared pin,修改模式获得和exclusive pin.

模式为shared(2)的pin会阻塞任何exclusive(3)的pin请求。
模式为shared(3)的pin也会阻塞任何exclusive(2)的pin请求。

所有的DDL都会对被处理的对象请求排他类型的lock和pin

当要对一个过程或者函数进行编译时,需要在library cache中pin该对象。在pin该对象以前,需要获得该对象
handle的锁定,如果获取失败,就会产生library cache lock等待。如果成功获取handle的lock,则继续在library
cache中pin该对象,如果pin对象失败,则会产生library cache pin等待。如果是存储过程或者函数,存在
library cache lock等待,则一定存在library cache pin等待;反过来则不一定;但如果是表引起的的等待,
通常出现的等待事件都是library cache lock等待,


可能发生library cache pin和library cache lock的情况:
1、在存储过程或者函数正在运行时被编译。
2、在存储过程或者函数正在运行时被对它们进行授权、或者回收权限等操作。
3、对某个表执行DDL期间,有另外的会话对该表执行DML或者DDL
   dml:insert,update,delete 等
   dml: modify 列,drop列,add 列,add 主键或约束,grant,revoke等  
4、PL/SQL对象之间存在复杂的依赖性  

每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件library cache pin,library cachelock直到超时.
通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。
如下所示:
SQL> alter procedure pin compile;
alter procedure pin compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.PIN
需要说明一点的是该ora-04021错误不会出现在alert log中。

下面通过实验来进行模拟:
SQL> show user
USER is "SYS"
SQL> create or replace procedure pin as
  2    pin_count number;
  3  begin
  4    select count(*) into pin_count from roger.ht01;
  5    dbms_lock.sleep(1800);
  6    dbms_output.put_line(pin_count);
  7  end;
  8  /

Procedure created.

SQL> create or replace PROCEDURE call is
  2  begin
  3    pin;
  4    dbms_lock.sleep(3000);
  5  end;
  6  /

Procedure created.

SQL> grant execute on pin to roger;

Grant succeeded.

SQL> grant execute on call to roger;

Grant succeeded.

SQL>

session 1:
SQL> show user
USER is "ROGER"
SQL> exec sys.call;

session 2:
SQL> revoke execute on pin from roger;

当然我这里session都hang住了。

SQL>  select event,count(*) from v$session group by event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
PL/SQL lock timer                                                         1
library cache pin                                                         1
jobq slave wait                                                           1
rdbms ipc message                                                         9
smon timer                                                                1
pmon timer                                                                1
Streams AQ: qmn slave idle wait                                           1
SQL*Net message to client                                                 1
Streams AQ: waiting for time management or cleanup tasks                  1
Streams AQ: qmn coordinator idle wait                                     1

10 rows selected.

SQL>
SQL> SELECT a.SID, a.username, a.program,c.p1raw
  2    FROM v$session a, x$kglpn b,v$session c
  3   WHERE a.saddr = b.kglpnuse
  4     AND b.kglpnmod <> 0
  5     AND b.kglpnhdl = c.p1raw;

       SID USERNAME        PROGRAM                             P1RAW
---------- --------------- ----------------------------------- --------
       143 ROGER           sqlplus@roger (TNS V1-V3)           2673ED04

SQL> select sql_text
  2    from v$sqlarea
  3   where (v$sqlarea.address, v$sqlarea.hash_value) in
  4         (select sql_address, sql_hash_value
  5            from v$session
  6           where sid in (select sid
  7                           from v$session a, x$kglpn b
  8                          where a.saddr = b.kglpnuse
  9                            and b.kglpnmod <> 0
 10                            and b.kglpnhdl in
 11                                (select p1raw
 12                                   from v$session_wait
 13                                  where event like 'library%')));

SQL_TEXT
----------------------------------------------------------------------
BEGIN sys.call; END;


---模拟library cache lock
session 1:
SQL> exec sys.pin;

session 2:
SQL> revoke execute on pin from roger;

session 3:
SQL> alter procedure pin compile;


SQL>  select event,count(*) from v$session where event like
  2   '%library%' group by event;

EVENT                                    COUNT(*)
-------------------------------------- ----------
library cache pin                               1
library cache lock                              1

SQL>
SQL> SELECT a.SID, a.username, a.program,c.p1raw
  2    FROM v$session a, x$kglpn b,v$session c
  3   WHERE a.saddr = b.kglpnuse
  4     AND b.kglpnmod <> 0
  5     AND b.kglpnhdl = c.p1raw
  6     AND c.event in('library cache lock')
  7  /

       SID USERNAME                       PROGRAM                                          P1RAW
---------- ------------------------------ ------------------------------------------------ --------
       143 ROGER                          sqlplus@roger (TNS V1-V3)                        2673ED04

SQL> select sql_text
  2    from v$sqlarea
  3   where (v$sqlarea.address, v$sqlarea.hash_value) in
  4         (select sql_address, sql_hash_value
  5            from v$session
  6           where sid in (select sid
  7                           from v$session a, x$kglpn b
  8                          where a.saddr = b.kglpnuse
  9                            and b.kglpnmod <> 0
 10                            and b.kglpnhdl in
 11                                (select p1raw
 12                                   from v$session_wait
 13                                  where event like 'library cache lock%')));

SQL_TEXT
-------------------------------------------------------
BEGIN sys.pin; END;

SQL> select Distinct /*+ ordered*/ w1.sid waiting_session,
  2                  h1.sid holding_session,
  3                  w.kgllktype lock_or_pin,
  4                  od.to_owner object_owner,
  5                  od.to_name object_name,
  6                  oc.Type,
  7                  decode(h.kgllkmod,
  8                         0,
  9                         'None',
 10                         1,
 11                         'Null',
 12                         2,
 13                         'Share',
 14                         3,
 15                         'Exclusive',
 16                         'Unknown') mode_held,
 17                  decode(w.kgllkreq,
 18                         0,
 19                         'None',
 20                         1,
 21                         'Null',
 22                         2,
 23                         'Share',
 24                         3,
 25                         'Exclusive',
 26                         'Unknown') mode_requested,
 27                  xw.KGLNAOBJ wait_sql,
 28                  xh.KGLNAOBJ hold_sql
 29    from dba_kgllock         w,
 30         dba_kgllock         h,
 31         v$session           w1,
 32         v$session           h1,
 33         v$object_dependency od,
 34         V$DB_OBJECT_CACHE   oc,
 35         x$kgllk             xw,
 36         x$kgllk             xh
 37   where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
 38         ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
 39         (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
 40         ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 41     and w.kgllktype = h.kgllktype
 42     and w.kgllkhdl = h.kgllkhdl
 43     and w.kgllkuse = w1.saddr
 44     and h.kgllkuse = h1.saddr
 45     And od.to_address = w.kgllkhdl
 46     And od.to_name = oc.Name
 47     And od.to_owner = oc.owner
 48     And w1.sid = xw.KGLLKSNM
 49     And h1.sid = xh.KGLLKSNM
 50     And (w1.SQL_ADDRESS = xw.KGLHDPAR And w1.SQL_HASH_VALUE = xw.KGLNAHSH)
 51     And (h1.SQL_ADDRESS = xh.KGLHDPAR And h1.SQL_HASH_VALUE = xh.KGLNAHSH);

WAITING_SESSION HOLDING_SESSION LOCK  OBJECT_OWN OBJECT_NAM TYPE       MODE_HELD MODE_REQU WAIT_SQL                            HOLD_SQL
--------------- --------------- ----  ---------- ---------- ---------- --------- --------- ----------------------------------- -----------------------------------
     159             158        Lock  SYS        PIN        PROCEDURE  Exclusive Exclusive revoke execute on pin from roger    alter procedure pin compile
     158             143        Pin   SYS        PIN        PROCEDURE  Share     Exclusive alter procedure pin compile         BEGIN sys.pin; END;

SQL>

在编译或修改对象之前我们可以通过如下sql语句来查询看该对象是否正在被使用:
SQL> col Owner for a15
SQL> col using_Object for a25
SQL> SELECT distinct sid using_sid,
  2                  s.SERIAL#,
  3                  kglpnmod "Pin Mode",
  4                  kglpnreq "Req Pin",
  5                  kglnaown "Owner",
  6                  kglnaobj "using_Object"
  7    FROM x$kglpn p, v$session s, x$kglob x
  8   WHERE p.kglpnuse = s.saddr
  9     AND kglpnhdl = kglhdadr
 10     And p.KGLPNUSE = s.saddr
 11     And kglpnreq = 0
 12     And upper(kglnaobj) = upper('pin')
 13  /

 USING_SID    SERIAL#   Pin Mode    Req Pin Owner      using_Object
---------- ---------- ---------- ---------- ---------- -------------------------
       143          5          2          0 SYS        PIN

另外如下的查询脚本也不错,可以收藏:
SQL> select distinct ses.ksusenum sid,
  2                  ses.ksuseser serial#,
  3                  ses.ksuudlna username,
  4                  ses.ksuseunm machine,
  5                  ob.kglnaown obj_owner,
  6                  ob.kglnaobj obj_name,
  7                  pn.kglpncnt pin_cnt,
  8                  pn.kglpnmod pin_mode,
  9                  pn.kglpnreq pin_req,
 10                  w.state,
 11                  w.event,
 12                  w.wait_Time,
 13                  w.seconds_in_Wait
 14  -- lk.kglnaobj, lk.user_name, lk.kgllksnm,
 15  --,lk.kgllkhdl,lk.kglhdpar
 16  --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
 17  --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
 18    from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
 19   where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
 20     and ob.kglhdadr = pn.kglpnhdl
 21     and pn.kglpnuse = ses.addr
 22     and w.sid = ses.indx
 23   order by seconds_in_wait desc
 24  /

 SID    SERIAL# USERNAME   MACHINE     OBJ_OWNER  OBJ_NAME   PIN_CNT   PIN_MODE    PIN_REQ STATE     EVENT               WAIT_TIME SECONDS_IN_WAIT
---- ---------- ---------- ----------- ---------- --------- -------- ---------- ---------- --------- ----------------------------------- ---------- ---------------
 143          5 ROGER      oracle      SYS        PIN              3          2          0 WAITING   PL/SQL lock timer                            0            1360
 159          7 SYS        oracle      SYS        PIN              0          0          3 WAITING   library cache pin                            0             454
                                                           
SQL>                

关于library cache pin和 library cache lock的 具体是如何进行的,可以通过
event 10049来进行,下一篇文章将进行介绍。

另外eygle的博客也有篇不错的文章,里面提到10g 中,grant 已经不要要获得library cache pin了,详见:
http://www.eygle.com/archives/2007/04/library_cache_pin_grant.html
如下链接也可以参考:
http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html
                                 

 

posted on 2011-09-16 13:00  Roger's oracle blog  阅读(285)  评论(0编辑  收藏  举报