ASH 데이터를 활용한 성능 분석: 특정 시간 동안 실행된 SQL 및 대기 이벤트 분석

서버 이관 프로젝트를 수행하는 과정에서 가장 중요한 과제 중 하나는 데이터베이스 성능의 지속적인 유지와 최적화다. 기존 환경과 새로운 환경에서의 성능 차이를 면밀히 분석하고, 예상치 못한 성능 저하가 발생할 경우 빠르게 원인을 파악하여 대응하는 것이 필수적이다.

 

Oracle 데이터베이스는 이러한 성능 분석을 위해 다양한 진단 도구를 제공하는데, 그중 ASH(Active Session History) 데이터는 실시간 세션 활동을 기록하고 분석할 수 있는 강력한 도구다. ASH 데이터를 활용하면 특정 시간 동안 실행된 SQL과 대기 이벤트를 분석하여, 성능 저하의 원인을 정확하게 진단할 수 있다.

 

이 글에서는 ASH 데이터를 기반으로 성능을 분석하는 방법과 주요 대기 이벤트를 해석하는 방법, 그리고 실무에서 활용할 수 있는 SQL 예제를 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 효과적으로 해결할 수 있도록 한다.

---

1. ASH(Active Session History) 데이터란?

1.1 ASH의 개념

ASH(Active Session History)는 Oracle 데이터베이스에서 실행 중인 활성 세션의 성능 정보를 실시간으로 수집하는 기능이다. Oracle은 매 초마다 실행 중인 세션 정보를 메모리에 저장하며, 이를 기반으로 세션 활동, SQL 실행 내역, 대기 이벤트(Wait Event), 리소스 소비 현황 등을 분석할 수 있다.

ASH 데이터는 특정 시간 동안 어떤 SQL이 실행되었으며, 어떤 이벤트로 인해 지연되었는지를 분석할 수 있어, 성능 문제의 원인을 찾는 데 매우 효과적이다.

---

2. ASH 데이터를 활용한 성능 분석 방법

ASH 데이터를 활용하여 특정 시간 동안 실행된 SQL과 대기 이벤트를 분석하려면 v$active_session_history 뷰를 조회해야 한다. 이 뷰에는 다음과 같은 주요 데이터가 저장된다.

• SAMPLE_TIME: 해당 샘플이 수집된 시간
• SESSION_ID: 실행 중인 세션의 ID
• SQL_ID: 실행 중인 SQL의 고유 ID
• WAIT_CLASS: 세션이 기다리고 있는 대기 이벤트 유형 (CPU, I/O, 네트워크 등)
• EVENT: 대기 이벤트의 상세 명칭
• SESSION_STATE: 현재 세션이 CPU를 사용 중인지, 대기 중인지 여부

2.1 특정 시간 동안 실행된 SQL 조회

아래 SQL을 실행하면 특정 시간 동안 실행된 SQL 목록과 실행 빈도를 확인할 수 있다.

 

SELECT sql_id, COUNT(*) AS execution_count

FROM v$active_session_history

WHERE sample_time BETWEEN TO_DATE('2024-02-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND TO_DATE('2024-02-07 10:30:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY sql_id ORDER BY execution_count DESC;

• sample_time 필터링: 특정 시간 범위 동안 실행된 SQL을 조회.
• execution_count: 해당 SQL이 실행된 횟수.
• ORDER BY execution_count DESC: 실행 빈도가 높은 SQL부터 정렬하여 확인.

이 데이터를 활용하면 이관 후 특정 시간대에 자주 실행되는 SQL이 성능 저하를 유발하는지 분석할 수 있다.

---

2.2 특정 SQL의 실행 패턴 분석

특정 SQL이 어느 시간대에 집중적으로 실행되었는지를 분석하면, 특정 업무 시간대에 성능 이슈가 발생하는지 확인할 수 있다.

 

SELECT TO_CHAR(sample_time, 'HH24:MI') AS time_slot, COUNT(*) AS execution_count

FROM v$active_session_history

WHERE sql_id = 'SQL_ID'

GROUP BY TO_CHAR(sample_time, 'HH24:MI') ORDER BY time_slot;

• sql_id 필터링: 특정 SQL의 실행 패턴을 분석.
• TO_CHAR(sample_time, 'HH24:MI'): 시간대별 실행 빈도를 분석.

이 데이터를 활용하면 특정 SQL이 업무 피크 시간에 집중적으로 실행되어 병목을 유발하는지를 분석할 수 있다.

---

3. 대기 이벤트 분석을 통한 성능 최적화

3.1 ASH에서 대기 이벤트 분석하기

대기 이벤트(Wait Event)는 SQL이 실행되는 동안 특정 자원(예: CPU, 디스크, 네트워크 등)을 기다리는 시간을 의미한다. 특정 SQL이 실행 속도가 느려지는 원인을 분석하려면 대기 이벤트를 확인해야 한다.

 

SELECT event, COUNT(*) AS wait_count

FROM v$active_session_history

WHERE sample_time BETWEEN TO_DATE('2024-02-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND TO_DATE('2024-02-07 10:30:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY event ORDER BY wait_count DESC;

• event 필터링: 특정 시간 동안 가장 많이 발생한 대기 이벤트 분석.
• COUNT(*) AS wait_count: 대기 이벤트 발생 횟수를 계산.

이 데이터를 활용하면 CPU, I/O, 네트워크 중 어떤 요소가 병목을 유발하는지 확인할 수 있다.

---

3.2 주요 대기 이벤트 유형 및 해결 방법

대기 이벤트설명해결 방법

CPU 사용량 증가	CPU를 과도하게 사용하는 SQL이 원인	실행 계획 분석 및 인덱스 최적화
User I/O	디스크 I/O 대기가 많을 때 발생	테이블 파티셔닝 또는 I/O 분산
enq: TX - row lock contention	트랜잭션 록 대기 이벤트	트랜잭션 크기 축소 또는 인덱스 조정
db file scattered read	Full Table Scan으로 인한 I/O 대기	인덱스 적용 및 SQL 튜닝

이러한 대기 이벤트를 ASH 데이터를 통해 분석하면 성능 저하의 근본 원인을 파악하고 적절한 해결책을 적용할 수 있다.

---

 

ASH 데이터를 활용하면 특정 시간 동안 실행된 SQL과 대기 이벤트를 분석하여 성능 저하 원인을 정확히 파악할 수 있다. 특히, SQL 실행 패턴 분석, 대기 이벤트 분석, 세션별 자원 사용량 분석 등을 통해, 서버 이관 후 성능 변화가 발생했을 때 신속하게 대응할 수 있다.

 

실무에서는 ASH 데이터를 기반으로 자주 실행되는 SQL을 튜닝하거나, 특정 시간대의 성능 문제를 분석하여 인덱스를 최적화하는 등의 조치를 취할 수 있다. 또한, CPU, 디스크 I/O, 록 대기 등의 주요 대기 이벤트를 모니터링하여 성능 저하를 사전에 예방하는 것이 중요하다..