HINT

HINT 란 ?

SQL 튜닝의 핵심 부분으로 일종의 지시 구문이라고 한다. 

즉, 오라클 옵티마이저에게 SQL 문 실행을 위한

데이터를 스캐닝하는 경로, 조인하는 방법 등을 알려주기 위해

SQL사용자가 SQL 구문에 작성하는 것을 뜻한다.

" 오라클이 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에 직접 최적의 실행 경로를 작성해주는 것이다 "

라는 정의가 가장 바른 것 같다.

 

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

그러기에 잘 알고 최적의 실행 경로를 알고 있을 경우에 사용해야 한다!

 

힌트의 쓰임새

1. 액세스 경로

2. 조인 순서

3. 병렬 및 직렬 처리

4. Optimizer의 목표를 변경 가능

5. 데이터 값을 정렬해야 하는 경우

6. 드라이빙 테이블을 원하는 대로 선정하고자 할 떄도 사용

 

 

힌트의 사용법

ALL_ROWS

     ㄴ 전체 RESOURCE 소비를 최소화시키기 위한 힌트.

         Cost-Based 접근방식으로 ALL_ROWS는 Full Table Scan을 선호하며

         CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택한다.

 

FIRST_ROWS

     ㄴ 조건에 맞는 첫 번째 row를 리턴하기 위한 Resource 소비를 최소화시키기 위한 힌트이며

          Cost-Based 접근방식을 사용.

 

CHOOSE

     ㄴ Data Dictionary가 해당 테이블에 대해 통계정보를 가지고 있다면

         Optimizer는 Cost-Based Approach를 선택하고,

         그렇지 않다면 Rule-Based Approach를 선택한다.

         Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지

         CBO(Cost Based Optimization) 인지를 선택한다.

         만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용한다.

 

 

Access Method

 

FULL(table_name) 

     ㄴ Table을 Full Scan 하기 원할 때 사용

 

HASH(table)

     ㄴ Hash Scan 선택하도록 지정 (HASHKEYS Parameter로 만들어진 Cluster내에 저장된 Table에만 적용)

CLUSTER(table_name) 

     ㄴ Cluster Scan을 선택하도록 지정. 따라서 Clustered Object만 적용

HASH_AJ 

     ㄴ NOT IN SubQuery를 HASH Anti-join으로 변형

 HASH_SJ

     ㄴ Correlated Exists Subquery를 Hash Semi-join으로 변형

INDEX(table_name index_name) 

     ㄴ 지정된 index를 강제적으로 쓰게끔 지정

               ㄴ in list predicat에 대해서도 가능.

               ㄴMulti-column inlists는 index를 사용할 수 없다.

INDEX_COMBINE(table_name index_name)
     ㄴ  index명이 주어지지 않으면 Optimizer는 해당 테이블의 Best Cost로 선택된

          Boolean Combination Index를 사용하며 Index 명이 주어지면

          주어진 특정 Bitmap Index의 Boolean Combination의 사용

INDEX_ASC(table_name index_name)
     ㄴ 지정된 index를 오름차순으로 쓰게끔 지정(기본은 오름차순)

 

INDEX_DESC(table_name index_name)
     ㄴ 지정된 index를 내림차순으로 쓰게끔 지정
    
INDEX_FFS(table index) 
     ㄴ Full table scan보다 빠른 Full index scan을 유도

ROWID(table) 
     ㄴ Rowid로 Table Scan을 하도록 지정

 

MERGE_AJ

     ㄴ NOT IN Subquery를 Merge Anti-join으로 변형

MERGE_SJ

     ㄴ Correalted EXISTS Subquery를 Merge Semi-join으로 변형

AND_EQUAL(Table Index1, Index 2...)
     ㄴ Single-Column Index의 Merge를 이용한 Access Path 선택.

        적어도 두 개 이상의 Index가 지정되어야 한다. Max로 5개까지 지정 가능

USE_CONCAT
     ㄴ 조건절의 OR를 UNION ALL 형식으로 변형한다. 일반적으로 변형은 비용 측면에서 효율적일 때만 일어난다.

 

Join Orders

  

ORDERED

     ㄴ From절에 기술된 테이블 순서대로 join이 일어나도록 유도

STAR

     ㄴ Star Query Plan이 사용 가능하다면 이를 이용하기 위한 Hint. Star Plan은

        규모가 가장 큰 테이블이 Query에서 Join Order상 마지막으로 위치하게 하고

        Nested Loop으로 Join이 일어나도록 유도한다.

        적어도 3개 테이블 이상이 조인에 참여해야 하며

        Large Table의 Concatenated Index는 최소 3 칼럼 이상을 Index에 포함해야 한다.

        테이블이 Analyze 되어 있다면 Optimizer가 가장 효율적인 Star Plan을 선택한다.

     

 

join Operations

 

USE_NL(table1 table 2...)

     ㄴ 테이블의 Join 시 테이블의 각 Row가 Inner 테이블을 Nested Loop 형식으로 Join 한다.

         지정된 table이 inner table이 된다. ( inner table <-> driving(outer) table )
         흔히 ORDERED Hint와 함께 쓴다.

USE_HASH (table_name) 

     ㄴ 각 테이블 간 HASH JOIN이 일어나도록 유도한다

USE_MERGE (table_name) 

     ㄴ  지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도한다.

DRIVING_SITE(table_name) 

     ㄴ QUERY의 실행이 ORACLE에 의해 선택된 SITE가 아닌 다른 SITE에서 일어나도록 유도.

Parallel Execution

NOPARALLEL(table_name)

     ㄴParallel Query Option을 사용하지 않도록 할 수 있음.

PARALLEL(table_name, degree) 

     ㄴ PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있다.

        예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을 실행하도록 할 수 있다.

        이때 parallel이란 글자와 괄호( '(' ) 사이에 blank를 넣지 않도록 주의해야 함

 

Additional Hints

CACHE(table)

      ㄴ full table scan시 retrieve 된 block을 LRU list에서

          most recently used end에 놓는다.
          즉, memory에 오래 존재하게 한다.

NOCACHE(table)

      ㄴfull table scan시 retrieve된 block을 LRU list에서 least recently used end에 놓는다.
 즉, memory에서 금방 내려가게 한다.

MERGE(view) 

      ㄴCOMPLEX_VIEW_MERGING = FALSE로 되어 있을 때 view 또는 subquery의 내용을 merge가능.

NOMERGE(view)

      ㄴCOMPLEX_VIEW_MERGING = TRUE로 되어 있을 때 사용
          view 또는 subquery의 내용을 merge불가능.
          view 또는 subquery자체의 query문에 의한 영향을 많이 받게 됨.

PUSH_SUBQ

      ㄴnomerged subqueries가 execution plan에서 가능한 가장 빠른 위치에서 evaluation 되도록 한다.
        일반적으로, merge 되지 않은 subqueries는 execution plan에서 마지막 step으로써 수행된다.

 

출처: https://devuna.tistory.com/35 [튜나 개발일기📚]