오라클 성능 트러블슈팅의 기초 (2012년)
Library Cache Pin 관련 대기 이벤트
Library Cache Pin 대기 이벤트 소개
- Library Cache Pin은 LCO를 실행하는 과정을 보호하는 시스템 락.
- 예를들어, 특정 세션이 프로시저 Pl을 실행 하는 도중에, 다른 세션이 해당 프로시저를 컴파일해서는 안되며, 거꾸로 특정 세션이 프로시저 Pl 을 컴파일하고 있는 도중에 다른 세션이 해당 프로시저를 실행해서도 안됨.
- 이처럼 특정 LCO의 실행 과정을 보호하기 위해 사용되는 것이 Library Cache Pin이며, 획득하지 못 할 경우 이 이벤트를 대기함
- Library Cache Pin 은 V$LOCK 뷰가 아닌 X$KGLPN 뷰에서 정보를 얻을 수 있음
DESC SYS.X$KGLPN
Name Null? Type
------------ -------- ---------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(8)
KGLPNUSE RAW(8)
KGLPNSES RAW(8)
KGLPNHDL RAW(8)
KGLPNLCK RAW(8)
KGLPNCNT NUMBER
KGLPNMOD NUMBER
KGLPNREQ NUMBER
KGLPNDMK NUMBER
KGLPNSPN NUMBER
;
SELECT KGLPNUSE,
KGLPNHDL
FROM SYS.X$KGLPN
WHERE ROWNUM <= 10
;
KGLPNUSE KGLPNHDL
---------------- ----------------
07000002C1C5A170 070000029EF91DB0
07000002C1C5A170 0700000298F48A30
07000002C1C5A170 070000029FD3C350
| KGLPNUSE | Library Cache Pin 을 획득하거나 대기하고 있는 세션의 주소. V$SESSION.SADDR값과 조인하면 세션 정보를 얻을 수 있음 |
| KGLPNHDL | Library Cache Lock 의 핸들 주소. V$SESSIONJ_WAIT.PlRAW 값과 조인하면 대기이벤트 정보를 얻을 수 있음 |
Library Cache Pin 테스트
- 테스트 시나리오는, 세션_1에서 SLEEP하는 프로시저를 수행하고, 세션_2에서 동일 프로시저를 수행하면서 'Library Cache Pin' 경합을 유도함
- reference_site : http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
Sesssion #1
- 세션_1에서 TEST_KGLLK 프로시저 컴파일 하고 수행
소스코드
-- TEST_KGLLK 프로시저 생성
CREATE OR REPLACE PROCEDURE TEST_KGLLK (L_SLEEP IN BOOLEAN , L_COMPILE IN BOOLEAN)
AS
BEGIN
IF (L_SLEEP ) THEN
SYS.DBMS_LOCK.SLEEP(60);
ELSIF (L_COMPILE ) THEN
EXECUTE IMMEDIATE 'ALTER PROCEDURE TEST_KGLLK COMPILE';
END IF;
END;
/
-- 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
EXEC TEST_KGLLK ( TRUE, FALSE);
수행내역
SQL >
1 CREATE OR REPLACE PROCEDURE TEST_KGLLK (L_SLEEP IN BOOLEAN , L_COMPILE IN BOOLEAN)
2 AS
3 BEGIN
4 IF (L_SLEEP ) THEN
5 SYS.DBMS_LOCK.SLEEP(60);
6 ELSIF (L_COMPILE ) THEN
7 EXECUTE IMMEDIATE 'ALTER PROCEDURE TEST_KGLLK COMPILE';
8 END IF;
9 END;
10 /
Procedure created.
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 EXEC TEST_KGLLK ( TRUE, FALSE);
PL/SQL procedure successfully completed.
Sesssion #2
- 세션_2에서 TEST_KGLLK 컴파일 수행(Library Cache Pin 대기)
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
EXEC TEST_KGLLK ( FALSE, TRUE);
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 EXEC TEST_KGLLK ( TRUE, FALSE);
Sesssion #3
- 세션_3에서 세션_2 모니터링
소스코드
-- 1. Library Cache Pin 세션 모니터링
set serveroutput on
col sid new_value sid
SELECT SID
FROM V$SESSION
WHERE CLIENT_INFO = 'SESSION_2';
BEGIN
PRINT_TABLE('SELECT *
FROM V$SESSION_WAIT
WHERE SID = &SID');
END;
/
-- 2. @LIBPIN 스크립트 사용
-- 이상한것은 책에서는 BLOCKING_SESSION을 찾을 수 있다고 했지만, 자기 SESSION_ID 밖에 못 찾음
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 pin'
ORDER BY S.SECONDS_IN_WAIT
/
수행내역
SQL >
1 set serveroutput on
SQL >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2';
SID
----------
9937
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9937
SEQ# : 81
EVENT : library cache pin
P1TEXT : handle address
P1 : 504403169386942624
P1RAW : 0700000296E3D0A0
P2TEXT : pin address
P2 : 504403169480899360
P2RAW : 070000029C7D7B20
P3TEXT : 100*mode+namespace
P3 : 301
P3RAW : 000000000000012D
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 6
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 set linesize 300
SQL >
1 col sid heading "Sid,Ser#|Spid" format a13
SQL >
1 col module heading "Module|Program" format a25
SQL >
1 col w_time format 99999
SQL >
1 col node format a5
SQL >
1 col holder format a15
SQL >
1 col sql_text format a35
SQL >
1 col p1 heading "s.p1|s.p1Text" format a25
SQL >
1 col p2 heading "P2|P2Text" format a25
SQL >
1 col p3 heading "P3|P3Text" format a25
SQL >
1 col kill_script format a50
SQL >
1 col kill_script2 format a20
SQL >
1
SQL >
1 SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
2 S.SID || ',' || S.SERIAL# AS SID,
3 S.MODULE AS MODULE,
4 S.SECONDS_IN_WAIT W_TIME,
5 TO_CHAR(S.BLOCKING_INSTANCE) NODE,
6 TO_CHAR(S.BLOCKING_SESSION) HOLDER,
7 (SELECT SUBSTR(SQL_TEXT, 1, 65)
8 FROM V$SQL SQ
9 WHERE SQ.ADDRESS = S.SQL_ADDRESS
10 AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
11 AND ROWNUM = 1) AS SQL_TEXT,
12 NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
13 NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
14 NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
15 'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
16 ' ; ' KILL_SCRIPT,
17 'kill -9 ' || P.SPID KILL_SCRIPT2
18 FROM V$SESSION S,
19 V$PROCESS P
20 WHERE S.PADDR = P.ADDR
21 AND S.EVENT = 'library cache pin'
22 ORDER BY S.SECONDS_IN_WAIT
23 /
-- BLOCKING_SESSION 값과 SID 값이 동일함 -_-;
Sid,Ser# Module s.p1 P2 P3
Spid Program W_TIME NODE HOLDER SQL_TEXT s.p1Text P2Text P3Text KILL_SCRIPT KILL_SCRIPT2
------------- ----------- ------ ----- --------------- ----------------------------------- ------------------------- ------------------------- ------------------------- -------------------------------------------------- --------------------
9937,41 SQL*Plus 6 1 9937 ALTER PROCEDURE TEST_KGLLK COMPILE handle address pin address 100*mode+namespace alter system kill session '9937,41' ; kill -9 3560198
504403169386942624 504403169480899360 301
1 row selected.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4303
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.