ASH를 이용한 대기 시간(WAIT TIME) 최적화: SQL 실행 중 발생하는 대기 시간을 줄이는 방법.

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스 성능이 예상과 다르게 동작하는 경우가 발생할 수 있다. 특히, SQL 실행 시간이 길어지는 원인 중 하나는 다양한 대기 이벤트(WAIT EVENT)로 인해 SQL이 지연되는 현상이다.

 

Oracle 데이터베이스에서 SQL이 실행될 때 CPU에서 바로 실행되지 않고, I/O 작업이나 락(Lock) 등의 이유로 일정 시간 대기하는 경우가 있다. 이 대기 시간이 증가하면 전체 트랜잭션 성능이 저하되고, 애플리케이션의 응답 속도도 느려질 수 있다.

 

이를 해결하기 위해 ASH(Active Session History) 데이터를 활용하여 대기 시간이 긴 SQL을 분석하고, 원인을 파악하여 최적화하는 것이 중요하다. ASH 데이터는 초 단위로 활성 세션 정보를 기록하고, SQL 실행 중 발생하는 대기 이벤트를 분석할 수 있도록 지원한다. 이를 활용하면 대기 시간이 길어지는 원인을 파악하고, 이를 최소화하여 데이터베이스 성능을 향상시킬 수 있다.

 

본 글에서는 ASH 데이터를 기반으로 SQL 실행 중 발생하는 대기 시간을 분석하는 방법과, 이를 최적화하는 전략을 상세히 설명한다. 이를 통해 서버 이관 후 예상치 못한 성능 저하 문제를 효과적으로 감지하고 해결할 수 있도록 한다.

---

대기 시간(WAIT TIME)이란?

대기 시간(WAIT TIME)은 SQL이 실행될 때 즉시 실행되지 못하고 특정 리소스를 기다리는 시간을 의미한다. Oracle에서는 이러한 대기 상태를 대기 이벤트(WAIT EVENT)로 기록하며, SQL이 어떤 이유로 지연되었는지 분석할 수 있다.

대기 시간은 대기 이벤트의 종류에 따라 크게 세 가지로 구분할 수 있다.

1. CPU 대기 (CPU Wait)

• SQL이 실행되려면 CPU 리소스를 할당받아야 하지만, CPU 사용률이 높으면 실행이 지연될 수 있다.
• CPU 대기가 많은 경우, 특정 SQL이 CPU를 과도하게 점유하거나, 병렬 처리가 제대로 작동하지 않는 경우일 수 있다.

2. I/O 대기 (I/O Wait)

• 디스크에서 데이터를 읽거나 쓰는 과정에서 대기 시간이 발생하는 경우.
• 특정 SQL이 Full Table Scan을 수행하거나, 인덱스를 제대로 활용하지 못하는 경우 I/O 대기가 증가할 가능성이 높다.

3. 락(Lock) 및 기타 대기 (Concurrency Wait & Other Waits)

• 여러 트랜잭션이 동시에 동일한 데이터에 접근하려 할 때, 충돌이 발생하면 특정 트랜잭션이 대기 상태가 될 수 있다.
• 락이 걸린 테이블을 다른 세션이 액세스하려 하면, 해당 세션이 대기하면서 성능 저하가 발생할 수 있다.

---

ASH 데이터를 활용한 대기 시간 분석 방법

1. 대기 시간이 긴 SQL 조회

특정 SQL이 실행될 때 대기 시간이 긴 SQL을 조회하려면 ASH 데이터를 활용하여 분석할 수 있다.

SELECT    sql_id,    event,    COUNT(*) AS wait_time  FROM    v$active_session_history  WHERE    session_state = 'WAITING'    AND sample_time >= SYSDATE - (30 / 1440) -- 최근 30분 조회  GROUP BY sql_id, event ORDER BY wait_time DESC;

