分析解决因”library cache pin”等待
===========================================================
作者: tolywang(http://tolywang.itpub.net)
发表于:2006.04.20 14:45
分类: Oracle数据库管理
出处：http://tolywang.itpub.net/post/48/70906
---------------------------------------------------------------
概述
造成数据库性能下降或挂起的原因很多,”library cache pin”等待是其中之一.当数据库性能严重下降或挂起时,我们通过查询v$session_wait,发现大量的”library cache pin”等待,查询的SQL语句如下:
#su - oracle
$svrmgrl
svrmgr>connect internal
svrmgr>select sid,event,p1,p2,p3 from v$session_wait where wait_time=0;
SID EVENT P1 P2 P3
---------- ------------------------- ---------- ---------- --------------
9 library cache pin 15417016 10090832 20
154 library cache pin 15417016 11224168 20
341 library cache pin 15417016 11449936 20
349 library cache pin 15417016 16489792 20
390 library cache pin 15417016 11992536 20
160 library cache pin 15417016 6166600 20
20 library cache pin 15417016 10868760 20
因此,我们断定数据库性能下降或挂起是由”library cache pin”引起的. 这种情况通常发生在大量使用数据库存储PL/SQL块的并发应用程序中,而在测试环境中则很难再现.
LIBRARY CACHE PIN等待事件
那么什么是”library cache pin”等待呢? “library cache pin” 事件是用来管理library cache的并发访问的, pin一个object会引起相应的heap被载入内存中,如果客户端需要修改或检测这个object它就必须在锁住后取得一个pin.
”library cache pin”的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.
“library cache pin”的参数如下,有用的主要是P1和P2:
P1 - KGL Handle address.
P2 - Pin address
P3 - 10*Mode + Namespace
其中,P1,P2可与x$kglpn和x$kglob表相关.x$kglpn和x$kglob是ORACLE数据库的内部数据字典..
等待一个pin意味着另有一个阻塞者(blocker),它处于更高级别或不兼容状态,我
通过以下matrix来判断是否兼容,”X”表示不兼容:
Blocker
Waiter
KGLM0 KGLMN KGLMS KGLMX
KGLM0 - X X X
KGLMN - - X X
KGLMS - - - X
KGLMX - ? X X
其中: KGLM0,KGLMN,KGLMS和KGLMX是X$KGLPN和X$KGLOB中的KGLHDLMD字段的几种状态.
KGLM0 0 no lock/pin held
KGLMN 1 null mode
KGLMS 2 share mode
KGLMX 3 exclusive mode
常见的原因及解决方法
“LIBRARY CACHE PIN”通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,如安装应用程序,升级,安装补丁程序等,但object的重新编译也可能发生在object变得无效时.
我们在处理因”LIBRARY CACHE PIN”引起的性能变慢或挂起时,应检查object无效方面的原因.当我们对object进行维护,如”ALTER”,”GRANT”,”REVOKE”时,就会使object变得无效, 通过object的”LAST_DDL”属性可查看到这些变化.
当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.这种情况我们可以通过library cache dump level 10,查找”ALTER …COMPILE” sql 语句和带有”lock=X” 或”pin=X”的object或handles得知.在某些时候,可能会报错,如”ORA-600 [17285]” “ORA-4061” “ORA-4065” “ORA-6508”等.
综上所述,我们在对PL/SQL存储过程中经常引用到的object进行修改,授权,收回授权时必须非常小心.实际上,解决这些问题大多要依靠应用程序的开发和维护,应用程序开发商应该考虑到某些方案的决策可能会给应用程序的伸缩性和性能带来负面影响.
以下列出几种可能产生”library cache pin”的情况及其避免方法:
1. 用户权限管理
当对用户的权限进行管理即进行”grant” “revoke”时,可能产生”library cache pin”.
建议的避免方法: 通过角色来对最终用户进行授权或收回授权,而不要用显性的方式即直接对最终用户授权或收回授权,从而避免产生”library
cache pin”
2. 高峰时的object管理
在系统运行高峰时对数据库object的管理可能产生”library cache pin”.
建议的避免方法: 把对数据库object的管理安排到负载相对较小的时侯.
3. 在PL/SQL包中存在大量的互跨的依赖性(dependency)
建议的避免方法: 尽可能按等级来排列它们的结构.
详细分析步骤
下面给出两种方法来分析”library cache pin”,目的是找出哪些session在等待资源,哪些session 正占着这些资源,而占着资源的这些session又在做什么,从而找到问题的根源并加以解决.
方法1.
(1). 通过查询V$SESSION_WAIT找出正在等待”library cache pin”的session,其SQL语句如下:
sql>select sid,substr(event,1,30),p1,p2,p3 from v$session_wait
where wait_time=0 and event like 'library cache pin%';
SID SUBSTR(EVENT,1,30) P1 P2 P3
----------------------------------------------------------------------------------------------
9 library cache pin 15417016 10090832 20
154 library cache pin 15417016 11224168 20
341 library cache pin 15417016 11449936 20
349 library cache pin 15417016 16489792 20
390 library cache pin 15417016 11992536 20
160 library cache pin 15417016 6166600 20
20 library cache pin 15417016 10868760 20
其中:
P1 列是Library Cache Handle Address
P2 列是Library Cache Pin Address.
(2). 把P1的值转换为十六进制,即15417016 --> EB3EB8,然后查询X$KGLPN表
(Library Cache Object Pin),可找到相关session,其SQL语句如下 (即把V$SESSION_WAIT中的P1与X$KGLPN中的KGLPNHDL相关连):
sql>select ADDR , INDX , KGLPNADR,KGLPNUSE,KGLPNSES,KGLPNHDL,
kGLPNLCK, KGLPNMOD, KGLPNREQ from x$kglpn where KGLPNHDL like '%EB3EB8%';
ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ
----------------------- ---------- -------- -------- -------- -------- -------- ------ -------
05B0CB30 1 00FB9D40 0011B830 0011B830 00EB3EB8 003ECD10 0 2
05B0CB88 2 00AB4468 000E56E0 000E56E0 00EB3EB8 004473A0 0 2
05B0CBE0 3 005E1848 000E7180 000E7180 00EB3EB8 00A30F60 0 2
05B0CC38 4 00B6FDD8 00126E20 00126E20 00EB3EB8 00DD6738 0 2
05B0CC90 5 0099F950 000BD370 000BD370 00EB3EB8 00E07F78 0 2
05B0CCE8 6 00AEB650 001194B0 001194B0 00EB3EB8 00DB6BC8 0 2
05B0CD40 7 00A5D818 000C0440 000C0440 00EB3EB8 0043A1A8 0 2
05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0
其中:
KGLPNHDL --- Library Cache Handle Address
KGLPNADR --- Library Cache Pin Address.
KGLPNSES --- 识别锁住此pin 的session
KGLPNREQ --- Pin 请求
KGNMODE --- Pin 锁
从以上查询结果可以看到,有一个session正占着pin锁(KGNMOD=3),而其它session正等待此pin锁(KGLPNREQ=2):
ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ
-----------------------------------------------------------------------------------------------------------------------------------
05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0
(3). 查询X$KGLOB (Library Cache Object),可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1与X$KGLOB中的KGLHDADR相关连):
sql> select * from X$KGLOB where KGLHDADR like '%EB3EB8%';
ADDR INDX KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNADLK KGLNAHSH KGLNATIM KGLNAPTM
KGLHDNSP KGLHDLMD KGLHDPMD KGLHDFLG KGLHDOBJ KGLHDLDC KGLHDIVC KGLHDEXC KGLHDLKC
KGLHDKMK KGLHDDMK K GLHDAMK KGLOBFLG KGLOBSTA KGLOBTYP KGLOBCNU KGLOBHS0 KGLOBHS1 KGLO
BHS2 KGLOBHS3 KGLOBHS4 KGLOBHS5 KGLOBHS6 KGLOBHS7 KGLOBPC0 KGLOBPC6 KGLOBMSZ KGLOBPSZ KGLOBPUS KGLOBCXF KGLOBSOR KGLOBPRS KGLOBDSK KGLOBBUF KGLOBUID KGLOBSID KGLOBOCT
05B0A230 30 00EB3EB 8 00EB3EB8
select count(*) from fa_facturas@decmovi where fa_cuenta -> KGLNAOBJ
= :1
1569934176 22-ENE-97 0 1 3 1342242
816 00EB3C30 2 0 67014 9 0 1
0 1 1 0 65535 1470 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 -4 -4 3
其中:
KGLNAOBJ ---- 相关object的名字(取前面80个字符)
这样,即可查出”library cache pin”是针对哪个object的(此处为”fa_facturas”).
(4). 把V$SESSION的SADDRL列与X$KGLPN的 KGLPNUSE 列相关连,并查询V$SESSION_WAIT,即可查出占着pin锁的session目前正在做什么,其SQL语句如下:
sql>select a.sid,a.username,a.program from v$session a,x$kglpn b where
a.saddr=b.kglpnuse and b.kglpnhdl like '%EB3EB8%' and b.kgnmod<>0;
sql>select event from v$session_wait where sid=<sid>;
通过以上查询,可看到此session正等待”Client Message”,即此用户可能已离开客户终端,这样通过把此session杀掉即可解决问题.
方法2
(1). 通过查询V$SESSION_WAIT找到正等待”library cache pin”的session(即等待者),其SQL语句如下:
sql>select sid Waiter,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like 'library cache pin%';
(2). 通过查询DBA_LOCK_INTERNAL和V$SESSION_WAIT,可得到与”library cache pin” 等待相关的object的名字,其SQL语句如下:
sql>select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
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 pin%') ;
(3). 查出”library cache pin”占有者(即阻塞者)的session id,其SQL语句如下:
sql>select sid Holder ,KGLPNUSE Sesion , 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 pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse ;
(4). 查出”library cache pin”占有者(阻塞者)正在等什么?
sql>select sid,substr(event,1,30),wait_time
from v$session_wait
where sid in (select sid from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse )
;
(5). 查出阻塞者正执行的SQL语句:
sql>select sid,sql_text
from v$session, v$sqlarea
where v$session.sql_address=v$sqlarea.address
and sid=<阻塞者的sid> ;
这样,就可找到”library cache pin”等待的根源,从而解决由此引起的性能问题
另外,也可通过 system dump来分析”library cache pin”等待举例如下:
系统状态:
9 : waiting for 'library cache pin' [PIN: handle=EB3EB8]
154: waiting for 'library cache pin' [PIN: handle=EB3EB8]
341: waiting for 'library cache pin' [PIN: handle=EB3EB8]
341: waiting for 'library cache pin' [PIN: handle=EB3EB8]
390: waiting for 'library cache pin' [PIN: handle=EB3EB8]
160: waiting for 'library cache pin' [PIN: handle=EB3EB8]
20 : waiting for 'library cache pin' [PIN: handle=EB3EB8]
阻塞者(Blockers):
~~~~~~~~--------
PIN: handle=EB3EB8 263: last wait for 'client message'
Object 名字:
~~~~~~~~~~~~
PIN: handle=EB3EB8 CRSR:select count(*) from fa_facturas@decmovi
where fa_cuenta
= :1
其他解决方法
当重新编译某个package时,应确认当前没有session在使用此package中的object. 当我们重新编译某个package时,如果有session正在执行此package中的procedure或function,那么编译就会挂起. 因为编译或分析(parse)package或procedure或function或view时,Oracle需要先取得”library cache lock”和”library cache pin”以保证在编译或分析(parse)期间没有session正使用此object – 因为我们正修改此object的定义并需要删除后用新的定义来重建此object.
我们可以用以下procedure来检查是否有session正在使用某个package,如果此procedure有结果返回,则此时不能编译此package,
sql> Connect SYS
password:
sql> set serveroutput on
sql>create or replace procedure who_is_using(obj_name varchar2) is
begin
dbms_output.enable(1000000);
for i in (SELECT distinct b.username,b.sid
FROM SYS.x$kglpn a,v$session b,SYS.x$kglob c
WHERE a.KGLPNUSE = b.saddr
and upper(c.KGLNAOBJ) like upper(OBJ_NAME)
and a.KGLPNHDL = c.KGLHDADR) loop
dbms_output.put_line('('||to_char(i.sid)||') - '||i.username);
end loop;
end;
sql>execute who_is_using('my_package%');
(14) – SCOTT #即scott用户正在执行 my_package
以下查询可得到正使用此package的session是否正占着”library cache pin”锁或正等待”library cache pin”锁:
sql>SELECT a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ,
c.KGLOBTYP
FROM
x$kglpn a,
v$session b,
x$kglob c
WHERE
a .KGLPNUSE = b.saddr and
upper(c.KGLNAOBJ) like upper('%my_package%') and
a.KGLPNHDL = c.KGLHDADR;
与LIBRARY CACHE PIN相关的参数
在OPS 8.1.5, 8.1.6和8.1.7.0中,一定要在initsid.ora文件中设置
_SQLEXEC_PROGRESSION_COST=0以避免因session等待”library cache pin”而造成的数据库性能严重下降或数据库挂起.
结束语
以上介绍了”library cache pin”的定义,产生原因及对数据库性能的影响,给出了详
细的分析步骤和解决方法,本文适合较有经验的数据库管理员阅读.
本文中给出的SQL语句请先测试后才能使用.
发表于:2007.05.14 21:53
分类: oracle 管理
出处：http://m77m78.itpub.net/post/125/287710
---------------------------------------------------------------
一、概述
一个实例中的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语句执行期间一直保持,在结束的时候才释放。
一、概述
一个实例中的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请求。
不同的操作会对对象请求不同的lock/pin
1、所有的DDL都会对被处理的对象请求排他类型的lock和pin
2、
当要对一个过程或者函数进行编译时，需要在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(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等方法实现。至于用那种方法好，见仁见智。
1803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
