Oracle - 반드시 알아야 하는 오라클 힌트절 7가지

 

출처: http://www.dbguide.net/knowledge.db?cmd=view&boardConfigUid=19&boardUid=183970

데이터 전문가 지식포털 DBGuide.net

CBO[1] 방식에서 옵티마이저는 주어진 환경(통계정보, SQL문) 하에서 최적의 실행계획(PLAN)을 제공한다. 그런데 잘못된 SQL문이나 부정확한 통계정보로 인하여 엉뚱한 실행 계획을 제공할 때도 간혹 있다. 이때 힌트절을 통해 잘못된 실행 계획을 바로 잡을 수 있다. 결국 힌트절은 옵티마이저의 실수를 만회할 수 있는 개발자의 마지막 무기라 할 수 있다. 

 

옵티마이저는 개발자가 사용하는 힌트절을 무조건 수용하지는 않는다. 만약 개발자가 터무니없는 힌트절을 사용하고자 한다면 옵티마이저는 무시할 것이다. 앞으로 새로운 버전의 엔진이 나올수록 그런 경향은 더욱 강해질 것이다.

 

SELECT /*+ [힌트절] */ ~

 

SQL문에서 힌트절은 주석문 안에 표시한다. 그래서 잘못된 힌트절로 인하여 에러를 리턴하는 일은 없다. 단지 힌트절의 내용대로 적용되지 않을 뿐이다. 오라클이 제공하는 힌트절의 종류는 굉장히 많다. 수많은 힌트절을 모두 이해하고 상황에 맞게 사용하기는 너무 힘들다. 이에 필자는 가장 많이 사용되는 핵심적인 7가지 힌트절만 소개하고자 한다. 7가지 힌트절만 알아도 대부분의 쿼리 튜닝에 부족함이 없다. 그 힌트절은 다음과 같다.

 

접근 순서를 결정하는 힌트절
1. ORDERED ? FROM 절에 나열된 테이블 순서대로 접근한다(SQL문에 종속적).
2. LEADING ? 테이블 접근 순서를 명시적으로 표시한다(SQL문에 독립적).

접근 방법을 결정하는 힌트절
3. USE_NL ? NESTED LOOP JOIN 방식으로 조인하도록 유도한다(순차적인 접근 방법).
4. USE_HASH ? HASH JOIN 방식으로 조인하도록 유도한다(해시 함수를 이용한 접근 방법).

자원 사용을 결정하는 힌트절
5. INDEX ? 인덱스를 통한 ACCESS PATH 유도한다.
6. FULL ? 테이블을 FULL SCAN한다.
7. PARALLEL ? 병렬 처리를 통하여 성능을 높인다.

 

접근 순서를 결정하는 힌트절 ? ORDERED, LEADING

ORDERED 힌트절과 LEADING 힌트절은 테이블 간 접근 순서를 결정하는 힌트절이다. 아래 인덱스 생성도를 통해 구체적으로 살펴 보자.

 

 

위의 그림에서 인덱스의 위치가 명확하므로 오라클 옵티마이저가 접근 순서를 잘못 판단할 가능성은 거의 없다. 1번과 3번 컬럼에만 인덱스가 존재하므로 테이블 접근 순서는 고객 → 주문이다. 옵티마이저는 인덱스가 있는 방향으로 접근하기 때문이다. 인덱스 생성도에서 인덱스 위치는 방향을 결정하는 중요한 요소다. (1번: 고객 테이블의 고객명 컬럼, 3번: 주문 테이블의 고객번호 컬럼)

 

 

 

위의 그림에서도 인덱스의 위치가 명확하므로 오라클 옵티마이저가 접근 순서를 잘못 판단할 가능성은 거의 없다. 2번과 4번 컬럼에만 인덱스가 존재하므로 테이블의 접근 순서는 주문 → 고객 이다. 왜냐하면 옵티마이저는 인덱스가 있는 방향으로 접근하기 때문이다. (2번: 고객 테이블의 고객번호 컬럼, 4번: 주문 테이블의 주문일자 컬럼)

 

이처럼 동일한 쿼리라도 인덱스 생성 포인트에 따라서 테이블 접근 방향은 가변적이다. 따라서 최소한의 코스트가 소요되는 접근 방향을 결정하고 그에 따른 필요한 위치에 인덱스를 생성한다면, 옵티마이저도 우리와 동일한 판단(플랜)을 할 것이다. 하지만 우리가 접하는 대부분의 경우는 이처럼 단순하지는 않다. 아래 그림과 같은 경우를 살펴보자.

 

 

위의 그럼처럼 1번, 2번, 3번, 4번 모든 컬럼 위치에 인덱스가 존재한다면 테이블의 접근 순서는 명확하지 않다. 고객 → 주문 방향으로 접근도 가능하고 주문 → 고객 방향으로 접근도 가능하다. 양？향 접근이 가능한 것이다.

