옵티마이저 힌트 사용법: 문법·적용 범위·검증

핵심 요약

Optimizer Hint는 SQL Statement 안의 특별한 주석을 통해 옵티마이저의 후보 생성과 실행계획 선택에 영향을 주는 지시입니다.

SELECT /*+ INDEX(e emp_dept_ix) */
       e.empno,
       e.ename
FROM emp e
WHERE e.deptno = :deptno;

Hint는 다음 항목에 영향을 줄 수 있습니다.

• Optimizer Goal
• Access Path
• Join Order
• Join Method
• Query Transformation
• Parallel Execution
• 일부 DML 처리 방식

그러나 Hint는 작성했다는 사실만으로 성공이 보장되는 강제 명령이 아닙니다. 문법·Alias·Query Block이 잘못되거나, Hint끼리 충돌하거나, 지시한 경로를 사용할 수 없으면 Oracle은 오류를 반환하지 않고 Hint의 일부 또는 전체를 무시할 수 있습니다.

Hint 사용의 완료 기준
  ≠ SQL Text에 Hint 주석이 존재함

Hint 사용의 완료 기준
  = 실제 Cursor의 실행계획에서 적용 상태와 작업량이 확인됨

Hint를 사용할 때는 다음 네 가지를 확인합니다.

1. Hint 문법과 위치가 올바른가?
2. SQL에서 사용한 Alias와 정확히 일치하는가?
3. 의도한 Query Block을 정확히 가리키는가?
4. 실제 Cursor의 Hint Usage Report와 실행 통계에서 적용되었는가?

이 이론의 범위

이 이론은 SQLP의 SQL 옵티마이저 → SQL 옵티마이징 원리 범위에서 Hint 문법, 적용 범위, 대표 분류, 조합 원리와 검증 절차를 다룹니다. 각 Index Scan·Join Method·Query Transformation·Parallel DML의 내부 동작과 SQL Plan Management의 운영 명령은 후속 이론에서 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Optimizer Hint의 목적과 한계를 설명한다.
• Hint 주석의 올바른 위치와 두 가지 기본 형식을 작성한다.
• 한 Statement Block에 작성할 수 있는 Hint 주석의 수를 설명한다.
• 여러 Hint를 하나의 주석 안에 작성하는 방법을 설명한다.
• FROM 절 Alias와 Hint의 Object 인자를 일치시킨다.
• Query Block과 QB_NAME의 필요성을 설명한다.
• Local Hint와 Query Block을 지정한 Global Hint를 구분한다.
• Access Path, Join Order, Join Method Hint를 구분한다.
• LEADING, USE_NL, INDEX Hint의 관계를 설명한다.
• Hint가 무시되는 대표 원인을 설명한다.
• SQL Plan Baseline, SQL Patch, SQL Profile의 Hint와 다른 역할을 구분한다.
• 실제 Cursor와 DBMS_XPLAN의 Hint Usage Report로 적용 여부를 검증한다.
• EXPLAIN PLAN과 실제 Cursor Plan의 차이를 설명한다.

1. Hint란 무엇인가

Hint는 SQL 작성자가 옵티마이저의 일반적인 판단에 추가 정보를 제공하여 특정 후보를 우선 검토하거나 일부 후보를 억제하도록 하는 지시입니다.

대표적으로 다음과 같은 선택에 영향을 줄 수 있습니다.

Hint를 적용해도 SQL의 논리적 결과 의미는 유지되어야 합니다. Hint 적용 후에는 성능뿐 아니라 다음 결과 정합성도 확인합니다.

• NULL 처리
• 중복 행
• Outer Join에서 보존되는 행
• 정렬 순서
• Top-N 경계값
• DML 대상 행

2. Hint의 기본 문법

2.1 SQL Statement Block 시작 키워드 바로 뒤에 작성한다

Hint 주석은 Statement Block을 시작하는 다음 키워드 바로 뒤에 위치해야 합니다.

SELECT
INSERT
UPDATE
DELETE
MERGE

올바른 예시는 다음과 같습니다.

SELECT /*+ FULL(e) */
       e.empno,
       e.ename
