ASH 데이터를 활용한 SQL 실행 패턴 분석: ASH 데이터를 통해 SQL의 실행 주기와 패턴 분석.

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스의 성능을 지속적으로 모니터링하고 최적화하는 것은 매우 중요한 작업이다. 특히, SQL 실행 패턴을 분석하는 것은 데이터베이스 성능 최적화의 핵심 요소 중 하나다.

 

SQL 실행 패턴을 분석하면 어떤 SQL이 자주 실행되는지, 특정 시간대에 부하가 집중되는지, 실행 시간이 예상보다 길어지는지 등을 파악할 수 있다. 이를 통해 자주 실행되는 SQL을 튜닝하거나, 피크 타임(peak time)에 부하를 분산하여 시스템의 안정성을 확보할 수 있다.

 

Oracle의 ASH(Active Session History) 데이터는 초 단위로 활성 세션 정보를 기록하며, SQL 실행 패턴을 분석하는 데 매우 유용한 데이터를 제공한다. ASH 데이터를 활용하면 실시간 및 과거 데이터를 기반으로 SQL 실행 패턴을 분석하고, 특정 SQL이 자주 실행되는 시간대나 특정 이벤트로 인해 지연되는 경우를 효과적으로 파악할 수 있다.

 

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

---

SQL 실행 패턴 분석의 필요성

1. 특정 SQL의 실행 빈도 및 주기 분석

• 자주 실행되는 SQL을 분석하면 시스템 부하를 초래하는 주요 원인을 파악할 수 있다.
• 동일한 SQL이 너무 자주 실행되는 경우 불필요한 반복 실행을 줄이기 위한 캐싱 전략을 고려할 수 있다.

2. 피크 타임(peak time) 분석

• SQL 실행이 특정 시간대에 집중되는 경우, 피크 타임을 분석하여 부하 분산 전략을 적용할 수 있다.
• ASH 데이터를 활용하면 특정 시간대에 실행된 SQL을 조회하고, 부하가 증가하는 시점을 파악할 수 있다.

3. 장시간 실행되는 SQL 탐색 및 최적화

• 실행 시간이 긴 SQL을 찾아 튜닝하면 전체적인 시스템 성능을 개선할 수 있다.
• 실행 계획(EXPLAIN PLAN) 분석을 통해 비효율적인 인덱스 사용, Full Table Scan, 불필요한 조인 등을 최적화할 수 있다.

4. 대기 이벤트(WAIT EVENT) 분석을 통한 실행 패턴 최적화

• 특정 SQL이 I/O 대기, CPU 대기, 락 대기 등으로 인해 실행 시간이 길어지는 경우 이를 분석하고 최적화할 수 있다.

---

ASH 데이터를 활용한 SQL 실행 패턴 분석 방법

1. 특정 SQL의 실행 빈도 분석

특정 SQL이 얼마나 자주 실행되는지를 분석하려면 ASH 데이터를 활용하여 SQL 실행 횟수를 조회할 수 있다.

SELECT    sql_id,    COUNT(*) AS execution_count  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (1) -- 최근 24시간 조회 GROUP BY    sql_id  ORDER BY    execution_count DESC;

 

• execution_count → 특정 SQL이 실행된 횟수.

이 데이터를 활용하면 자주 실행되는 SQL을 파악하고, 실행 빈도를 줄이거나 효율적인 실행 방식을 고려할 수 있다.

---

2. 특정 시간대의 SQL 실행 패턴 분석

SQL이 특정 시간대에 집중적으로 실행되는지 확인하려면 ASH 데이터를 활용하여 시간별 실행 패턴을 분석할 수 있다.

SELECT    TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS time_slot,    COUNT(*) AS execution_count  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (7) -- 최근 7일간 데이터 조회 GROUP BY    TO_CHAR(sample_time, 'YYYY-MM-DD HH24')  ORDER BY    time_slot;

