Oracle library cache pin

Library cache pin |lock

--原理分析，

Library chace中包含了很多不同的对象，游标，表，视图，存储过程当对象在被使用时，不能被改变。被锁当一个会话需要使用该对象，会获得改对象的library lock(null, shared or exclusive)  Pin，当改对象被修改，这会话会视图重新编译，所有视图访问该对象的用户也会重新编译改对象（后续改进），产生了大量的library cache pin，library cache lock。

--错误

每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.

超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。

(see ) 

--成因

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请求。

--相关视图

DBA_KGLLOCK : one row for each lock or pin of the instance 

DBA_KGLLOCK是一个视图，它联合了x$kgllk和x$kglpn的部分信息。

-KGLLKUSE  session address 

-KGLLKHDL  Pin/lock handle 

-KGLLKMOD/KGLLKREQ  Holding/requested mode 

0           no lock/pin held 

1           null mode 

2           share mode 

3           exclusive mode 

-KGLLKTYPE Pin/lock 

(created via the $ORACLE_HOME/rdbms/admin/catblock.sql) 

V$ACCESS : one row for each object locked by any user 

-SID       session sid 

-OWNER     username 

-OBJECT    object name 

-TYPE      object type 

V$DB_OBJECT_CACHE : one row for each object in the library cache 

-OWNER         object owner 

-NAME          object name or cursor text 

-TYPE          object type 

-LOCKS         number of locks on this object 

-PINS          number of pins on this object 

DBA_DDL_LOCKS  : one row for each object that is locked (exception made of the cursors) 

SESSION_ID 

OWNER 

NAME 

TYPE 

MODE_HELD 

MODE_REQUESTED 

V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session 

-p1 = object address   | handle address

-p2 = lock/pin address 

-p3= Encoded Mode & Namespace 

-p1raw= 16进制

 Use P1RAW rather than P1                                                                                         

        This is the handle of the library cache object which the waiting session wants to acquire a pin on.   

x$kglob

该基表主要是library cache object的相关信息

x$kglpn

它是与x$kgllk相对应的表﹐是关于pin的相关信息。

它主要用于解决library cache pin

-----------------

 ADDR     RAW(8)   

 INDX     NUMBER 

 INST_ID  NUMBER 

 KGLPNADR RAW(8) 

 KGLPNUSE RAW(8) --会话地址,对应 v$session中的saddr

 KGLPNSES RAW(8) 

 KGLPNSID NUMBER 

 KGLPNHDL RAW(8) 

 KGLPNLCK RAW(8) 

 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) 

 KGLPNFLG NUMBER 

 KGLPNDMK NUMBER 

 KGLPNSPN NUMBER ---对应跟踪文件的savepoint的值

 KGLNAHSH NUMBER 

 

x$kgllk

该基表保存了library cache中对象的锁信息，

主要用于解决library cache lock。

引用该基表的视图有﹕

DBA_DDL_LOCKS ﹑DBA_KGLLOCK ﹑GV$ACCESS ﹑GV$OPEN_CURSOR 

----------------------------

ADDR        RAW(8)

INDX        NUMBER

INST_ID     NUMBER

KGLLKADR    RAW(8)

KGLLKUSE    RAW(8) --会话地址(对应v$session的saddr)

KGLLKSES    RAW(8) --owner地址

KGLLKSNM    NUMBER --SID

KGLLKHDL    RAW(8) --句柄

KGLLKPNC    RAW(8) --the address of the call pin

KGLLKPNS    RAW(8) --对应跟踪文件中的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的值

KGLNAHSH    NUMBER --sql的hash值(对应v$session的sql_hash_value)

KGLLKSQLID  VARCHAR2(13) --sql ID，sql标识符

KGLHDPAR    RAW(8) --sql地址(对应v$session的sql_address) 

KGLHDNSP    NUMBER

USER_NAME   VARCHAR2(30)

KGLNAOBJ    VARCHAR2(60)

KGLLKEST    DATE

KGLLKEXC    NUMBER

KGLLKCTP    VARCHAR2(64)

How to find why an ORA-4021 occurs

--------------------------------------------------------------------------------------------------------------------------------------MAIN LOCk

select /*+ ordered */

w1.sid  waiting_session

, h1.sid  holding_session

, w.kgllktype lock_or_pin

,  w.kgllkhdl address

,  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 

from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 

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 

/

  

--After Run

WAITING_SESSION HOLDING_SESSION lock ADDRESS  MODE_HELD MODE_REQU 

--------------- --------------- ---- -------- --------- --------- 

             16              12 Pin  03FA2270 Share     Exclusive  

-- 查询那个用户下的那个对象正在被请求pin：                                    

SELECT 

kglnaown "Owner"

, kglnaobj "Object"                                                                     

FROM x$kglob                                                                                                 

WHERE kglhdadr='&1RAW'                                                                                       

/          

返回的OBJECT可能是具体的对象，也可能是一段SQL。  

--找到被锁的对象

select to_owner,to_name  from v$object_dependency where to_address = '03FA2270'; 

TO_NAME 

------------- 

DBMS_PIPE  

You can find which library objects are used by each session via following

--For the blocked session | waiting request

select distinct kglnaobj from x$kgllk  where  

kgllkuse in (select saddr from v$session where sid = 16)

/

--For the blocking session  | hoding

select distinct kglnaobj from x$kgllk  where  

kgllkuse in (select saddr from v$session where sid = 12); 

One of those objects can be the cursor or statement that each session is  

executing/trying to execute. 

You can also use the $ORACLE_HOME/rdbms/admin/utldtree.sql utility to find out  

how the dependency tree looks like and which objects are dependent on e.g.  

DBMS_PIPE. One of those objects will be the sql statement of the holding  

session.

A variant script on utldtree.sql stands in [NOTE:139594.1] and  

gives which objects an object depends on. 

--为了避免这种情况，可以在编译过程或函数等对象时，先查看一下是否有会话正在使用该对象

查询语句如下：

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'); 

--------------------------------------------------补充------------------------------------------------------------------------------------------------------

1>check session for library cache lock

select 

sid

, program 

, machine

from v$session 

where paddr in 

( 

SELECT s.paddr FROM x$kglpn p, v$session s 

WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0 and kglpnhdl in 

(

select p1raw from v$session_wait 

where event in ('library cache pin','library cache lock' ,'library cache load lock') 

) 

)

/

2>批量kill -9

select spid from v$process where addr in ( 

SELECT s.paddr 

FROM x$kglpn p, v$session s 

WHERE p.kglpnuse=s.saddr --AND p.kglpnmod <> 0 

and kglpnhdl in ( select p1raw from v$session_wait where event in (' library cache pin','library cache lock' ) ) )

/

 

-----------------------------------------------------------------------------------------------------------------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

 )

 ;

--------------------------------------------------------------------------------------------------------------------library cache pin |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) 

/

--After run

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 sid,p1,p2,event  from v$session_wait where event like '%libr%' ;