FROM emp e;

다음 주석은 +가 없으므로 일반 주석이며 Hint로 해석되지 않습니다.

SELECT /* FULL(e) */
       e.empno,
       e.ename
FROM emp e;

/*+에서 +는 주석 시작 기호 바로 뒤에 있어야 합니다.

2.2 한 Statement Block에는 Hint 주석을 하나만 작성한다

하나의 Statement Block에는 Hint가 들어 있는 주석을 하나만 작성합니다. 여러 Hint가 필요하면 하나의 Hint 주석 안에서 공백으로 구분합니다.

SELECT /*+ LEADING(d e) USE_NL(e) INDEX(e emp_dept_ix) */
       d.dname,
       e.empno
FROM dept d
JOIN emp e
  ON e.deptno = d.deptno;

다음처럼 Hint 주석을 여러 개로 나누지 않습니다.

SELECT /*+ LEADING(d e) */
       /*+ USE_NL(e) */
       d.dname,
       e.empno
FROM dept d
JOIN emp e
  ON e.deptno = d.deptno;

2.3 한 줄 주석 형식도 사용할 수 있다

Oracle은 --+ 형식도 지원합니다.

SELECT --+ FULL(e)
       e.empno,
       e.ename
FROM emp e;

--+ 형식은 줄바꿈 전까지가 하나의 주석이므로 Hint와 필요한 인자를 같은 줄에 작성해야 합니다. 실무에서는 여러 Hint를 읽기 쉽게 작성하기 위해 /*+ ... */ 형식을 주로 사용합니다.

3. SQL에서 사용한 Object 이름과 Hint 인자를 일치시킨다

테이블 관련 Hint의 Object 인자는 SQL Statement에 나타난 이름과 정확히 일치해야 합니다.

3.1 Alias가 없는 경우

SELECT /*+ FULL(emp) */
       empno,
       ename
FROM emp;

3.2 Alias가 있는 경우

SELECT /*+ FULL(e) */
       e.empno,
       e.ename
FROM emp e;

FROM 절에서 EMP에 E라는 Alias를 부여했다면 다음 Hint는 의도대로 적용되지 않을 수 있습니다.

SELECT /*+ FULL(emp) */
       e.empno,
       e.ename
FROM emp e;

기본 원칙은 다음과 같습니다.

SQL이 Object를 참조한 이름
  = Hint가 Object를 참조하는 이름

스키마가 SQL에 표시되더라도 테이블 관련 Hint에는 일반적으로 스키마명을 붙이지 않고 SQL에 나타난 테이블명 또는 Alias를 사용합니다.

4. Query Block과 Hint 적용 범위

복잡한 SQL에는 여러 개의 SELECT가 존재할 수 있으며 각 SELECT는 하나의 Query Block을 구성합니다.

SELECT e.empno,
       e.ename
FROM emp e
WHERE e.deptno IN (
    SELECT d.deptno
    FROM dept d
    WHERE d.loc = 'SEOUL'
);

바깥 Query Block
  → EMP를 조회하는 SELECT

안쪽 Query Block
  → DEPT를 조회하는 SELECT

Hint 대상 Query Block이 불명확하면 다음 문제가 발생할 수 있습니다.

• 바깥 Block의 Hint가 안쪽 Object를 제대로 참조하지 못함
• Inline View Merging 후 원래 Query Block이 달라짐
• Subquery Unnesting 후 Object의 내부 위치가 달라짐
• 같은 Alias가 여러 Query Block에 존재해 대상을 혼동함

4.1 QB_NAME으로 Query Block을 식별한다

SELECT /*+ QB_NAME(main_qb) */
       e.empno,
       e.ename
FROM emp e
WHERE e.deptno IN (
    SELECT /*+ QB_NAME(dept_qb) */
           d.deptno
    FROM dept d
    WHERE d.loc = 'SEOUL'
);

MAIN_QB
  → 바깥 SELECT

DEPT_QB
  → 안쪽 SELECT

4.2 Local Hint와 Query Block 지정 Hint

