오라클 힌트 작성 원칙과 주요 힌트 (오라클/Oracle/SQLP 실기/쿼리 튜닝)

 

오늘 포스트에서는 오라클 실행계획 제어를 위해 사용할 수 있는 힌트에 대해서 정리해본다.

오라클의 SQL이 원하는 방법으로 실행되도록 하고자 하는 분이나, SQLP 시험을 준비하고 있는 분들께 유용할 것이다.

 

 

 

1. 오라클 Hint 란?

 

오라클에서 힌트는 SQL 구문 내에 포함된 주석 형태의 명령어를 말한다. 옵티마이저에게 특정 실행 계획을 선택하도록 지시하는 역할을 한다.

힌트를 사용해서 데이터 액세스 방식(테이블/인덱스 사용), 조인 방법과 순서, 병렬 처리 등을 유도할 수 있다.

 

Hint라고 명명되었지만 Directive이기 때문에 사용자가 명시하면 syntax나 semantic이 틀리게 작성되지 않는 한 옵티마이저는 힌트에 따라 실행계획을 수립한다.

 

옵티마이저가 언제나 최적의 실행계획을 수립해주는 것은 아니다. 통계정보 부족이나 파라미터 설정 등  여러가지 제약사항으로 인해서 사용자가 예상한 최적 실행계획이 수립되지 않을 수 있다. 힌트로 옵티마이저가 더 효과적인 실행계획을 수립할 수 있도록 함으로써 쿼리 성능을 최적화할 수 있다.

 

2. Hint 기본 원칙

 

2.1 적용 원칙

• 힌트는 해당 문장 블록의 최적화에만 적용된다
  • `SELECT`, `UPDATE`,  `DELETE` 문
  • 복합 문장의 부모 문 또는 서브쿼리
  • 복합 쿼리의 일부
  • `UNION` 연산자로 결합된 두 쿼리로 이루어진 복합 쿼리는 두 개의 문장 블록을 가진다.
    • 따라서 최상단에만 힌트를 기재하면 첫 번째 구성 쿼리의 힌트는 해당 쿼리의 최적화에만 적용되고, 두 번째 구성 쿼리의 최적화에는 적용되지 않는다.
• 문장 블록 하나는 힌트 주석을 하나만 사용할 수 있다.
• 구문 오류가 있는 힌트는 무시된다.
  • 단, 동일 주석 내 올바른 힌트는 사용된다.매우 복잡한 쿼리(여러 테이블 조인)에 일부 액세스 패스/조인에 대한 힌트만 주는 경우 옵티마이저는 이 힌트를 사용하지 않을 수 있다

 

2.2 작성 원칙

• 힌트 주석은 `SELECT`, `UPDATE`, `DELETE` 키워드 뒤에만 올 수 있다.
  • 예외) `APPEND`  `PARALLEL` 힌트는 `INSERT` 키워드 뒤에 올 수 있다.
• `+` 기호는 Oracle이 주석을 힌트 목록으로 해석하도록 한다.
  • `+` 기호는 주석 구분자 바로 뒤에 와야 하며, 공백을 허용하지 않는다.
  • 따라서 `/*+ hint 기재 */` 형식으로 작성하면 된다.
• 주석에 여러 hint가 포함된 경우 공백으로 구분해줘야 한다.
  • 콤마(`,`)로 구분하는 게 아님을 유의해야 한다.
• hint에 테이블명을 기입할 때 다음과 같은 규칙을 지켜야 한다.
  • `FROM`절에 Alias를 사용했다면 Alias를 기입해야 한다.
  • `FROM`절에 스키마명을 명시했더라도 스키마명은 생략해야 한다.

 

2.3 일반적인 작성 순서

 

오라클 공식 문서나 한국에 출간된 서적을 보면 힌트를 기재할 때 순서에 대한 별도의 설명이 존재하지는 않는다. 힌트를 기재할 때 순서는 그다지 중요하지 않은 것 같다. 

다만 가독성을 위해서 아래 순서에 따라 작성하면 좋다. 대부분의 문서나 서적의 예시도 비슷한 순서를 따른다.

