library cache pin &lock 模拟

原帖地址 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