ASH에서 특정 세션 분석하기: 문제를 일으키는 특정 세션을 추적하고 해결하는 방법

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스 성능을 유지하는 것은 가장 중요한 과제 중 하나다. 서버 환경이 변경되면 기존과는 다른 성능 패턴이 나타날 수 있으며, 특정 세션(Session)이 과도한 리소스를 사용하거나, 트랜잭션 대기를 유발하는 등의 문제를 일으킬 가능성이 높다.

 

특정 세션이 CPU, 메모리, 디스크 I/O를 과도하게 사용하는 경우, 전체적인 시스템 성능이 저하될 수 있다. 또한, 특정 세션이 다른 세션을 블로킹(Blocking)하거나, 장시간 실행되는 쿼리를 유지하는 경우 데이터베이스의 응답 속도가 느려지고, 서비스 장애로 이어질 위험도 커진다.

 

이러한 문제를 해결하기 위해 ASH(Active Session History) 데이터를 활용하여 특정 세션을 분석하고 문제를 해결하는 과정이 필수적이다. 본 글에서는 ASH를 활용하여 특정 세션을 추적하는 방법과 성능 문제를 해결하는 실무적인 접근법을 상세히 설명한다.

---

1. 특정 세션 분석이 필요한 이유

1.1 특정 세션이 성능 저하를 유발하는 주요 원인

특정 세션이 성능 문제를 일으키는 주요 원인은 다음과 같다.

1. CPU 사용량 과다
   • 특정 세션이 CPU를 독점적으로 사용하여 다른 세션이 실행되기 어려운 경우.
   • 복잡한 SQL 또는 무거운 연산이 포함된 쿼리를 실행하는 경우.
2. 디스크 I/O 과부하
   • 특정 세션이 대량의 데이터를 읽거나 쓰면서 디스크 I/O 부하를 발생시키는 경우.
   • Full Table Scan이 빈번한 SQL을 실행하는 경우.
3. 락(Lock) 대기 유발
   • 특정 세션이 트랜잭션을 길게 유지하면서 다른 세션이 락 대기로 인해 지연되는 경우.
   • 대량의 업데이트, 삭제 작업을 수행하는 경우.
4. 블로킹 세션(Blocking Session) 발생
   • 특정 세션이 다른 세션을 차단하면서, 전체적인 응답 속도가 느려지는 경우.
   • 대량 트랜잭션이 발생할 때 자주 나타남.

---

2. ASH를 활용한 특정 세션 분석 방법

ASH 데이터를 활용하면 문제를 일으키는 특정 세션을 실시간으로 추적하고, 해당 세션이 어떤 SQL을 실행하고 있는지 확인할 수 있다.

2.1 특정 시간 동안 활성화된 세션 조회

다음 SQL을 실행하면 특정 시간 동안 활성화된 세션 목록과 실행 중인 SQL ID를 확인할 수 있다.

SELECT    session_id,    sql_id,    COUNT(*) AS active_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    session_id,    sql_id  ORDER BY    active_count DESC;

 

• session_id: 특정 세션의 고유 ID.
• sql_id: 실행 중인 SQL의 고유 ID.
• active_count: 해당 세션이 활성화된 횟수(즉, 실행된 SQL의 빈도).

이 데이터를 분석하면, 가장 자주 실행되거나 리소스를 많이 소비하는 세션을 찾아낼 수 있다.

---

2.2 특정 세션의 대기 이벤트(Wait Event) 조회

특정 세션이 CPU를 과도하게 사용하거나, 디스크 I/O 또는 트랜잭션 대기로 인해 성능 저하를 유발하는 경우 해당 세션의 대기 이벤트를 분석해야 한다.

SELECT    session_id,    event,    COUNT(*) AS wait_count  FROM   v$active_session_history  WHERE   session_id = 1234 -- 특정 세션 ID 입력  GROUP BY session_id, event ORDER BY wait_count DESC;

 

• event: 해당 세션에서 가장 많이 발생한 대기 이벤트.
• wait_count: 대기 이벤트가 발생한 횟수.

이 데이터를 활용하면, 특정 세션이 CPU, 디스크 I/O, 락 대기 등 어떤 원인으로 인해 성능 저하를 유발하는지 파악할 수 있다.

---

2.3 블로킹 세션(Blocking Session) 감지

특정 세션이 다른 세션을 차단하면서 트랜잭션 대기를 발생시키고 있는지 확인하려면 다음 SQL을 실행한다.

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

 

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

이 데이터를 활용하면, 블로킹 세션이 성능 문제를 유발하고 있는지 확인하고, 필요하면 해당 세션을 종료하는 등의 조치를 취할 수 있다.

---

3. 특정 세션의 문제 해결 방법

3.1 CPU 사용량이 높은 세션 최적화

특정 세션이 CPU를 과도하게 사용하는 경우, 실행 중인 SQL을 최적화해야 한다.

-- 실행 중인 SQL의 실행 계획 확인  EXPLAIN PLAN FOR  SELECT    *  FROM    employees  WHERE    department = 'Sales'; SELECT    *  FROM    TABLE(DBMS_XPLAN.DISPLAY);

• 실행 계획을 확인하여 Full Table Scan이 발생하면 인덱스를 추가하여 성능을 개선할 수 있다.

CREATE INDEX emp_dept_idx ON employees(department);

• 결과: 불필요한 연산을 줄이고 Index Scan을 활용하여 실행 속도를 개선.

---

3.2 블로킹 세션 강제 종료(Kill Session)

블로킹 세션이 장시간 유지되는 경우, 관리자가 해당 세션을 강제 종료할 수 있다.

ALTER SYSTEM KILL SESSION '1234,56789';

• 1234,56789: 종료할 세션의 SID와 SERIAL#.

강제 종료 전에는 세션이 수행 중인 작업이 중요한지 확인하고, 가능하면 트랜잭션을 커밋 또는 롤백한 후 종료하는 것이 바람직하다.

---

3.3 트랜잭션 대기 시간 단축

트랜잭션 대기가 길어지는 경우, 트랜잭션 크기를 조정하거나, 커밋(Commit) 주기를 최적화하여 락 유지 시간을 줄일 수 있다.

-- 비효율적인 SQL (트랜잭션이 너무 길게 유지됨)  BEGIN  UPDATE    employees  SET    salary = salary * 1.1  WHERE    department = 'Sales'; -- 많은 데이터가 한 번에 수정됨 COMMIT; END; -- 최적화된 SQL (트랜잭션 크기를 조정)  BEGIN FOR emp IN (   SELECT      emp_id    FROM      employees    WHERE      department = 'Sales' ) LOOP  UPDATE    employees  SET    salary = salary * 1.1  WHERE    emp_id = emp.emp_id; COMMIT; -- 개별적으로 커밋하여 락 유지 시간 단축 END LOOP; END;

 

ASH 데이터를 활용한 특정 세션 분석은 데이터베이스 성능 저하의 주요 원인을 식별하고 최적화하는 데 필수적인 과정이다.

특정 세션이 CPU를 과도하게 사용하거나, 디스크 I/O 부하를 발생시키거나, 트랜잭션 대기로 인해 시스템 성능을 저하시킬 경우, ASH 데이터를 활용하여 문제를 추적하고 해결할 수 있다.