library cache pin,library cache lock和library cache load lock

具体见METALINK NOTE：444560.1

[@more@]

在library cache中，对象以handle和object heap的方式存储
1、library cache lock
通过在handle上加锁的方式控制进程对对象的并行访问，主要是对对象的PARSE和编译，该锁是死锁检测敏感的
Parameters:
handle address
Address of the object being loaded.
lock address
Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.
mode
Indicates the data pieces of the object which need to be loaded.
2、library cache pin
该事件是用来管理library cache的并行访问，pin一个对象意味着将object heap调入内存中。当需要修改一个对象的定义的时候，需要先获取library cache lock，然后获取library cache pin，该锁对死锁检测是不敏感的
Parameters:
handle address
Address of the object being loaded.
pin address
Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is basically a State Object.
mode
Indicates which data pieces of the object that needs to be loaded.
namespace
See "namespace".
3、library cache load lock
当需要将对象LOAD到内存中去时，需要等待library cache load lock事件
Parameters:
object address
Address of the object being loaded.
lock address
Address of load lock being used
4、为什么需要library cache lock和library cache pin这两种锁
oracle需要在更改对象的定义的时候获取这两种锁以避免其它会话使用这些对象
当SQL被硬解析的时候，会话也必须获取library cache lock以避免其它会话访问或者更改对象
5、如何减少library cache lock等待
a.可能需要调整shared pool的大小
b.可能需要更多游标共享
c.可能需要减少对象invalidation的次数
6、如何减少library cache pin等待
减少对对象定义的修改
7、如何减少library cache load lock的等待
a.增大shared pool
b.增加session cached cursors
c.减少硬解析
8、如何查找被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(10g前)或2048(10g)表示这个sql正在运行﹐
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相关信息

SQL> SELECT distinct decode(kglpnreq,0,'holding_session: '||s.sid,'waiting_session: '||s.sid) sid,
2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
3 FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
4 WHERE p.kglpnuse=s.saddr
5 AND kglpnhdl=sw.p1raw
6 and kglhdadr=sw.p1raw
7 and event like 'library cache%'
8 and (a.hash_value, a.address) IN (
9 select
10 DECODE (sql_hash_value,
11 0,
12 prev_hash_value,
13 sql_hash_value
14 ),
15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
16 from v$session s2
17 where s2.sid=s.sid
18 )
19 ;

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)
;
为了避免这种情况，可以在编译过程或函数等对象时，先查看一下是否有会话正在使用该对象，查询语句如下：

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

library cache pin
object:
select session_id sid,lock_type type,lock_id1 object_name
from dba_lock_internal
where mode_requested<>'None'
and mode_requested<>mode_held
and session_id in (select sid from v$session_wait where wait_time=0
and event like 'library cache lock%');

pin holder:
select sid holder,kglpnmod held,kglpnreq req
from x$kglpn,v$session
where kglpnhdl in (select p1raw from v$session_wait where wait_time=0
and event like 'library cache lock%')
and kglpnmod<>0
and v$session.saddr=x$kglpn.kglpnuse;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/85922/viewspace-1021957/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/85922/viewspace-1021957/