ASH에서 비효율적인 SQL 식별하기: 비효율적인 SQL을 찾고 최적화하는 기법.

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능이 기존 환경과 다르게 동작하는 경우가 발생할 수 있다. 새로운 하드웨어와 소프트웨어 환경에서는 SQL 실행 방식이 예상과 다르게 변화할 가능성이 있으며, 이에 따라 성능 저하 문제가 발생할 수 있다.

 

특히, 비효율적인 SQL은 CPU, 메모리, 디스크 I/O 등의 리소스를 과도하게 소비하고, 다른 트랜잭션의 성능에도 부정적인 영향을 미칠 수 있다. 이러한 SQL을 방치하면 전체적인 시스템 성능이 저하되며, 특정 시간대에는 애플리케이션 응답 속도가 급격히 느려지는 현상이 발생할 수 있다.

 

이러한 문제를 해결하기 위해 Oracle ASH(Active Session History) 데이터를 활용하여 비효율적인 SQL을 식별하고 최적화하는 것이 중요하다. ASH 데이터는 실시간으로 SQL 실행 정보를 기록하며, 실행 시간이 길거나 리소스를 과도하게 사용하는 SQL을 추적하는 데 유용하다.

 

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

---

비효율적인 SQL이 성능 저하를 유발하는 주요 원인

1. 풀 테이블 스캔(Full Table Scan) 증가

• 테이블 전체를 스캔하는 쿼리는 실행 시간이 길어지고 디스크 I/O 부하가 급증할 수 있다.
• 인덱스를 활용하지 못하는 SQL은 불필요한 데이터 접근이 많아지고 성능 저하를 유발한다.

2. 과도한 조인(Join) 연산

• 조인 개수가 많거나, 적절한 인덱스를 활용하지 못하면 SQL 실행 속도가 급격히 저하될 수 있다.
• 해시 조인(Hash Join), 병렬 조인(Parallel Join) 등의 비효율적인 실행 계획이 선택될 가능성이 높다.

3. 불필요한 정렬(SORT) 연산 발생

• ORDER BY, GROUP BY, DISTINCT 등의 연산이 메모리가 아닌 디스크에서 처리되면 성능이 저하될 수 있다.
• PGA(Process Global Area)가 부족하면 정렬이 Temp Tablespace에서 수행되면서 디스크 I/O 부하를 초래한다.

4. 대량의 데이터 조회 및 불필요한 데이터 반환

• 필요한 데이터만 조회하지 않고 과도한 컬럼이나 행을 조회하면, 네트워크 부하와 디스크 I/O가 증가할 수 있다.
• 서브쿼리를 많이 사용하는 경우, 최적화되지 않으면 불필요한 연산이 반복될 가능성이 높다.

5. 락(Lock) 및 동시성 문제

• 동일한 데이터에 여러 트랜잭션이 동시에 접근하면서 경합이 발생하면, 일부 SQL이 대기 상태가 된다.
• 락이 자주 발생하는 테이블에서 트랜잭션이 길어지면 시스템 전체 성능이 저하될 수 있다.

---

ASH 데이터를 활용한 비효율적인 SQL 식별 방법

1. 실행 시간이 긴 SQL 조회

SQL 실행 시간이 비정상적으로 긴 SQL을 찾기 위해 ASH 데이터를 분석하여 평균 실행 시간이 긴 SQL을 조회할 수 있다.

 

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을 식별하고, 실행 계획을 분석하여 최적화할 수 있다.

---

2. 풀 테이블 스캔 발생 SQL 조회

테이블 전체를 스캔하는 SQL을 찾아 인덱스를 추가할 수 있도록 ASH 데이터를 활용하여 Full Table Scan이 발생하는 SQL을 조회한다.

 

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

• db file scattered read → Full Table Scan이 발생한 SQL을 의미.

이 데이터를 활용하면 불필요한 풀 테이블 스캔이 발생하는 SQL을 분석하고, 인덱스를 추가하여 성능을 개선할 수 있다.

---

3. 디스크 I/O 부하를 유발하는 SQL 조회

디스크 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 >= SYSDATE - (1)  GROUP BY    sql_id  ORDER BY    io_waits DESC;

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

이 데이터를 활용하면 디스크 부하를 유발하는 SQL을 식별하고, 인덱스를 추가하거나 실행 계획을 최적화할 수 있다.

---

4. 불필요한 정렬 연산을 포함한 SQL 조회

정렬 연산(SORT)이 많이 발생하는 SQL을 찾기 위해 Temp Tablespace를 과도하게 사용하는 SQL을 조회할 수 있다.

 

SELECT    sql_id,    COUNT(*) AS temp_usage  FROM    v$active_session_history  WHERE    event IN (     'direct path read temp', 'direct path write temp'   )    AND sample_time >= SYSDATE - (1)  GROUP BY    sql_id  ORDER BY    temp_usage DESC;

• temp_usage → Temp Tablespace를 사용한 횟수.

이 데이터를 활용하면 불필요한 정렬 연산을 수행하는 SQL을 분석하고, 인덱스를 활용하거나 정렬 방식을 변경하여 최적화할 수 있다.

---

비효율적인 SQL 최적화를 위한 해결 방법

1. 인덱스 추가 및 실행 계획 최적화

• 풀 테이블 스캔이 발생하는 SQL을 분석하고, 필요한 경우 인덱스를 추가.
• 실행 계획(EXPLAIN PLAN)을 확인하여 조인 방식을 최적화.

 

CREATE INDEX emp_dept_idx ON employees(department_id);

---

2. 정렬 연산 최소화

• ORDER BY, GROUP BY, DISTINCT 등을 사용할 때 인덱스를 활용하여 정렬을 최소화.
• PGA 메모리를 충분히 할당하여 정렬 작업이 Temp Tablespace에서 수행되지 않도록 조정.

 

ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

---

3. 디스크 I/O 부하 최소화

• 자주 사용되는 데이터를 캐싱하거나, Materialized View를 활용하여 I/O 부하를 줄임.
• 불필요한 SELECT * 대신 필요한 컬럼만 조회하여 디스크 접근을 최소화.

 

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

---

ASH 데이터를 활용하면 비효율적인 SQL을 실시간으로 식별하고, 실행 시간이 긴 SQL, 풀 테이블 스캔 발생 SQL, 디스크 I/O 부하를 유발하는 SQL 등을 분석할 수 있다.

 

특히, SQL 실행 계획을 최적화하고, 인덱스를 추가하며, 불필요한 정렬 연산을 줄이면 데이터베이스 성능을 크게 개선할 수 있다.