dba_kgllock,x$kglob,v$session

转自：

http://space6212.itpub.net/post/12157/220675

LIBRARY CACHE PIN和LIBRARY CACHE LOCK(二)

作者 space6212 13:35 |    静态链接网址 |    最新回复 (0) |    引用 (0) |  Oracle技术

在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(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) 
; 

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等方法实现。至于用那种方法好，见仁见智。

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

转载于:http://blog.itpub.net/280958/viewspace-705626/