• 최적화 목표
• 쿼리 변환
• 조인 순서
• 조인 방식
• 액세스 방식

 

3. 주요 힌트

오라클의 주요 힌트에 대해서 알아본다.

 

3.1 최적화 목표

아래 3가지 힌트를 사용해서 옵티마이저에게 최적화 목표를 지시할 수 있다.

 

(1) 전체 범위 처리

`ALL_ROWS` 

 

(2) 응답 n건에 대해 최적화

`FIRST_ROWS(n)`

 

(3) 병렬 처리

`PARALLEL(table_name parallel_degree)`

반대로 병렬이 일어나지 않게 하려면 `NOPARALLEL(table_name)`을 사용하면 된다.

 

3.2 쿼리 변환

쿼리 변환 힌트에 대해서 알아본다. 쿼리 변환에 대해서 설명하지는 않는다. 쿼리 변환에 대해서 알아보고 싶다면 다음 포스팅을 참고하면 된다.

https://wvwv.tistory.com/118

[SQL] 쿼리 변환에 대해서 알아보자 (Oracle/PostgreSQL/SQL Server/MySQL)

 

`USE_CONCAT` ↔️  `NO_EXPAND`

• `USE_CONCAT` 힌트는 `UNION ALL`, `OR` 조건으로 쿼리가 분해되도록 한다.

 

`NO_UNNEST` ↔️ `UNNEST` 

• `NO_UNNEST` 힌트는 서브쿼리 언네스팅 방지하도록 한다.
  • SELECT절 스칼라 서브쿼리
  • WHERE 조건절 서브쿼리 
• 서브쿼리 블록에 힌트를 기재해주어야 함

 

`NO_MERGE(t)` ↔️ `MERGE(t)`

• `NO_MERGE` 힌트는 뷰 머징을 방지한다.
  • `FROM`절의 인라인뷰가 있는 경우
  • 아래 연산이나 구문이 인라인뷰에 포함되면 머지가 일어나지 않는다. (즉, `MERGE` 힌트를 써도 소용 없다.)
    • `ROWNUM`
    • `DISTINCT`, `GROUP BY`
    • 집합 연산자
      • `UNION`
      • `UNION ALL`
      • `MINUS`
      • `INTERSECT`

 

`PUSH_PRED` 

• 조인 조건 Pushdown을 유도한다.
  • NL 조인으로 소트 연산을 생략하고 싶을 때
  • leading 테이블 인덱스 순서에 따라서 출력하고 싶을 때
• 뷰 머지가 일어나지 않은 경우에만 작동하므로, `NO_MERGE` 힌트와 함께 사용한다.

 

`PUSH_PRED` 힌트 사용 예제는 다음과 같다.

select /*+ no_merge(e) push_pred(e) */ *
from  dept d
	, (select empno, ename, deptno from emp) e
where e.deptno(+) = d.deptno
and   d.loc = 'CHICAGO';

 

3.3 조인 순서

 

(1) 단순한 조인문

 

`ORDERED`

• 조인절에 기재한 순서대로 조인이 일어나게 하고자 할 때 사용한다.

 

`LEADING(table_name1 table_name2 table_name3 ...)`

• `table_name1` ➡️ `table_name2` ➡️ `table_name3`의 순서로 조인되도록 한다.

 

`SWAP_JOIN_INPUTS(table_name)`

• 해시 조인에 대한 build input을 지정하고자 할 때 사용한다.

 

`NO_SWAP_JOIN_INPUTS(table_name)`

• 해시 조인에 대한 build input이 변경되지 않도록 할 때 사용한다.

 

위 힌트들에 대한 예시를 하자면,

`a` =(nl)=> `b` =(nl)=> `c` =(hash)=> `d` 순으로 조인하고자 할 때는 다음과 같이 힌트를 작성하면 된다.

-- 1) ordered 힌트 사용
select /*+ ordered use_nl(b) use_nl(c) use_hash(d) */ *
from a, b, c, d;

-- 2) leading 힌트 사용
select /*+ leading(a, b, c, d) use_nl(b) use_nl(c) use_hash(d) */ *
from d, c, b, a;

 

 