• sql_id → 실행된 SQL의 ID.
• event → 해당 SQL이 대기한 이벤트.
• wait_time → 해당 SQL이 대기한 횟수.

이 데이터를 활용하면 가장 대기 시간이 긴 SQL을 식별할 수 있으며, SQL 실행 계획을 분석하여 최적화할 수 있다.

---

2. CPU 대기 시간이 높은 SQL 조회

CPU 대기 시간이 긴 SQL을 분석하려면 CPU 사용량이 높은 세션을 조회해야 한다.

SELECT    sql_id,    COUNT(*) AS cpu_waits  FROM    v$active_session_history  WHERE    session_state = 'WAITING'    AND wait_class = 'CPU'    AND sample_time >= SYSDATE - (30 / 1440)  GROUP BY    sql_id  ORDER BY    cpu_waits DESC;

• cpu_waits → 해당 SQL이 CPU 대기로 인해 실행이 지연된 횟수.

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

---

3. I/O 대기 시간이 높은 SQL 조회

디스크 I/O 대기로 인해 실행이 지연되는 SQL을 분석하려면 User I/O 관련 대기 이벤트를 조회해야 한다.

SELECT    sql_id,    COUNT(*) AS io_waits  FROM    v$active_session_history  WHERE    wait_class = 'User I/O'    AND sample_time >= SYSDATE - (30 / 1440)  GROUP BY    sql_id  ORDER BY    io_waits DESC;

• io_waits → 특정 SQL이 디스크 I/O 대기로 인해 지연된 횟수.

이 데이터를 활용하면 디스크 부하를 유발하는 SQL을 찾아 인덱스를 추가하거나, 실행 계획을 조정하여 디스크 사용량을 줄일 수 있다.

---

4. 락(Lock) 대기 시간이 긴 SQL 조회

다른 트랜잭션과의 충돌로 인해 SQL 실행이 지연되는 경우, 락 대기 시간이 긴 SQL을 분석해야 한다.

SELECT    sql_id,    COUNT(*) AS lock_waits  FROM    v$active_session_history  WHERE    wait_class = 'Concurrency'    AND sample_time >= SYSDATE - (30 / 1440)  GROUP BY    sql_id  ORDER BY    lock_waits DESC;

• lock_waits → 특정 SQL이 락 대기로 인해 실행이 지연된 횟수.

이 데이터를 활용하면 락을 유발하는 트랜잭션을 찾아 최적화하고, 필요하면 트랜잭션 격리 수준을 조정할 수 있다.

---

대기 시간 최적화를 위한 해결 방법

1. CPU 대기 시간 최적화

• CPU 사용량이 높은 SQL을 분석하고 실행 계획을 최적화.
• 병렬 처리(PARALLEL 힌트) 적용을 통해 CPU 부하를 분산.
• 불필요한 계산을 줄이도록 SQL을 최적화.

SELECT /*+ PARALLEL(emp,4) */ * FROM employees emp;

---

2. I/O 대기 시간 최적화

• 자주 조회되는 테이블에 인덱스를 추가하여 Full Table Scan을 방지.
• 대량의 데이터를 처리하는 SQL을 배치 작업으로 수행하여 부하를 줄임.
• I/O 부하가 큰 테이블을 파티셔닝하여 디스크 접근 효율을 향상.

CREATE INDEX emp_dept_idx ON employees(department);

---

3. 락 대기 시간 최적화

• 락이 자주 발생하는 테이블을 분석하고, 트랜잭션 크기를 줄여 충돌을 최소화.
• 락을 최소화하기 위해 READ COMMITTED 격리 수준을 활용.

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

---

ASH 데이터를 활용하면 데이터베이스에서 발생하는 대기 시간을 실시간으로 분석하고 최적화할 수 있다.

특히, CPU, I/O, 락 대기 시간이 높은 SQL을 식별하고 실행 계획을 최적화하면 SQL 성능을 크게 개선할 수 있다.