DevelopOracle

Oracle - 쿼리 튜닝 가이드

Oracle 쿼리 튜닝 가이드

오라클 데이터베이스에서 쿼리 성능을 최적화하는 종합 가이드입니다.

쿼리 튜닝은 SQL 문의 실행 시간을 단축하고 시스템 리소스 사용을 최적화하는 프로세스입니다. 효과적인 튜닝을 위해서는 실행 계획 분석, 인덱스 활용, 통계 정보 관리 등 다양한 기법을 이해해야 합니다.

주제별 가이드

1. 실행 계획 분석

쿼리의 실행 계획을 확인하고 분석하는 방법을 다룹니다.

  • EXPLAIN PLAN 사용법
  • AUTOTRACE 활용
  • SQL 모니터링
  • 운영 환경에서 실행 계획 조회
  • SQL_ID를 이용한 추적
  • GATHER_PLAN_STATISTICS 힌트
  • AWR에서 과거 계획 조회

2. 인덱스 최적화

인덱스를 효과적으로 생성하고 활용하는 방법을 다룹니다.

  • 적절한 인덱스 생성
  • 인덱스 사용 유도
  • B-Tree, 비트맵, 함수 기반 인덱스
  • 복합 인덱스 설계 원칙
  • 인덱스 통계 및 재구성
  • 불필요한 인덱스 찾기

3. 인덱스 힌트 사용법

인덱스 힌트를 사용하여 옵티마이저를 제어하는 방법을 다룹니다.

  • INDEX, INDEX_DESC 힌트
  • INDEX_FFS, INDEX_SS 힌트
  • INDEX_COMBINE, INDEX_JOIN 힌트
  • NO_INDEX, FULL 힌트
  • 힌트 조합 및 주의사항
  • 힌트 효과 확인

4. 조인 최적화

효율적인 조인 방법을 선택하고 최적화하는 방법을 다룹니다.

  • Nested Loop Join
  • Hash Join
  • Merge Join
  • 조인 순서 최적화
  • Anti Join과 Semi Join
  • Outer Join 최적화

5. 서브쿼리 최적화

서브쿼리를 효율적으로 작성하고 최적화하는 방법을 다룹니다.

  • EXISTS vs IN
  • NOT EXISTS vs NOT IN
  • 스칼라 서브쿼리 최적화
  • 인라인 뷰 활용
  • WITH 절 (CTE)
  • 윈도우 함수로 변환

6. 통계 정보 관리

옵티마이저가 최적의 실행 계획을 수립할 수 있도록 통계 정보를 관리하는 방법을 다룹니다.

  • 통계 수집
  • 테이블, 인덱스, 컬럼 통계 확인
  • 히스토그램 관리
  • 통계 정보 고정 및 잠금
  • 자동 통계 수집
  • 증분 통계 (파티션)

7. SQL 재작성 기법

비효율적인 SQL을 효율적으로 재작성하는 기법을 다룹니다.

  • UNION ALL vs UNION
  • COUNT, DISTINCT 최적화
  • OR 조건 최적화
  • LIKE 조건 최적화
  • NULL 처리 최적화
  • 페이징 최적화

8. 파티셔닝 활용

대용량 테이블의 성능과 관리 효율성을 향상시키는 파티셔닝 기법을 다룹니다.

  • Range, List, Hash 파티션
  • Composite 파티션
  • 파티션 프루닝
  • 파티션 관리
  • Reference, Interval 파티션
  • 파티션 인덱스

9. 힌트 사용 전략

다양한 힌트를 효과적으로 사용하는 방법을 다룹니다.

  • PARALLEL, APPEND 힌트
  • FIRST_ROWS, ALL_ROWS 힌트
  • 조인 힌트
  • 쿼리 변환 힌트
  • 병렬 처리 힌트
  • 힌트 조합 및 검증

10. 성능 모니터링

데이터베이스 성능을 모니터링하고 문제를 진단하는 방법을 다룹니다.

  • AWR 리포트
  • 대기 이벤트 분석
  • SQL 성능 분석
  • 세션 모니터링
  • ASH (Active Session History)
  • 성능 지표 모니터링

튜닝 프로세스

  1. 문제 식별: 느린 쿼리, 높은 리소스 사용 확인
  2. 실행 계획 분석: EXPLAIN PLAN, AUTOTRACE로 실행 방식 파악
  3. 통계 정보 확인: 최신 통계 정보 유지
  4. 인덱스 검토: 적절한 인덱스 존재 여부 확인
  5. SQL 재작성: 비효율적인 SQL 개선
  6. 힌트 적용: 필요시 힌트로 실행 계획 제어
  7. 성능 측정: 튜닝 전후 성능 비교
  8. 모니터링: 지속적인 성능 관리

튜닝 체크리스트

기본 점검 사항

  • 실행 계획 확인 (EXPLAIN PLAN, AUTOTRACE)
  • 통계 정보 최신 여부 확인
  • 적절한 인덱스 존재 여부
  • 인덱스가 사용되는지 확인
  • 조인 순서 및 방식 확인
  • 불필요한 DISTINCT, UNION 제거
  • 서브쿼리 최적화 검토
  • WHERE 절 조건 순서 확인
  • 함수 사용으로 인한 인덱스 미사용 체크
  • 파티션 프루닝 활용 여부

성능 개선 우선순위

  1. 테이블 풀 스캔 제거: 적절한 인덱스 생성
  2. 조인 최적화: 작은 테이블 먼저, 적절한 조인 방식
  3. 불필요한 정렬 제거: ORDER BY, DISTINCT, UNION
  4. 중복 액세스 제거: 같은 테이블 여러 번 조회
  5. 대량 데이터 처리: BULK COLLECT, FORALL 사용

참고사항

  • 튜닝 전후 반드시 성능 측정 (SET TIMING ON)
  • 운영 환경과 유사한 데이터로 테스트
  • 힌트 남용 지양 (옵티마이저 신뢰)
  • 정기적인 통계 수집 스케줄링
  • SQL 표준 준수로 이식성 향상

관련 문서

이 가이드는 오라클 쿼리 튜닝의 핵심 주제를 체계적으로 정리한 것입니다. 각 주제별 상세 문서를 참고하여 단계적으로 학습하시기 바랍니다.

Share