`a` =(hash)=> `b` =(NL)=> `c` =(hash)=> `d` 순으로 조인하되, build input이 바뀌면 안될 때는 다음과 같이 힌트를 작성하면 된다.

/*+ leading(a b c d) use_hash(b) use_nl(c) use_hash(d) no_swap_inputs(d) */

 

 

(2) 서브쿼리 존재 시 조인 순서 제어

 

`PUSH_SUBQ`

• Nested 서브쿼리를 메인쿼리보다 먼저 실행하도록 한다.
• 언네스트 되지 않은 서브쿼리에만 작동하기 때문에 `NO_UNNEST` 힌트를 함께 기재하는 것을 추천한다.
• 메인쿼리 블록에 작성해야 한다.
  • 메인/서브쿼리 블록 어디에 써야 잘 작동하는 건지 말이 다 다른데 대부분의 예시에서 메인쿼리 블록에 작성되어 있다.

 

`QB_NAME(nm)`

• 서브쿼리의 쿼리 블록 명을 지정하고 싶을 때 사용한다.

-- qn_name(nm) 힌트
select /*+ leading(dept@qb1) */ * 
from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno
				from dept);

 

 

3.4 조인 방식

 

(1) Nested Loop Join

`USE_NL`  ↔️ `NO_USE_NL`

• NL이 배치 I/O로 작동하면 정렬 순서가 달라질 수 있다.
  • 따라서, 메인쿼리에도 `ORDER BY` 기재가 필요하다.

 

(2) Sort Merge Join

`USE_MERGE` ↔️ `NO_USE_MERGE`

• inner 테이블에는 정렬이 무조건 일어난다.
  • 단, 인덱스가 있으면 처리시간이 적기는 하다.

 

(3) Hash Join

`USE_HASH` ↔️ `NO_USE_HASH`

 

(4) Semi Join / Anti Join

`NL_SJ`, `NL_AJ` -- NL Semi/Anti Join

 

`MERGE_SJ`, `MERGE_AJ` -- Sort Merge Semi/Anti Join

 

`HASH_SJ`, `HASH_AJ` -- Hash Semi/Anti Join

 

• 주로 `EXIST`, `IN` 서브쿼리에 사용되는 힌트다.
• 서브쿼리의 경우 언네스트 된 경우에만 작동하기 때문에 `UNNEST` 힌트와 함께 사용해야 한다.

 

`UNNEST`와 `NL_SJ` 사용 예시

select /*+ leading(emp) */ * 
from emp
where deptno in (select /*+ unnest nl_sj */ deptno
				from dept);

 

 

`UNNEST`와 `NL_AJ` 사용 예시

select * 
from dept d
where not exists (select /*+ unnest nl_aj */ 'x'
				from emp
				where deptno = d.deptno);

 

 

3.5 액세스 방식

 

(1) 테이블

 

`FULL(table_name)`

• 테이블 풀스캔을 유도하고자 할 때 사용한다.
• 5~10% 이상의 데이터를 조회해야 하는 경우 인덱스 random access보다 테이블 full scan이 유리할 수 있다.
• 파티셔닝 되어 있는 테이블의 경우 해당 파이션만 풀스캔한다.

 

`ROWID(table_name)`

• rowid를 사용해서 접근하도록 한다.

 

아래는 `공급업체` 테이블을 인덱스 패스트풀스캔으로 접근해서 조건에 해당하는 데이터를 필터링하고, 필터링한 결과에서 얻은 rowid로 테이블 액세스를 하도록 하는 힌트의 예시이다.

select /*+ ordered use_nl(b) no_merge(b) rowid(b) */ b.*
from (
	select /*+ index_ffs(공급업체 공급업체_X01) */ rowid rid
	from 공급업체
	where instr(업체명, '네트웍스') > 0
	order by rowid -- 💫 클러스터링팩터 향상 & 버퍼pinning 효과
	) a
	, 공급업체 b
)
where b.rowid = a.rid

 

 

(2) 클러스터 오브젝트

 

`CLUSTER(table_name)`

• cluster scan을 유도한다.

 

`HASH(table_name)`

• hash scan을 유도한다.

 

(3) 인덱스

 

`INDEX(table_name index_name)`

