Oracle Wait Event를 추적하는 것은 데이터베이스의 성능 병목현상을 진단하고 최적화하기 위해 중요한 작업입니다. Wait Event는 쿼리나 프로세스가 특정 리소스에 대해 기다리는 이유를 나타냅니다. 아래는 Oracle Wait Event를 추적하는 방법에 대한 가이드입니다.
1. Wait Event 확인을 위한 주요 뷰
Oracle은 다양한 성능 뷰를 통해 Wait Event를 실시간 및 과거 데이터로 제공합니다:
1.1 실시간 추적 (V$ 뷰)
• V$SESSION: 현재 세션별 Wait Event 상태. • V$SYSTEM_EVENT: 시스템 수준 Wait Event 통계. • V$SESSION_WAIT: 현재 세션의 대기 이벤트 세부 정보. • V$ACTIVE_SESSION_HISTORY: 최근의 세션 활동 및 대기 이벤트.
1.2 히스토리 데이터 추적 (AWR 및 DBA_HIST 뷰)
• DBA_HIST_SYSTEM_EVENT: AWR 스냅샷에 저장된 시스템 수준 대기 이벤트. • DBA_HIST_ACTIVE_SESS_HISTORY: 세션의 과거 대기 이벤트 데이터.
2. Wait Event 실시간 확인
2.1 특정 세션의 Wait Event 확인
현재 세션이 대기 중인 이벤트를 확인하려면:
SELECT s.sid, s.serial#, s.username, sw.event, sw.wait_time, sw.seconds_in_wait FROM v$session s JOIN v$session_wait sw ON s.sid = sw.sid WHERE s.username IS NOT NULL;
• EVENT: 세션이 대기 중인 이벤트 이름. • WAIT_TIME: 대기가 끝난 후의 시간 (0이면 현재 대기 중). • SECONDS_IN_WAIT: 대기 시간(초).
2.2 시스템 전체 Wait Event 확인
시스템 수준에서 대기 이벤트를 확인하려면:
SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event NOT LIKE 'SQL*Net%';
• TOTAL_WAITS: 이벤트가 발생한 총 횟수. • TIME_WAITED: 대기 시간(밀리초). • AVERAGE_WAIT: 평균 대기 시간.
3. Wait Event의 유형과 진단
Oracle Wait Event는 리소스 대기와 비리소스 대기로 나뉩니다.
3.1 주요 Wait Event 유형
• I/O 관련 이벤트: • db file sequential read: 인덱스 또는 특정 블록 읽기. • db file scattered read: 전체 테이블 스캔 시 발생. • 락(Lock) 관련 이벤트: • enq: TX - row lock contention: 행 잠금 대기. • latch: cache buffers chains: 래치 경합. • 네트워크 관련 이벤트: • SQL*Net more data from client: 클라이언트에서 데이터를 수신 대기. • 로그 관련 이벤트: • log file sync: 트랜잭션 커밋 시 REDO 로그 동기화 대기.
4. AWR 리포트를 통한 Wait Event 분석
AWR 리포트는 특정 시간 범위의 Wait Event를 분석할 때 유용합니다.
AWR 리포트 생성
@?/rdbms/admin/awrrpt.sql
• AWR 리포트의 Top 5 Timed Events 섹션에서 가장 많은 시간이 소요된 Wait Event를 확인.
5. Active Session History(ASH)를 사용한 분석
ASH는 최근의 세션 활동과 대기 이벤트를 추적합니다.
5.1 특정 시간 동안의 Wait Event 확인
SELECT event, COUNT(*) FROM v$active_session_history WHERE sample_time > SYSDATE - INTERVAL '5' MINUTE GROUP BY event ORDER BY COUNT(*) DESC;
5.2 특정 SQL_ID의 Wait Event 확인
SELECT sql_id, event, COUNT(*) FROM v$active_session_history WHERE sql_id = 'SQL_ID값' GROUP BY sql_id, event ORDER BY COUNT(*) DESC;
6. SQL Monitoring 활용
Oracle Real-Time SQL Monitoring은 SQL 실행 중 발생하는 Wait Event를 추적할 수 있습니다.
실행 중 SQL의 Wait Event 확인
SELECT sql_id, event, wait_class, time_waited FROM v$sql_monitor WHERE sql_id = 'SQL_ID값';
7. Wait Event 히스토리 데이터 분석
AWR 스냅샷에 저장된 과거 Wait Event를 분석하려면 DBA_HIST_SYSTEM_EVENT를 사용합니다.
7.1 특정 시간 범위의 Wait Event 확인
SELECT event, total_waits, time_waited FROM dba_hist_system_event WHERE snap_id BETWEEN 100 AND 110
AND event NOT LIKE 'SQL*Net%';
8. Wait Event 상세 분석 도구
Oracle이 제공하는 도구로 Wait Event를 더 깊이 분석할 수 있습니다: • DBMS_XPLAN: SQL 실행 계획에 포함된 Wait Event 확인. • SQL Trace: 세션의 Wait Event를 상세히 분석.
ALTER SESSION SET sql_trace = TRUE;
9. Wait Class 활용
Wait Event를 그룹화하여 분석할 수 있습니다. Wait Class는 대기 이벤트를 유형별로 나눕니다.
주요 Wait Class
• Application: 사용자 애플리케이션 대기. • Concurrency: 경합(락, 래치). • User I/O: 데이터 블록 읽기/쓰기. • System I/O: 파일 시스템 관련 I/O. • Network: 클라이언트-서버 통신.
Wait Class별 대기 시간 확인
SELECT wait_class, SUM(time_waited) AS total_wait_time FROM v$system_event GROUP BY wait_class ORDER BY total_wait_time DESC;
10. Wait Event 모니터링 자동화
PL/SQL 프로시저를 작성해 Wait Event를 주기적으로 모니터링하고 알림을 설정할 수 있습니다.
Wait Event 알림 예제
DECLARE
threshold NUMBER := 10000; -- 기준 대기 시간 (밀리초)
BEGIN
FOR rec IN (
SELECT event, time_waited
FROM v$system_event
WHERE time_waited > threshold
) LOOP
DBMS_OUTPUT.PUT_LINE('High Wait Event: ' || rec.event || ', Time Waited: ' || rec.time_waited);
END LOOP;
END;
요약
1. 실시간 분석: • V$SESSION_WAIT와 V$SYSTEM_EVENT로 현재 대기 이벤트 추적. • ASH로 최근 대기 이벤트 분석. 2. 히스토리 분석: • AWR 및 DBA_HIST_SYSTEM_EVENT를 통해 과거 대기 이벤트 확인. 3. 문제 진단: • Wait Event 유형과 Wait Class를 분석하여 병목현상을 식별. 4. 모니터링 도구: • SQL Monitoring, AWR, ASH, DBMS_XPLAN을 결합하여 추적.
이 방법들을 활용하면 Wait Event를 체계적으로 추적하고 데이터베이스의 성능 병목을 효과적으로 해결할 수 있습니다.