본문 바로가기
IT/AWR-ASH

ASH를 활용한 SQL 튜닝: 실행 시간이 긴 SQL을 최적화하는 접근법

by free-inf 2025. 2. 9.

서버 이관 프로젝트를 수행하는 과정에서 가장 중요한 요소 중 하나는 데이터베이스의 성능을 유지하고 최적화하는 것이다. 기존 서버 환경과 새로운 서버 환경은 CPU, 메모리, 디스크 I/O 성능, 네트워크 속도 등이 다를 수 있기 때문에, 예상치 못한 성능 저하가 발생할 가능성이 높다.

 

특히 실행 시간이 긴 SQL이 많아지면 데이터베이스 전체 성능이 저하되고, 트랜잭션이 지연되며, 사용자 응답 속도가 느려지는 문제가 발생할 수 있다. 이를 해결하기 위해 ASH(Active Session History) 데이터를 활용하여 실행 시간이 긴 SQL을 분석하고 최적화하는 과정이 필요하다.

 

본 글에서는 ASH를 활용하여 실행 시간이 긴 SQL을 분석하는 방법과 이를 최적화하는 접근법을 상세히 설명한다. 이를 통해 서버 이관 후에도 안정적인 데이터베이스 성능을 유지하고 운영 효율성을 극대화할 수 있도록 한다.

1. ASH(Active Session History)란?

1.1 ASH의 개념

ASH(Active Session History)는 Oracle 데이터베이스에서 현재 활성화된 세션의 실행 정보를 실시간으로 수집하는 기능이다. Oracle은 매 초마다 현재 실행 중인 SQL과 해당 SQL의 대기 이벤트를 기록하며, 이를 통해 실시간 성능 분석 및 문제 해결이 가능하다.

1.2 ASH를 활용한 SQL 튜닝의 필요성

  • 실시간 SQL 실행 분석 가능: 실행 시간이 긴 SQL을 빠르게 식별할 수 있다.
  • CPU 및 I/O 병목 파악: 특정 SQL이 CPU를 과도하게 사용하거나, 디스크 I/O를 많이 발생시키는지 분석 가능하다.
  • 대기 이벤트(Wait Event) 확인: SQL이 어떤 이유로 실행이 지연되는지 분석하여 튜닝 방향을 설정할 수 있다.

2. 실행 시간이 긴 SQL 분석 방법

ASH 데이터를 활용하면 특정 SQL이 실행되는 동안 CPU 사용량, 대기 이벤트, 실행 빈도 등 다양한 성능 정보를 분석할 수 있다.

2.1 ASH를 활용한 실행 시간 분석

다음 SQL을 실행하면 실행 시간이 긴 SQL 목록을 조회할 수 있다.

SELECT 
  sql_id, 
  COUNT(*) AS execution_count, 
  AVG(time_waited) AS avg_wait_time 
FROM 
  v$active_session_history 
WHERE 
  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 
  avg_wait_time DESC;
  • sql_id: 실행된 SQL의 고유 ID.
  • execution_count: 해당 SQL이 실행된 횟수.
  • avg_wait_time: SQL이 실행될 때 평균 대기 시간(단위: 밀리초).

이 데이터를 분석하면 실행 시간이 길고 빈번하게 실행되는 SQL을 우선적으로 튜닝할 수 있다.

2.2 실행 시간이 긴 특정 SQL의 상세 분석

특정 SQL의 실행 시간이 길다면, 해당 SQL이 CPU를 많이 사용하는지, I/O가 많아지는지, 락(Lock) 대기 시간이 길어지는지 분석해야 한다.

CPU 사용량이 높은 SQL 조회

SELECT 
  sql_id, 
  COUNT(*) AS cpu_usage 
FROM 
  v$active_session_history 
WHERE 
  session_state = 'ON CPU' 
GROUP BY 
  sql_id 
ORDER BY 
  cpu_usage DESC;
 
  • session_state = 'ON CPU': 현재 CPU에서 실행 중인 SQL만 조회.

이 데이터를 활용하면 CPU 사용량이 높은 SQL을 식별하여, 실행 계획을 최적화할 수 있다.

디스크 I/O가 많은 SQL 조회

SELECT 
  sql_id, 
  COUNT(*) AS io_waits 
FROM 
  v$active_session_history 
WHERE 
  wait_class = 'User I/O' 
GROUP BY 
  sql_id 
ORDER BY 
  io_waits DESC;
 
  • wait_class = 'User I/O': 디스크에서 데이터를 읽거나 쓰면서 대기한 SQL만 조회.

디스크 I/O가 많은 SQL은 Full Table Scan을 유발할 가능성이 높아, 적절한 인덱스를 활용하여 성능을 개선해야 한다.

