ASH 데이터를 기반으로 자동 SQL 튜닝 수행하기: SQL 튜닝 어드바이저와 ASH 데이터 활용법.

서버 이관 프로젝트를 진행하면서 새로운 환경에서 데이터베이스 성능이 예상과 다르게 동작할 가능성이 높다. 특히, 이전 환경에서는 원활하게 실행되던 SQL이 새로운 서버 환경에서는 CPU, 메모리, 디스크 I/O 등의 리소스를 과도하게 사용하거나, 실행 시간이 지연될 수 있다.

 

SQL 성능 저하는 단순히 데이터베이스 서버의 문제일 수도 있지만, 실행 계획이 변경되거나 특정 리소스가 부족한 경우에도 발생할 수 있다. 이를 해결하기 위해 수동으로 SQL을 분석하고 최적화하는 방식도 있지만, 보다 효율적인 방법은 ASH(Active Session History) 데이터를 활용하여 자동 SQL 튜닝을 수행하는 것이다.

 

Oracle은 SQL 성능 분석 및 최적화를 자동으로 수행할 수 있도록 SQL 튜닝 어드바이저(SQL Tuning Advisor)를 제공하며, 이를 활용하면 비효율적인 SQL을 자동으로 감지하고, 실행 계획을 개선하는 방법을 추천받을 수 있다. 또한 ASH 데이터를 활용하면, 성능 저하를 일으키는 SQL을 실시간으로 분석하고, SQL 튜닝 어드바이저와 연계하여 최적화 작업을 자동화할 수 있다.

 

본 글에서는 ASH 데이터를 기반으로 SQL 튜닝을 수행하는 방법과, SQL 튜닝 어드바이저를 활용하여 자동으로 SQL을 최적화하는 전략을 상세히 설명한다. 이를 통해 서버 이관 후 SQL 성능 문제를 신속하게 해결하고, 데이터베이스 운영의 안정성을 확보할 수 있도록 한다.

---

SQL 튜닝의 필요성

1. 실행 계획 변화로 인한 성능 저하

• 서버 이관 후 기존 환경과 인덱스 구조, 데이터 분포, 실행 계획이 달라질 수 있다.
• 실행 계획이 최적화되지 않으면 풀 테이블 스캔(Full Table Scan), 불필요한 정렬(SORT), 비효율적인 조인(JOIN) 등이 발생하여 성능이 저하될 가능성이 높다.

2. 과도한 리소스 사용(SQL 과부하 문제)

• 특정 SQL이 CPU 사용량을 급격히 증가시키거나, 메모리를 과도하게 점유할 경우, 다른 트랜잭션의 성능도 저하될 수 있다.
• SQL 튜닝을 통해 과도한 리소스 사용을 방지하고, 불필요한 부하를 줄이는 것이 중요하다.

3. 대기 이벤트(WAIT EVENT) 증가

• 특정 SQL이 디스크 I/O 대기, 네트워크 대기, 락(Lock) 경합 등의 원인으로 지연될 수 있다.
• ASH 데이터를 활용하면, 어떤 대기 이벤트가 SQL 성능을 저하시켰는지 분석할 수 있다.

---

ASH 데이터를 활용한 SQL 성능 분석 방법

1. 실행 시간이 긴 SQL 조회

실행 시간이 오래 걸리는 SQL을 찾으려면 ASH 데이터를 활용하여 실행 시간이 가장 긴 SQL을 조회할 수 있다.

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

• execution_time → 실행 시간이 긴 SQL을 식별하는 데 활용.

이 데이터를 활용하면 실행 시간이 비정상적으로 긴 SQL을 분석하고, SQL 튜닝 어드바이저를 활용하여 자동 최적화를 수행할 수 있다.

---

2. CPU 사용량이 높은 SQL 조회

CPU 사용량이 높은 SQL을 분석하려면 ASH 데이터를 활용하여 특정 SQL의 CPU 점유율을 확인할 수 있다.

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

• cpu_usage → 특정 SQL이 CPU를 많이 사용한 횟수.

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

---

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을 찾아 인덱스를 추가하거나, 실행 계획을 조정하여 디스크 사용량을 줄일 수 있다.

---

SQL 튜닝 어드바이저를 활용한 자동 SQL 최적화

SQL 튜닝 어드바이저는 Oracle이 제공하는 SQL 자동 최적화 도구로, 실행 계획을 분석하고 최적의 튜닝 방안을 추천한다.

1. 특정 SQL에 대해 SQL 튜닝 어드바이저 실행

아래 SQL을 실행하면, 특정 SQL에 대한 튜닝 어드바이저 추천을 받을 수 있다.

DECLARE  l_task VARCHAR2(100); BEGIN l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(   sql_id => 'SQL_ID', scope => 'COMPREHENSIVE',    time_limit => 60, task_name => 'sql_tuning_task1' ); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task1'); END;

• SQL_ID → 튜닝할 SQL의 ID를 입력.
• scope = 'COMPREHENSIVE' → 전체적인 튜닝 분석 수행.
• time_limit = 60 → 분석 수행 시간(초).

이 명령을 실행하면 튜닝 어드바이저가 SQL 실행 계획을 분석하고 최적화 방안을 추천한다.

---

2. 튜닝 결과 확인

튜닝 분석 결과를 확인하려면 아래 SQL을 실행하면 된다.

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task1') FROM dual;

• 이 명령을 실행하면 SQL 튜닝 어드바이저의 분석 결과를 보고서 형식으로 출력한다.

튜닝 결과에는 SQL 실행 계획 변경, 인덱스 추가, 힌트(Hint) 적용 등의 최적화 방안이 포함될 수 있다.

---

3. SQL 자동 튜닝 적용

튜닝 어드바이저가 추천하는 실행 계획을 자동으로 적용하려면 아래 SQL을 실행한다.

BEGIN    DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'sql_tuning_task1');  END;

• 이 명령을 실행하면 튜닝 어드바이저가 추천한 최적화 프로파일을 자동으로 적용한다.

이를 통해 수동으로 SQL을 튜닝할 필요 없이, Oracle이 자동으로 최적화된 실행 계획을 적용할 수 있다.

---

ASH 데이터를 활용하면 SQL 실행 패턴을 분석하고, 성능이 저하된 SQL을 신속하게 감지할 수 있다. 이를 기반으로 SQL 튜닝 어드바이저를 활용하면 비효율적인 SQL을 자동으로 최적화하고, 실행 계획을 개선하여 데이터베이스 성능을 향상시킬 수 있다.

 

특히, 서버 이관 후에는 실행 계획이 예상치 못하게 변경될 가능성이 높으므로, ASH 데이터를 기반으로 주기적으로 SQL 튜닝을 수행하는 것이 중요하다. 이를 통해 데이터베이스의 안정성과 성능을 지속적으로 유지할 수 있을 것이다.