library cache pin&lock (1)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: library cache pin&lock (1)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
68049b45 关于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等待, |
|
1 2 3 4 5 6 7 8 |
可能发生library cache pin和library cache lock的情况: 1. 在存储过程或者函数正在运行时被编译. 2. 在存储过程或者函数正在运行时被对它们进行授权. 或者回收权限等操作. 3. 对某个表执行DDL期间, 有另外的会话对该表执行DML或者DDL 4. PL/SQL对象之间存在复杂的依赖性 dml: insert, update, delete 等 dml: modify列, drop列,add列, add主键或约束, grant, revoke等 |
|
1 2 3 |
每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件library cache pin, library cache lock直到超时. 通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误. 如下所示: |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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. |
|
1 2 3 4 5 |
session 1: SQL> show user USER is "ROGER" SQL> exec sys.call; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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> 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; |
|
1 |
模拟library cache lock |
|
1 2 |
session 1: SQL> exec sys.pin; |
|
1 2 |
session 2: SQL> revoke execute on pin from roger; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
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> 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> 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 |
|
1 2 3 4 5 6 7 8 9 10 |
关于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 |
3 Responses to “library cache pin&lock (1)”
roger写得不错，学习了1
For test！
Webmaster, I am the admin at SEOPlugins.org. We profile SEO Plugins for WordPress blogs for on-site and off-site SEO. I’d like to invite you to check out our recent profile for a pretty amazing plugin which can double or triple traffic for a Worpdress blog. You can delete this comment, I didn’t want to comment on your blog, just wanted to drop you a personal message. Thanks, Rich
Leave a Reply
You must be logged in to post a comment.