library cache pin是用来管理library cache的并发访问的。pin一个object会引起相应的heap被载入内存（如果此前没有被加载），pins可以在Null、Share、Exlusive这三种模式下获得，可以认为pin是一种特定形式的锁。当library cache pin等待事件出现时，通常说明该pin被其他用户以非兼容模式持有，它一般发生在编译或重新编译PL/SQL、VIEW、TYPES等object时。编译通常都是显性的，但alter、grant、revoke等操作也会使object变得无效，可以通过object的last_ddl_time观察到这些变化。当object变得无效时，oracle会在第一次访问此object时试图去重新编译它，如果此时其他session已经把此object pin到library cache中，就会出现问题，特别是当有大量的活动session并且存在较复杂的dependence时。某些情况下，重新编译object可能花费很长时间，从而阻塞其他试图访问该object的进程。以下通过一个例子来模拟和解释这个等待。
1、创建测试用存储过程
create or replace procedure pining is
begin
null;
end;
/
create or replace procedure calling is
begin
pining;
dbms_lock.sleep(3000);
end;
/
2、模拟竞争
session 1：
exec calling;
calling中调用pining，此时pining过程上获得共享pin，如果此时尝试对pining进行授权或重新编译，将产生library cache pin等待，直到calling执行完毕。
session 2：
grant execute on pining to scott;
revoke execute on pining from scott;
此时session2被挂起。
3、原因分析
从v$session_wait入手，得到哪些会话正在经历library cache pin的等待
col event for a30
col p1text for a20
col p2text for a20
col p3text for a20
col wait_class for a20
select t.sid, seq#, t.event, t.p1text, t.p1, t.p1raw, t.p2text, t.p2, t.p2raw, t.p3text, t.p3, t.p3raw, t.wait_class#, t.wait_class, t.wait_time, t.seconds_in_wait, t.state from v$session_wait t where event like 'library%';
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ------------------------------ -------------------- ---------- ---------------- -------------------- ---------- ---------------- -------------------- ---------- ---------------- ----------- -------------------- ---------- --------------- -------------------
142 42 library cache pin handle address 8791880329 000007FF04E76AD8 pin address 8791880412 000007FF04E8AED8 100*mode+namespace 1061758865 0000609100010003 4 Concurrency 0 52 WAITING
对于library cache pin等待事件，参数p1raw表示等待对象的handle，查询x$kglob，可以得到对象的具体信息
col kglnaown for a10
col kglnaobj for a20
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr = '000007FF04E76AD8';
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------------- ---------- -------------------- ---------- ----------------
000000001F6523A8 000007FF04E76AD8 000007FF04E76AD8 SYS PINING 1587262593 000007FF04E6ED60
上面两个查询可以结合一下，直接获得library cache pin等待的对象
col kglnaown for a10
col kglnaobj for a20
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr in
(select p1raw from v$session_wait where event like 'library%');
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------------- ---------- -------------------- ---------- ----------------
000000001F651390 000007FF04E76AD8 000007FF04E76AD8 SYS PINING 1587262593 000007FF04E6ED60
获得持有等待对象的session信息
col username for a10
col program for a20
select a.sid,
a.username,
a.program,
b.addr,
b.kglpnadr,
b.kglpnuse,
b.kglpnses,
b.kglpnhdl,
b.kglpnlck,
b.kglpnmod,
b.kglpnreq
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnhdl in
(select p1raw from v$session_wait where event like 'library%');
SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---------- ---------- -------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------
141 SYS sqlplus.exe 000000001F652648 000007FF04F15960 000007FF0BE2D6F8 000007FF0BE2D6F8 000007FF04E76AD8 000007FF04F15A60 2 0
该会话正在等待一次PL/SQL lock timer计时
select event from v$session_wait where sid = 141;
EVENT
------------------------------
PL/SQL lock timer
获得持有对象用户执行的代码
select sql_text
from v$sqlarea
where (v$sqlarea.address, v$sqlarea.hash_value) in
(select sql_address, sql_hash_value
from v$session
where sid in (select sid
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnhdl in
(select p1raw
from v$session_wait
where event like 'library%')));
SQL_TEXT
--------------------------------------------------------------------------------
BEGIN calling; END;
于是检查calling， 发现dbms_lock.sleep(3000)，也就是141会话出现的PL/SQL lock timer等待，从而找到了142会话发生library cache pin等待的原因。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28974745/viewspace-2138334/ ，如需转载，请注明出处，否则将追究法律责任。