Hint가 위치한 Query Block의 Object를 대상으로 할 때는 Query Block 이름을 생략할 수 있습니다.

SELECT /*+ QB_NAME(main_qb) FULL(e) */
       e.empno
FROM emp e;

지원되는 Hint에서는 @query_block_name 형식으로 특정 Query Block을 지정할 수 있습니다.

SELECT /*+ QB_NAME(main_qb)
           FULL(@main_qb e) */
       e.empno
FROM emp e;

Query Block 이름은 Hint의 적용 대상을 명확히 하는 식별자입니다. QB_NAME을 작성한 사실만으로 Transformation을 막거나 특정 계획을 강제하는 것은 아닙니다.

5. 자주 사용하는 Hint 분류

모든 Hint 이름을 먼저 외우기보다 다음 순서로 학습하는 것이 안전합니다.

1. 해당 Access Path와 Join 방식의 작동 원리를 이해한다.
2. 옵티마이저가 왜 현재 계획을 선택했는지 확인한다.
3. 대안 계획의 작업량을 검증할 때 필요한 Hint만 사용한다.

GATHER_PLAN_STATISTICS는 계획 모양을 특정 방식으로 강제하기 위한 Hint가 아니라, 실제 Operation별 실행 통계를 확인하기 위한 진단 목적의 Hint입니다.

6. Join Hint를 조합하는 원리

다음 SQL을 보겠습니다.

SELECT /*+ LEADING(d e)
           USE_NL(e)
           INDEX(e emp_dept_ix) */
       d.dname,
       e.empno,
       e.ename
FROM dept d
JOIN emp e
  ON e.deptno = d.deptno
WHERE d.loc = :loc;

Hint가 표현하는 의도는 다음과 같습니다.

LEADING(d e)
  → D를 먼저 읽고 E를 다음에 조인하도록 유도

USE_NL(e)
  → E가 Nested Loops Join의 Inner Row Source가 되도록 유도

INDEX(e emp_dept_ix)
  → E 접근에 EMP_DEPT_IX를 사용하도록 유도

이를 처리 흐름으로 표현하면 다음과 같습니다.

1. DEPT에서 조건에 맞는 선행 행을 만든다.
2. 선행 DEPT 행마다 EMP를 반복 탐색한다.
3. EMP 탐색에 EMP_DEPT_IX를 사용한다.

6.1 USE_NL(e)와 Join Order의 관계

USE_NL(e)는 E를 Nested Loops Join의 Inner Row Source로 사용하는 방향을 지시합니다.

D → E
  → E를 Inner로 사용할 수 있음

E → D
  → E가 선행 Row Source이므로 USE_NL(E)의 의도와 맞지 않을 수 있음

따라서 Join Method Hint만 보지 말고 다음 항목을 함께 확인합니다.

• 실제 Join Order
• 지정한 Object가 Outer인지 Inner인지
• 해당 Object의 Access Path
• 선행 Row Source의 실제 행 수
• Inner 접근의 반복 횟수

같은 원리는 USE_HASH(e)와 USE_MERGE(e)를 해석할 때도 Join Order와 함께 확인해야 합니다.

7. Hint가 무시되거나 의도와 다르게 적용되는 원인

7.1 문법과 위치 오류

• /*+ 또는 --+ 형식이 아님
• Statement Block 시작 키워드 바로 뒤에 있지 않음
• Hint 이름이나 인자에 오타가 있음
• 한 Statement Block의 Hint를 여러 Hint 주석으로 분리함

Oracle은 잘못된 Hint 때문에 SQL 전체를 항상 오류 처리하지 않습니다. 같은 주석 안의 올바른 다른 Hint는 별도로 고려될 수 있습니다.

7.2 Alias 불일치

SQL
  → FROM EMP E

Hint
  → FULL(EMP)

결과
  → SQL에서 Object가 E로 노출되므로 Hint 대상 불일치

7.3 Query Block 불일치

• 바깥 Query Block에서 안쪽 Subquery Object를 잘못 참조
• QB_NAME을 잘못 지정하거나 중복 사용
• Transformation 후 Object가 다른 Query Block으로 이동
• 동일한 Alias가 여러 Query Block에 존재

7.4 서로 충돌하는 Hint

SELECT /*+ FULL(e) INDEX(e emp_dept_ix) */
       e.empno
