오라클 튜닝 힌트 - olakeul tyuning hinteu

힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.

SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 바꿀수 있도록 말그대로 Optimizer에게 힌트를 주는 것이다.

Optiomizer라고 해서 항상 최선의 실행 계획을 수립할 수는 없으므로 테이블이나 인덱스의 잘못된 실행 계획을 사용자가 직접 바꿀 수 있도록 도와주는 지시구문.

오라클 힌트 종류

 SELECT /*+ INDEX(idx_col1) */
             id, password, name
  FROM emp;

 SELECT /*+ ORDERED  INDEX(b idx_col1) */
             id, password, name
  FROM emp a
         , depart b

※ 주의! 주석 표시 뒤에 '+' 기호가 있다.
 

◆ INDEX Access Operation 관련 HINT

◆ JOIN Access Operator 관련 HINT

◆ JOIN시 DRIVING 순서 결정 HINT

◆ 기타 HINT

※ 추가

ALL_ROWS : 가장 좋은 단위 처리량의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (즉, 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화)

FIRST_ROWS : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (첫번째 레코드의 추출 시간을 최소화할 목적으로 최적화)

CHOOSE : 최적자(optimizer)가 그 문에 의해 접근된 테이블을 위해 통계의 존재에 근거를 두는 SQL문을 위해 rule-based 접근 방법과 cost-based 접근 방법 사이에 선택하게 됩니다.

CLUSTER : 지정된 테이블에 대한 클러스터 스캔.

HASH : 지정된 테이블에 대한 해쉬 스캔.

ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔.

RULE : explicitlly chooses rule-based optimization for a statement block. rule-base Optimizer를 사용.

◆ 주의

 SELECT /*+ ORDERED USE_NL(A B C) INDEX(B ITOREDRETL1) */

    FROM TORDERDTL  B, TORDER  A, TITEM  C

  WHERE ...

1. ORDERED : FROM 절에 기술한 테이블 순(B → A → C 순)으로 조인함. USE_NL에 사용한 순서가 아님.

    ※ 참고) LEADING, 예) LEADING(B) : FROM 절의 기술 순서와 상관없이 B테이블이 가장 먼저 선행됨.

2. USE_NL : 조인방법을 Nested Loops방식으로 선택.

    예) USE_NL(A B), 테이블명이 아닌 테이블에 대한 Alias명

    참고) USE_HASH, USE_MERGE

3. INDEX : 특정 인덱스를 오름차순으로 읽음.

    예) INDEX(B  ITORDERDTL1) : B는 TORDERDTL 테이블의 Alias명, 사용할 인덱스명 기술

    참고) USE_DESC(B ITORDERDTL1) : ITORDERDTL1 인덱스를 내림차순으로 읽음

◆ 실행계획 살펴보기 1

 SELECT *

    FROM ( ==> 인라인뷰 3

                ( ==> 인라인뷰 2

                   ( ==> 인라인뷰 1

                   )

                )

              )

과 같이 되어 있다고 하자.

이럴 경우 실행계획을 보면, 우리가 예상했던대로 인라인뷰1 ==> 인라인뷰2 ==> 인라인뷰3 순서로 드라이빙 되지 않는 경우가 있다.

이런 경우 강제로 위 순서대로 실행계획을 수립하도록 하는 힌트

SELECT /*+ ORDERED */

            *

  FROM ( .....

위 힌트를 사용하면 맨 깊숙한 인라인뷰부터 실행된다.

◆ 힌트 사용하기

1. /*+ USE_CONCAT */

USE_CONCAT : 조건절에 있는 OR 연산자조건 (또는 IN 연산자 조건)을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결(Concatenation)하는 실행계획을 수립하도록 유도하는 힌트.

반드시 처리주관 조건이 OR로 나누어졌을 때 적용해야 하며, 잘못 사용하면 비효율이 발생할 수 있으므로 주의해야 함.

예>

SELECT /*+ USE_CONCAT */

   FROM employees

 WHERE job = &job

       OR dept_no = &deptno;

풀어쓰자면

SELECT *

    FROM employees

  WHERE job = &job

UNION ALL

SELECT *

    FROM employees

WHERER dept_no = &deptno;

WHERE 절 이후에 나오는  컬럼에 맞게 인덱스를 탄다.

2. /*+ NO_EXPAND */

조건절에 있는 OR 연산자 조건 또는 IN 연산자 조건을 연결 실행계획으로 처리되지 않도록 할 때 사용하는 힌트.

USE_CONCAT의 반대 개념.

예>

SELECT /*+ NO_EXPAND */

   FROM customer

 WHERE cust_type in ('A','B');

참고 : http://blog.naver.com/hkjhc107?Redirect=Log&logNo=130035827974

◆ 참고

1. Nested Loop

   - 테이블의 인덱스끼리 inner-outer 루프를 형성하여 결과를 쿼리하는 방식입니다.

   - 제일 많은 유형의 실행계획입니다.

2. Sort Merge

   - 쿼리의 결과가 많은 양의 데이터를 읽는 경우, 테이블들을 각각 full-scan하여 같은 키값을 갖는 데이터끼리 조인하여 실행합니다.

   - Sort-Merge 방식은 많은 메모리와 디스크 I/O를 필요로 하기 때문에, sqlplus를 실행하는 주체의 메모리/CPU/디스크 스펙에 많은 영향을 받습니다.

3. Hash Join

   - 한 테이블은 매우 많은 Row를 갖고, 다른 한 테이블은 매우 적은 Row를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은 테이블은 인덱스를 태우는 것보다 full-scan을 하는 것이 유리할 때 사용됩니다.

[출처]

1. http://f1angel.tistory.com/139

2. http://spring3oyh.blogspot.kr/2011/11/blog-post_03.html