물론 옵티마이저는 수집한 통계정보에 근거하여 가장 최소한의 노력이 드는 방향을 결정하려 할 것이다. 하지만 항상 올바른 결정만 하는 것은 아니다. 만약 우리가 판단하는 접근 방향과 옵티마이저가 판단하는 접근 방향이 다르다면, 우리는 힌트절을 통해 테이블 접근 방향을 변경할 수 있다. ORDERED 힌트절은 FROM 절에 나열된 테이블 순서대로 접근하고자 할 때 사용한다.

 

위 쿼리에서 사용한 ORDERED 힌트절로 인하여 옵티마이저는 고객 → 주문 방향으로 접근을 진행할 것이다. 앞서 연재한 ‘오라클 공정쿼리 작성법’ 부분에서 필자는 쿼리의 FROM 절에 접근 순서대로 테이블을 나열할 것을 강조한 바 있다. 이는 개발자 간에 테이블 접근 순서를 명시적으로 공유하는 방법이자, 오라클 옵티마이저의 잘못된 접근 순서를 ORDERED 힌트절을 사용해 바로잡기에 용이하기 때문이다.

ORDERED 힌트절은 FROM 절에 나열된 테이블 순으로 접근을 유도 하지만, LEADING 힌트절은 테이블 접근 순서를 명시적으로 표시할 수 있다. ORDERED 힌트절보다 훨씬 개선된 힌트절이다.왜냐하면 LEADING 힌트절은 쿼리의 FROM 절에 종속적이지 않기 때문이다.

 

위의 쿼리에서 사용한 LEADING(B A) 힌트절로 인해 옵티마이저는 주문 → 고객 방향으로 접근을 진행할 것이다. 만약 LEADING(A B) 힌트절을 사용한다면 고객 → 주문 방향으로 접근을 진행할 것이다. 다음 그림을 보면서 LEADING 힌트절에 대해서 좀 더 구체적으로 알아 보자.

 

위의 그림에서 접근 순서는 2가지가 가능하다. 가 → 나 → 다 → 라 방향으로 접근하는 경우와 라 → 다 → 나 → 가 방향으로 접근하는 경우다. 접근 방향에 맞는 인덱스만 잘 생성돼 있다면 옵티마이저가 잘못된 플랜을 제시할 가능성은 별로 없다. 설사 잘못된 경우라도 힌트절을 통해 쉽게 제어할 수 있다. 경우의 수가 2가지만 있기 때문이다. 힌트절은 다음과 같이 사용할 수 있다.

/*+ LEADING(가 나 다 라) */ 혹은 /*+ LEADING(라 다 나 가) */

그렇다면, 아래 그림과 같은 경우라면 어떨까?

 

위의 그림에서 접근 경로는 12가지다(가 → 나 → 다 → 라, 가 → 나 → 라 → 다, …, …). 만약 조건1로 가 테이블로 진입할 때, 그 다음 접근 순서는 나, 다, 라 테이블 중에서 선택해야 할 것이다. 만약 조건2로 나 테이블로 진입 시 그 다음 접근 순서는 가 테이블이 되며, 이후 접근 순서는 다, 라 테이블 중에서 선택해야 할 것이다.

접근 방향에 맞는 인덱스가 잘 생성돼 있고 최신의 통계정보를 갖고 있다면, 옵티마이저가 잘못된 플랜을 제시할 가능성은 낮다. 하지만 접근 경로에 대한 경우의 수가 많다면 옵티마이저가 잘못된 판단(플랜)을 할 가능성도 존재한다. 만약 그러한 상황이 발생한다면, LEADING 힌트절을 통하여 접근 순서를 변경하면 된다. 접근 순서에 대한 판단과 최종 결정은 사람이 한다. 옵티마이저가 제시하는 접근 순서는 참고만 해야 하며, 잘못된 악성 쿼리에 대한 문제점을 옵티마이저 탓으로 돌려서는 안된다. 요즈음, 옵티마이저의 성능이 좋아져서 신뢰도가 높아졌지만 기계는 기계일 뿐이다. 사람의 판단을 대체하지는 못한다. 아직까지는 사람의 판단과 결정에 도움을 주는 보조 도구로서 이해해야 한다.

덧붙여, 테이블 접근 순서에 대한 결정 기준으로 다음 3가지 사실을 인지하도록 하자.
1. 진입형 테이블을 결정한다. → 조건 중에서 조회 범위가 작은 테이블을 우선함
2. 연결 확장형보다는 연결 축소형 테이블을 우선한다. → 조회 범위 줄어드는 JOIN을 우선함
3. OUTER JOIN보다는 INNER JOIN을 우선한다. → INNER JOIN은 조회 범위 축소 가능

 

접근 방법을 결정하는 힌트절 USE_NL, USE_HASH

