Oracle数据库性能优化_library cache pin、library cache lock等待事件介绍

数据库出现library cache pin/lock等待事件，导致数据库运行缓慢，下面我们来一起对这两个等待事件library cache pin、library cache lock进行测试。

library cache pin、library cache lock事件介绍

引用eygle地描述：
Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.
Lock比pin具有更高的级别.
Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.
在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.
通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待.
根据晶晶小妹的描述：
Library cache lock与Library cache pin的模式。
所有在Library cache中的对象，都由两部分组成，一个句柄、至少一个子堆。这一点可以参考上面的图。
句柄中记录的有对象的名字、命名空间、Lock的持有者和等待者、Pin的持有者和等待者、一些标志信息，最重要的，句柄中记有堆的地址。
在Library cache中寻找对象时，先计算HASH值，在HASH表中找到句柄，再经由句柄，找到对象实际的内存地址。在这个过程中，有两个重要的数据项需要被锁保护起来。一个是对象句柄、另一个就是对象的内存堆。在对象句柄上加的锁就是Library cache lock，在内存堆上加的锁，就是Library cache pin。
下面先讨论一下Library cache lock。
（1） Library cache lock
Locks 除了阻止不相容的对句柄的访问，以保护句柄中数据的完整性外，获得Locks也是在缓存中定位对象的唯一方式，即：进程在对句柄上加锁的同时，完成在内存中定位堆的操作。
在句柄上获得Lock、并在内存中定位到堆后，对象可以Pin自己的堆。如果对象相关信息不在内存中，Pinning一个对象将导致它和它的的子堆被装载（此种情况时，如果是多个对象Pin一个对象，将可能会造成Pin等待）
Lock 有三种模式
" Share(s) : 读对象
" Exclusive(x) : 修改或创建对象
" Null(n) : 专用于为会话持续
注意：存贮对象可以被锁在以上任意一种方式，瞬时对象只能被锁在Null方式。
Null 锁在执行SQL声明的解析阶段被获得，此后一直持有。它不阻止任何DDL。也用术语“易碎解析锁”称乎它（breakable parse lock）。
在以下两种情况下Null锁被打碎：
" 当锁所在对象有一个独占Pin时
" 锁所在对象的任何依赖对象有一个独占Pin时
（2） Library cache pin：
Pin有两种模式：
" Share (s) : 读一个对象堆
" Exclusive (x) : 修改一个对象堆
无论存贮对象还是瞬时对象，都能被Pinned在Share或Exclusive模式。当修改对象时，进程首先会以Share模式Pin对象，进行错误和安全检查，然后在以Exclusive模式Pin住对象。Pin的解除将会导致相关对象上的易碎锁Break
不同类型的操作所需要的不同类型的lock/pin：
1).所有的DDL操作都会在需要处理的对象上放一个Exclusive(排他)类型的Lock和Pin(仅仅当执行的时候加上)。
如:重编译,截断表,给对象授权,等等
2).所有对对象的使用都需要一个null类型lock和shared类型的pin(仅仅当执行的时候加上)。如:使用视图,执行过程,等等。
以上规则，也同样应用于对象所有依赖的对象。如:一个依赖于其他视图的视图,一个依赖于其他包的包。
获取awr的report,我们可能看到以下等待事件:

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
library cache lock 75,884 1,409,500 48.44
latch free 34,297,906 1,205,636 41.43
library cache pin 563 142,491 4.90
db file scattered read 146,283 75,871 2.61
enqueue 2,211 13,003 .45
-------------------------------------------------------------

这里的library cache lock和library cache pin都是我们关心的.接下来我们就研究一下这几个等待事件.
(一).LIBRARY CACHE PIN等待事件
Oracle文档上这样介绍这个等待事件:
library cache pin
This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.

"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"通常是发生在编译或重新编译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.创建测试用存储过程

