오라클 성능 트러블슈팅의 기초 (2012년)
Library Cache Lock 관련 대기 이벤트
Library Cache Lock 대기 이벤트 소개
- Library Cache Lock은 라이브러리 캐시 오브젝트(Library Cache Object. LCO)를 보호하는 시스템 락
- LCO는 Shared Pool의 라이브러리 캐시 영역에 저장되는 모든 종류의 공유 오브젝트(테이블, 뷰, SQL 커서, 함수, 포르시저)를 의미함
- Library Cache Lock은 여러 세션이 동시에 동일한 LCO를 변경하는 것을 방지하기 위해 사용되며, 획득하지 못한 경우 Library Cache Lock 대기 이벤트 발생
- Library Cache Lock은 V$LOCK이 아닌 X$KGLLK 뷰에서 확인 가능
DESC SYS.X$KGLLK
Name Null? Type
------------ -------- -------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(8)
KGLLKUSE RAW(8)
KGLLKSES RAW(8)
KGLLKSNM NUMBER
KGLLKHDL RAW(8)
KGLLKPNC RAW(8)
KGLLKPNS RAW(8)
KGLLKCNT NUMBER
KGLLKMOD NUMBER
KGLLKREQ NUMBER
KGLLKFLG NUMBER
KGLLKSPN NUMBER
KGLLKHTB RAW(8)
KGLNAHSH NUMBER
KGLLKSQLID VARCHAR2(13)
KGLHDPAR RAW(8)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30)
KGLNAOBJ VARCHAR2(60)
;
SELECT KGLLKUSE,
KGLLKHDL,
KGLNAOBJ
FROM SYS.X$KGLLK
WHERE ROWNUM <= 10
;
KGLLKUSE KGLLKHDL KGLNAOBJ
---------------- ---------------- --------------------------------------------------------------
07000002C0C6E760 07000002A0FD4788 select priority from resource_mapping_priority$ where attrib
07000002C0C6E760 07000002A0FA7C90 select priority from resource_mapping_priority$ where attrib
07000002C0C6E760 070000029EF55060 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
07000002C0C6E760 070000029EF8A408 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
07000002C0C6E760 070000029DF6F3C8 select value, consumer_group from resource_group_mapping$ wh
07000002C0C6E760 070000029DF8E930 select value, consumer_group from resource_group_mapping$ wh
07000002C0C6E760 07000002A0FE9490 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
07000002C0C6E760 07000002A0FC1070 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
07000002C0C6E760 07000002A0FD3918 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
07000002C0C6E760 07000002A0FC0EF0 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
| COLUMN | TYPE | DESCRIPTION |
|---|---|---|
| ADDR | RAW(4) | address of this row/entry in the array or SGA |
| INDX | NUMBER | index number of this row in the fixed table array |
| INST_ID | NUMBER | 8.x oracle instance number |
| KGLLKADR | RAW(4) | address of the lock |
| KGLLKUSE | RAW(4) | user session holding the lock. VIEW:X$KSUSE ADDR |
| KGLLKSES | RAW(4) | session holding the lock |
| KGLLKSNM | NUMBER | session number of session holding lock |
| KGLLKHDL | RAW(4) | object handle for the lock |
| KGLLKPNC | RAW(4) | pin for the object for the call |
| KGLLKPNS | RAW(4) | pin for the object for the session |
| KGLLKCNT | NUMBER | reference count |
| KGLLKMOD | NUMBER | lock mode held => 1 : NULL => 2 : S => 3 : X => ELSE : Mode |
| KGLLKREQ | NUMBER | lock mode requested => 1 : NULL => 2 : S => 3 : X => ELSE : Req |
| KGLLKFLG | NUMBER | status flags => KGLLKBRO 0x01 this lock is broken => KGLLKCBB 0x02 this lock can be broken, see Note 1 => KGLLKPNC 0x04 "kgllkpnc" is a valid pin for the call => KGLLKPNS 0x08 "kgllkpns" is a valid pin for the session => KGLLKCGA 0x10 this lock is in CGA memory |
| KGLLKSPN | NUMBER | savepoint number when lock acquired |
| KGLNAHSH | NUMBER | hash val. of obj. locked |
| KGLHDPAR | RAW(4) | address of parent handle or self, if none |
| KGLHDNSP | NUMBER | namespace of obj. locked |
| USER_NAME | VARCHAR2(30) | user name of the session owner |
| KGLNAOBJ | VARCHAR2(60) | name of object |
Library Cache Lock 테스트
- 책에서는 PROC1 프로시저를 10만번 생성하는 스크립트 수행하여 'Library Cache Lock' 경합을 유도하였지만 테스트 진행이 잘 안되서 'Table Modify' 및 UPDATE 방식으로 테스트 변경
SESSION #1
소스코드
-- 1. 테이블 1억건 생성
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
DROP TABLE XSOFT_T;
CREATE TABLE XSOFT_T AS
SELECT TO_CHAR(LEVEL) CNT
FROM DUAL
CONNECT BY LEVEL <= 10000
;
DROP TABLE XSOFT_T2;
CREATE TABLE XSOFT_T2(CNT VARCHAR2(100))
;
ALTER SESSION ENABLE PARALLEL DML ;
INSERT /*+ APPEND */
INTO XSOFT_T2
SELECT /*+ FULL(A) PARALLEL(A 8) FULL(B) PARALLEL(B 8) */
A.CNT + B.CNT
FROM XSOFT_T A,
XSOFT_T B
;
-- 2. MODIFY 수행
ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(3000));
ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(100));
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DROP TABLE XSOFT_T;
Table dropped.
SQL >
1 CREATE TABLE XSOFT_T AS
2 SELECT TO_CHAR(LEVEL) CNT
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000
5 ;
Table created.
SQL >
1 DROP TABLE XSOFT_T2;
Table dropped.
SQL >
1 CREATE TABLE XSOFT_T2(CNT VARCHAR2(100))
2 ;
Table created.
SQL >
1 ALTER SESSION ENABLE PARALLEL DML ;
Session altered.
SQL >
1 INSERT /*+ APPEND */
2 INTO XSOFT_T2
3 SELECT /*+ FULL(A) PARALLEL(A 8) FULL(B) PARALLEL(B 8) */
4 A.CNT + B.CNT
5 FROM XSOFT_T A,
6 XSOFT_T B
7 ;
100000000 rows created.
SQL >
1 ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(3000));
Table altered.
SQL >
1 ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(100));
SESSION #2
- XSOFT_T2.CNT 컬럼 UPDATE해서 'Library Cache Lock' 유도
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
UPDATE XSOFT_T2 SET CNT = 10000000;
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE XSOFT_T2 SET CNT = 10000000;
SESSION #3에서 SESSION #2 모니터링
- Library Cache Lock 모니터링
소스코드
-- 1. V$SESSION_WAIT 뷰로 SESSION #2 모니터링
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. X$KGLLK 뷰를 통해 확인
col kglnaobj format a10
col lkmode format a10
col sql_text format a30
SELECT H.SID AS H_SID,
SUBSTR(S.SQL_TEXT, 1, 40) AS SQL_TEXT,
K.KGLHDNSP,
K.KGLNAOBJ,
DECODE(K.KGLLKMOD, 3, '3(X)',
2, '2(S)',
1, '1(N)',
K.KGLLKMOD) AS LKMODE
FROM SYS.X$KGLLK K,
V$SESSION_WAIT W,
V$SESSION H,
V$SQLAREA S
WHERE H.SADDR = K.KGLLKUSE
AND H.SQL_ID = S.SQL_ID(+)
AND W.SID = &SID
AND W.EVENT = 'library cache lock'
AND K.KGLLKHDL = (CASE WHEN W.P1RAW LIKE '00000000%' THEN SUBSTR(W.P1RAW, 9, 8)
ELSE W.P1RAW || ''
END)
AND K.KGLLKMOD > 0 -- 이 조건에서 데이터 필터링이 되서 한건도 안나옴(값은 0임)
;
-- 3. @LIBLOCK 스크립트로 확인
-- 이 스크립트는 X$KGLLK를 바라보지 않고 V$SESSION에서 Waiting Session과 Blocking Session(Node 포함)을 한번에 찾음
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 >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2'
4 ;
SID
----------
9875
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9857
SEQ# : 6
EVENT : library cache lock
P1TEXT : handle address
P1 : 504403169284868160
P1RAW : 0700000290CE4840
P2TEXT : lock address
P2 : 504403169440959360
P2RAW : 070000029A1C0B80
P3TEXT : 100*mode+namespace
P3 : 201
P3RAW : 00000000000000C9
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 col kglnaobj format a10
SQL >
1 col lkmode format a10
SQL >
1 col sql_text format a30
SQL >
1 SELECT H.SID AS H_SID,
2 SUBSTR(S.SQL_TEXT, 1, 40) AS SQL_TEXT,
3 K.KGLHDNSP,
4 K.KGLNAOBJ,
5 DECODE(K.KGLLKMOD, 3, '3(X)',
6 2, '2(S)',
7 1, '1(N)',
8 K.KGLLKMOD) AS LKMODE
9 FROM SYS.X$KGLLK K,
10 V$SESSION_WAIT W,
11 V$SESSION H,
12 V$SQLAREA S
13 WHERE H.SADDR = K.KGLLKUSE
14 AND H.SQL_ID = S.SQL_ID(+)
15 AND W.SID = &SID
16 AND W.EVENT = 'library cache lock'
17 AND K.KGLLKHDL = (CASE WHEN W.P1RAW LIKE '00000000%' THEN SUBSTR(W.P1RAW, 9, 8)
18 ELSE W.P1RAW || ''
19 END)
20 AND K.KGLLKMOD > 0 -- 이 조건에서 데이터 필터링이 되서 한건도 안나옴(값은 0임)
21 ;
no rows selected
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 lock'
22 ORDER BY S.SECONDS_IN_WAIT
23 /
-- V$SESSION 에서 BLOCKING_SESSION 값과 BLOCKING_INSTANCE 값 확인 가능
Sid,Ser# Module s.p1 P2 P3
Spid Program W_TIME NODE HOLDER SQL_TEXT s.p1Text P2Text P3Text KILL_SCRIPT KILL_SCRIPT2
------------- ---------- ------ ----- --------------- ----------------------------------- ------------------------- ------------------------- ------------------------- -------------------------------------------------- --------------------
9857,6 SQL*Plus 5 1 9906 UPDATE XSOFT_T2 SET CNT = 10000000 handle address lock address 100*mode+namespace alter system kill session '9857,6' ; kill -9 1228864
504403169284868160 504403169323693832 201
1 row selected.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4302
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.