ORDERED와 LEADING은 테이블 간 접근 순서를 결정하는 힌트절이지만 USE_NL, USE_HASH는 테이블 간 접근 방법을 결정하는 힌트절이다. 이러한 힌트절을 통해 오라클 조인 방식을 상황에 맞게 선택해 사용할 수 있다.

오라클의 조인 방식에는 Nested Loop Join, Hash Join, Sort Merge Join 3가지가 있지만, 이중에서 Sort Merge Join은 사용되는 경우가 거의 없다. 개발자가 가장 많이 접하는 조인 방식은 Nested Loop Join이다. 이는 순차적인 루프에 의한 테이블 간 접근 방식이며, 온라인성(OLTP) 쿼리에 많이 사용된다. Hash Join은 해시 함수를 이용한 테이블 간 접근 방식으로, 배치성(Batch) 쿼리에 많이 사용된다. 먼저 USE_NL 힌트절에 대해 살펴보자.

 

위 그림의 USE_NL 힌트절에 의한 Nested Loop Join 처리 과정은 다음과 같다.
1. 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한다. (선행 테이블 결정)
2. ‘홍길동’ 고객의 수만큼 순차적으로 주문 테이블을 고객번호 컬럼으로 접근한다. (순차적 접근)
3. 주문 테이블에서 주문일자가 ‘20141201’인 정보만 필터한다.

즉, 고객 테이블에서 주문 테이블로 순차적으로 접근 하겠다는 의미다. 진행 방향인 1번 컬럼과 3번 컬럼엔 인덱스가 반드시 존재해야 한다. 다음으로 USE_HASH 힌트절에 대해 살펴보자.

 

위 그림의 USE_HASH 힌트절에 의한 Hash Join 처리 과정은 다음과 같다.
1. 조직 테이블에서 사업부가 ‘강원사업부’인 조직들을 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 분류한 다음, 해시 테이블을 생성한다.
(해시 함수를 이용해 해시 테이블을 구성)
2. 집계 테이블에서 집계년월이 ‘201412’인 자료를 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 변환 후 해시 테이블로 순차적으로 접근한다.
(해시 함수를 통하여 해시 테이블을 탐색)

즉, 해시 함수를 이용하여 조직 테이블과 집계 테이블을 조인하겠다는 의미다. 조회 조건 컬럼인 1번, 4번 컬럼의 인덱스는 사용되고 있으며, 조인 컬럼인 2번, 3번 컬럼에는 인덱스가 존재하더라도 사용되지는 않는다.

Hash Join에서는 작은 테이블을 먼저 접근하는 것이 성능 면에서 더 좋다. 해시 테이블 구성 작업에 부하가 많이 발생하기 때문이다. 작은 테이블을 접근하여 해시 함수를 사용해서 해시 테이블을 생성하며, 이후 큰 테이블을 접근하여 해시 함수를 사용해서 순차적으로 해시 테이블로 접근한다. 이러한 조인 방식은 대량 데이터를 처리하는 배치성 프로그램에 적합한 조인 방식이다.

아래 그림은 복잡한 쿼리를 인덱스 생성도로 단순화한 것이다.

 

만약, 위 쿼리(그림)에 대한 오라클 옵티마이저가 제시하는 플랜과 개발자가 생각하는 플랜이 아래 내용처럼 일치하지 않는다면, 우리는 어떤 힌트절을 주어야 할까?

오라클 옵티마이저 접근순서: 가 → 다 → 마 → 바 → 라 → 나
접근방법: 전구간 USE_NL

개발자가 생각하는 접근순서: 가 → 라 → 다 → 마 → 바 → 나
접근방법: 가…라 구간은 USE_HASH, 나머지 구간은 USE_NL

일단, 힌트절을 적용하기 전에 사용되는 인덱스 위치를 알아 보자. 오라클 옵티마이저가 제시하는 플랜에서 사용하는 인덱스는 가(1) → 다(5) → 마(7) → 바(9) → 라(11) → 나(3)이다. 진행 방향의 목적지 컬럼에 인덱스가 있음을 알 수 있다. 반면에, 개발자가 생각하는 플랜에서 사용하는 인덱스는 가(1) → 라(12) → 다(5) → 마(7) → 바(9) → 나(3)이다. 이때 가…라 구간의 인덱스 위치는 목적지 컬럼이 아닌 조건절 컬럼에 있음을 유의하자. 힌트절은 다음과 같이 접근순서와 접근 방법에 대해 복합적으로 적용하면 된다.

/*+ LEADING(가 라 다 마 바 나) USE_HASH(라) USE_NL(다 마 바 나) */

 

자원 사용을 결정하는 힌트절 - INDEX, FULL, PARALLEL

 

