全部博文（27）
分类： Oracle
2011-01-06 23:33:06
一、概述
一个实例中的library cache包括了不同类型对象的描述,如:游标,索引,表,视图,过程,等等.
这些对象不能在他们被使用的时候改变,他们在被使用的时候会被一种library locks and pins的机制锁住.
一个会话中,需要使用一个对象,会在该对象上先得到一个library lock(null, shared or exclusive模式的)
这是为了,防止其他会话也访问这个对象(例如:重编译一个包或视图的时候,会加上exclusive类型的锁)或更改对象的定义.
总的来说，library cache pin和library cache lock都是用于share pool的并发控制的。pin和lock都可以看作是一种锁。
locks/pins会在SQL语句执行期间一直保持,在结束的时候才释放。
每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.
超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。
二、library cache pin和library cache lock成因
lock主要有三种模式: Null,share(2),Exclusive(3).
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.
同样pin有三种模式,Null,shared(2)和exclusive(3).
只读模式时获得共享pin,修改模式获得排他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 pin等待，不一定会存在library cache lock等待；
但如果是表引起的，则一般只有library cache lock等待，则不一定存在library cache pin。
可能发生library cache pin和library cache lock的情况：
1、在存储过程或者函数正在运行时被编译。
2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。
3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。
4、PL/SQL对象之间存在复杂的依赖性
每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.
超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。
例如：
SES1:
执行：exec p_sleep;
假设存储过程p正在运行，且运行时间很长
SES2:
执行：grant execute on p_sleep to system
对p进行编译，如果之前没有其他会话lock存储过程p的handle，则本会话会将获取p的handle锁定；但会话pin p时会失败，此时在SES2上产生library cache pin等待。如果超过5分钟仍然不能完成pin p，则会报错：
ORA-04021: 等待锁定对象 SUK.P_SLEEP 时发生超时。此时，本会话会释放p的handle lock。(也可能是ORA-04020错误)
SES3:
执行：grant execute on p_sleep to system
在这个会话中继续编译p，则该会话在获取p的handle锁定时会失败，在本会话产生library cache lock等待。如果SES2超时，则本会话会获取p的handle lock，v$session_wait上的等待事件也由library cache lock变成ibrary cache pin，直到超时。
library cache pin
查询v$session_wait视图中library cache pin对应的P1、P2、P3
P1 = Handle address
这个就是引起library cache pin等待的对象被pin到library cache中的handle。一般用P1RAW(十六进制)代替p1(十进制)
可以用以下sql查询那个用户下的那个对象正在被请求pin：
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW';
返回的OBJECT可能是具体的对象，也可能是一段SQL。
P2 = Pin address
自身的pin地址。一般用P2RAW(十六进制)代替P2(十进制)
P3 = Encoded Mode & Namespace
在library cache pin和library cache lock(一)我们简单介绍了介绍library cache pin和library cache lock的成因，下面介绍如何解决library cache pin和library cache lock等待。
三、解决方法
有两种方法可以查询library cache pin的相关信息，推荐使用第二种。
使用这种方法前，有必要先了解以下表或视图：x$kglob、x$kgllk、x$kglpn、DBA_KGLLOCK
1) x$kglob
该基表主要是library cache object的相关信息。
X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
引用该基表的视图有﹕GV$ACCESS、GV$OBJECT_DEPENDENCY、GV$DB_OBJECT_CACHE、GV$DB_PIPES、DBA_LOCK_INTERNAL﹑DBA_DDL_LOCKS
2) x$kgllk
该基表保存了library cache中对象的锁信息，主要用于解决library cache lock。
其名称含义是：
[K]ernel Layer
[G]eneric Layer
[L]ibrary Cache Manager ( defined and mapped from kqlf )
Object Locks
X$KGLLK - Object [L]oc[K]s
引用该基表的视图有﹕
DBA_DDL_LOCKS ﹑DBA_KGLLOCK ﹑GV$ACCESS ﹑GV$OPEN_CURSOR
SQL> desc x$kgllk;
名称 类型
---------- -----------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(4)
KGLLKUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLLKSES RAW(4) ---owner地址
KGLLKSNM NUMBER ---SID
KGLLKHDL RAW(4) ---句柄
KGLLKPNC RAW(4) ---the address of the call pin
KGLLKPNS RAW(4) ---对应跟踪文件中的session pin值
KGLLKCNT NUMBER
KGLLKMOD NUMBER ---持有锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKREQ NUMBER ---请求锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKFLG NUMBER ---cursor的状态﹐8(
KGLLKSPN NUMBER ---对应跟踪文件的savepoint的值
KGLLKHTB RAW(4)
KGLNAHSH NUMBER ---sql的hash值(对应v$session的sql_hash_value)
KGLLKSQLID VARCHAR2(13) ---sql ID，sql标识符
KGLHDPAR RAW(4) ---sql地址(对应v$session的sql_address)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30) ---会话的用戶名
KGLNAOBJ VARCHAR2(60) ---对象名称或者已分析并打开cursor的sql的前60个字符
3) x$kglpn
X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
它是与x$kgllk相对应的表﹐是关于pin的相关信息。它主要用于解决library cache pin
引用该表的视图有﹕
DBA_KGLLOCK
SQL> desc x$kglpn;
名称 类型
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---句柄
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---对应跟踪文件的savepoint的值
4) DBA_KGLLOCK
DBA_KGLLOCK是一个视图，它联合了x$kgllk和x$kglpn的部分信息。
通过查询，我们可以知道DBA_KGLLOCK视图的构建语句：
SQL> SELECT * FROM DBA_VIEWS WHERE VIEW_NAME='DBA_KGLLOCK';
select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk
union all
select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype from x$kglpn
了解了用到的几个主要视图或表的结构，我们就可以写出编写查询来查看相关信息：
方法一、只能查询library cache pin相关信息
SELECT distinct decode(kglpnreq,
0,
'holding_session: ' || s.sid,
'waiting_session: ' || s.sid) sid,
s.SERIAL#,
kglpnmod "Pin Mode",
kglpnreq "Req Pin",
a.sql_text,
kglnaown "Owner",
kglnaobj "Object"
FROM x$kglpn p, v$session s, v$sqlarea a, v$session_wait sw, x$kglob x
WHERE p.kglpnuse = s.saddr
AND kglpnhdl = sw.p1raw
and kglhdadr = sw.p1raw
and event like 'library cache%'
and (a.hash_value, a.address) IN
(select DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session s2
where s2.sid = s.sid);
SID SERIAL# Pin Mode Req Pin SQL_TEXT Owner Object
-------------------- ---------- ---------- ---------- ---------------------------------------- ------------ --------------------
blocked_sid: 16 195 0 3 grant execute on p_s SUK P_SLEEP
blocker_sid: 20 15 2 0 begin p_sleep; end; SUK P_SLEEP
得到这个结果后，你可以根据实际情况kill掉阻塞的会话或者被阻塞的会话。
方法二、可以查询library cache pin和library cache lock 的信息
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,
xh.KGLNAOBJ hold_sql
from dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1,
v$object_dependency od,
V$DB_OBJECT_CACHE oc,
x$kgllk xw,
x$kgllk xh
where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
(((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name = oc.Name
And od.to_owner = oc.owner
And w1.sid = xw.KGLLKSNM
And h1.sid = xh.KGLLKSNM
And (w1.SQL_ADDRESS = xw.KGLHDPAR And w1.SQL_HASH_VALUE = xw.KGLNAHSH)
And (h1.SQL_ADDRESS = xh.KGLHDPAR And h1.SQL_HASH_VALUE = xh.KGLNAHSH);
WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL
--------------- --------------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- --------- -------------- ------------------------------------------------------------ ------------------------------------------------------------
18 19 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleep to system grant execute on p_sleep to system
19 12 Pin SUK P_SLEEP PROCEDURE Share Exclusive grant execute on p_sleep to system begin p_sleep; end;
23 25 Lock SUK P_SLEEP2 PROCEDURE Exclusive Exclusive grant execute on p_sleep2 to system grant execute on p_sleep2 to system
25 14 Pin SUK P_SLEEP2 PROCEDURE Share Exclusive grant execute on p_sleep2 to system begin p_sleep2; end;
为了避免这种情况，可以在编译过程或函数等对象时，先查看一下是否有会话正在使用该对象，查询语句如下：
SELECT Distinct sid using_sid,
s.SERIAL#,
kglpnmod "Pin Mode",
kglpnreq "Req Pin",
kglnaown "Owner",
kglnaobj "using_Object"
FROM x$kglpn p, v$session s, x$kglob x
WHERE p.kglpnuse = s.saddr
AND kglpnhdl = kglhdadr
And p.KGLPNUSE = s.saddr
And kglpnreq = 0
And upper(kglnaobj) = upper('&obj');
如果有结果返回，则等待这些对话的操作执行完毕再重新编译，也可以把这些会话kill。
四、其他解决方法
上面主要是用到SQL查询相关信息的解决方法，也可以使用10046、trace等方法实现。至于用那种方法好，见仁见智。
诊断和解决 "Library Cache Pin" Waits一例
Oracle uses library cache pins to manage library cache concurrency. This tip outlines a method to deal with "library cache pin" wait events that are blocking other users
The first step is to see who is waiting for Library Cache Pins:
SQL>
SQL>
SQL> tti "Users Waiting for Library Cache Pins"
SQL> SELECT sid, event, p1raw, seconds_in_wait, wait_time
2 FROM sys.v_$session_wait
3 WHERE event = 'library cache pin'
4 AND state = 'WAITING'
5 /
Wed Aug 11 page 1
Users Waiting for Library Cache Pins
SID EVENT P1RAW SECONDS_IN_WAIT
---------- -------------------- ---------------- ---------------
374 library cache pin 000000051862E
944 library cache pin 000000051862E
1057 library cache pin 000000051862E
776 library cache pin 000000051862E
4 rows selected.
P1raw is the "Handle Address" of the object that is blocking. Execute the following query to get the object's owner and name:
SQL> tti "Object that is Blocking"
SQL>
SQL>
SQL> SELECT kglnaown AS owner, kglnaobj as Object
2 FROM sys.x$kglob
3 WHERE kglhdadr='&P1RAW'
4 /
Enter value for p1raw: 000000051862E
old 3: WHERE kglhdadr='&P1RAW'
new 3: WHERE kglhdadr='000000051862E
Wed Aug 11 page 1
Object that is Blocking
OWNER OBJECT
-------- -------------------------------------------------- --------------------
begin SP_EMP.PROC1@orcl(:a,:b,:c); end;
Identify the users that are waiting/ blocking:
SQL> tti "Blocking/Waiting Users"
SQL> col SID_SERIAL format a12
SQL> SELECT s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
2 FROM sys.x$kglpn p, sys.v_$session s
3 WHERE p.kglpnuse = s.saddr
4 AND kglpnhdl = '&P1RAW'
5 /
Enter value for p1raw: 000000051862E
old 4: AND kglpnhdl = '&P1RAW'
new 4: AND kglpnhdl = '000000051862E
Wed Aug 11 page 1
Blocking/Waiting Users
SID_SERIAL Mode Held Request
------------ ---------- ----------
374,1390 0 2
776,2906 0 2
944,2193 0 2
991,59496 3 0
1057,1966 0 2
5 rows selected.
In the above example, session 991 is blocking the other sessions listed. Killing session 911 should solve the problem. However, before killing the session, you may want to collect evidence of the problem so you can log a TAR. To do so, collect 3 SYSTEMSTATE dumps at 30 seconds intervals, then submit them to Oracle support for further analysis.
