[ORACLE] ALL_OUTLINE_HINTS 완벽 가이드 : 실행 계획 힌트 정보 완전 분석

[ORACLE] ALL_OUTLINE_HINTS

1. 개요

ALL_OUTLINE_HINTS 뷰는 Oracle Database에서 SQL 실행 계획의 Outline Hint 정보를 확인하기 위한 시스템 딕셔너리 뷰입니다. 이 뷰는 ALL_OUTLINES에 저장된 SQL Outline(실행계획 템플릿)에 포함된 힌트(Hint)의 세부 내용을 보여주며, Optimizer가 SQL 실행 시 어떤 힌트를 적용하도록 설계되어 있는지를 명확히 분석할 수 있게 합니다. 즉, SQL 튜닝과 실행 계획 재현(Reproducibility)을 위한 핵심 진단 도구입니다.

2. 개념 이해

SQL Outline은 특정 SQL 문에 대해 Optimizer가 동일한 실행계획을 유지하도록 힌트를 저장해두는 기능입니다. 이때 Outline 내부에 어떤 힌트가 포함되어 있는지를 확인할 수 있는 뷰가 바로 ALL_OUTLINE_HINTS입니다. 이를 통해 DBA는 SQL 실행 계획이 변경된 원인을 역추적하고, 힌트 기반으로 튜닝 전략을 세울 수 있습니다.

3. 주요 컬럼 구조

아래는 Oracle의 ALL_OUTLINE_HINTS 뷰에 포함된 대표적인 컬럼 구조입니다.

컬럼명	데이터 타입	설명
NAME	VARCHAR2(30)	해당 Outline의 이름 (ALL_OUTLINES.NAME과 연관)
CATEGORY	VARCHAR2(30)	Outline이 속한 카테고리
HINT	VARCHAR2(512)	Optimizer에 적용되는 힌트 내용 (예: INDEX, FULL, USE_NL 등)
HINT_TYPE	VARCHAR2(30)	힌트의 유형 (JOIN, ACCESS, OPTIMIZER 관련 등)
STAGE	NUMBER	Optimizer 처리 단계(Stage)에 해당하는 값
JOIN_POS	NUMBER	JOIN 힌트의 적용 위치 정보

4. 핵심 역할

• Optimizer가 적용한 힌트의 종류와 순서를 파악할 수 있습니다.
• SQL 실행계획의 재현성(Reproducibility)을 유지할 때 유용합니다.
• 과거 Outline 기반 실행계획을 분석하여 성능 저하 원인을 추적할 수 있습니다.

5. 관련 뷰 비교

ALL_OUTLINE_HINTS는 ALL_OUTLINES와 밀접하게 연동되며, 두 뷰를 함께 조회하면 SQL Outline의 전체 구조를 완벽하게 분석할 수 있습니다. 또한, Oracle 11g 이후에는 SQL Plan Management 기능(DBA_SQL_PLAN_BASELINES)이 등장하여 Outline을 대체하지만, ALL_OUTLINE_HINTS는 여전히 레거시 및 추적용으로 사용됩니다.

비교 항목	ALL_OUTLINES	ALL_OUTLINE_HINTS
역할	SQL Outline 메타정보 저장	Outline에 포함된 힌트 정보 저장
주요 컬럼	NAME, SQL_TEXT, CATEGORY	NAME, HINT, HINT_TYPE
분석 초점	SQL의 전체 구조	Optimizer 힌트 세부 내용
활용 목적	SQL 식별 및 관리	튜닝 원인 분석 및 최적화 검증

6. 사용 예제

-- 1) 특정 Outline에 포함된 Hint 조회
SELECT name, hint
FROM all_outline_hints
WHERE name = 'SYS_OUTLINE_24010109153012345';

-- 2) Outline과 힌트를 함께 분석
SELECT o.name, o.sql_text, h.hint
FROM all_outlines o
JOIN all_outline_hints h
  ON o.name = h.name
WHERE o.category = 'DEFAULT';

