ASH 보고서에서 Session Blocking 감지: 다른 세션을 차단하는 블로킹 세션을 감지하는 방법.

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스의 성능을 유지하고 최적화하는 것은 매우 중요한 작업이다. 새로운 서버 환경에서는 기존 환경과 비교하여 세션 동작 방식, 트랜잭션 처리 속도, 리소스 경합 등이 달라질 수 있으며, 이에 따라 예상치 못한 성능 저하가 발생할 가능성이 높다.

 

특히 Session Blocking(세션 블로킹)은 데이터베이스에서 성능 저하를 유발하는 대표적인 문제 중 하나다. 블로킹 세션이란 한 세션(Session A)이 특정 리소스(예: 테이블의 특정 행)를 점유하고 있는 동안, 다른 세션(Session B)이 동일한 리소스를 요청하면서 대기 상태가 되는 상황을 의미한다. 블로킹이 심할 경우, 다수의 세션이 대기하면서 전체적인 트랜잭션 처리 속도가 저하되고, 애플리케이션 응답 시간이 길어질 수 있다.

 

이러한 문제를 해결하기 위해 Oracle ASH(Active Session History) 데이터를 활용하여 블로킹 세션을 감지하고 분석하는 것이 중요하다. ASH 데이터는 세션 활동을 실시간으로 기록하며, 특정 세션이 다른 세션을 차단하고 있는지 분석하는 데 유용한 정보를 제공한다.

 

본 글에서는 ASH 데이터를 활용하여 블로킹 세션을 감지하는 방법과, 블로킹 세션을 해결하기 위한 최적화 전략을 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 저하 문제를 효과적으로 감지하고 해결할 수 있도록 한다.

---

Session Blocking이 발생하는 주요 원인

1. 트랜잭션이 길어지면서 발생하는 블로킹

• 긴 트랜잭션이 실행되는 동안, 다른 세션이 동일한 리소스에 접근하려 하면 블로킹이 발생할 수 있다.
• 대량의 데이터를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)할 때, 트랜잭션이 커밋(COMMIT)되지 않으면 다른 세션이 해당 리소스를 기다려야 한다.

2. 테이블 및 행 단위의 락(Lock) 경합

• 한 세션이 특정 테이블의 행을 수정하는 동안, 다른 세션이 해당 행에 접근하면 락이 발생하면서 블로킹 상태가 된다.
• 특히 테이블 전체에 락을 거는 DDL(예: ALTER TABLE, CREATE INDEX)이나 대량 트랜잭션이 실행될 경우 블로킹 시간이 길어질 수 있다.

3. 인덱스와 관련된 블로킹

• 인덱스를 갱신하는 작업 중, 다른 세션이 해당 인덱스를 참조하는 경우 블로킹이 발생할 수 있다.
• 인덱스 리빌드(ALTER INDEX REBUILD) 작업이 진행될 때, 다른 세션이 동일한 인덱스를 사용하면 블로킹이 증가할 가능성이 크다.

4. 동시성 문제로 인한 블로킹

• 멀티 세션 환경에서 여러 프로세스가 동시에 동일한 리소스에 접근할 경우, 데이터 경합으로 인해 블로킹이 발생할 가능성이 높다.
• 블로킹이 누적되면 데드락(Deadlock)으로 이어질 수도 있다.

---

ASH 데이터를 활용한 블로킹 세션 감지 방법

1. 현재 블로킹 상태인 세션 조회

현재 블로킹 상태인 세션을 분석하려면 ASH 데이터를 활용하여 블로킹 세션과 대기 중인 세션을 조회할 수 있다.

SELECT    blocking_session,    session_id,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    session_state = 'WAITING'    AND blocking_session IS NOT NULL    AND sample_time >= SYSDATE - (10 / 1440) -- 최근 10분간 조회  GROUP BY    blocking_session,    session_id  ORDER BY    wait_count DESC;

