ASH 데이터를 활용한 세션 히스토리 조회: 특정 사용자의 세션 히스토리를 조회하는 방법.

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스의 성능을 안정적으로 유지하는 것은 필수적인 과제다. 특히, 특정 사용자의 세션이 비정상적으로 오래 실행되거나 시스템 리소스를 과도하게 사용하는 경우, 해당 사용자의 세션 히스토리를 분석하는 것이 중요하다.

 

Oracle에서는 ASH(Active Session History) 데이터를 활용하여 특정 사용자의 세션이 어떤 SQL을 실행했는지, 어떤 대기 이벤트가 발생했는지, CPU 및 디스크 I/O를 얼마나 사용했는지 등을 실시간으로 분석할 수 있다.

ASH는 초 단위로 활성 세션 데이터를 기록하는 기능을 제공하며, 이를 활용하면 특정 사용자의 세션 활동을 추적하여 성능 문제를 진단하고 최적화할 수 있다. 이를 통해 서버 이관 후 특정 사용자의 성능 문제가 발생하는 경우 원인을 신속하게 분석하고 해결할 수 있다.

 

본 글에서는 ASH 데이터를 활용하여 특정 사용자의 세션 히스토리를 조회하는 방법과, 이를 활용한 성능 분석 기법을 상세히 설명한다.

---

특정 사용자의 세션 히스토리를 조회하는 이유

비효율적인 SQL 탐색 및 최적화

• 특정 사용자의 세션이 비효율적인 SQL을 실행하면 CPU 부하가 증가하고, 트랜잭션이 지연되면서 전체 시스템 성능이 저하될 수 있다.
• ASH 데이터를 활용하면 해당 사용자가 실행한 SQL을 조회하고 실행 계획을 최적화할 수 있다.

세션 지연 원인 분석

• 특정 사용자의 세션이 응답 시간이 길어지거나, 비정상적으로 오래 실행되는 경우, ASH 데이터를 분석하여 어떤 대기 이벤트가 발생했는지 확인할 수 있다.
• 디스크 I/O, 네트워크 대기, 블로킹 세션 등의 문제를 실시간으로 파악할 수 있다.

블로킹 세션(Blocking Session) 탐색

• 특정 사용자의 세션이 다른 세션을 차단하여 데드락(Deadlock) 또는 트랜잭션 지연이 발생하는 경우, ASH 데이터를 활용하여 블로킹 세션을 찾아 해결할 수 있다.

사용자 활동 로그 분석

• 특정 사용자가 언제 어떤 작업을 수행했는지 분석하여, 데이터 무결성 및 보안 로그를 검토할 수 있다.

---

ASH 데이터를 활용한 특정 사용자 세션 조회 방법

1. 특정 사용자의 최근 세션 조회

특정 사용자가 최근에 실행한 세션 정보를 조회하려면 ASH 데이터를 활용하여 해당 사용자의 세션 활동을 확인할 수 있다.

SELECT    session_id,    sql_id,    user_id,    sample_time,    session_state,    wait_class,    event  FROM    v$active_session_history  WHERE    user_id = (     SELECT        user_id      FROM        dba_users      WHERE        username = '사용자명'   )    AND sample_time >= SYSDATE - (10 / 1440) -- 최근 10분 조회    ORDER BY sample_time DESC;

 

• session_id: 사용자의 세션 ID.
• sql_id: 사용자가 실행한 SQL ID.
• user_id: 사용자 ID.
• sample_time: 해당 세션이 기록된 시점.
• session_state: 'ON CPU'(실행 중) 또는 'WAITING'(대기 중) 상태.
• wait_class & event: 해당 세션에서 발생한 대기 이벤트.

이 데이터를 활용하면 특정 사용자의 최근 실행 SQL과 세션 상태를 확인할 수 있다.

---

2. 특정 사용자의 실행 SQL 내역 조회

특정 사용자가 실행한 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.user_id = (     SELECT        user_id      FROM        dba_users      WHERE        username = '사용자명'   )    AND a.sample_time >= SYSDATE - (30 / 1440) -- 최근 30분 조회  ORDER BY a.sample_time DESC;

• sql_text: 사용자가 실행한 SQL의 원문.
• event: 실행 중 발생한 대기 이벤트.
• wait_time: 대기 시간(밀리초).

이 데이터를 분석하면 특정 사용자가 실행한 SQL이 비효율적인지 확인하고, 실행 계획을 최적화할 수 있다.

---

3. 특정 사용자의 CPU 사용량 분석

특정 사용자가 CPU를 과도하게 사용하는 경우, 이를 분석하여 최적화할 필요가 있다.

SELECT    session_id,    sql_id,    COUNT(*) AS cpu_usage  FROM    v$active_session_history  WHERE    user_id = (     SELECT        user_id      FROM        dba_users      WHERE        username = '사용자명'   )    AND session_state = 'ON CPU'    AND sample_time >= SYSDATE - (30 / 1440) -- 최근 30분 조회    GROUP BY session_id, sql_id ORDER BY cpu_usage DESC;

• cpu_usage: 특정 SQL이 실행된 횟수.

이 데이터를 활용하면 특정 사용자가 실행하는 SQL 중 CPU 부하가 높은 쿼리를 찾아 최적화할 수 있다.

---

4. 특정 사용자의 대기 이벤트 분석

특정 사용자가 실행한 SQL이 어떤 대기 이벤트로 인해 지연되고 있는지 분석하면, 성능 문제를 해결하는 데 도움이 된다.

SELECT    session_id,    event,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    user_id = (     SELECT        user_id      FROM        dba_users      WHERE        username = '사용자명'   )    AND session_state = 'WAITING'    AND sample_time >= SYSDATE - (30 / 1440) -- 최근 30분 조회    GROUP BY session_id, event ORDER BY wait_count DESC;

• event: 대기 이벤트 종류.
• wait_count: 해당 이벤트가 발생한 횟수.

대표적인 대기 이벤트와 의미는 다음과 같다.

• db file sequential read → 인덱스 스캔으로 인한 I/O 대기
• db file scattered read → Full Table Scan으로 인한 I/O 대기
• enq: TX - row lock contention → 트랜잭션 락 대기
• log file sync → REDO 로그 기록 대기

이 데이터를 활용하면 특정 사용자의 SQL이 왜 지연되는지 파악하고, 대기 이벤트를 줄이기 위한 최적화 작업을 수행할 수 있다.

---

5. 특정 사용자의 블로킹 세션(Blocking Session) 확인

특정 사용자가 다른 세션을 블로킹하여 성능 저하를 유발하는지 확인하려면 다음 SQL을 실행한다.

SELECT    blocking_session,    session_id,    event,    wait_time  FROM    v$session  WHERE    blocking_session IS NOT NULL    AND user_id = (     SELECT        user_id      FROM        dba_users      WHERE        username = '사용자명'   )  ORDER BY    wait_time DESC;

• blocking_session: 다른 세션을 차단하는 블로킹 세션 ID.
• wait_time: 대기 시간(밀리초).

이 데이터를 활용하면 특정 사용자가 실행한 SQL이 다른 트랜잭션을 차단하고 있는지 확인할 수 있다.

---

ASH 데이터를 활용하면 특정 사용자의 세션 히스토리를 실시간으로 조회하고, SQL 실행 내역과 대기 이벤트를 분석하여 성능 문제를 신속하게 해결할 수 있다.

 

특히, CPU 사용량이 높은 SQL을 식별하고, 특정 대기 이벤트로 인해 세션이 지연되는 원인을 파악하며, 블로킹 세션을 찾아 해결하면 데이터베이스 성능을 최적화할 수 있다.