DevelopOracle

Oracle - 힌트 사용 전략

Oracle 힌트 사용 전략

오라클 힌트를 효과적으로 사용하여 쿼리 실행 계획을 제어하는 방법을 다룹니다.

힌트는 옵티마이저에게 특정 실행 방법을 지시하는 강력한 도구입니다. 하지만 잘못 사용하면 오히려 성능을 저하시킬 수 있으므로 신중하게 사용해야 합니다.

자주 사용하는 힌트

PARALLEL 힌트

대용량 데이터 처리에 효과적입니다.

-- PARALLEL 힌트 (대용량 처리)
SELECT /*+ PARALLEL(e, 4) */ *
FROM employees e
WHERE hire_date >= DATE '2020-01-01';

-- 테이블별 병렬도 지정
SELECT /*+ PARALLEL(e, 4) PARALLEL(d, 2) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- AUTO 병렬도
SELECT /*+ PARALLEL(e, AUTO) */ *
FROM employees e;

-- 주의사항:
-- - CPU 리소스 집약적
-- - 배치 작업에 적합
-- - OLTP 환경에서는 신중하게 사용

APPEND 힌트

Direct Path Insert로 성능 향상.

-- APPEND 힌트 (Direct Path Insert)
INSERT /*+ APPEND */ INTO employees_backup
SELECT * FROM employees;

-- PARALLEL과 조합
INSERT /*+ APPEND PARALLEL(employees_backup, 4) */
INTO employees_backup
SELECT * FROM employees;

-- 장점:
-- - Redo 로그 최소화
-- - 빠른 INSERT
-- - 단, Undo 공간 절약 안 됨

FIRST_ROWS 힌트

빠른 응답이 중요한 경우 사용.

-- FIRST_ROWS 힌트 (빠른 응답)
SELECT /*+ FIRST_ROWS(10) */ *
FROM employees
WHERE department_id = 10
ORDER BY hire_date DESC;

-- OLTP 애플리케이션에 적합
SELECT /*+ FIRST_ROWS(1) */ *
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC;

-- 특징:
-- - 첫 N개 행을 빠르게 반환
-- - Index Scan 선호
-- - 전체 처리량보다 응답 속도 우선

ALL_ROWS 힌트

전체 처리량 최적화.

-- ALL_ROWS 힌트 (전체 처리량 최적화)
SELECT /*+ ALL_ROWS */ *
FROM employees
WHERE department_id = 10;

-- 배치 작업에 적합
SELECT /*+ ALL_ROWS */ COUNT(*)
FROM large_table;

-- 특징:
-- - 전체 데이터 처리에 최적화
-- - Full Table Scan 선호 가능
-- - 배치 작업에 적합

최적화 목표 힌트

-- RULE 힌트 (비권장 - 레거시)
SELECT /*+ RULE */ *
FROM employees
WHERE department_id = 10;

-- CHOOSE 힌트 (기본값)
SELECT /*+ CHOOSE */ *
FROM employees;

-- OPTIMIZER_FEATURES_ENABLE
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('19.1.0') */ *
FROM employees;

액세스 방법 힌트

-- FULL 힌트 (Full Table Scan 강제)
SELECT /*+ FULL(e) */ *
FROM employees e
WHERE department_id = 10;

-- ROWID 힌트
SELECT /*+ ROWID(e) */ *
FROM employees e
WHERE rowid = 'AAAHYdAABAAAV0KAAA';

-- CLUSTER 힌트
SELECT /*+ CLUSTER(e) */ *
FROM employees e
WHERE department_id = 10;

조인 힌트

조인 방식과 순서를 제어합니다.

-- USE_NL (Nested Loop Join)
SELECT /*+ USE_NL(e d) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- USE_HASH (Hash Join)
SELECT /*+ USE_HASH(e d) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- USE_MERGE (Merge Join)
SELECT /*+ USE_MERGE(e d) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- LEADING (조인 순서 지정)
SELECT /*+ LEADING(d e) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- ORDERED (FROM 절 순서대로)
SELECT /*+ ORDERED */
       e.employee_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id;

쿼리 변환 힌트

-- UNNEST (서브쿼리를 조인으로 변환)
SELECT /*+ UNNEST */ *
FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

-- NO_UNNEST (서브쿼리 유지)
SELECT /*+ NO_UNNEST */ *
FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700
);

-- MERGE (뷰 병합)
SELECT /*+ MERGE(v) */ *
FROM (
  SELECT * FROM employees WHERE salary > 5000
) v
WHERE v.department_id = 10;

-- NO_MERGE (뷰 유지)
SELECT /*+ NO_MERGE(v) */ *
FROM (
  SELECT * FROM employees WHERE salary > 5000
) v
WHERE v.department_id = 10;

병렬 처리 힌트

-- PARALLEL (병렬 처리)
SELECT /*+ PARALLEL(e, 4) */ *
FROM employees e;