FROM emp e;

같은 Object에 서로 반대되는 Access Path를 동시에 지시하면 충돌할 수 있습니다. 관련 Hint가 무시되거나 하나의 Hint만 최종 계획에 반영될 수 있습니다.

7.5 실행할 수 없는 경로

• 지정한 인덱스가 존재하지 않음
• 인덱스가 사용할 수 없는 상태임
• Join 조건이 지정한 Join Method에 적합하지 않음
• Outer Join의 순서 제약과 충돌함
• SQL 구조상 지정한 Access Path를 사용할 수 없음

7.6 Transformation 이후 대상이 달라짐

View Merging이나 Subquery Unnesting 등으로 Query Block 구조가 변하면 원래 작성한 Hint의 Object와 Query Block 대상이 달라질 수 있습니다.

이 경우 다음 항목을 확인합니다.

• Transformation 전 Query Block 이름
• 최종 실행계획의 Query Block 이름과 Object Alias
• Hint Usage Report의 사용·미사용 상태와 사유
• Outline에 나타난 최종 계획 재현 정보

7.7 다른 계획 관리 수단과 함께 사용됨

다음 기능은 Hint와 관련될 수 있지만 역할이 서로 다릅니다.

SQL Profile은 특정 실행계획을 직접 고정하는 기능과는 구분해야 합니다. SQL Plan Baseline이나 SQL Patch가 함께 적용되면 SQL Text에 작성한 Hint만 보고 최종 계획을 예측하기 어렵기 때문에 실제 Cursor의 Note·Outline·Hint Report를 확인합니다.

8. Hint 적용 여부를 검증하는 방법

Hint 사용의 핵심은 실제 실행된 Cursor를 확인하는 것입니다.

8.1 대표 Bind 값으로 SQL을 실행한다

SELECT /*+ GATHER_PLAN_STATISTICS
           LEADING(d e)
           USE_NL(e)
           INDEX(e emp_dept_ix) */
       d.dname,
       e.empno,
       e.ename
FROM dept d
JOIN emp e
  ON e.deptno = d.deptno
WHERE d.loc = :loc;

GATHER_PLAN_STATISTICS를 사용하거나 Session의 STATISTICS_LEVEL을 ALL로 설정하면 ALLSTATS LAST에서 Operation별 실제 실행 통계를 확인할 수 있습니다.

8.2 SQL ID와 Child Cursor를 확인한다

같은 SQL Text에도 Optimizer 환경이나 Bind 특성에 따라 여러 Child Cursor가 존재할 수 있습니다.

검증 대상은 다음과 같이 명확해야 합니다.

SQL ID
Child Number
실행에 사용한 Bind 값
실행한 Session의 Optimizer 환경

8.3 실제 Cursor Plan과 Hint Usage Report를 확인한다

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id          => :sql_id,
        cursor_child_no => :child_no,
        format          => 'ALLSTATS LAST ALIAS NOTE HINT_REPORT'
    )
);

다음 내용을 확인합니다.

• 의도한 Access Path가 실제로 선택되었는가?
• LEADING에 따른 Join Order가 적용되었는가?
• 지정 Object가 의도한 Join의 Inner Row Source인가?
• 예상 Rows와 실제 Rows의 차이는 어느 Operation에서 시작되는가?
• Operation의 Starts와 Buffer 작업량은 합리적인가?
• Query Block Name과 Object Alias가 의도와 일치하는가?
• Hint Usage Report에서 각 Hint가 사용되었는가?
• 사용되지 않은 Hint의 이유가 표시되는가?
• Note에 Baseline, Profile, Dynamic·Adaptive 관련 정보가 있는가?

Hint Usage Report는 지원 버전에서 DBMS_XPLAN의 HINT_REPORT 형식으로 확인합니다. TYPICAL은 일반적으로 최종 계획에서 사용되지 않은 Hint를 보여 주고, ALL 또는 HINT_REPORT를 포함한 상세 형식은 사용·미사용 Hint를 더 폭넓게 확인하는 데 활용할 수 있습니다.

