실시간 ASH 데이터를 조회하는 방법: 현재 진행 중인 세션 활동을 조회하는 SQL.

  서버 이관 프로젝트를 수행하는 과정에서 데이터베이스의 성능을 안정적으로 유지하는 것은 매우 중요한 과제다. 서버 환경이 변경되면 기존 환경과는 다른 성능 패턴이 나타날 수 있으며, 예기치 않은 성능 저하가 발생할 가능성이 높다. 이러한 문제를 신속하게 감지하고 대응하기 위해서는 실시간으로 데이터베이스의 세션 활동을 모니터링하는 것이 필수적이다.

Oracle 데이터베이스는 성능 모니터링을 위해 ASH(Active Session History) 데이터를 제공하며, 이를 활용하면 현재 실행 중인 SQL, 세션 상태, 대기 이벤트(Wait Event), CPU 및 I/O 사용 현황 등을 실시간으로 조회할 수 있다.

 

본 글에서는 실시간 ASH 데이터를 조회하는 방법과, 현재 진행 중인 세션 활동을 모니터링하는 SQL 쿼리를 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 신속하게 진단하고 해결할 수 있도록 한다.

---

1. 실시간 ASH 데이터 조회의 필요성

1.1 ASH 데이터란?

ASH(Active Session History)는 Oracle 데이터베이스에서 활성화된 세션의 정보를 실시간으로 저장하는 기능이다. Oracle은 매 초마다 현재 실행 중인 세션의 정보를 메모리에 기록하며, 이를 활용하면 특정 시점의 데이터베이스 활동을 즉각적으로 분석할 수 있다.

1.2 실시간 ASH 데이터 조회가 중요한 이유

• 현재 실행 중인 SQL을 분석하여 성능 저하의 원인을 즉시 파악 가능
• CPU, 메모리, 디스크 I/O 사용량이 높은 세션을 감지하여 성능 튜닝 수행 가능
• 대기 이벤트(Wait Event) 분석을 통해 트랜잭션 병목 문제 해결 가능
• 블로킹 세션을 빠르게 감지하여 데이터베이스 정체를 방지 가능

실시간 ASH 데이터를 활용하면 성능 문제를 빠르게 감지하고 해결하여 데이터베이스의 안정성을 유지할 수 있다.

---

2. 실시간 ASH 데이터를 조회하는 SQL

ASH 데이터를 활용하여 현재 실행 중인 세션 활동을 조회하는 SQL을 단계별로 설명한다.

2.1 현재 활성 세션 조회

현재 실행 중인 세션 목록을 확인하고, 어떤 SQL이 실행되고 있는지 조회하는 SQL이다.

 

SELECT    session_id,    sql_id,    sql_plan_hash_value,    session_state,    wait_class,    event,    time_waited  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (1 / 1440) -- 최근 1분간의 데이터 조회  ORDER BY sample_time DESC;

• session_id: 실행 중인 세션의 ID.
• sql_id: 실행 중인 SQL의 고유 ID.
• sql_plan_hash_value: 실행 계획 해시값(Plan Hash Value).
• session_state: 'ON CPU'(실행 중) 또는 'WAITING'(대기 중) 상태.
• wait_class & event: 해당 세션이 대기 중인 이벤트.
• time_waited: 해당 이벤트에서 대기한 시간(단위: 밀리초).

이 쿼리를 실행하면, 현재 실행 중이거나 최근 1분간 실행된 세션의 활동을 모니터링할 수 있다.

---

2.2 가장 많은 리소스를 사용 중인 SQL 조회

CPU, 메모리, I/O 사용량이 높은 SQL을 식별하여 최적화할 때 유용하다.

SELECT    sql_id,    COUNT(*) AS execution_count  FROM    v$active_session_history  WHERE    session_state = 'ON CPU'    AND sample_time >= SYSDATE - (1 / 1440) -- 최근 1분간의 데이터 조회    GROUP BY sql_id ORDER BY execution_count DESC;

 

• session_state = 'ON CPU': 현재 CPU에서 실행 중인 SQL만 조회.
• execution_count: 특정 SQL이 실행된 횟수.

이 데이터를 분석하면, CPU를 많이 사용하는 SQL을 식별하고 실행 계획을 최적화할 수 있다.

---

2.3 블로킹 세션(Blocking Session) 조회

블로킹 세션은 다른 세션의 실행을 방해하면서 데이터베이스 정체를 유발하는 주요 원인 중 하나이다. 다음 SQL을 실행하면 블로킹 세션을 조회할 수 있다.

SELECT    blocking_session,    session_id,    event,    wait_time  FROM    v$session  WHERE    blocking_session IS NOT NULL  ORDER BY    wait_time DESC;

 

• blocking_session: 다른 세션을 차단하는 블로킹 세션 ID.
• session_id: 대기 중인 세션의 ID.
• event: 대기 중인 이벤트.
• wait_time: 대기 시간(단위: 밀리초).

이 데이터를 활용하면, 블로킹 세션을 식별하고, 필요 시 해당 세션을 종료하여 문제를 해결할 수 있다.

---

2.4 디스크 I/O 부하가 높은 세션 조회

디스크 I/O 사용량이 많은 세션을 찾고, Full Table Scan 등을 최적화할 수 있다.

SELECT    session_id,    sql_id,    COUNT(*) AS io_waits  FROM    v$active_session_history  WHERE    wait_class = 'User I/O'    AND sample_time >= SYSDATE - (1 / 1440) -- 최근 1분간의 데이터 조회    GROUP BY session_id, sql_id ORDER BY io_waits DESC;

• wait_class = 'User I/O': 디스크에서 데이터를 읽거나 쓰는 동안 대기한 세션만 조회.
• io_waits: 특정 SQL이 디스크 I/O로 인해 지연된 횟수.

이 데이터를 활용하면, 디스크 I/O 사용량이 많은 SQL을 식별하여 인덱스를 최적화하거나 실행 계획을 변경할 수 있다.

---

2.5 특정 세션의 실행 SQL 상세 조회

특정 세션이 어떤 SQL을 실행하고 있는지 상세히 조회할 수 있다.

SELECT    a.session_id,    a.sql_id,    q.sql_text,    a.event,    a.wait_time  FROM    v$active_session_history a    JOIN v$sql q ON a.sql_id = q.sql_id  WHERE    a.session_id = 1234 -- 특정 세션 ID 입력    AND a.sample_time >= SYSDATE - (1/1440)   ORDER BY a.sample_time DESC;

 

• session_id = 1234: 특정 세션의 실행 SQL만 조회.
• sql_text: 해당 세션에서 실행 중인 SQL의 전체 텍스트.

이 데이터를 활용하면, 특정 세션이 실행하는 SQL이 비효율적인지 분석하고 최적화할 수 있다.

---

 

실시간 ASH 데이터를 조회하면 현재 실행 중인 세션의 활동을 즉각적으로 분석할 수 있으며, 데이터베이스 성능 문제를 신속하게 해결하는 데 중요한 역할을 한다.

 

특히, CPU 사용량이 높은 SQL, 디스크 I/O 부하가 심한 SQL, 블로킹 세션 등을 실시간으로 추적할 수 있으며, 이를 기반으로 실행 계획을 최적화하거나 세션을 관리하는 전략을 수립할 수 있다.