oracle library chace,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_SESSIONHOLDING_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%' ;