오라클 성능 트러블슈팅의 기초 (2012년)
래치 관련 대기 이벤트
래치 관련 대기 이벤트 소개
- 래치는 버퍼 캐시 (Buffer Cache)나 공유 풀(Shared Pool)의 메모리 구조를 탐색하는 과정에서 시용하는 동기화 객체.
- 예들들어, 사용자가 SQL 문장을 수행 요청하면 서버 프로셰스는 Library Cache Latch 를 획득하고 라이브러리 캐시 영역을 탐색하는데, 만약 라이브러리 캐시 영역에서 해당 SQL 문장을 찾지 못하면 Shared Pool Latch 를 획득하고 공유 풀에서 새로운 메모리 를 할당받음.
- 대부분의 래치는 독점 모드(ExclusiveMode)로 획득해야 하므로, 동시에 많은 수의 세션이 라이브러리 캐시 영역을 탐색하거나 공유 풀에서 새로운 메모리를 할당받으변 래치 경합이 발생하게 됨
- 래치 를 획득하지 못하고 대기하는 세션은 latch:< 메모리 리 영역 이름>과 같은 이벤트를 대기하는데, Library Cache Latch 를 획득하는 과정에서 경합이 발생하면 latch:library cache 이벤트를 대기하고, Shared Pool Latch를 획득하는 과정에서 경합이 발생하면 latch: shared pool 이벤트를 대기함
래치 관련 대기 이벤트 테스트
- 테스트 시나리오는, DBA_TABLES 뷰에서 테이블 이름을 얻으면서 동적인 SQL을 문을 세션_1에서 수행하는데, 반복적으로 수행하는 과정에서 Hard Parse와 Soft Parse를 반복적으로 수행하여 'latch: shared pool' 이벤트 유도
Session #1
- Literal SQL을 통해 Hard Parse, Soft Parse 유도함
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
DECLARE
v_cnt NUMBER;
BEGIN
FOR IDX IN 1..100 LOOP
FOR R IN (SELECT TABLE_NAME
FROM DBA_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*)
INTO V_CNT
FROM ' || R.TABLE_NAME || '
WHERE 1 = 0';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END LOOP;
END;
/
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DECLARE
2 v_cnt NUMBER;
3 BEGIN
4 FOR IDX IN 1..100 LOOP
5 FOR R IN (SELECT TABLE_NAME
6 FROM DBA_TABLES) LOOP
7 BEGIN
8 EXECUTE IMMEDIATE 'SELECT COUNT(*)
9 INTO V_CNT
10 FROM ' || R.TABLE_NAME || '
11 WHERE 1 = 0';
12 EXCEPTION
13 WHEN OTHERS THEN
14 NULL;
15 END;
16 END LOOP;
17 END LOOP;
18 END;
19 /
Session #2
- Literal SQL을 통해 Hard Parse, Soft Parse 유도함
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
DECLARE
v_cnt NUMBER;
BEGIN
FOR IDX IN 1..100 LOOP
FOR R IN (SELECT TABLE_NAME
FROM DBA_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*)
INTO V_CNT
FROM ' || R.TABLE_NAME || '
WHERE 1 = 0';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END LOOP;
END;
/
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DECLARE
2 v_cnt NUMBER;
3 BEGIN
4 FOR IDX IN 1..100 LOOP
5 FOR R IN (SELECT TABLE_NAME
6 FROM DBA_TABLES) LOOP
7 BEGIN
8 EXECUTE IMMEDIATE 'SELECT COUNT(*)
9 INTO V_CNT
10 FROM ' || R.TABLE_NAME || '
11 WHERE 1 = 0';
12 EXCEPTION
13 WHEN OTHERS THEN
14 NULL;
15 END;
16 END LOOP;
17 END LOOP;
18 END;
19 /
latch: shared pool 모니터링
소스코드
-- 1. latch: cache buffers chains 세션 모니터링
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. 상세 모니터링
-- 2-1) V$SESSION.BLOCKING_SESSION 확인
-- 래치를 점유하는 시간이 매우 짧기 때문에 확인 안됨
SELECT BLOCKING_SESSION
FROM V$SESSION
WHERE SID = &SID
;
-- 2-2) V$LATCHHOLDER 확인
-- 래치 점유시간이 워낙 짧기 때문에 이 뷰를 통해서도 블로킹 세션 찾기 어려움
SELECT H.PID,
H.SID,
H.LADDR,
H.NAME,
H.GETS
FROM V$LATCHHOLDER H,
V$SESSION_WAIT S
WHERE S.P1RAW = H.LADDR
AND S.SID = &SID
;
수행내역
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
----------
9889
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9889
SEQ# : 5263
EVENT : latch: cache buffers chains
P1TEXT : address
P1 : 504403170016220752
P1RAW : 07000002BC65D650
P2TEXT : number
P2 : 122
P2RAW : 000000000000007A
P3TEXT : tries
P3 : 0
P3RAW : 00
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : -1
SECONDS_IN_WAIT : 0
STATE : WAITED SHORT TIME
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 SELECT BLOCKING_SESSION
2 FROM V$SESSION
3 WHERE SID = &SID
4 ;
BLOCKING_SESSION
----------------
SQL >
1 SELECT H.PID,
2 H.SID,
3 H.LADDR,
4 H.NAME,
5 H.GETS
6 FROM V$LATCHHOLDER H,
7 V$SESSION_WAIT S
8 WHERE S.P1RAW = H.LADDR
9 AND S.SID = &SID
10 ;
no rows selected
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4305
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.