본문 바로가기
IT/AWR-ASH

ASH 데이터를 활용한 병목 현상 해결하기: 시스템 성능을 저하시킬 수 있는 병목 요소 분석.

by free-inf 2025. 2. 11.

서버 이관 프로젝트를 진행하는 과정에서 가장 중요한 목표 중 하나는 데이터베이스의 성능을 안정적으로 유지하는 것이다. 기존 서버와 새로운 서버의 하드웨어 및 소프트웨어 환경이 다를 수 있기 때문에, 이전에는 문제되지 않았던 병목(Bottleneck) 현상이 새롭게 발생할 가능성이 높다.

 

병목 현상은 CPU, 메모리, 디스크 I/O, 네트워크, 트랜잭션 락(Lock) 등의 요소에서 발생할 수 있으며, 특정 시간대에 집중적으로 나타나거나 특정 SQL이 실행될 때 급격히 증가할 수 있다. 이를 해결하지 않으면 SQL 실행 시간이 길어지고, 트랜잭션이 지연되며, 전체적인 서비스 성능이 저하될 수 있다.

 

이러한 병목 현상을 빠르게 감지하고 해결하기 위해서는 Oracle의 ASH(Active Session History) 데이터를 활용하는 것이 효과적이다. ASH 데이터를 분석하면 어떤 SQL이 리소스를 과도하게 사용하는지, 어떤 대기 이벤트가 많이 발생하는지, 특정 시간대에 성능이 저하되는 패턴이 있는지 등을 파악할 수 있다.

 

본 글에서는 ASH 데이터를 활용하여 병목 현상을 분석하고 해결하는 방법을 설명하고, 성능 최적화를 위한 실무적인 접근법을 제시한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 조기에 감지하고 대응할 수 있도록 한다.

1. ASH 데이터를 활용한 병목 현상 분석의 필요성

1.1 병목 현상이란?

병목 현상(Bottleneck)은 특정 자원이 과부하 상태가 되어 시스템 전체 성능을 저하시킬 때 발생하는 문제를 의미한다. 데이터베이스에서는 다음과 같은 요소에서 병목이 발생할 수 있다.

  1. CPU 병목: 특정 SQL이 CPU를 과도하게 사용하여 다른 작업이 지연되는 경우.
  2. 디스크 I/O 병목: Full Table Scan 또는 대량의 데이터를 읽고 쓰는 과정에서 디스크 부하가 발생하는 경우.
  3. 메모리 병목: 비효율적인 SQL 실행으로 인해 Buffer Cache 또는 Shared Pool이 부족해지는 경우.
  4. 트랜잭션 락 병목: 특정 세션이 데이터를 독점하여 다른 세션이 대기하는 경우.
  5. 네트워크 병목: 원격 데이터베이스와의 통신 속도가 느려지는 경우.

병목 현상이 지속되면 응답 시간이 증가하고, 트랜잭션이 실패하거나, 서비스가 중단될 위험이 커지므로, 이를 신속하게 감지하고 해결하는 것이 중요하다.

2. ASH 데이터를 활용한 병목 분석 방법

2.1 특정 시간 동안 CPU 사용량이 높은 SQL 조회

CPU 병목이 발생하는 경우, ASH 데이터를 활용하여 CPU 사용량이 높은 SQL을 분석할 수 있다.

 

SELECT 
  sql_id, 
  COUNT(*) AS cpu_usage 
FROM 
  v$active_session_history 
WHERE 
  session_state = 'ON CPU' 
  AND 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 
  cpu_usage DESC;
  • session_state = 'ON CPU': CPU에서 실행 중인 SQL만 조회.
  • COUNT(*) AS cpu_usage: 특정 SQL의 CPU 사용 빈도를 계산.

이 데이터를 활용하면 CPU를 과도하게 사용하는 SQL을 식별하고, 실행 계획을 최적화하여 성능을 개선할 수 있다.

2.2 디스크 I/O 병목 분석 – I/O 대기 시간이 긴 SQL 조회

디스크 I/O 병목이 발생하면 데이터 읽기/쓰기 속도가 느려지고 SQL 실행 시간이 증가할 수 있다.

SELECT 
  sql_id, 
  COUNT(*) AS io_waits 
FROM 
  v$active_session_history 