-- PARALLEL_INDEX (병렬 인덱스 스캔)
SELECT /*+ PARALLEL_INDEX(e, idx_emp_dept, 4) */ *
FROM employees e
WHERE department_id = 10;

-- PQ_DISTRIBUTE (병렬 조인 분배 방식)
SELECT /*+ PARALLEL(e, 4) PARALLEL(d, 4) PQ_DISTRIBUTE(d, HASH, HASH) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- NO_PARALLEL (병렬 처리 방지)
SELECT /*+ NO_PARALLEL(e) */ *
FROM employees e;

기타 유용한 힌트

-- CACHE (테이블을 버퍼 캐시에 유지)
SELECT /*+ CACHE(e) */ *
FROM employees e;

-- NOCACHE (캐시하지 않음)
SELECT /*+ NOCACHE(e) */ *
FROM employees e;

-- PUSH_PRED (조건을 뷰 안으로 이동)
SELECT /*+ PUSH_PRED(v) */ *
FROM (
  SELECT * FROM employees
) v
WHERE v.department_id = 10;

-- NO_PUSH_PRED (조건 이동 방지)
SELECT /*+ NO_PUSH_PRED(v) */ *
FROM (
  SELECT * FROM employees
) v
WHERE v.department_id = 10;

-- RESULT_CACHE (결과 캐싱)
SELECT /*+ RESULT_CACHE */ department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- NO_RESULT_CACHE (결과 캐싱 방지)
SELECT /*+ NO_RESULT_CACHE */ *
FROM employees;

힌트 조합

-- 복잡한 쿼리의 힌트 조합
SELECT /*+
  LEADING(d l e)
  USE_NL(l e)
  INDEX(e idx_emp_dept)
  PARALLEL(e, 4)
  FIRST_ROWS(100)
*/
  e.employee_id,
  e.last_name,
  d.department_name,
  l.city
FROM departments d,
     locations l,
     employees e
WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND l.country_id = 'US';

힌트 사용 시 주의사항

-- 1. 테이블 Alias 일치
-- 잘못된 예
SELECT /*+ FULL(employees) */ *
FROM employees e;

-- 올바른 예
SELECT /*+ FULL(e) */ *
FROM employees e;

-- 2. 힌트 문법 오류 확인
-- 잘못된 예 (공백 없음)
SELECT /*+FULL(e)*/ *
FROM employees e;

-- 올바른 예
SELECT /*+ FULL(e) */ *
FROM employees e;

-- 3. 힌트 충돌 방지
-- 충돌 예
SELECT /*+ FULL(e) INDEX(e idx_emp_dept) */ *
FROM employees e;

-- 올바른 예 (하나만 선택)
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e;

힌트 효과 확인

-- 실행 계획에서 힌트 적용 여부 확인
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(e, 4) */ *
FROM employees e;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Note 섹션 확인:
-- - "Degree of Parallelism is 4" (힌트 적용됨)
-- - "ignored" (힌트 무시됨)

-- 실행 계획 비교
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

힌트 사용 전략

  1. 힌트 없이 먼저 테스트: 옵티마이저가 자동으로 최적 계획 선택
  2. 통계 정보 확인: 최신 통계 정보 유지
  3. 실행 계획 분석: 문제점 파악 후 힌트 적용
  4. 단계적 적용: 한 번에 하나씩 힌트 추가
  5. 효과 검증: 힌트 적용 전후 성능 비교
  6. 문서화: 힌트 사용 이유 주석으로 기록

힌트를 사용하지 말아야 할 경우

  • 옵티마이저가 이미 최적 계획을 선택한 경우
  • 통계 정보가 정확하고 최신인 경우
  • 데이터 분포가 자주 변경되는 경우
  • 애플리케이션 이식성이 중요한 경우
  • 힌트의 효과를 정확히 이해하지 못한 경우

실전 예제

-- 예제 1: 대용량 배치 작업
INSERT /*+ APPEND PARALLEL(emp_summary, 8) */
INTO emp_summary
SELECT /*+ PARALLEL(e, 8) FULL(e) */
       department_id,
       COUNT(*) as emp_count,
       AVG(salary) as avg_salary
FROM employees e
GROUP BY department_id;

-- 예제 2: OLTP 페이징 쿼리
SELECT /*+ FIRST_ROWS(20) INDEX_DESC(o pk_orders) */ *
FROM orders o
WHERE customer_id = 100
  AND order_date >= SYSDATE - 30
ORDER BY order_date DESC
FETCH FIRST 20 ROWS ONLY;

-- 예제 3: 복잡한 조인 최적화
SELECT /*+
  LEADING(l d e j)
  USE_HASH(d e)
  USE_NL(j)
  PARALLEL(e, 4)
  FULL(e)
*/
  e.employee_id,
  e.last_name,
  d.department_name,
  l.city,
  j.job_title
FROM locations l,
     departments d,
     employees e,
     jobs j
WHERE l.location_id = d.location_id
  AND d.department_id = e.department_id
  AND e.job_id = j.job_id
  AND l.country_id = 'US';

관련 문서

Share