Oracle 서버 이관 시 쿼리 튜닝 대상 SQL 목록 추출 방법: 사례 및 쿼리 예시

 

Oracle 서버 이관 시 쿼리 튜닝 대상 SQL 목록 추출 방법: 사례 및 쿼리 예시

서버 이관 프로젝트를 수행할 때, 이관 대상 시스템에서 쿼리 튜닝의 대상이 되는 SQL 목록을 사전에 파악하는 일은 매우 중요합니다. 이 작업은 이관 후 예상치 못한 성능 문제를 예방하고, 안정적인 운영 환경을 구축하기 위한 기초 자료로 활용됩니다. 본 글에서는 Oracle 데이터베이스 환경에서 쿼리 튜닝 대상 SQL 목록을 추출하는 방법과 함께, 이를 위해 활용할 수 있는 쿼리 예시를 자세히 설명드립니다.

1. 동적 성능 뷰를 활용한 SQL 목록 추출

기본적인 접근 방식은 데이터베이스에서 실행된 SQL 문에 대한 통계를 제공하는 동적 성능 뷰(V$SQL, V$SQLAREA 등)와 AWR 리포트 자료를 활용하는 것입니다. 이를 통해 실행 빈도, CPU 및 I/O 비용, 캐시 적중률 등 다양한 성능 지표를 확인할 수 있으며, 이러한 지표를 기반으로 튜닝 대상 SQL을 선별할 수 있습니다.

예를 들어, V$SQLAREA 뷰는 공유 SQL 영역에 저장된 SQL 문들의 통계 정보를 제공합니다. 아래와 같은 쿼리를 실행하면 최근 실행된 SQL 목록과 각 SQL 문의 실행 횟수, 평균 실행 시간, CPU 사용량 등을 확인할 수 있습니다.

SELECT sql_id,
       executions,
       elapsed_time,
       cpu_time,
       buffer_gets,
       disk_reads,
       sql_text
FROM   v$sqlarea
WHERE  executions > 0
ORDER BY buffer_gets DESC;
    

이 쿼리는 실행 횟수가 0보다 큰 SQL 문들을 대상으로, 버퍼 읽기(buffer_gets)가 많은 순서대로 정렬하여 출력합니다. 버퍼 읽기가 많은 SQL 문은 데이터베이스 자원을 많이 사용하는 경우가 많으므로, 튜닝 대상이 될 가능성이 높습니다. 또한, CPU 사용량이나 디스크 읽기(disk_reads) 값도 함께 확인하여 전체 시스템 성능에 미치는 영향을 고려할 수 있습니다.

2. AWR 리포트를 활용한 SQL 성능 분석

AWR(Automatic Workload Repository) 리포트를 활용하면, 특정 기간 동안의 시스템 전체 성능 통계와 함께 쿼리별 성능 지표를 상세히 분석할 수 있습니다. 예를 들어, 아래와 같은 쿼리를 통해 AWR 스냅샷 간의 SQL 성능 데이터를 조회할 수 있습니다.

SELECT *
FROM   dba_hist_sqlstat
WHERE  snap_id BETWEEN :start_snap AND :end_snap
ORDER BY buffer_gets DESC;
    

이 쿼리에서는 AWR 스냅샷 기간을 지정하여 해당 기간 동안의 SQL 실행 통계를 조회합니다. 스냅샷 번호(:start_snap, :end_snap)는 DBA가 미리 설정한 기간의 스냅샷 번호를 입력하면 되며, 버퍼 읽기 순으로 정렬된 결과를 통해 성능 저하가 우려되는 SQL을 선별할 수 있습니다.

3. 실행 계획(Explain Plan) 분석을 통한 상세 튜닝 대상 파악

실행 계획 분석은 SQL 튜닝의 핵심 작업 중 하나입니다. V$SQL_PLAN 뷰를 활용하여 각 SQL 문의 실행 계획을 분석하면, 옵티마이저가 선택한 경로를 확인할 수 있으며, 불필요한 풀 스캔(full table scan)이나 인덱스 미사용 여부를 파악할 수 있습니다.

아래는 특정 SQL 문의 실행 계획을 조회하는 예시 쿼리입니다.

SELECT plan_id,
       operation,
       options,
       object_name,
       optimizer
FROM   v$sql_plan
WHERE  sql_id = 'your_sql_id'
ORDER BY id;
    

이 쿼리는 특정 SQL 문의 SQL_ID를 기반으로 실행 계획의 각 단계를 출력합니다. 실행 계획의 각 단계에서 인덱스 사용 여부나 조인 방식 등의 정보를 확인하여, 튜닝이 필요한 부분을 도출할 수 있습니다.

4. 캐시 적중률 분석

또한, SQL 문이 캐시에서 얼마나 자주 재사용되는지를 파악하기 위해 V$SQLAREA 뷰의 CACHE_HIT_RATIO 컬럼을 참고할 수 있습니다. 캐시 적중률이 낮은 경우, 매번 새로운 실행 계획이 생성되면서 불필요한 파싱 비용이 발생할 수 있으므로, 이 부분에 대한 튜닝도 함께 고려해야 합니다.

5. Oracle SPA와의 연계 활용

DBA는 Oracle SPA(SQL Performance Analyzer)와 같은 도구를 활용하여, 변경 전후의 실행 계획과 성능 지표를 정량적으로 비교할 수 있습니다. SPA를 이용하면 특정 SQL 문의 실행 계획을 분석하고, 힌트 적용이나 인덱스 재설계 후의 성능 변화를 비교 분석할 수 있어, 단순한 정적 분석을 넘어 실질적인 성능 개선 효과를 확인할 수 있습니다.

6. 목록 추출 작업의 활용 및 결론

이처럼 Oracle 서버 이관 시 쿼리 튜닝 대상 SQL 목록을 추출하는 작업은 여러 동적 성능 뷰와 AWR 리포트, 그리고 Oracle SPA와 같은 전문 도구들을 활용하여 체계적으로 수행해야 합니다. 위에 제시한 쿼리 예시와 절차를 참고하여, 각 SQL 문의 실행 계획과 성능 통계를 면밀히 분석하고, 이를 바탕으로 이관 후 최적화 작업의 기반을 마련하는 것이 중요합니다.

추출된 SQL 목록은 이관 후 튜닝 작업의 기준 자료로 활용될 뿐만 아니라, 추후 발생할 수 있는 성능 문제에 대한 대응 계획 수립에도 큰 도움이 됩니다. 선별된 SQL 목록을 기반으로, 각 쿼리에 대해 우선순위를 부여하고, 병목 현상 및 리소스 소모가 큰 쿼리에 대해 집중적으로 튜닝 작업을 진행하게 됩니다.

결론적으로, Oracle 서버 이관 시 쿼리 튜닝 대상 SQL 목록 추출 작업은 서버 이관 프로젝트의 성공적인 진행과 안정적인 데이터베이스 운영을 위해 매우 중요한 사전 작업입니다. 이를 통해 미리 쿼리 성능을 파악하고, 향후 발생할 수 있는 문제에 신속히 대응할 수 있는 기반을 마련할 수 있음을 다시 한 번 강조드립니다.