Hint로 쿼리 튜닝하기

Hint를 왜 써야 할까?

 

 오라클이 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에 직접 최적의 실행 경로를 작성하여 SQL 문에서 인덱스 설정이 Optimizer에 의존한 실행 계획보다 효율적인 실행 계획을 구사할 수 있기 떄문이다.

1. OPTIMIZER_MODE

• CHOOSE : Optimizer가 CBO와 RBO 두 가지가 모두 선택 가능할 경우에 사용
• ALL_ROWS : CBO 환경경에서 전체적인 처리, 즉 Batch 환경에서 주로 사용
• FIRST_ROWS : CBO 환경에서 빠른 응답시간을 얻고자 할 때, 즉 ON-LINE 환경에서 주로 사용
• Rule : SQL에 대한 실행 계획이 여러 개 있을 때 가장 낮은 순위의 실행계획을 사용

 

OPTIMIZER_MODE 비교 

RULE	Analyze작업이 불필요, 일정한 응답속도를 기대	전문가에게 의존도가 매우 높음, optimizer 대부분의 기능을 사용 못함
CHOOSE	optimizer 기능을 최대한 활용	주기적인 Analyze작업이 필요, 비효율적인 실행계획수립 발생
FIRST_ROWS	Nested Loop 위주의 실행계획수립	일부 Hash Join으로 바꾸는 작업 필요
ALL_ROWS	Hash Join 위주의 실행계획 수립	일부를 Nested Loop로 바꾸는 작업 필요

RBO(Rule Based Optimizer) : 규칙 기반 CBO (Cost Based Optimizer) : 비용 기반

 

2. Access Methods

/*+ FULL(table_name) */

Table Full Scan 

예)  /*+ FULL(@SEL$2 A@SEL$2) */    

쿼리블록 

/*+ 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를 내림차순으로 쓰게끔 지정한다.

SQL> SELECT /*+ index_desc(emp pk_emp) */ empno FROM emp WHERE rownum = 1 ;

위 실행은 제일 큰 것 하나가 조회되므로, max function의 기능을 대신할 수 있다.

/*+ 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 형식으로 변형한다. 일반적으로 변형은 비용 측면에서 효율적일 때만 일어난다.

3. 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을 선택한다.

4. 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에서 일어나도록 유도한다.

5. 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를 넣지 않도록 주의해야 한다.

- DEGREE의 의미 및 결정

Parallel Query에서 degree란 하나의 operation 수행에 대한 server process의 개수를 의미하며 이러한 degree 결정에 영향을 주는 요인들에는 다음과 같은 것들이 있다.

(1) system의 CPU 개수
(2) system의 maximum process 개수
(3) table이 striping 되어 있는 경우, 그 table이 걸쳐있는 disk의 개수
(4) data의 위치 (즉, memory에 cache 되어 있는지, disk에 있는지)
(5) query의 형태 (예를 들어 sorts 혹은 full table scan)

SQL> SELECT /*+ PARALLEL(emp, 4) */ * FROM emp;

6. 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으로써 수행된다.
subqueries가 상대적으로 inexpensive 하고 rows의 수를 줄일 수 있다면, subqueries를 더 일찍 evaluation 하는 것이 performance를 향상할 것이다.
subquery가 remote table에 적용되거나, merge join을 사용하는 join 된 table에 적용된다면 이 hint는 적용되지 않는다.

출처 
devuna.tistory.com/35
gregorio78.tistory.com/98
sqlmaria.com/2020/03/10/what-are-query-block-names-and-how-to-find-them/