락(Lock) 대기가 많은 SQL 조회

SELECT 
  sql_id, 
  COUNT(*) AS lock_waits 
FROM 
  v$active_session_history 
WHERE 
  wait_class = 'Concurrency' 
GROUP BY 
  sql_id 
ORDER BY 
  lock_waits DESC;
 
  • wait_class = 'Concurrency': 트랜잭션 간 락 대기로 인해 SQL이 지연된 경우 조회.

락 대기가 많은 SQL은 트랜잭션 크기를 줄이거나, 인덱스를 활용하여 행(Row) 수준에서 락이 발생하도록 조정하는 것이 필요하다.

3. 실행 시간이 긴 SQL 최적화 방법

3.1 실행 계획(Execution Plan) 분석 및 최적화

실행 시간이 긴 SQL은 불필요한 연산이 포함될 가능성이 크므로 실행 계획을 분석해야 한다.

EXPLAIN PLAN FOR 
SELECT 
  * 
FROM 
  employees 
WHERE 
  department = 'Sales';
SELECT 
  * 
FROM 
  TABLE(DBMS_XPLAN.DISPLAY);
 
  • 실행 계획을 확인하여 Full Table Scan이 발생하는 경우 인덱스를 추가하거나, 조인 방식(Join Type)을 변경하는 방법을 고려할 수 있다.

3.2 적절한 인덱스 활용

Full Table Scan이 발생하는 SQL은 적절한 인덱스를 적용하여 성능을 개선할 수 있다.

 
-- 인덱스가 없는 경우 
(Full Table Scan 발생) 
SELECT 
  * 
FROM 
  employees 
WHERE 
  department = 'Sales';
-- 적절한 인덱스를 추가하여 최적화
CREATE INDEX emp_dept_idx ON employees(department);
  • 결과: Full Table Scan을 제거하고 Index Scan을 활용하여 실행 속도를 개선.

3.3 바인드 변수(Bind Variable) 사용

SQL이 반복 실행될 때 바인드 변수를 사용하면 SQL 실행 계획을 공유하여 성능을 개선할 수 있다.

-- 비효율적인 SQL (리터럴 값을 사용하여 실행)
SELECT 
  * 
FROM 
  employees 
WHERE 
  department = 'Sales';
-- 최적화된 SQL (바인드 변수 사용)
SELECT 
  * 
FROM 
  employees 
WHERE 
  department = : dept_name;
 
  • 바인드 변수를 사용하면 SQL이 공유 메모리(Shared Pool)에 캐싱되어 불필요한 SQL 파싱 비용을 줄일 수 있다.

3.4 트랜잭션 크기 조정 및 커밋(Commit) 빈도 조절

긴 트랜잭션이 락을 유지하면 다른 SQL이 대기 상태로 유지되면서 전체적인 성능이 저하될 수 있다.

 
-- 비효율적인 트랜잭션 (대량 데이터를 한 번에 업데이트)
UPDATE 
  employees 
SET 
  salary = salary * 1.1 
WHERE 
  department = 'Sales';
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 튜닝은 실행 시간이 긴 SQL을 분석하고 최적화하여 데이터베이스 성능을 향상시키는 핵심 기법이다.

ASH 데이터를 활용하면 CPU 사용량이 높은 SQL, 디스크 I/O가 많은 SQL, 락 대기가 많은 SQL을 식별할 수 있으며, 이를 최적화하면 전체적인 시스템 성능을 개선할 수 있다.

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

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

ASH 데이터를 활용한 성능 추세 분석: 일정 기간 동안 성능 변화 감지 및 튜닝.  (0) 2025.02.10
SQL 실행 계획과 ASH 데이터 비교 분석: 실행 계획과 세션 데이터를 함께 분석하여 문제 해결.  (0) 2025.02.10
실시간 ASH 데이터를 조회하는 방법: 현재 진행 중인 세션 활동을 조회하는 SQL.  (0) 2025.02.10
ASH에서 특정 세션 분석하기: 문제를 일으키는 특정 세션을 추적하고 해결하는 방법  (0) 2025.02.10
SQL 실행 빈도 분석: 가장 많이 실행되는 SQL을 찾아 성능 최적화를 수행하는 방법  (0) 2025.02.09
락(Lock) 관련 대기 분석: 테이블 락, 행(Row) 락 등 트랜잭션 대기로 인한 문제 해결  (0) 2025.02.09
디스크 I/O 대기 분석: 디스크 읽기/쓰기 지연이 성능 저하에 미치는 영향 분석  (0) 2025.02.09
CPU 대기 이벤트 분석: CPU 사용량이 높은 SQL을 식별하고 최적화하는 방법  (0) 2025.02.09

관련글

티스토리툴바