[oracle@fykj udump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 10 21:12:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 27256168 bytes
Fixed Size 251176 bytes
Variable Size 19360128 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
SQL>
SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(3000);
6 end;
7 /
Procedure created.
2.模拟
首先执行calling过程,在calling过程中调用pining过程
此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待
直到calling执行完毕.
session 1:

[oracle@fykj oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 10 21:15:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> exec calling
此时calling开始执行
session 2:

[oracle@fykj udump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 10 21:16:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
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
等待3秒就超时,seq#会发生变化
SQL>
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
SQL>
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

在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示
我们看到,library cache pin等待的对象的handle地址为:52D6730C
通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:
Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='52D6730C'
/
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
404F9FF0 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

这里KGLNAHSH代表该对象的Hash Value
由此我们知道,在PINING对象上正经历library cache pin的等待.
然后我们引入另外一个内部视图X$KGLPN:
Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object i[N]s

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
/
SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
----- ----------- ---------------------------------------- -------- -------- -------- -------- -------- -------- ---------- ----------
13 SYS sqlplus@fykj mailto:sqlplus@fykj]mailto:sqlplus@fykj (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0

通过联合v$session,可以获得当前持有该handle的用户信息.
对于我们的测试sid=13的用户正持有该handle
那么这个用户正在等什么呢?

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

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;

这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.
我们这个calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因
至此就找到了Library Cache Pin的原因.简化一下以上查询:
1.获得Library Cache Pin等待的对象
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
-------- -------- -------- ---------- -------------------- ---------- --------
404F2178 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

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%')
/
SQL>
SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---------- ---------- ------------------------------------------------ -------- -------- -------- -------- -------- -------- ---------- ----------
13 SYS sqlplus@fykj mailto:sqlplus@fykj]mailto:sqlplus@fykj (TNS V1-V3) 404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0

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%')))
/
SQL_TEXT
--------------------------------------------------------------------------------
BEGIN calling; END;

在grant之前和之后我们可以转储一下shared pool的内容观察比较一下:

[color=#ff9999]SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';
Session altered.

在grant之前:
从前面的查询获得pining的Handle是52D6730C:
******************************************************
BUCKET 67790:
LIBRARY OBJECT HANDLE: handle=52d6730c
name=SYS.PINING
hash=891b08ce timestamp=04-10-2012 21:43:51
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1
--在Object上存在共享pin
--在handle上存在Null模式锁定,此模式允许其他用户继续以Null/shared模式锁定该对象
lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARY OBJECT: bject=52d65ba4
type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change alloc(K) size(K)
----- -------- -------- ------ ---- ------ -------- --------
0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55
4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48

在发出grant命令后:
******************************************************
BUCKET 67790:
LIBRARY OBJECT HANDLE: handle=52d6730c
name=SYS.PINING
hash=891b08ce timestamp=04-10-2012 21:43:51
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1
--由于calling执行未完成,在object上仍让保持共享pin
--由于grant会导致重新编译该对象,所以在handle上的排他锁已经被持有
--进一步的需要获得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出现.
lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARY OBJECT: bject=52d65ba4
type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change alloc(K) size(K)
----- -------- -------- ------ ---- ------ -------- --------
0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55
4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48

实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的:
1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的
exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.
2.以shared模式pin该对象,以执行安全和错误检查.
3.共享pin被释放,重新以排他模式pin该对象,执行重编译.
4.使所有依赖该过程的对象失效
5.释放exclusive lock和exclusive pin
(二).LIBRARY CACHE LOCK等待事件
library cache lock
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
■ One client can prevent other clients from accessing the same object
■ The client can maintain a dependency for a long time which does not allow another client to change the object
This lock is also obtained to locate an object in the library cache.
如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:
session 3:

[oracle@fykj oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 10 21:52:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> alter procedure pining compile;

此进程挂起,我们查询v$session_wait视图可以获得以下信息:
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
....
13 rows selected

由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.
我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现.
所以应该尽量避免在高峰期进行以上操作.
另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现，所以在应用开发的过程中,我们也应该注意这方面的内容