library cache pin wait 높을 때 점검 쿼리

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : library cache pin wait 높을 때 점검 쿼리

db 사용중 latch wait 에 의해 CPU 사용량이 높을때, 점검하는 방안

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하는 상태가 된다. 

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 v.sid Holder ,x.KGLPNUSE session_, x.KGLPNMOD Held, x.KGLPNREQ Req 
from x$kglpn x, v$session v
where x.KGLPNHDL in (select p1raw from v$session_wait 
where wait_time=0 and event like 'library cache pin%') 
and x.KGLPNMOD <> 0 
and v.saddr=x.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>;

참조 : 

http://cluster1.cafe.daum.net/_c21_/bbs_search_read?grpid=Z767&fldid=7vGJ&datanum=23&openArticle=true&docid=Z767%7C7vGJ%7C23%7C20030803070003&q=gmsc&srchid=CCBZ767%7C7vGJ%7C23%7C20030803070003

https://community.oracle.com/thread/473415