[ Oracle ] 오라클 힌트(Hint) 개념 및 사용법 정리

---

SQL 튜닝 - Oracle Hint 개념

 

오라클 힌트는 SQL 튜닝의 핵심 부분으로 일종의 지시 구문입니다.

오라클 옵티마이저(Optimizer)에게 SQL문 실행을 위한 데이터를 스캐닝하는 경로,

조인하는 방법 등을 알려주기 위해 SQL 사용자가 SQL 구문에 작성하는 것을 뜻합니다.

 

오라클이 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에

직접 최적의 실행 경로를 작성해 주는 것인데, 사용자가 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지

알고 있는 경우 옵티마이저에 의존한 실행 계획보다 훨씬 효율적인 실행 계획을 구사할 수 있습니다.

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

 

정리해 보면

오라클 힌트는 SQL문 내에 주석 형태로 포함되며, 옵티마이저가 SQL 문을 분석할 때

힌트를 참고하여 특정 실행 계획을 선택하도록 유도합니다.

단, 힌트는 선택적인 것이므로 옵티마이저가 힌트를 무시할 수도 있습니다.

 

그렇다면 성능 최적화를 위해 모든 쿼리에 힌트를 적용해야 할까요? 

힌트를 사용했을 때 장단점을 나열해 보면 다음과 같습니다.

 

[ 오라클 힌트 장단점 ]

 

• 장점
  • 성능 향상 : 특정 쿼리에 대해 더 나은 성능을 제공하는 실행 계획을 강제할 수 있습니다.
  • 제어력 : 쿼리 실행 계획에 대한 제어력을 가질 수 있어, 자동 최적화가 실패하는 경우에도 효율적인 실행을 보장할 수 있습니다.
  • 문제 해결 : 성능 문제가 발생할 때, 힌트를 사용하여 문제를 해결할 수 있습니다.
• 단점
  • 유지 보수 : 힌트는 특정 실행 계획에 의존하기 때문에 데이터 분포나 통계가 변하면 쿼리 성능이 떨어질 수 있습니다.
  • 휴대성 부족 : 힌트가 있는 쿼리는 다른 데이터베이스 시스템에서는 동작하지 않을 수 있습니다.
  • 오버라이딩 : 힌트는 항상 옵티마이저가 이를 따르지 않을 수 있습니다.
  • 복잡성 : 쿼리에 너무 많은 힌트를 사용하면 쿼리가 복잡해지고 가독성이 떨어질 수 있습니다.

 

장점만 보면 SQL 튜닝할 때 꼭 필요한 구문인데, 단점을 보면 생각이 많아지는 구문이기도 합니다.

인덱스, 조인의 개념을 정확히 알고 사용하지 않은 무분별한 힌트의 사용은 성능 저하를 초래하기 때문에

최적의 실행 경로를 알고 있을 경우 적절하게 사용하면 될 거 같습니다.

 

추가로 힌트를 사용했을 때 오타가 있는 경우에는 잘못 사용된 힌트는

무시되어 힌트가 없는 것처럼 동작하기 때문에 큰 관계가 없습니다.

---

SQL 튜닝 - Oracle Hint 사용법

 

힌트는 Oracle 옵티마이저에게 힌트를 준다고 생각해도 무방합니다.

사용하는 방법은 아래의 쿼리문과 같이 /*+ */ 안에 지정하는 형태로 사용합니다.

 

만약 /*+ FULL(tb_empl) */라는 힌트를 사용했다면 tb_empl 테이블에 대해 풀 테이블 스캔을 사용하라고 지시합니다.

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

---

SQL 튜닝 - Oracle Hint 종류와 예제

 

그렇다면 힌트의 종류를 예제 코드를 통해서 개념과 사용법을 알아봅시다.

 

• FULL 힌트
  • 위에서 언급했듯 특정 테이블에 대해 풀 테이블 스캔을 사용하도록 지시합니다.

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

 

• INDEX 힌트
  • 특정 인덱스를 사용하도록 지시합니다.

SELECT /*+ INDEX(tb_empl empl_idx1) */ * 
FROM tb_empl 
WHERE empno = 1234;

 

• NO_INDEX 힌트
  • 특정 인덱스를 사용하지 않도록 지시합니다.

SELECT /*+ NO_INDEX(tb_empl empl_idx1) */ * 
FROM tb_empl 
WHERE empno = 1234;

 

• FIRST_ROWS 힌트
  • Query 결과의 첫 번째 몇 개의 행을 빨리 반환하도록 최적화합니다.

SELECT /*+ FIRST_ROWS(10) */ * 
FROM tb_empl 
WHERE deptno = 10;

 

• ALL_ROWS 힌트
  • 전체 Query 결과를 최적화합니다.

SELECT /*+ ALL_ROWS */ * 
FROM tb_empl 
WHERE deptno = 10;

 

• USE_NL 힌트
  
  • 중첩 루프 조인을 사용하도록 지시합니다.

SELECT /*+ USE_NL(tb_empl tb_dept) */ * 
FROM tb_empl e, tb_dept d 
WHERE e.deptno = d.deptno;

 

• USE_MERGE 힌트
  • 병합 조인을 사용하도록 지시합니다.

SELECT /*+ USE_MERGE(tb_empl tb_dept) */ * 
FROM tb_empl e, tb_dept d 
WHERE e.deptno = d.deptno;

 

• USE_HASH 힌트
  • 해시 조인을 사용하도록 지시합니다.

SELECT /*+ USE_HASH(tb_empl tb_dept) */ * 
FROM tb_empl e, tb_dept d 
WHERE e.deptno = d.deptno;

 

 

[ 인덱스 성능 최적화 예제 ]

-- 인덱스를 사용하여 성능 최적화
SELECT /*+ INDEX(tb_empl emp_idx1) */ empno, ename 
FROM tb_empl 
WHERE deptno = 10;

-- 병합 조인을 사용하여 성능 최적화
SELECT /*+ USE_MERGE(tb_empl tb_dept) */ empno, ename, dname 
FROM tb_empl 
JOIN tb_dept ON emp.deptno = dept.deptno;

 

정리하자면

오라클 힌트를 사용하면 특정 상황에서 쿼리 성능을 크게 개선할 수 있지만,

항상 옵티마이저의 기본 동작을 신뢰하고, 힌트는 필요한 경우에만 신중하게 사용해야 합니다.

 

힌트를 사용해서 튜닝하는 방법 말고도 일반적인 SQL 튜닝 기법이 있는데

성능 개선을 위해 알아보시는 분들을 위해 아래 박스로 간단하게 정리해 보겠습니다.

 

■ SQL 튜닝 기법

1. 바인드 변수를 사용한다.
: 상수 값이 변경되어도 이전 질의를 사용함으로 최적화 실행 단계를 줄인다.
2. 가급적 WHERE 절의 조건에 인덱스 칼럼을 모두 사용한다.
3. 인덱스 칼럼에 사용하는 연산자는 가급적 = 을 사용한다.
4. 인덱스 칼럼은 변형을 사용하지 않는다.
5. OR 보다는 AND를 사용한다.
6. HAVING 보다는 가급적 WHERE 절에서 데이터를 필터링한다.
7. DISTINCT는 가급적 사용하지 않는다.

---

마치며

 

오늘은 Oracle Hint 구문에 대해 알아봤습니다.

다음 포스팅에서 뵙겠습니다.