8.4 ALLSTATS LAST의 전제

ALLSTATS LAST를 작성했다고 실제 통계가 자동으로 존재하는 것은 아닙니다.

실행 통계 수집 조건 예시
  → GATHER_PLAN_STATISTICS Hint 사용
  → STATISTICS_LEVEL = ALL

실행 통계가 수집되지 않았다면 예상 계획 정보는 보이더라도 실제 Operation별 Rows와 I/O 통계가 충분히 표시되지 않을 수 있습니다.

8.5 EXPLAIN PLAN과 실제 Cursor Plan을 구분한다

EXPLAIN PLAN은 SQL을 실제로 끝까지 실행하지 않고 예상 계획을 Plan Table에 저장합니다.

다음 요소가 운영 실행과 달라질 수 있습니다.

• 실제 Bind 값과 Bind Type
• 실제 사용된 Child Cursor
• Session Parameter
• Adaptive 기능의 최종 선택
• 실행 통계
• 현재 Cursor에 연결된 계획 관리 정보

따라서 Hint 문법과 후보 계획을 빠르게 확인할 때 EXPLAIN PLAN을 사용할 수 있지만, 최종 검증은 실제 실행된 Cursor를 DBMS_XPLAN.DISPLAY_CURSOR로 확인하는 것이 더 적합합니다.

9. Hint를 사용하는 안전한 순서

1. 업무 결과와 성능 목표를 확정한다.
2. Hint가 없는 실제 SQL과 Cursor Plan을 확인한다.
3. 예상 Rows와 실제 Rows가 어긋나는 지점을 찾는다.
4. SQL·통계·제약조건·인덱스·데이터 분포의 근본 원인을 점검한다.
5. 비교하려는 대안 계획에 필요한 Hint만 최소한으로 작성한다.
6. Alias·Query Block·Hint 충돌·실행 가능성을 확인한다.
7. SQL ID와 Child Cursor를 지정해 Hint Usage Report를 확인한다.
8. 실제 Rows·Starts·I/O·응답시간을 비교한다.
9. 대표값·인기값·희소값·대량 반환값에서 회귀 검증한다.
10. Hint 사용 이유, 적용 범위, 제거 조건을 문서화한다.

Hint는 튜닝 원리를 대신하는 도구가 아닙니다. 원인을 이해한 뒤 대안 계획을 검증하거나 제한적으로 계획을 제어하는 수단입니다.

10. Hint 적용 후 확인해야 할 품질 기준

Hint 적용 후 Cost 숫자만 낮아졌다고 튜닝이 완료된 것은 아닙니다. 예상과 실제 작업량, 결과 정합성, 운영 부작용을 함께 검증해야 합니다.

11. 자주 혼동하는 판단과 정확한 기준

12. 핵심 정리

Hint 위치
  → Statement Block 시작 키워드 바로 뒤

Hint 형식
  → /*+ ... */
  → --+ ...  (한 줄)

Hint 주석 수
  → Statement Block당 하나
  → 여러 Hint는 같은 주석 안에서 공백으로 구분

Object 지정
  → SQL에서 사용한 테이블명 또는 Alias와 정확히 일치

Query Block
  → 필요하면 QB_NAME으로 식별
  → @query_block_name으로 지원 Hint의 대상 지정

Join Hint
  → Join Order와 Inner·Outer 관계를 함께 확인

검증
  → 실제 SQL ID·Child Cursor
  → DBMS_XPLAN.DISPLAY_CURSOR
  → ALIAS·NOTE·HINT_REPORT
  → ALLSTATS LAST는 실행 통계 수집이 전제

좋은 Hint 사용은 Hint를 많이 작성하는 것이 아닙니다. 필요한 결정만 최소한으로 제어하고, 실제 Cursor에서 적용 상태와 작업량을 확인하며, 데이터와 운영 환경 변화에도 안전한지 검증하는 것입니다.