LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법

LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법
====================================================

PURPOSE
-------
v$session_wait event 에 'library cache lock'이 발생하면서 session이
waiting 되는 경우가 있다. 오랫동안 이 현상이 지속될 경우 어떤 session이
이 library cache lock 을 갖고 있는지 확인해 볼 수 있다.

Explanation
-----------
v$session_wait view에 'library cache lock'이 나타날 수 있는 경우가
어떠한 것이 있는 지 알아보자.

예를 들어 다음과 같은 alter table 문장의 경우를 살펴보자.
ALTER TABLE x MODIFY (col1 CHAR(200));

X 라는 Table의 row가 많다면 위의 문장으로 인해서 모든 row의 값이
200 bytes로 update되어야 하므로 굉장히 오랜 시간이 걸리게 된다.
이 작업 중에는 Table에 dml이 실행되어도 waiting되는 데, 이런 때에
'library cache lock' 이라고 나오게 된다.

또 package가 compile 되는 중에는 다른 user가 같은 package 내의
procedure나 function 등을 실행시켜도 waiting이 걸리면서 library cache
lock 이나 library cache pin event가 나타난다.

이 library cache lock 을 잡고 있는 session을 확인해 보자.

1. waiting session의 session address 확인
library cache lock으로 waiting하고 있는 session의 sid를 찾아서
그 session address - v$session의 saddr column 을 확인한다.

2. 다음과 같은 sql로 해당 library cache lock을 잡고 있는 session을
확인할 수 있다.

아래의 SQL 중에서 'saddr_from_v$session' 부분에 위의 1번에서 찾은
Waiting session의 saddr 값을 입력한다.
단, 아래의 sql은 반드시 sys 또는 internal user에서 실행해야 한다.

SELECT SID,SERIAL#,USERNAME,TERMINAL,PROGRAM
FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

3. 2번에서 확인한 blocking session에 의해 waiting하고 있는 session들도
확인할 수 있다. 2번에서 찾은 session의 saddr 값을 다음 sql에 대입하여
찾을 수 있다.

SELECT SID,USERNAME,TERMINAL,PROGRAM
FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

4. blocking session이 왜 오래 걸리는 것인지, v$session_wait 를 다시
확인하거나 실행하고 있는 sql 문장이나 object 등을 v$sql view 등을 확인
해 보아야 한다. 작업이라면 종료될 때까지 기다릴 수 있겠지만, 비정상적인
경우 또는 waiting session을 위해서는 다음과 같이 kill 할 수도 있다.

alter system kill session 'SID, SERIAL#';