옵티마이저 힌트에 대해서

회사에서 개발을 진행하면서 옵티마이저가 최적이라고 생각하는 실행 계획과 실제 최적의 실행 계획은 종종 차이가 있다는 것을 알게 되었다.

이때, 내가 진행했던 개선 방향은 

1. 해당 테이블에 대해 이미 존재하는 다른 인덱스로 변경하여 개선 가능한가?
2. 인덱스를 수정하거나 새로 추가하여 개선 가능한가?

 

1번으로 해결 가능한 부분은 SQL에 액세스 방식이나 조인 순서, 조인 방식 대한 Hint를 추가하여 개선을 진행했고,
1번으로 해결이 불가능한 경우 즉, 2번의 경우에는 인덱스의 순서를 변경하거나 구성 컬럼을 변경하는 작업을 진행했다.

 

해당 글에서는 1번의 케이스에 적용한 옵티마이저 힌트에 대해서 간단하게 정리하고자 한다.

 

옵티마이저 Hint

오브젝트 통계와 시스템 통계 정보를 바탕으로 최적의 프로시저를 만드는 옵티마이저도 때때로 복잡한 경우에는 한계가 발생한다.

 

이러한 경우에 직접 SQL에 효율적인 경로에 대한 Hint를 부여함으로써 개선할 수 있다.

 

아래는 A 테이블에 대해서 IDX_01라는 인덱스로 액세스하도록 설정하는 힌트를 적용한 예시이다.

SELECT /*+ INDEX(A, IDX_01) */
   x, y, z
FROM A
WHERE A.id = '000001'

 

Hint 사용 주의사항

힌트를 사용할 떄는 주의할 사항이 있다.

 

첫째로, 힌트와 힌트 사이에는 콤마(,)를 사용하면 안 된다.

부적절한 케이스
/* INDEX(A), FULL(B) */   

적절한 케이스
/* INDEX(A) FULL(B) */

 

둘째로, 테이블을 지정할 때, 스키마명까지 명시하면 안 된다.

부적절한 경우
SELECT /*+ FULL(KHS.A) */

적절한 경우
SELECT /*+ FULL(A) */

 

셋째로, FROM 절 테이블명에 Alias를 지정했다면, 힌트에도 Alias를 사용해야 한다.

부적절한 경우
SELECT /*+ FULL(TAB1) */
FROM TAB1 A

적절한 경우
SELECT /*+ FULL(A) */
FROM TAB1 A

 

옵티마이저 반자율

힌트를 적용할경우, 특정 테이블에 대해 액세스 방식만 힌트를 주고 나머지는 옵티마이저에게 맡기는 것도 가능하다.

 

아래의 SQL의 경우 a 테이블을 액세스할 때 IDX_dt라는 인덱스에 접근하도록만 힌트를 명시하고,
조인 방식과 순서 그리고 B 테이블에 대한 액세스 방식은 옵티마이저가 판단하도록 맡겼다.

SELECT /*+ INDEX(a, IDX_dt) */
    x, y, z
FROM A a, B b
WHERE a.dt =: dt
  AND a.id = b.id

 

 

이번에는 SQL에 옵티마이저가 다른 방식을 선택하지 못하도록 힌트를 빈틈없이 고정한 케이스이다.

SELECT /*+ LEADING(A) USE_NL(B) INDEX(a, IDX_dt) INDEX(b, IDX_id) */
    x, y, z
FROM A a, B b
WHERE a.dt =: dt
  AND a.id = b.id

 

 

통계 정보나 실행 환경의 변화로 인해서 옵티마이저가 최적의 경로를 찾지 못할 경우를 대비하여 이미 힌트 적용을 시작했다면 빈틈없이 설정하는 것이 좋은 방향이라고 생각한다.

 

 

여기서 모두 기술하지는 않을 것이지만 다른 힌트 종류를 언급만 하고 마무리하고자 한다.

• 액세스 방식 : FULL, INDEX, INDEX_DESC ...
• 조인 순서 : ORDERD, LEADING...
• 조인 방식 : USE_NL, USE_MERGER, USE_HASH, NL_SG, MERGER_SJ, HASH_SJ
  
  

다른 쿼리 변환, 병렬 처리 등등을 위한 힌트도 존재하지만 현재로서는 액세스 방식, 조인 순서, 조인 방식에만 집중하고자 한다.