오라클 성능 트러블슈팅의 기초 (2012년)
뮤텍스 관련 대기 이벤트
0
0
19,464
by 구루비스터디 뮤텍스 대기이벤트 V$SESSION_WAIT V$MUTEX_SLEEP_HISTORY MUTEX [2023.09.06]
뮤텍스 관련 대기 이벤트
뮤텍스 소개
- 뮤텍스는 Oracle lOg R2 에 서 추가된 새로운 동기화 객체.
- 오라클이 사용하는 특정 객체들은 그 사용 빈도가 지나치게 높아서 래치와 같은 가벼운 객체로도 충분한 성능을 내기가 어려운 경우가 많음(Library Cache Latch 가 대표적인 예).
- 사용자가 수행 요청하는 모든 SQL 문은 반드시 Library Cache Latch 를 획득하고 라이브러리 캐시 영역을 접근해야 하는데, 래치가 아무리 가볍다고 하더라도 펼연적으로 래치 경합이 발생할 수 있음.
- 이런 이유 때문에 Oracle l1g 부터는 Library Cache Latch는 사용하지 않고(아예 사라짐)，Library Cache Mutex 를 사용함.
- 뮤텍스는 워낙 가벼운 객체이기 때문에 뮤텍스의 획득 회수를 제공하는 뷰가 존재하지 않는데, 뮤텍스를 획득하고 해제하는 행위를 기록하는 것 자체가 부담이 될 수 있기 때문.
- 이런 이유 때문에 뮤텍스를 획득하는 과정에서 대기 (Wait)가 발생하는 경우 에만 해당 정보를 기록함.
V$SESSION_WAIT 뷰에서 정보 얻기
소스코드
SELECT P1,
P2RAW,
COUNT(*)
FROM V$SESSION
WHERE EVENT = 'cursor: pin S wait on X'
AND WAIT_TIME = 0
GROUP BY P1,
P2RAW
;
| P1 | 뮤텍스 아이디를 의미하며, V$MUTEX_SLEEP_HISTORY.MUTEXT_IDENTIFIER 컬럼과 동일함 |
| P2RAW | 뮤텍스 홀더의 세션 어드레스를 의미함. V$SESSION.SADDR 컬럼과 동일함 |
- 위의 두 값을 이용하면 어떤 세션이 어떤 뮤텍스를 점유함으로서 대기가 발생했는지 추적 가능함
V$MUTEX_SLEEP_HISTORY 뷰에서 정보 얻기
- 뮤텍스에 의해 대기 현상은 V$MUTEX_SLEEP, V$MUTEX_SLEEP_HISTORY 뷰를 통해 분석 가능함
DESC V$MUTEX_SLEEP
Name Null? Type
------------- ------- --------------
MUTEX_TYPE VARCHAR2(32)
LOCATION VARCHAR2(40)
SLEEPS NUMBER
WAIT_TIME NUMBER
;
SELECT *
FROM V$MUTEX_SLEEP
WHERE ROWNUM <= 10
;
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
--------------- -------------------------------- ------ ----------
Cursor Stat kksFindCursorStat [KKSSTALOC3] 1 6
Cursor Parent kkspsc0 [KKSPRTLOC26] 3 8
Cursor Parent kksfbc [KKSPRTLOC2] 1 6
Cursor Pin kkslce [KKSCHLPIN2] 844 8148033
Cursor Pin kksfbc [KKSCHLPIN1] 1 9787
;
DESC V$MUTEX_SLEEP_HISTORY
MUTEX_IDENTIFIER SLEEP_TIMESTAMP MUTEX_TYPE GETS SLEEPS REQUESTING_SESSION BLOCKING_SESSION LOCATION MUTEX_VALUE P1 P1RAW P2 P3 P4 P5
---------------- ------------------------------- ---------------- ---- ---------- ------------------ ---------------- --------------------- ---------------- ---------- ---------------- ---------- ---------- ---------- -----
1146918755 05-APR-12 06.10.30.393569 AM Cursor Parent 9 1 9902 9934 kksfbc [KKSPRTLOC2] 00 13 00 0 0 0
1299653039 05-APR-12 09.46.44.391569 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
4210584983 05-APR-12 09.46.38.339478 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
1194380816 05-APR-12 05.10.04.227956 AM Cursor Pin 12 14 9957 9901 kkslce [KKSCHLPIN2] 000026940000000C 0 00 0 0 0
2246118947 05-APR-12 09.46.42.303668 AM Cursor Pin 1 2 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
2920326977 05-APR-12 09.46.38.512431 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
27866834 05-APR-12 09.46.43.952397 AM Cursor Pin 1 2 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
643237928 05-APR-12 09.46.43.094252 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
2077966511 05-APR-12 09.46.38.734815 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
3832746397 05-APR-12 09.46.44.175636 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
;
SELECT *
FROM V$MUTEX_SLEEP_HISTORY
WHERE ROWNUM <= 10
;
Name Null? Type
--------------------- -------- --------------
MUTEX_IDENTIFIER NUMBER
SLEEP_TIMESTAMP TIMESTAMP(6)
MUTEX_TYPE VARCHAR2(32)
GETS NUMBER
SLEEPS NUMBER
REQUESTING_SESSION NUMBER
BLOCKING_SESSION NUMBER
LOCATION VARCHAR2(40)
MUTEX_VALUE RAW(8)
P1 NUMBER
P1RAW RAW(8)
P2 NUMBER
P3 NUMBER
P4 NUMBER
P5 VARCHAR2(64)
;
SELECT NAME
FROM V$EVENT_NAME
WHERE NAME LIKE '%mutex%' OR NAME LIKE 'cursor:%'
;
NAME
--------------------------
cursor: mutex X
cursor: mutex S
cursor: pin S wait on X
cursor: pin X
cursor: pin S
MUTEX 테스트
- 테스트 시나리오는 아주 많은 수의 테이블을 액세스하는 SQL 문장을 생성하고, 그 결과를 LONG_PARSE.SQL 파일에 저장한 뒤, LONG_PARSE.SQL 파일을 수행하여 하드 파싱 유발하여 뮤텍스 유도
Session #1 @LONG_PARSE 파일 생성 및 수행
소스코드
-- 1. @LONG_PARSE 파일 생성
SET heading OFF
SET timing OFF
SET feedback OFF
SET serveroutput ON SIZE 100000
var v_sql CLOB;
BEGIN
:v_sql := 'EXPLAIN PLAN FOR SELECT COUNT(*) FROM ';
FOR R IN (SELECT T1.TABLE_NAME
FROM USER_TABLES T1,
USER_TABLES T2
WHERE ROWNUM <= 300
AND T1.TABLE_NAME NOT LIKE '%S%') LOOP
:v_sql := :v_sql || R.TABLE_NAME || ', ';
END LOOP;
:v_sql := :v_sql || ' DUAL WHERE 1 = 0;';
dbms_output.put_line(:v_sql);
END;
/
spool long_parse.sql
EXEC DBMS_OUTPUT.PUT_LINE(:v_sql);
spool OFF
SET heading ON
SET timing ON
SET feedback ON
SET serveroutput OFF
-- 2. @LONG_PARSE 파일 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
@long_parse
수행내역
SQL >
1 SET heading OFF
SQL >
1 SET timing OFF
SQL >
1 SET feedback OFF
SQL >
1 SET serveroutput ON SIZE 100000
SQL >
1 var v_sql CLOB;
SQL >
1 BEGIN
2 :v_sql := 'SELECT COUNT(*) FROM ';
3
4 FOR R IN (SELECT T1.TABLE_NAME
5 FROM USER_TABLES T1,
6 USER_TABLES T2
7 WHERE ROWNUM <= 300
8 AND T1.TABLE_NAME NOT LIKE '%S%') LOOP
9
10 :v_sql := :v_sql || R.TABLE_NAME || ', ';
11
12 END LOOP;
13
14 :v_sql := :v_sql || ' DUAL WHERE 1 = 0;';
15
16 dbms_output.put_line(:v_sql);
17 END;
18 /
SQL >
1 spool OFF
SQL >
1 SET heading ON
SQL >
1 SET timing ON
SQL >
1 SET feedback ON
SQL >
1 SET serveroutput OFF
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 @long_parse
Session #2 @LONG_PARSE 파일 수행
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
@long_parse
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 @long_parse
Mutex 모니터링
소스코드
-- 1. 세션_2 모니터링
-- 세션_1에서 해당 SQL에 대해 LCO를 등록한 상태에서 실행계획을 만들고 있으므로,
--- 세션_2는 파싱하는 동안 대기해야 하며, 이 때 cursor: pin S wait on X 이벤트 대기함
-- cursor: pin S wait on X 이벤트는, Curor Pin을 공유모드(Shared)로 획득하려고 하지만, 다른 세션이 (Exclusive)로 획득하고 있어서 대기함을 의미함
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 홀더 찾기
-- V$SESSION.BLOCKING_SESSION 컬럼으로 확인 가능
-- 하지만, 지속시간이 매우 짧은 다른 뮤텍스들은 래치 경합과 마찬가지로 홀더 찾기가 어려울 수 있음
SELECT BLOCKING_SESSION
FROM V$SESSION
WHERE SID = &SID
;
-- 3. V$MUTEX_SLEEP_HISTORY 뷰에서 확인
-- MUTEX_IDENTIFIER 값을 통해 경합 대상이 되는 오브젝트 이름 얻을 수 있음
col obj_name format a10
col mutex_type format a10
col LOCATION format a10
SELECT *
FROM (SELECT BLOCKING_SESSION AS H_SID,
(SELECT KGLNAOBJ
FROM SYS.XM$KGLOB
WHERE KGLNAHSH = MUTEX_IDENTIFIER
AND ROWNUM = 1) AS OBJ_NAME,
MUTEX_TYPE,
LOCATION,
SLEEPS,
GETS,
TO_CHAR(SLEEP_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS SLEEP_TIMESTAMP
FROM V$MUTEX_SLEEP_HISTORY
WHERE REQUESTING_SESSION = &SID
ORDER BY SLEEP_TIMESTAMP DESC)
WHERE ROWNUM = 1
;
수행내역
SQL >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2'
4 ;
SID
----------
9839
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9839
SEQ# : 503
EVENT : cursor: pin S wait on X
P1TEXT : idn
P1 : 17636530
P1RAW : 00000000010D1CB2
P2TEXT : value
P2 : 41777146888192
P2RAW : 000025FF00000000
P3TEXT : where|sleeps
P3 : 21474836898
P3RAW : 00000005000001A2
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 0
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 SELECT BLOCKING_SESSION
2 FROM V$SESSION
3 WHERE SID = &SID
4 ;
BLOCKING_SESSION
----------------
1 row selected.
SQL >
1 col obj_name format a10
SQL >
1 col mutex_type format a10
SQL >
1 col LOCATION format a10
SQL >
1 SELECT *
2 FROM (SELECT BLOCKING_SESSION AS H_SID,
3 (SELECT KGLNAOBJ
4 FROM SYS.XM$KGLOB
5 WHERE KGLNAHSH = MUTEX_IDENTIFIER
6 AND ROWNUM = 1) AS OBJ_NAME,
7 MUTEX_TYPE,
8 LOCATION,
9 SLEEPS,
10 GETS,
11 TO_CHAR(SLEEP_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS SLEEP_TIMESTAMP
12 FROM V$MUTEX_SLEEP_HISTORY
13 WHERE REQUESTING_SESSION = &SID
14 ORDER BY SLEEP_TIMESTAMP DESC)
15 WHERE ROWNUM = 1
16 ;
H_SID OBJ_NAME MUTEX_TYPE LOCATION SLEEPS GETS SLEEP_TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------
9727 EXPLAIN PL Cursor Pin 464 1 2012/04/05 15:19:37
1 row selected.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4306
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.