[ORACLE] ALL_OUTLINES 완전 가이드 : 개념 활용 예제 SPM 전환 전략

[ORACLE] ALL_OUTLINES

1. 개요

ALL_OUTLINES 뷰는 Oracle Database의 SQL 성능 안정성을 유지하기 위한 핵심 데이터 사전 뷰로, Optimizer가 생성한 실행 계획을 Stored Outline 형태로 저장하여 동일한 SQL 문이 동일한 실행 계획으로 수행되도록 보장하는 기능을 제공합니다. 이 뷰는 SQL 실행 계획의 일관성을 유지하고, 시스템 업그레이드나 통계 변경 이후에도 예측 가능한 성능을 확보하기 위한 필수 요소입니다.

2. 등장 배경

Oracle Optimizer는 통계 정보, 인덱스 구성, 파라미터 설정 등에 따라 실행 계획이 변동될 수 있습니다. 이러한 변화를 제어하기 위해 Oracle은 Stored Outline 기능을 도입했으며, 그 정보가 바로 ALL_OUTLINES 뷰에 저장됩니다. 따라서 ALL_OUTLINES는 데이터베이스의 SQL 튜닝 이력과 실행 계획 고정 내역을 파악하는 데 매우 유용합니다.

3. 주요 목적

• 특정 SQL 문에 대해 고정된 실행 계획을 강제 적용
• Optimizer 통계 변경에 따른 성능 저하 방지
• DB 업그레이드 후에도 동일한 실행 계획 유지
• SQL 튜닝 힌트를 대체하는 안정적인 계획 관리

4. 기본 구조

ALL_OUTLINES는 다음과 같은 주요 컬럼을 포함하고 있으며, 각 컬럼은 SQL 최적화 및 관리에 직접적으로 활용됩니다.

컬럼명	데이터 타입	설명
NAME	VARCHAR2	Outline의 고유 이름
CATEGORY	VARCHAR2	Outline이 속한 카테고리 이름
USED	VARCHAR2	실행 시 Outline이 사용되었는지 여부 (‘YES’ / ‘NO’)
SQL_TEXT	VARCHAR2	Outline이 적용된 SQL 문
OUTLINE_STATUS	VARCHAR2	‘ENABLED’ 또는 ‘DISABLED’ 상태
OUTLINE_CATEGORY	VARCHAR2	Outline 그룹을 식별하기 위한 논리적 구분

5. 관련 뷰와 비교

ALL_OUTLINES는 DBA_OUTLINES 및 USER_OUTLINES와 함께 사용됩니다. 각 뷰는 접근 범위에 따라 역할이 달라집니다.

뷰 이름	조회 범위	주요 용도
DBA_OUTLINES	데이터베이스 전체의 모든 Outline	관리자가 전체 시스템 수준의 계획을 관리
ALL_OUTLINES	접근 권한이 부여된 스키마의 Outline	공유 또는 협업 환경에서 SQL 계획 조회
USER_OUTLINES	자신의 스키마 내 Outline	개별 개발자의 SQL 최적화 관리

6. 사용 예시

다음은 ALL_OUTLINES를 통해 SQL 실행 계획을 확인하고 관리하는 대표적인 예시입니다.

-- 1) 현재 사용자에게 허용된 OUTLINE 목록 조회
SELECT name, category, used, outline_status 
FROM all_outlines;

-- 2) 특정 SQL 문에 적용된 Outline 확인
SELECT name, sql_text 
FROM all_outlines
WHERE sql_text LIKE '%SELECT * FROM EMP%';

-- 3) 사용된 Outline만 필터링
SELECT name, category, used 
FROM all_outlines
WHERE used = 'YES';

7. Stored Outline 생성 및 적용

Oracle은 CREATE OUTLINE 구문을 통해 SQL 실행 계획을 저장할 수 있습니다.

-- Stored Outline 생성
CREATE OUTLINE emp_select
FOR CATEGORY hr_outline
ON SELECT * FROM emp WHERE deptno = 10;

-- Stored Outline 적용
ALTER SESSION SET use_stored_outlines = hr_outline;

이후 동일한 SQL을 실행하면, Optimizer는 기존 Outline을 기반으로 동일한 실행 계획을 강제 적용합니다. 이로써 환경 변화나 통계 갱신에도 성능이 일정하게 유지됩니다.

8. ALL_OUTLINES vs SQL Plan Baseline

Oracle 11g 이후에는 Stored Outline 기능이 SQL Plan Baseline으로 대체되었습니다. 두 기능은 목적은 동일하지만 구조와 관리 방식이 다릅니다.

구분	Stored Outline (ALL_OUTLINES)	SQL Plan Baseline
도입 버전	Oracle 8i	Oracle 11g 이상
저장 위치	데이터 딕셔너리	SQL Management Base
관리 도구	DBMS_OUTLN	DBMS_SPM
적용 방식	세션 또는 시스템 단위	자동 또는 수동 로딩
장점	단순, 빠른 적용	지능적 계획 관리 및 진화 기능
현재 상태	비추천(Deprecated)	표준 기능으로 대체

9. 실무에서의 활용 포인트

1. 성능 회귀 방지: SQL 실행 계획이 변경되어 속도가 느려질 경우 ALL_OUTLINES를 통해 이전 계획 복원
2. DB 업그레이드 전 대비: 새 버전 업그레이드 전 주요 SQL의 Stored Outline 생성
3. 운영 안정성 확보: 통계 재수집 작업 이후에도 실행 계획 고정 유지
4. 테스트 환경 검증: 동일한 SQL이 동일한 계획으로 실행되는지 비교 검증

10. 트러블슈팅 가이드

Stored Outline이 적용되지 않는 경우, 아래 항목을 우선 점검해야 합니다.

문제 상황	원인	해결 방법
Outline이 적용되지 않음	세션 파라미터가 비활성 상태	ALTER SESSION SET use_stored_outlines = TRUE;
SQL 텍스트 불일치	공백 또는 주석으로 인한 SQL mismatch	정확히 동일한 SQL 텍스트 사용
Outline 상태 DISABLED	관리자가 비활성화 처리함	UPDATE OUTLN.OL$ SET ENABLED='Y' WHERE NAME='...';

11. 보안 및 관리적 고려

Stored Outline 정보는 OUTLN 스키마에 저장되며, 이 스키마는 데이터베이스 실행 계획의 핵심 정보를 포함하므로 권한 관리가 매우 중요합니다. 무분별한 접근은 실행 계획 유출 또는 성능 왜곡을 유발할 수 있습니다.

12. 결론

ALL_OUTLINES는 Oracle SQL 성능 안정성의 초석으로, 운영 환경에서의 일관된 성능을 유지하고 예측 가능한 시스템 동작을 보장하는 도구입니다. 비록 최신 버전에서는 SQL Plan Baseline으로 대체되었지만, 레거시 시스템 유지보수나 SQL 회귀 분석에서는 여전히 강력한 효용성을 지닙니다. ALL_OUTLINES를 이해하면 Oracle Optimizer의 동작 원리를 더욱 깊이 있게 파악할 수 있습니다.

13. 참고 출처

• Oracle Database 19c SQL Tuning Guide – Chapter 18. Managing Stored Outlines
• Oracle DBMS_OUTLN Package Reference
• Oracle 23ai SQL Management Base Overview