WHERE 
  wait_class = 'User I/O' 
  AND 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 
  io_waits DESC;
  • wait_class = 'User I/O': 디스크 I/O 대기로 인해 지연된 SQL만 조회.
  • COUNT(*) AS io_waits: 특정 SQL이 디스크 I/O로 인해 지연된 횟수.

이 데이터를 활용하면, 디스크 I/O를 과도하게 사용하는 SQL을 분석하고, 인덱스 적용 또는 실행 계획 최적화를 통해 성능을 개선할 수 있다.

2.3 트랜잭션 락 병목 분석 – 블로킹 세션 감지

트랜잭션 락이 발생하면 특정 세션이 데이터를 점유하고 있어 다른 세션이 대기하는 문제가 발생할 수 있다.

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: 대기 시간(밀리초).

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

3. ASH 데이터를 활용한 병목 해결 방법

3.1 CPU 사용량 최적화 – 실행 계획 분석 및 인덱스 활용

CPU 사용량이 높은 SQL이 있다면 실행 계획을 분석하고, Full Table Scan을 방지하기 위해 적절한 인덱스를 추가해야 한다.

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);
  • 결과: CPU 사용량이 감소하고 실행 속도가 향상됨.

3.2 디스크 I/O 최적화 – 테이블 파티셔닝 적용

디스크 I/O 부하를 줄이기 위해 테이블 파티셔닝을 적용하면 특정 데이터 범위만 조회할 수 있어 성능이 개선될 수 있다.

CREATE TABLE sales (
  sale_id NUMBER, sale_date DATE, amount NUMBER
) PARTITION BY RANGE (sale_date) (
  PARTITION p202401 
  VALUES 
    LESS THAN (
      TO_DATE('2024-02-01', 'YYYY-MM-DD')
    ), 
    PARTITION p202402 
  VALUES 
    LESS THAN (
      TO_DATE('2024-03-01', 'YYYY-MM-DD')
    )
);
  • 결과: 디스크 I/O 부하가 감소하고, 특정 날짜의 데이터만 조회하도록 최적화됨.

3.3 트랜잭션 락 최적화 – 커밋(Commit) 주기 조정

트랜잭션 크기를 조정하여 락이 길게 유지되지 않도록 설정할 수 있다.

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 데이터를 활용한 병목 분석은 데이터베이스 성능을 저하시킬 수 있는 요소를 실시간으로 감지하고 해결하는 필수적인 과정이다.

특정 SQL이 CPU, 디스크 I/O, 트랜잭션 락 등의 문제를 유발하는지 분석하고, 적절한 인덱스 적용, 실행 계획 최적화, 트랜잭션 조정 등의 기법을 활용하면 시스템 성능을 안정적으로 유지할 수 있다.

서버 이관 후에는 기존과 다른 성능 패턴이 나타날 가능성이 높으므로, 지속적인 모니터링과 최적화가 필수적이다. 이를 통해 데이터베이스의 안정성을 유지하고 최적의 성능을 확보할 수 있을 것이다.

저작자표시 비영리 변경금지

'IT > AWR-ASH' 카테고리의 다른 글

ASH 데이터 보관 기간 설정 및 관리: ASH 데이터의 보관 기간을 설정하고 관리하는 방법.  (0) 2025.02.14
Oracle RAC 환경에서 ASH 활용하기: 다중 노드 환경에서 세션 성능을 분석하는 방법.  (0) 2025.02.13
조인 최적화와 ASH 분석: 조인이 많은 SQL의 실행 패턴을 분석하여 최적화하는 방법.  (0) 2025.02.11
인덱스 미사용 SQL 분석: ASH에서 특정 SQL이 인덱스를 사용하고 있는지 확인하는 방법.  (0) 2025.02.11
ASH 데이터를 활용한 성능 추세 분석: 일정 기간 동안 성능 변화 감지 및 튜닝.  (0) 2025.02.10
SQL 실행 계획과 ASH 데이터 비교 분석: 실행 계획과 세션 데이터를 함께 분석하여 문제 해결.  (0) 2025.02.10
실시간 ASH 데이터를 조회하는 방법: 현재 진행 중인 세션 활동을 조회하는 SQL.  (0) 2025.02.10
ASH에서 특정 세션 분석하기: 문제를 일으키는 특정 세션을 추적하고 해결하는 방법  (0) 2025.02.10

관련글

티스토리툴바