Library Cache Lock , Pin 조회 테스트+킬구문포함

OS환경 : Oracle Linux7.3(64bit)

 

DB 환경 : Oracle Database 12.2.0.1

 

쿼리 : 

#라이브러리 캐시 락 조회 테스트

세션1

SQL> 
create table t ( id number, name char (10) );
insert INTO t
select rownum, lpad (rownum, 10, '0' ) from dual
connect by level <= 1000000;
alter table t modify name char (24); 

세션2

SQL> select sid from v$mystat where rownum = 1;

 SID                                                                      
-----                                                                      
 63

select count(*) from t;

 

세션3

SQL> SELECT * FROM v$session_wait WHERE sid=63;

또는 아래쿼리(킬 구문 포함)

-- 이 스크립트는 X$KGLLK를 바라보지 않고 V$SESSION에서 Waiting Session과 Blocking Session(Node 포함)을 한번에 찾음

SQL> 
set linesize 300
col sid heading "Sid,Ser#|Spid" format a13
col module heading "Module|Program" format a25
col w_time format 99999
col node format a5
col holder format a15
col sql_text format a35
col p1 heading "s.p1|s.p1Text"  format a25
col p2 heading "P2|P2Text"      format a25
col p3 heading "P3|P3Text"      format a25
col kill_script format a50
col kill_script2 format a20

SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
       S.SID || ',' || S.SERIAL# AS SID,
       S.MODULE AS MODULE,
       S.SECONDS_IN_WAIT W_TIME,
       TO_CHAR(S.BLOCKING_INSTANCE) NODE,
       TO_CHAR(S.BLOCKING_SESSION) HOLDER,
       (SELECT SUBSTR(SQL_TEXT, 1, 65)
        FROM   V$SQL SQ
        WHERE  SQ.ADDRESS = S.SQL_ADDRESS
        AND    SQ.HASH_VALUE = S.SQL_HASH_VALUE
        AND    ROWNUM = 1) AS SQL_TEXT,
       NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
       NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
       NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
       'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
       ' ; ' KILL_SCRIPT,
       'kill -9  ' || P.SPID KILL_SCRIPT2
FROM   V$SESSION    S,
       V$PROCESS    P       
WHERE  S.PADDR       = P.ADDR
AND    S.EVENT       = 'library cache lock'
ORDER  BY S.SECONDS_IN_WAIT
/

 

#라이브러리 캐시 핀 조회 테스트

함수 생성

SQL> 
create or replace function test_pin( v_loop in number )
return number is v_deptno number;
begin
  select deptno into v_deptno from dept where deptno = 10;
     for count in 1..v_loop
  loop
     dbms_lock.sleep(10);
  end loop;
  return v_deptno;
end;
/

 

세션1

SQL> select test_pin(10) from dual;

 

세션2

SQL> 
select sid from v$mystat where rownum = 1;

alter table dept modify deptno number(3);

alter function test_pin compile;

 

세션3

2번 세션에서 alter function test_pin compile 구문 실행 후 수행.

SQL> 
select event
,wait_time
,seconds_in_wait
,state
from v$session_wait 
where event = 'library cache pin'
/

또는 아래쿼리(킬 구문 포함)

SQL> 
col object for a10
col 
SELECT /*+ ORDERED */
       lo.kglnaobj AS object ,
       decode( ll.kgllkmod , 0 , 'none' , 1 , 'null' , 2 , 'share' , 3 , 'exclusive' , ll.kgllkmod ) AS CurrentLockMode ,
       decode( ll.kgllkreq , 0 , 'none' , 1 , 'null' , 2 , 'share' , 3 , 'exclusive' , ll.kgllkreq ) AS RequestLockMode ,
       ll.kgllktype Type ,
       s.schemaname HolderSchema,
       s.program HolderProgram,
       s.module HolderModule,
       'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' ||' ; ' KILL_SCRIPT,
       'kill -9  ' || p.SPID KILL_SCRIPT2
FROM   dba_kgllock ll ,
       x$kglob lo ,
       v$session s,
       v$process p       
WHERE  s.PADDR = p.ADDR
AND    ll.kgllkhdl = lo.kglhdadr
AND    ll.kgllkuse = s.saddr
AND    ll.kgllkreq  > 0
/

 

 

결과값 : 

라이브러리 캐시 락 조회 결과

 

라이브러리 캐시 핀 조회 결과

 

 

Library Cache Lock , Pin 조회 테스트+(킬).txt

다운로드

 

 

참조 : 

http://wiki.gurubee.net/pages/viewpage.action?pageId=26742450

http://wiki.gurubee.net/pages/viewpage.action?pageId=22544404#%EB%8C%80%EA%B8%B0%EC%9D%B4%EB%B2%A4%ED%8A%B8%EB%B3%84%EC%B6%94%EA%B0%80%EC%A0%81%EC%9D%B8%EB%8D%B0%EC%9D%B4%ED%84%B0%EC%88%98%EC%A7%91%21-2%29LibraryCacheLock%ED%85%8C%EC%8A%A4%ED%8A%B8

http://otsteam.tistory.com/92