• blocking_session → 다른 세션을 차단하고 있는 세션의 ID.
• session_id → 블로킹으로 인해 대기 중인 세션의 ID.
• wait_count → 블로킹 대기 횟수.

이 데이터를 활용하면 가장 많은 블로킹을 발생시키는 세션을 식별하고, 해당 세션이 어떤 트랜잭션을 실행 중인지 분석할 수 있다.

---

2. 블로킹이 발생한 SQL 조회

블로킹을 유발한 SQL을 분석하려면 해당 세션에서 실행된 SQL을 조회해야 한다.

SELECT    sql_id,    blocking_session,    session_id,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    session_state = 'WAITING'    AND blocking_session IS NOT NULL    AND sample_time >= SYSDATE - (10 / 1440) -- 최근 10분간 조회  GROUP BY    sql_id,    blocking_session,    session_id  ORDER BY    wait_count DESC;

• sql_id → 블로킹을 유발한 SQL의 ID.
• blocking_session → 블로킹을 발생시킨 세션의 ID.

이 데이터를 활용하면 블로킹의 원인이 되는 SQL을 식별하고, 실행 계획을 최적화하여 블로킹 시간을 줄일 수 있다.

---

3. 블로킹이 많이 발생하는 테이블 조회

특정 테이블에서 블로킹이 자주 발생하는 경우, 해당 테이블에서 실행된 트랜잭션을 분석할 필요가 있다.

SELECT    object_name,    blocking_session,    COUNT(*) AS block_count  FROM    v$active_session_history ash    JOIN dba_objects obj ON ash.current_obj # = obj.object_id WHERE ash.session_state = 'WAITING' AND blocking_session IS NOT NULL AND sample_time >= SYSDATE - (10/1440) -- 최근 10분간 조회  GROUP BY    object_name,    blocking_session  ORDER BY    block_count DESC;

• object_name → 블로킹이 자주 발생하는 테이블 이름.
• block_count → 해당 테이블에서 발생한 블로킹 횟수.

이 데이터를 활용하면 블로킹이 자주 발생하는 테이블을 식별하고, 해당 테이블의 트랜잭션 전략을 조정할 수 있다.

---

블로킹 세션 해결을 위한 최적화 전략

1. 트랜잭션 크기 조정 및 커밋 빈도 증가

• 트랜잭션 크기를 줄이고, 변경 사항을 주기적으로 커밋하면 블로킹이 줄어든다.
• 대량의 데이터를 수정하는 작업은 여러 개의 작은 트랜잭션으로 나누어 수행하는 것이 좋다.

COMMIT;

---

2. 인덱스 활용 및 실행 계획 최적화

• 인덱스를 활용하여 특정 행만 조회하면 불필요한 테이블 락을 방지할 수 있다.
• 실행 계획(EXPLAIN PLAN)을 분석하여 불필요한 테이블 스캔을 줄인다.

CREATE INDEX emp_dept_idx ON employees(department_id);

---

3. 트랜잭션 격리 수준 조정

• 락 대기를 줄이기 위해 READ COMMITTED 격리 수준을 활용하면 블로킹 시간을 줄일 수 있다.

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

---

4. Deadlock 예방을 위한 트랜잭션 순서 조정

• 트랜잭션이 동일한 순서로 리소스에 접근하도록 변경하면 데드락(Deadlock) 가능성을 줄일 수 있다.
• 락 타임아웃(LOCK TIMEOUT)을 설정하여 일정 시간이 지나면 자동으로 롤백되도록 설정할 수도 있다.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10;

---

ASH 데이터를 활용하면 블로킹 세션을 실시간으로 감지하고, 블로킹이 자주 발생하는 SQL과 테이블을 분석할 수 있다.

 

특히, 트랜잭션 크기 조정, 인덱스 최적화, 락 최소화 등의 방법을 적용하면 블로킹 시간을 줄이고 데이터베이스 성능을 크게 개선할 수 있다.