• time_slot → 특정 시간대의 SQL 실행 빈도를 분석하는 데 활용.

이 데이터를 활용하면 SQL 실행이 특정 시간대에 집중되는 패턴을 분석하고, 트래픽이 높은 시간대에 부하를 분산할 수 있는 전략을 세울 수 있다.

---

3. 실행 시간이 긴 SQL 조회

실행 시간이 길어지는 SQL을 분석하려면 ASH 데이터를 활용하여 평균 실행 시간을 계산할 수 있다.

SELECT    sql_id,    COUNT(*) AS execution_count,    AVG(elapsed_time) AS avg_execution_time  FROM    v$active_session_history  WHERE    session_state = 'WAITING'    AND sample_time >= SYSDATE - (1) -- 최근 24시간 조회  GROUP BY sql_id ORDER BY avg_execution_time DESC;

• avg_execution_time → 해당 SQL의 평균 실행 시간.

이 데이터를 활용하면 실행 시간이 비정상적으로 긴 SQL을 식별하고, 실행 계획을 최적화하여 성능을 개선할 수 있다.

---

4. 특정 SQL의 대기 이벤트 분석

SQL 실행 중 발생하는 대기 이벤트를 분석하려면 ASH 데이터를 활용하여 특정 SQL이 대기한 이벤트를 조회할 수 있다.

SELECT    sql_id,    event,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (1) -- 최근 24시간 조회 GROUP BY    sql_id,    event  ORDER BY    wait_count DESC;

 

• event → SQL 실행 시 발생한 주요 대기 이벤트.
• wait_count → 특정 대기 이벤트가 발생한 횟수.

이 데이터를 활용하면 SQL 실행이 지연되는 주요 원인을 파악하고, 불필요한 대기 시간을 줄이기 위한 최적화 작업을 수행할 수 있다.

---

SQL 실행 패턴 최적화를 위한 해결 방법

1. 자주 실행되는 SQL 최적화

• 자주 실행되는 SQL을 캐싱하여 불필요한 실행을 줄인다.
• 애플리케이션에서 동일한 데이터를 반복 조회하는 경우, 결과를 캐싱하여 데이터베이스 부하를 줄일 수 있다.
• 자주 사용되는 SQL은 MATERIALIZED VIEW를 활용하여 성능을 향상.

CREATE MATERIALIZED VIEW emp_summary AS  SELECT    department_id,    COUNT(*)  FROM    employees  GROUP BY    department_id;

 

---

2. 피크 타임 부하 분산

• 트래픽이 높은 시간대에 배치 작업을 피하거나, 트랜잭션을 분산하여 실행.
• 리소스 사용량이 많은 작업은 오프 피크 타임(off-peak time)으로 조정.

ALTER SYSTEM SET resource_manager_plan = 'LOW_PRIORITY_WORKLOAD_PLAN';

---

3. 실행 시간이 긴 SQL 튜닝

• 풀 테이블 스캔(Full Table Scan)을 방지하고 인덱스를 활용하도록 SQL을 최적화.
• 조인 방식 변경(Nested Loop Join 활용), 인덱스 추가 등을 통해 실행 속도를 향상.

CREATE INDEX emp_dept_idx ON employees(department_id);

---

4. 대기 이벤트 감소

• I/O 부하를 줄이기 위해 테이블 파티셔닝 적용.
• 락 대기를 줄이기 위해 트랜잭션 크기를 줄이고, READ COMMITTED 격리 수준을 활용.

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

---

ASH 데이터를 활용하면 SQL 실행 패턴을 실시간으로 분석하고, 실행 빈도, 실행 시간, 대기 이벤트 등의 요소를 효과적으로 모니터링할 수 있다.

 

특히, 자주 실행되는 SQL을 최적화하고, 피크 타임 부하를 조정하며, 실행 시간이 긴 SQL을 튜닝하면 데이터베이스 성능을 크게 개선할 수 있다.