오라클 힌트 조회

오라클 힌트(Oracle Hints)는 SQL 실행 계획을 최적화하기 위해 개발자가 직접 오라클 옵티마이저(Optimizer)에 지시를 내릴 수 있도록 하는 기능입니다. /*+ HINT */ 형식으로 SQL 문 내부에 주석 형태로 작성됩니다.

---

오라클 힌트 기본 문법

SELECT /*+ HINT_NAME */ column1, column2 
FROM table_name;

• /*+ HINT_NAME */ 부분에 적용할 힌트를 작성합니다.
• 여러 개의 힌트를 적용할 수도 있습니다.

---

주요 오라클 힌트 종류 및 예시

1) 실행 계획 관련 힌트
(1) FULL(table) – 전체 테이블 스캔 강제

SELECT /*+ FULL(emp) */ * 
FROM emp 
WHERE deptno = 10;

• emp 테이블을 인덱스를 사용하지 않고 Full Table Scan하도록 유도합니다.

(2) INDEX(table index_name) – 특정 인덱스 사용

SELECT /*+ INDEX(emp emp_idx) */ * 
FROM emp 
WHERE deptno = 10;

• emp 테이블의 emp_idx 인덱스를 사용하도록 유도합니다.

(3) NO_INDEX(table index_name) – 특정 인덱스 사용 금지

SELECT /*+ NO_INDEX(emp emp_idx) */ * 
FROM emp 
WHERE deptno = 10;

• emp 테이블에서 emp_idx 인덱스를 사용하지 않도록 설정합니다.

(4) ROWID 접근 방식 강제

SELECT /*+ ROWID(emp) */ * 
FROM emp 
WHERE ROWID = 'AAACpWAABAAABSsAAA';

• ROWID를 이용하여 데이터를 빠르게 접근합니다.

2) 조인 관련 힌트
(5) USE_NL(outer_table inner_table) – NL 조인 강제

SELECT /*+ USE_NL(emp dept) */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;

• Nested Loop Join(NL 조인)을 강제 적용합니다.

(6) USE_HASH(outer_table inner_table) – 해시 조인 강제

SELECT /*+ USE_HASH(emp dept) */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;

• 해시 조인(Hash Join) 방식으로 실행하도록 유도합니다.

(7) ORDERED – 조인 순서 강제

SELECT /*+ ORDERED */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;

• FROM 절에 명시된 순서대로 조인을 수행합니다.

3) 병렬 처리 관련 힌트
(8) PARALLEL(table degree) – 병렬 쿼리 실행

SELECT /*+ PARALLEL(emp 4) */ * 
FROM emp;

• emp 테이블을 4개의 병렬 프로세스를 사용하여 조회합니다.

(9) NO_PARALLEL(table) – 병렬 실행 비활성화

SELECT /*+ NO_PARALLEL(emp) */ * 
FROM emp;

• emp 테이블을 병렬 처리를 하지 않고 단일 프로세스로 실행합니다.

4) 기타 최적화 힌트
(10) APPEND – INSERT 성능 향상

INSERT /*+ APPEND */ INTO emp_new 
SELECT * FROM emp;

• Direct Path Insert를 사용하여 데이터를 빠르게 삽입합니다.

(11) FIRST_ROWS(n) – 응답 시간 최적화

SELECT /*+ FIRST_ROWS(10) */ * 
FROM emp 
ORDER BY sal DESC;

• 처음 10개의 행을 빨리 가져오는 방식으로 최적화합니다.

(12) ALL_ROWS – 전체 처리량 최적화

SELECT /*+ ALL_ROWS */ * 
FROM emp 
ORDER BY sal DESC;

• 전체 실행 비용을 최소화하는 방식으로 최적화합니다.

---

복합 힌트 예제

(1) 특정 인덱스를 사용하고, 병렬 처리를 활성화하며, NL 조인을 강제

SELECT /*+ INDEX(emp emp_idx) PARALLEL(emp 4) USE_NL(emp dept) */ 
       emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;

• emp 테이블에서 emp_idx 인덱스를 사용
• emp 테이블을 4개 프로세스로 병렬 처리
• Nested Loop 조인 방식 적용

힌트 사용 시 주의점

1. 옵티마이저가 항상 힌트를 따르는 것은 아님
   • 옵티마이저는 힌트를 무시하고 최적화된 실행 계획을 선택할 수도 있습니다.
2. 잘못된 힌트 사용 시 성능 저하 발생 가능
   • 적절한 힌트를 사용하지 않으면 오히려 성능이 저하될 수 있음.
3. 테스트 필수
   • 실행 계획을 확인 (EXPLAIN PLAN)하여 힌트가 적용되었는지 반드시 테스트해야 합니다.

실행 계획 확인 방법

1) EXPLAIN PLAN 사용

EXPLAIN PLAN FOR 
SELECT /*+ FULL(emp) */ * 
FROM emp;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• 실행 계획을 확인하여 힌트 적용 여부를 체크합니다.

2) AUTOTRACE 옵션 사용

SET AUTOTRACE ON;
SELECT /*+ FULL(emp) */ * FROM emp;

• SQL 실행과 함께 실제 실행 계획을 확인합니다.

---

관련 블로그

오라클 통계정보 갱신