패키지 프로시져 함수 컴파일 락 발생

패키지/프로시져/함수 컴파일시 락 발생 이벤트 확인

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
and 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
<source lang=sql>
alter system kill session '100,20';

SELECT  C.OBJECT_NAME AS OBJECT_NAME,
A.SESSION_ID AS SESSION_ID,
B.SERIAL# AS SERIAL_NO,
A.OS_USER_NAME AS OS_USER_NAME,
B.machine AS machine,
A.ORACLE_USERNAME AS ORACLE_USERNAME,
B.STATUS AS STATUS
FROM V$LOCKED_OBJECT A, V$SESSION B, DBA_OBJECTS C
WHERE A.SESSION_ID = B.SID AND A.OBJECT_ID = C.OBJECT_ID

ALTER SYSTEM KILL SESSION  ‘세션아이디,오브젝트아이디’

select SID  from v$access where object_name=’TEST_PRO’;

SID => 60

select sid,serial#,username,status from v$session where sid=60;

alter system kill session ‘sid,serial#’;

4. 3번에서 해결되지 않으면 os process kill

select a.spid
from v$process,v$session b
where a.addr=b.paddr
and  b.sid=60;

86662

ps -ef|grep 86662

kill -9 86662

패키지 프로시져 함수 컴파일 락 발생

DB CAFE