• 인덱스명 기입은 optional이다.
• 1) 인덱스를 명시하지 않는 경우
  • 옵티마이저가 사용 가능한 인덱스들을 검토함
• 2) 하나의 인덱스명을 기재하는 경우
  • 해당 인덱스를 사용함
• 3) 여러 인덱스명을 기재하는 경우
  • 옵티마이저가 비용을 고려해 선택하여 사용하거나
  • 여러 인덱스에 액세스한 결과를 merge에 제공할 수 있음

 

`INDEX(table_name (column_name1, column_name2, ...) )`

• 해당 컬럼을 가진 인덱스를 선택하도록 한다.
• 단 컬럼명1, 컬럼명2가 모두 존재하는 인덱스가 없으면 무시된다.
• 컬럼명1, 컬럼명2가 모두 존재하는 인덱스가 여러 개인 경우, 알파벳 정렬 순으로 더 상위의 인덱스를 택한다.

 

`NO_INDEX(T IDX_NM)`

• 특정 인덱스 사용하지 않고자 할 때 사용할 수 있다.
• 또는 Full scan 유도하고자 할 때도 사용할 수 있다.

 

(3-1) 인덱스 스캔 방향

 

`INDEX_ASC(T IDX_NM)`

• 순방향으로 인덱스를 스캔한다.

 

`INDEX_DESC(T IDX_NM)`

• 역방향으로 인덱스를 스캔한다.

 

(3-1) 스캔 방식

Range Scan을 하고자 할 때는 그냥 `INDEX` 힌트를 사용하면 된다.

• Range Scan이 가능한 경우
  • ` = `, `IN` : 상수 equi 조건이 선두일 때
•  불가한 경우
  • 액세스 조건에 컬럼 형변환이 일어나면 사용되지 않는다.
  • `IS NULL` 조건을 걸었는데 옵티마이저가 인덱스에 NULL 데이터가 없을 수 있다고 판단하는 경우 테이블 풀스캔이 일어난다.

 

`INDEX_FFS`, `NO_INDEX_FFS`

• Index FFS(Fast Full Scan)이 일어나도록 한다.
• FFS을 사용하면 인덱스 블록을 멀티블록으로 읽어오기 때문에 인덱스의 정렬 순서가 보장되지 않는다.
• 테이블 접근이 필요한 경우 사용되지 않는다. 💫
• FFS을 쓰고 싶은데 인덱스에 없는 컬럼을 참조하고 싶은 경우 인라인뷰 처리 후 rowid로 셀프조인을 하면 된다.

 

`INDEX_SS`

• Index SS(Skip Scan)이 일어나도록 한다.
• 액세스 조건이 `IN`일 때는 적용되지 않는다.
  • Range scan이 적용된다. 💫

`INDEX_COMBINE(table_name index_name1 index_name2)`

• 비트맵 인덱스를 사용하게 한다.
• 실제 비트맵 인덱스를 사용하거나,비트맵 컨버전을 통해 실행될 수 있다.

 

`INDEX_JOIN(T IDX_1 IDX_2)`

• 인덱스들만으로 결과 집합 생성하고자 할 때 사용한다.
  • 크기가 작은 인덱스를 PGA에 해시맵을 생성해 조인하는 형식이다.
  • 조인 해시 키는 rowid
• 테이블 액세스가 필요 없는 경우에만 사용 가능하다.

 

4. 마치며

 

힌트는 오라클의 강력한 쿼리 최적화 도구이다. 오늘 포스트에서는 Hint의 개념과 적용/작성 원칙에 대해서 설명하고, 최적화 목표, 쿼리 변환, 조인 순서, 조인 방식, 액세스 방식 등 다양한 Hint 예제를 통해 옵티마이저가 효과적인 실행 계획을 수립하도록 유도하는 방법을 알아 봤다.

 

힌트에 대한 사용법에 대해서 공부하고 실제 쿼리에 적용해본다면 SQL Tuning과 성능 개선에 큰 도움이 될 것이다. 본 포스팅에서 제시한 적용 및 작성 원칙을 참고하여 실제 SQL 튜닝에 활용하여 성능 개선에 활용해보자.