library cache pin&lock (1)
<DIV>原帖地址 <a href="http://www.killdb.com/?p=447" target="_blank">http://www.killdb.com/?p=447</A></DIV><DIV> </DIV>
<DIV>关于library cache pin和library cache lock,是一个让人比较疑惑的问题。</DIV>
<DIV>我这里主要是指的event,首先来说下其原理:<BR>lock主要有三种模式:Null,share(2),Exclusive(3).<BR>在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.<BR>在修改对象时,需要获得Exclusive(排他)锁定.</DIV>
<DIV>pin操作跟lock一样,也有三种模式,Null,shared(2)和exclusive(3).<BR>只读模式时获得shared pin,修改模式获得和exclusive pin.</DIV>
<DIV>模式为shared(2)的pin会阻塞任何exclusive(3)的pin请求。<BR>模式为shared(3)的pin也会阻塞任何exclusive(2)的pin请求。</DIV>
<DIV>所有的DDL都会对被处理的对象请求排他类型的lock和pin</DIV>
<DIV>当要对一个过程或者函数进行编译时,需要在library cache中pin该对象。在pin该对象以前,需要获得该对象<BR>handle的锁定,如果获取失败,就会产生library cache lock等待。如果成功获取handle的lock,则继续在library<BR>cache中pin该对象,如果pin对象失败,则会产生library cache pin等待。如果是存储过程或者函数,存在<BR>library cache lock等待,则一定存在library cache pin等待;反过来则不一定;但如果是表引起的的等待,<BR>通常出现的等待事件都是library cache lock等待,</DIV>
<DIV><BR>可能发生library cache pin和library cache lock的情况:<BR>1、在存储过程或者函数正在运行时被编译。<BR>2、在存储过程或者函数正在运行时被对它们进行授权、或者回收权限等操作。<BR>3、对某个表执行DDL期间,有另外的会话对该表执行DML或者DDL<BR> dml:insert,update,delete 等<BR> dml: modify 列,drop列,add 列,add 主键或约束,grant,revoke等 <BR>4、PL/SQL对象之间存在复杂的依赖性 </DIV>
<DIV>每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件library cache pin,library cachelock直到超时.<BR>通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。<BR>如下所示:<BR>SQL> alter procedure pin compile;<BR>alter procedure pin compile<BR>*<BR>ERROR at line 1:<BR>ORA-04021: timeout occurred while waiting to lock object SYS.PIN<BR>需要说明一点的是该ora-04021错误不会出现在alert log中。</DIV>
<DIV>下面通过实验来进行模拟:<BR>SQL> show user<BR>USER is "SYS"<BR>SQL> create or replace procedure pin as<BR> 2 pin_count number;<BR> 3 begin<BR> 4 select count(*) into pin_count from roger.ht01;<BR> 5 dbms_lock.sleep(1800);<BR> 6 dbms_output.put_line(pin_count);<BR> 7 end;<BR> 8 /</DIV>
<DIV>Procedure created.</DIV>
<DIV>SQL> create or replace PROCEDURE call is<BR> 2 begin<BR> 3 pin;<BR> 4 dbms_lock.sleep(3000);<BR> 5 end;<BR> 6 /</DIV>
<DIV>Procedure created.</DIV>
<DIV>SQL> grant execute on pin to roger;</DIV>
<DIV>Grant succeeded.</DIV>
<DIV>SQL> grant execute on call to roger;</DIV>
<DIV>Grant succeeded.</DIV>
<DIV>SQL> </DIV>
<DIV>session 1:<BR>SQL> show user<BR>USER is "ROGER"<BR>SQL> exec sys.call;</DIV>
<DIV>session 2:<BR>SQL> revoke execute on pin from roger;</DIV>
<DIV>当然我这里session都hang住了。</DIV>
<DIV>SQL> select event,count(*) from v$session group by event;</DIV>
<DIV>EVENT COUNT(*)<BR>---------------------------------------------------------------- ----------<BR>PL/SQL lock timer 1<BR>library cache pin 1<BR>jobq slave wait 1<BR>rdbms ipc message 9<BR>smon timer 1<BR>pmon timer 1<BR>Streams AQ: qmn slave idle wait 1<BR>SQL*Net message to client 1<BR>Streams AQ: waiting for time management or cleanup tasks 1<BR>Streams AQ: qmn coordinator idle wait 1</DIV>
<DIV>10 rows selected.</DIV>
<DIV>SQL> <BR>SQL> SELECT a.SID, a.username, a.program,c.p1raw<BR> 2 FROM v$session a, x$kglpn b,v$session c<BR> 3 WHERE a.saddr = b.kglpnuse<BR> 4 AND b.kglpnmod <> 0<BR> 5 AND b.kglpnhdl = c.p1raw;</DIV>
<DIV> SID USERNAME PROGRAM P1RAW<BR>---------- --------------- ----------------------------------- --------<BR> 143 ROGER <a href="mailto:sqlplus@roger" target="_blank">sqlplus@roger</A> (TNS V1-V3) 2673ED04</DIV>
<DIV>SQL> select sql_text<BR> 2 from v$sqlarea<BR> 3 where (v$sqlarea.address, v$sqlarea.hash_value) in<BR> 4 (select sql_address, sql_hash_value<BR> 5 from v$session<BR> 6 where sid in (select sid<BR> 7 from v$session a, x$kglpn b<BR> 8 where a.saddr = b.kglpnuse<BR> 9 and b.kglpnmod <> 0<BR> 10 and b.kglpnhdl in<BR> 11 (select p1raw<BR> 12 from v$session_wait<BR> 13 where event like 'library%')));</DIV>
<DIV>SQL_TEXT<BR>----------------------------------------------------------------------<BR>BEGIN sys.call; END;</DIV>
<DIV><BR>---模拟library cache lock<BR>session 1:<BR>SQL> exec sys.pin;</DIV>
<DIV>session 2:<BR>SQL> revoke execute on pin from roger;</DIV>
<DIV>session 3:<BR>SQL> alter procedure pin compile;</DIV>
<DIV><BR>SQL> select event,count(*) from v$session where event like<BR> 2 '%library%' group by event;</DIV>
<DIV>EVENT COUNT(*)<BR>-------------------------------------- ----------<BR>library cache pin 1<BR>library cache lock 1</DIV>
<DIV>SQL><BR>SQL> SELECT a.SID, a.username, a.program,c.p1raw<BR> 2 FROM v$session a, x$kglpn b,v$session c<BR> 3 WHERE a.saddr = b.kglpnuse<BR> 4 AND b.kglpnmod <> 0<BR> 5 AND b.kglpnhdl = c.p1raw<BR> 6 AND c.event in('library cache lock')<BR> 7 /</DIV>
<DIV> SID USERNAME PROGRAM P1RAW<BR>---------- ------------------------------ ------------------------------------------------ --------<BR> 143 ROGER <a href="mailto:sqlplus@roger" target="_blank">sqlplus@roger</A> (TNS V1-V3) 2673ED04</DIV>
<DIV>SQL> select sql_text<BR> 2 from v$sqlarea<BR> 3 where (v$sqlarea.address, v$sqlarea.hash_value) in<BR> 4 (select sql_address, sql_hash_value<BR> 5 from v$session<BR> 6 where sid in (select sid<BR> 7 from v$session a, x$kglpn b<BR> 8 where a.saddr = b.kglpnuse<BR> 9 and b.kglpnmod <> 0<BR> 10 and b.kglpnhdl in<BR> 11 (select p1raw<BR> 12 from v$session_wait<BR> 13 where event like 'library cache lock%')));</DIV>
<DIV>SQL_TEXT<BR>-------------------------------------------------------<BR>BEGIN sys.pin; END;</DIV>
<DIV>SQL> select Distinct /*+ ordered*/ w1.sid waiting_session,<BR> 2 h1.sid holding_session,<BR> 3 w.kgllktype lock_or_pin,<BR> 4 od.to_owner object_owner,<BR> 5 od.to_name object_name,<BR> 6 oc.Type,<BR> 7 decode(h.kgllkmod,<BR> 8 0,<BR> 9 'None',<BR> 10 1,<BR> 11 'Null',<BR> 12 2,<BR> 13 'Share',<BR> 14 3,<BR> 15 'Exclusive',<BR> 16 'Unknown') mode_held,<BR> 17 decode(w.kgllkreq,<BR> 18 0,<BR> 19 'None',<BR> 20 1,<BR> 21 'Null',<BR> 22 2,<BR> 23 'Share',<BR> 24 3,<BR> 25 'Exclusive',<BR> 26 'Unknown') mode_requested,<BR> 27 xw.KGLNAOBJ wait_sql,<BR> 28 xh.KGLNAOBJ hold_sql<BR> 29 from dba_kgllock w,<BR> 30 dba_kgllock h,<BR> 31 v$session w1,<BR> 32 v$session h1,<BR> 33 v$object_dependency od,<BR> 34 V$DB_OBJECT_CACHE oc,<BR> 35 x$kgllk xw,<BR> 36 x$kgllk xh<BR> 37 where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and<BR> 38 ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and<BR> 39 (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and<BR> 40 ((w.kgllkreq != 0) and (w.kgllkreq != 1))))<BR> 41 and w.kgllktype = h.kgllktype<BR> 42 and w.kgllkhdl = h.kgllkhdl<BR> 43 and w.kgllkuse = w1.saddr<BR> 44 and h.kgllkuse = h1.saddr<BR> 45 And od.to_address = w.kgllkhdl<BR> 46 And od.to_name = oc.Name<BR> 47 And od.to_owner = oc.owner<BR> 48 And w1.sid = xw.KGLLKSNM<BR> 49 And h1.sid = xh.KGLLKSNM<BR> 50 And (w1.SQL_ADDRESS = xw.KGLHDPAR And w1.SQL_HASH_VALUE = xw.KGLNAHSH)<BR> 51 And (h1.SQL_ADDRESS = xh.KGLHDPAR And h1.SQL_HASH_VALUE = xh.KGLNAHSH);</DIV>
<DIV>WAITING_SESSION HOLDING_SESSION LOCK OBJECT_OWN OBJECT_NAM TYPE MODE_HELD MODE_REQU WAIT_SQL HOLD_SQL<BR>--------------- --------------- ---- ---------- ---------- ---------- --------- --------- ----------------------------------- -----------------------------------<BR> 159 158 Lock SYS PIN PROCEDURE Exclusive Exclusive revoke execute on pin from roger alter procedure pin compile<BR> 158 143 Pin SYS PIN PROCEDURE Share Exclusive alter procedure pin compile BEGIN sys.pin; END;</DIV>
<DIV>SQL> </DIV>
<DIV>在编译或修改对象之前我们可以通过如下sql语句来查询看该对象是否正在被使用:<BR>SQL> col Owner for a15<BR>SQL> col using_Object for a25<BR>SQL> SELECT distinct sid using_sid,<BR> 2 s.SERIAL#,<BR> 3 kglpnmod "Pin Mode",<BR> 4 kglpnreq "Req Pin",<BR> 5 kglnaown "Owner",<BR> 6 kglnaobj "using_Object"<BR> 7 FROM x$kglpn p, v$session s, x$kglob x<BR> 8 WHERE p.kglpnuse = s.saddr<BR> 9 AND kglpnhdl = kglhdadr<BR> 10 And p.KGLPNUSE = s.saddr<BR> 11 And kglpnreq = 0<BR> 12 And upper(kglnaobj) = upper('pin')<BR> 13 /</DIV>
<DIV> USING_SID SERIAL# Pin Mode Req Pin Owner using_Object<BR>---------- ---------- ---------- ---------- ---------- -------------------------<BR> 143 5 2 0 SYS PIN</DIV>
<DIV>另外如下的查询脚本也不错,可以收藏:<BR>SQL> select distinct ses.ksusenum sid,<BR> 2 ses.ksuseser serial#,<BR> 3 ses.ksuudlna username,<BR> 4 ses.ksuseunm machine,<BR> 5 ob.kglnaown obj_owner,<BR> 6 ob.kglnaobj obj_name,<BR> 7 pn.kglpncnt pin_cnt,<BR> 8 pn.kglpnmod pin_mode,<BR> 9 pn.kglpnreq pin_req,<BR> 10 w.state,<BR> 11 w.event,<BR> 12 w.wait_Time,<BR> 13 w.seconds_in_Wait<BR> 14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,<BR> 15 --,lk.kgllkhdl,lk.kglhdpar<BR> 16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,<BR> 17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl<BR> 18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w<BR> 19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)<BR> 20 and ob.kglhdadr = pn.kglpnhdl<BR> 21 and pn.kglpnuse = ses.addr<BR> 22 and w.sid = ses.indx<BR> 23 order by seconds_in_wait desc<BR> 24 /</DIV>
<DIV> SID SERIAL# USERNAME MACHINE OBJ_OWNER OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT<BR>---- ---------- ---------- ----------- ---------- --------- -------- ---------- ---------- --------- ----------------------------------- ---------- ---------------<BR> 143 5 ROGER oracle SYS PIN 3 2 0 WAITING PL/SQL lock timer 0 1360<BR> 159 7 SYS oracle SYS PIN 0 0 3 WAITING library cache pin 0 454<BR> <BR>SQL> </DIV>
<DIV>关于library cache pin和 library cache lock的 具体是如何进行的,可以通过<BR>event 10049来进行,下一篇文章将进行介绍。</DIV>
<DIV>另外eygle的博客也有篇不错的文章,里面提到10g 中,grant 已经不要要获得library cache pin了,详见:<BR><a href="http://www.eygle.com/archives/2007/04/library_cache_pin_grant.html" target="_blank">http://www.eygle.com/archives/2007/04/library_cache_pin_grant.html</A><BR>如下链接也可以参考:<BR><a href="http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/" target="_blank">http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/</A><BR><a href="http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html" target="_blank">http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html</A><BR> </DIV>
页:
[1]