LIBRARY CACHE PIN' WAIT EVENT가 나타나는 경우의 해결방법
=========================================================



PURPOSE
-------
v$session_wait event 에 library cache pin이 다량으로 발생할 때
확인하는 방법을 알아보자.



Explanation
-----------

V$SESSION_WAIT view에서 library cache pin이 발생하는 경우는 매우
드물다. 하지만, 어떤 경우는 library cache pin이 많은 session에서
나타나서 사용자가 작업을 못하는 경우가 생기기도 한다. 이런 현상은
sequence 등을 동시에 access하려고 할 때 발생할 수도 있다.

이 경우는 특정 session이 library cache handle 내의 object를 pin 한
상태에서 다른 이유로 처리를 못하고 waiting하는 상태이다. 이때 같은
object를 사용해야 하는 다른 session들은 이 object를 역시 library cache
내에서 pin하지 못하여 계속 waiting하는 상태가 된다.

아래의 sql을 이용하여 library cache pin이 나타나는 경우에 object를
잡고 있는 session을 확인해 보자.


1. V$SESSION_WAIT view에서 library cache pin 으로 waiting하는
session들을 확인한다.

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. waiting하는 object를 확인한다.

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. 해당 object를 lock 걸고 있는 holder session 을 확인한다.

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. holder session이 왜 waiting하고 있는 지 상태를 확인한다.

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. holder session이 실행하는 sql을 확인한다.
아래의 에 4번에서 확인한 sid를 대입하여 조회한다.

select sid,sql_text
from v$session, v$sqlarea
where v$session.sql_address=v$sqlarea.address
and sid=
;


6. holder session이 비정상적인 상태이거나 문제가 있다면 해당
session을 kill하여 다른 session들이 처리되도록 할 수도 있다.
holder session의 sid가 100번 이라면,

select sid, serial# from v$session where sid=100;

SID SERIAL#
---------- ----------
100 20

alter system kill session '100,20';