-- 3) 특정 테이블에 사용된 힌트 유형 확인
SELECT hint_type, COUNT(*)
FROM all_outline_hints
WHERE hint LIKE '%EMP%'
GROUP BY hint_type;

7. 실무 활용 전략

DBA와 SQL 튜너는 ALL_OUTLINE_HINTS를 활용하여 다음과 같은 실무적 분석을 수행할 수 있습니다:

1. SQL 실행계획 변경 원인 분석: Optimizer가 자동으로 Outline을 변경했을 때 어떤 힌트가 변했는지 확인합니다.
2. 성능 저하 SQL 검증: 과거 버전에서 빠르게 수행되던 SQL의 Outline과 현재 버전의 Hint를 비교하여 원인을 도출합니다.
3. SQL 고정화 전략: 빈번히 실행되는 핵심 SQL에 대해 Outline 기반 힌트를 직접 조정하여 안정적 실행계획을 유지합니다.

8. 성능 관리와 튜닝 인사이트

SQL 튜닝의 핵심은 “의도한 실행계획을 안정적으로 유지하는 것”입니다. ALL_OUTLINE_HINTS는 이러한 목적을 위해 힌트 단위의 제어가 가능하다는 점에서 매우 유용합니다.

문제 상황	원인 분석	해결 방법
SQL 성능 저하	Optimizer 힌트 변경 또는 누락	ALL_OUTLINE_HINTS로 기존 힌트 비교
실행계획 불안정	버전 업그레이드 시 Plan Hash Value 변경	Outline 재생성 및 힌트 검증
힌트 오용	비호환 힌트 또는 잘못된 구문	DBMS_XPLAN.DISPLAY와 병행 점검

9. Oracle 버전별 차이점

Oracle 10g 이후에는 Stored Outlines가 비추천(Deprecated)되고 대신 SQL Plan Baseline 기능이 도입되었습니다. 그러나 여전히 ALL_OUTLINE_HINTS는 과거 SQL 성능 비교 및 Legacy 시스템 유지보수에 중요한 역할을 담당합니다.

Oracle 버전	Outline 기능 지원	대체 기술	비고
8i ~ 9i	Stored Outline 전면 지원	없음	ALL_OUTLINE_HINTS 핵심 뷰
10g	부분 지원 (Deprecated 경고)	SQL Plan Baseline	이행 단계
11g 이상	호환성 유지 수준	SPM (SQL Plan Management)	튜닝 분석용으로만 활용

10. SQL 튜너가 알아야 할 실전 팁

• Outline 힌트를 직접 수정하기보다는 새로운 Outline을 재생성하는 것이 안정적입니다.
• SQL Plan Baseline을 사용할 때도 기존 Outline의 힌트를 비교 기준으로 삼으면 효과적입니다.
• DBMS_OUTLN 패키지를 이용하면 Outline의 생성 및 관리가 가능합니다.

11. 보안 및 유지보수 관점

Outline은 DBA 권한이 필요한 객체이며, ALL_OUTLINE_HINTS 조회 시에는 권한 제어가 필수적입니다. 특히, 테스트 환경에서 생성된 Outline이 운영 환경에 적용될 경우 성능 이슈가 발생할 수 있으므로, 주기적인 검증이 필요합니다.

12. 결론

ALL_OUTLINE_HINTS는 SQL 실행계획을 통제하고 분석하는 데 있어 가장 세밀한 단위의 정보를 제공하는 Oracle의 강력한 뷰입니다. SQL 성능 유지, 실행계획 안정화, Legacy 시스템 최적화 등 다양한 실무 영역에서 유용하게 활용될 수 있습니다. SQL Plan Baseline이 주류가 된 현재에도, Outline Hint는 “SQL 실행계획의 원형 기록자(Plan Historian)”로서 튜닝 전문가에게 귀중한 통찰을 제공합니다.

13. 참고 출처

• Oracle Database 19c SQL Tuning Guide – Chapter 16. Managing Stored Outlines
• Oracle Database Reference – Data Dictionary Views (ALL_OUTLINE_HINTS)
• Oracle 23ai Documentation – SQL Plan Management and Outline Views