本文深入探讨Oracle数据库中的Library Cache并发控制机制，详细分析Library Cache Pin等待事件的成因及其解决方法，并通过实例演示如何定位和解决Library Cache Pin等待问题。
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被持有,那么数据库此时就要产生等待.
在很多statspack的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" 是用来管理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@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 14:16:57 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 47256168 bytes Fixed Size 451176 bytes Variable Size 29360128 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. SQL>
|
2.模拟
首先执行calling过程,在calling过程中调用pining过程。此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待，直到calling执行完毕.
session 1:
[oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:13:43 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: SQL> exec calling
|
此时calling开始执行
session 2:
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:14:16 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: 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 [P]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@jumper.hurray.com.cn (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@jumper.hurray.com.cn (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的内容观察比较一下:
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=09-06-2004 16: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: object=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=09-06-2004 16: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: object=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是如何交替发挥作用的:
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9417382/viewspace-937432/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9417382/viewspace-937432/
扫一扫
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
