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

제품 : ORACLE SERVER

작성날짜 : 2003-12-09

'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_requestedmode_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을 확인한다.
아래의 <SID> 에 4번에서 확인한 sid를 대입하여 조회한다.

select sid,sql_text 
from v$session, v$sqlarea 
where v$session.sql_address=v$sqlarea.address 
and sid=<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';



출처 : https://forums.oracle.com/forums/thread.jspa?threadID=471415