library cache pin/lock
"LIBRARY CACHE PIN"通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,
如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效,
可以通过object的"LAST_DDL"观察这些变化.
当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin
到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新
编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.
1.创建测试用存储过程
SQL> create or replace PROCEDURE pining
2
IS
3
BEGIN
4
NULL;
5
END;
6
/
SQL> create or replace procedure calling
2
is
3
begin
4
pining;
5
dbms_lock.sleep(3000);
6
end;
7
/
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
2
from v$session_wait where event like 'library%';
SID
SEQ# EVENT
P1 P1RAW
P2 P2RAW
P3
WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ----------
8
268 library cache pin
1389785868 52D6730C 1387439312 52B2A4D0
301
0
2 WAITING
SID
SEQ# EVENT
P1 P1RAW
P2 P2RAW
P3
WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ----------
8
269 library cache pin
1389785868 52D6730C 1387439312 52B2A4D0
301
0
2 WAITING
SID
SEQ# EVENT
P1 P1RAW
P2 P2RAW
P3
WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ----------
8
270 library cache pin
1389785868 52D6730C 1387439312 52B2A4D0
301
0
0 WAITING
ADDR
KGLHDADR KGLHDPAR KGLNAOWN
KGLNAOBJ
KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ----------
404F9FF0 52D6730C 52D6730C SYS
PINING
2300250318 52D65BA4
SID USERNAME
PROGRAM
ADDR
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK
KGLPNMOD
KGLPNREQ
----- ----------- ---------------------------------------- --------
13 SYS
(TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8
2
0
SQL> select * from v$session_wait where sid=13;
SID
SEQ# EVENT
P1TEXT
P1 P1RAW
P2TEXT
P2 P2RAW
P3TEXT
P3 P3RAW
WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ------------------- --------- ----------
13
25 PL/SQL lock timer
duration
120000 0001D4C0
0 00
0 00
0
1200 WAITING
SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
SID USERNAME
PROGRAM
ADDR
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK
KGLPNMOD
KGLPNREQ
---------- ---------- ----------------------------------------------13 SYS
(TNS V1-V3)
404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8
2
0
SQL> select * from v$session_wait;
SID SEQ# EVENT
P1TEXT
P1 P1RAW
P2TEXT
P2 P2RAW
P3TEXT
P3 P3RAW
WAIT_TIME SECONDS STATE
---- ---- ------------------- --------------- ---------- --------
11
143 library cache pin
handle address
1390239716 52DD5FE4 pin address
1387617456 52B55CB0 100*mode+namespace 301 0000012D
0
6 WAITING
13
18 library cache lock
handle address
1390239716 52DD5FE4 lock address 1387433984 52B29000 100*mode+namespace 301 0000012D
0
3 WAITING
8
415 PL/SQL lock timer
duration
120000
0001D4C0
0 00
0 00
0
63 WAITING
....
近日数据库出现library cache pin的问题,网上转过来一个简单的查询办法,以备日后使用。
Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.
Lock比pin具有更高的级别.
Lock比pin具有更高的级别.
Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.
在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.
通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待.
在很多statspack的report中,我们可能看到此等待事件。
在很多statspack的report中,我们可能看到此等待事件。
这里的library cache lock和library cache pin都是我们关心的.接下来我们就研究一下这几个等待事件.
(一).LIBRARY CACHE PIN等待事件
Oracle文档上这样介绍这个等待事件:
"library cache pin" 是用来管理library cache的并发访问的,pin一个object会引起相应的heap被
载入内存中(如果此前没有被加载),Pins可以在三个模式下获得:NULL,SHARE,EXCLUSIVE,可以认为pin是一种特定
形式的锁.
当Library Cache Pin等待事件出现时,通常说明该Pin被其他用户已非兼容模式持有.
"library cache pin" 是用来管理library cache的并发访问的,pin一个object会引起相应的heap被
载入内存中(如果此前没有被加载),Pins可以在三个模式下获得:NULL,SHARE,EXCLUSIVE,可以认为pin是一种特定
形式的锁.
当Library Cache Pin等待事件出现时,通常说明该Pin被其他用户已非兼容模式持有.
"library cache pin"的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.
"library cache pin"的参数如下,有用的主要是P1和P2:
P1 - KGL Handle address.
P2 - Pin address
P3 - Encoded Mode & Namespace
"library cache pin"的参数如下,有用的主要是P1和P2:
"LIBRARY CACHE PIN"通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,
如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效,
可以通过object的"LAST_DDL"观察这些变化.
当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin
到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新
编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.
下面让我们通过一个例子来模拟及解释这个等待:
1.创建测试用存储过程
SQL> create or replace PROCEDURE pining
Procedure created.
SQL> create or replace procedure calling
Procedure created.
2.模拟
首先执行calling过程,在calling过程中调用pining过程
此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待
直到calling执行完毕.
首先执行calling过程,在calling过程中调用pining过程
此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待
直到calling执行完毕.
session 1:
SQL> exec calling
此时calling开始执行
session 2:
SQL> grant execute on pining to eygle;
此时session 2挂起
ok,我们开始我们的研究:
从v$session_wait入手,我们可以得到哪些session正在经历library cache pin的等待
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
---- ---------- ------------------- ---------- -------- ----------
等待3秒就超时,seq#会发生变化
SQL>
---- ---------- ------------------- ---------- -------- ----------
SQL>
---- ---------- ------------------- ---------- -------- ----------
在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示
我们看到,library cache pin等待的对象的handle地址为:52D6730C
通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:
通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='52D6730C'
from X$KGLOB
where KGLHDADR ='52D6730C'
ADDR
-------- -------- -------- ---------- -------------------- ----------
404F9FF0 52D6730C 52D6730C SYS
这里KGLNAHSH代表该对象的Hash Value
由此我们知道,在PINING对象上正经历library cache pin的等待.
然后我们引入另外一个内部视图X$KGLPN:
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.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0
/
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0
/
----- ----------- ---------------------------------------- --------
通过联合v$session,可以获得当前持有该handle的用户信息.
对于我们的测试sid=13的用户正持有该handle
对于我们的测试sid=13的用户正持有该handle
那么这个用户正在等什么呢?
SQL> select * from v$session_wait where sid=13;
---------- ---------- ------------------- --------- ----------
Ok,这个用户正在等待一次PL/SQL lock timer计时.
得到了sid,我们就可以通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作.
SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';
SQL_TEXT
--------------------------------------------------------------------
BEGIN calling; END;
--------------------------------------------------------------------
BEGIN calling; END;
这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.
我们这个calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因
也就是PL/SQL lock timer正在等待的原因
至此就找到了Library Cache Pin的原因.
简化一下以上查询:
1.获得Library Cache Pin等待的对象
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
ADDR
KGLHDADR KGLHDPAR KGLNAOWN
KGLNAOBJ
KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------
404F2178 52D6730C 52D6730C SYS
PINING
2300250318 52D65BA4
-------- -------- -------- ---------- -------------------- ---------
404F2178 52D6730C 52D6730C SYS
2.获得持有等待对象的session信息
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%')
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
---------- ---------- ----------------------------------------------13 SYS
3.获得持有对象用户执行的代码
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%')))
SELECT sql_text
SQL_TEXT
---------------------------------------------------------------------
BEGIN calling; END;
---------------------------------------------------------------------
BEGIN calling; END;
实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的:
1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的
exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.
2.以shared模式pin该对象,以执行安全和错误检查.
3.共享pin被释放,重新以排他模式pin该对象,执行重编译.
4.使所有依赖该过程的对象失效
5.释放exclusive lock和exclusive pin
1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的
exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.
2.以shared模式pin该对象,以执行安全和错误检查.
3.共享pin被释放,重新以排他模式pin该对象,执行重编译.
4.使所有依赖该过程的对象失效
5.释放exclusive lock和exclusive pin
(二).LIBRARY CACHE LOCK等待事件
如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:
session 3:
SQL> alter procedure pining compile;
此进程挂起,我们查询v$session_wait视图可以获得以下信息:
SQL> select * from v$session_wait;
---- ---- ------------------- --------------- ---------- --------
....
13 rows selected
由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.
我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现.
所以应该尽量避免在高峰期进行以上操作.
所以应该尽量避免在高峰期进行以上操作.
另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现.
所以在应用开发的过程中,我们也应该注意这方面的内容.
所以在应用开发的过程中,我们也应该注意这方面的内容.
文章引用自:http://blog.chinaunix.net/u/12960/showart.php?id=100227