지금까지 접근 순서를 결정하는 힌트절(ORDERED, LEADING)과 접근 방법을 결정하는 힌트절(USE_NL, USE_HASH)에 대해 살펴보았다. 다음으로 자원 사용을 결정하는 힌트절에 대해 알아 보자. 자원 사용을 결정하는 힌트절은 INDEX, FULL PARALLEL 등이 있다.

 

위 그림처럼 두 개의 조건절에 모두 인덱스가 존재할 때 옵티마이저는 통계정보에 근거해 최소한의 비용이 소요되는 인덱스를 선택할 것이다. 하지만 통계정보가 실제 정보를 반영하지 못하여 옵티마이저가 잘못된 선택을 한다든지 혹은 우리가 원하는 인덱스가 아닐 때는 힌트절을 통해 명시적으로 인덱스를 지정할 수 있다. 추가적으로 아래와 같은 INDEX 관련 힌트절도 있다.

INDEX_SS - 결합인덱스의 선행 컬럼 조건이 입력되지 않을 때 사용한다(INDEX SKIP SCAN).
INDEX_FFS - 인덱스만을 빠르게 전체 스캔한다(INDEX FAST FULL SCAN).
INDEX_DESC - 인덱스를 통하여 데이터를 역순으로 스캔한다.

 

위 그림을 보면 주문일자 조건절로 구간 조회를 하고 있다. 주문 테이블의 전체 건수에 비해 구간 조회 건수가 매우 적다면 인덱스를 통해 접근하는 것이 빠를 것이다. 하지만 구간 조회 범위가 크다면 Random Access 부하가 클 것이다. 이때는 인덱스를 통한 Random Access보다 테이블을 직접 Full Scan하는 것이 더 빠를 수도 있다.

이와 같은 경우는 대개 배치성 쿼리에서 많이 볼 수 있다. 그렇다면 그 기준은 어떻게 정하는 것이 좋은가? 상황에 따라 가변적이지만, 필자는 1% 범위를 기준으로 한다. 즉 조회 건수가 테이블 전체 건수의 1% 미만일 때 인덱스를 사용하게 하고, 1% 이상일 때 FULL SCAN 힌트절 사용을 고려한다.

 

PARALLEL 힌트절은 FULL 힌트절과 같이 사용된다. 병렬 처리를 위한 힌트절이므로 처리 성능은 매우 좋으나, 자원을 독점적으로 사용하므로 Multi User 환경에서는 주의해야 한다. 만약 수치값을 1로 주면, FULL 힌트절만 작동할 것이다. 또한 수치값을 주지 않는다면 사용 가능한 자원 모두를 사용하므로 주의해야 한다. 필자는 대개 4 정도의 값을 준다.

배치 튜닝에서 마법사 같은 힌트절 삼총사: USE_HASH, FULL, PARALLEL

가장 많이 사용하는 7가지 힌트절에 대해서 알아 보았다. 이중에서 USE_HASH, FULL, PARALLEL 3가지 힌트절은 배치성 쿼리에서 가장 많이 사용되는 힌트절이다. 한마디로 대용량 데이터 처리 및 조회에 빈번하게 사용하는 힌트절이다. 위의 3가지 힌트절만 잘 조합하여 사용한다면 배치 튜닝에서 전혀 어려움이 없다. 힌트절을 사용함에 있어서 처음부터 3가지를 조합하여 사용하기 보다는 다음의 3단계로 나누어서 사용 여부를 검토해야 한다.

1. USE_HASH 힌트절만 사용해서 조회 가능한지 검토 → 적당히 무거운 쿼리에 사용
2. 조회 범위가 크다면 FULL 힌트절 추가 사용을 검토 → 대개 이 단계에서 튜닝 완료
3. 대용량 데이터의 빠른 처리가 요구 될 때 PARALLEL 힌트절 사용 → 제한적 사용

특히 PARALLEL 힌트절은 병렬 처리를 위한 힌트절이며 자원을 독점적으로 사용하므로, 사용 여부를 신중하게 결정해야 한다. 대부분의 DBA가 가장 많이 사용하는 힌트절이기도 하다. 일반 개발자들은 사용을 주저할 수도 있으나, 필자는 통제 가능한 범위 내에서 사용을 권장하는 편이다. 모든 배치성 쿼리에 사용 할 것이 아니라, 꼭 필요한 Heavy 쿼리를 선별하여 제한적으로 사용해야 한다.

 

 

이번 연재에서는 수많은 힌트절 중에서 7가지 힌트절에 대해서만 설명을 하였다. 이것만으로도 충분하다는 것을 필자는 자신한다. 힌트절을 사용해야 하는 경우의 99% 이상 커버됨을 경험했다. 다음 연재에서는 오라클 플랜을 쉽게 이해하는 방법에 대해 자세히 알아 보자.

 

 

[1] http://www.gurubee.net/lecture/3480

[2] https://tocsg.tistory.com/32