1. Hint
Hint 는 Optimizer 의 행동을 제어하기 위해 사용한다.
Hint 가 무시되는 경우는 해당 힌트로 제어하려는 행동 자체가 불가능한 경우이다.
Hint 가 사용되면 CBO 의 경우 Transformation 에 있어 해당 Hint 에 대해 Cost 산정 없이 선택한다.
Hint 로 Rule 을 사용하는 경우 CBO 를 사용하지 않겠다는 의미와 동일하다.
Hint 는 가능한 사용을 피하고 Optimizer 가 올바른 판단을 할 수 있을 정도로 꼭 필요한 상황에 사용해야
한다. 지나친 사용은 Optimizer 의 유연성을 (다른 플랜을 만드는데 있어서) 떨어뜨린다.
2. Usage of Hint
A. Hint 의 사용은 /*+ hint_name */ 과 같이 사용하며 일반적으로 메인 쿼리의 Select 다음 위치에 사용한다.
3. Type of Hint
A. Hints for Optimization Approaches and Goals
i. ALL_ROWS
ii. FIRST_ROWS(n)
말 그대로 Optimizer 의 Goal 을 지정하는 Hint 이다. All_ROWS 는 결과전체를 산출하기 위한 최적의 Plan 을 만들어내며, FIRST_ROWS(n) 은 n 개 만큼의 row(s) 를 반환하는데 있어 최적의 Plan 을 만들어 낸다. 제어 방법은 세션레벨에서 ALTER SESSION 을 사용하여 OPTIMIZER_MODE 를 지정하는 방법과 세션 레벨에서 /*+ ALL_ROWS */ 또는 /*+ FIRST_ROWS(n) */ 과 같이 사용한다.
B. Hints for Enabling Optimizer Features
i. optimizer_features_enable(’11.1.0.6’)
Optimizer 는 ORACLE Version 마다 그 능력(?) 이 다르다. 해당 버전에 따른 New Feature 가 있는데 이를 제어하기 위한 방법으로 위와 같이 사용한다.
C. Hints for Access Paths
i. FULL
ii. CLUSTER
iii. HASH
iv. INDEX and NO INDEX
v. INDEX_ASC and INDEX_DESC
vi. INDEX_COMBINE
vii. INDEX_JOIN
viii. INDEX FFS and NO INDEX FFS
ix. INDEX_SS and NO_INDEX_SS
x. INDEX_SS_ASC and INDEX_SS_DESC
데이터에 대한 접근 방법은 크게 두가지로 나뉘어질 수 있다. 이를테면 인덱스를 사용하느냐 사용하지 않느냐란 것이다.
인덱스를 사용하지 않는다는 것은 no_index 와 같이 사용하는 것을 말함이 아니다. 이는 특정 인덱스를 쓰지 않겠다고 지정한 것일뿐이며 Optimizer 는 다른 Index 를 이용할 수 있다.
인덱스를 사용하지 않겠다는 가장 확실한 표현은 full 힌트의 사용이다.
/*+ CLUSTER HINT 와 HASH 에 대해 추가조사 */
INDEX COMBINE 은 한 객체에 대한 두개의 B-Tree 혹은 Bitmap Index 를 이용해 Bitmap and / or 연산을 거쳐 RowID 를 이용한 엑세스를 이용하는 Hint 이다.
INDEX JOIN 은 두개의 인덱스에 Query 의 모든 Where 절과 Select-List 를 가지고 있을 때 ROWID 를 이용해 조인을 실시한다. (테이블 엑세스가 발생하지 않는다.)
/*+ INDEX COMBINE, INDEX JOIN Sample 추가 */
INDEX_FFS 는 Fast Full Scan 을 의미한다. INDEX FFS 가 가능한 상황은 한정적이다. 가장 일반적이고 쉬운 예로는 Primary key 로 구성된 Single Column Index 인 경우 해당 컬럼의 Count 연산에 대해 Index Fast Full Scan 을 할 수 있다.
D. Hints for Join Orders
i. LEADING
ii. ORDERED
두 힌트 모두 Access 순서를 제어하는 힌트이다.
Leading 의 경우 /*+ leading(a b c) */ 이런식으로 사용이 가능하다. 내부에 들어가는 argument 는 Table 의 이름이나 alias 이다.
ordered 의 경우 /*+ ordered */ 와 같이 사용한다. ordered 의 경우 from 절에 기술된 순서대로 Table 에 Access 한다.
두 힌트의 차이라고 하면 Leading 의 경우 Table 의 엑세스 순서를 단순히 Alias (또는 Table 이름) 로 제어가 가능하지만 ordered 의 경우에는 From 절의 Table 엑세스 순서를 조정해야 하는 추가적인 노력이 필요하다.
E. Hints for Join Operations
i. USE_NL and NO_USE_NL
ii. USE_NL_WITH_INDEX
iii. USE_MERGE and NO_USE_MERGE
iv. USE_HASH and NO_USE_HASH
보통 특정 Join 을 하도록 유도하기 때문에 no_(join_type) 과 같은 Hint 는 거의 사용되지 않는다.
use_nl 의 경우 /*+ use_nl(a b) */ 혹은 /*+ use_nl(a) use_nl(b) */ 와 같이 사용된다. argument 로 사용되는 alias 에는 inner Table (NL Join), Prove Table (Hash Join) 을 기술한다.
use_nl_with_index 의 경우 /*+ use_nl_with_index (inner_table inner_table_index) */ 와 같이 사용된다.
이를 테면 다음과 같은 쿼리의 의미는 I alias 에 해당하는 테이블을 Inner Table 로 NL Join 을 하되 item_product_ix 인덱스를 사용하라는 의미이다.
SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;
만약 위 Hint 를 사용하지 않는다면 아래와 같이 Hint 가 많아진다.
SELECT /*+ LEADING(h) USE_NL(I) INDEX(I item_product_ix) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;
/*+ Join Hint 로 Access 순서가 고정되는지를 확인. 고정된다면 위 구문의 leading hint 는 불필요하다. */
use_merge , use_hash의 경우 use_nl 의 사용과 동일하다.
F. Hints for Online Application Upgrade
i. CHANGE_DUPKEY_ERROR_INDEX
ii. IGNORE_ROW_ON_DUPKEY_INDEX
iii. RETRY_ON_ROW_CHANGE
/*+위의 hint 들은 정체 불명이다. 테스트가 필요하다 */
일단 ORACLE 에 소개된 내용을 그대로 전달하자면 다음과 같다.
CHANGE_DUPKEY_ERROR_INDEX 를 사용하면 지정한 테이블의 인덱스에 대해 Unique Violation 을 명확히 표현하겠다란 표현이다.
이 힌트에서 테이블, 인덱스를 인자로 받는데 여기서 인덱스는 존재해야 하며 반드시 Unique 해야 한다.
위 힌트가 사용되면 Violation Unique Index 에 대해 ORA-001 이 아닌 ORA-38911 로 표현된다.
IGNORE_ROW_ON_DUPKEY_INDEX 를 사용하면 지정한 테이블의 인덱스에 대해 Insert 시 발생하는
Unique Violation 을 무시하겠다는 표현이다.
이 힌트는 오직 하나의 테이블에 대한 Insert 만 해당되며 update, delete, merge, multitable insert 에대
해서는 적용되지 않는다.Unique Violation 이 발생하는 경우 row-level rollback 이 일어나며 다음 row
에 대한 연산으로 넘어간다.
/*+ RETRY_ON_ROW_CHANGE Hint 추가정보 수집 */
G. Hints for Parallel Execution
i. PARALLEL and NO PARALLEL
ii. PARALLEL INDEX and NO PARALLEL INDEX
iii. PQ DISTRIBUTE
Table, Insert 를 Parallel 처리할 때 사용하는 힌트이다.
Hint 는 statement-level PARALLEL hint 와 object-level PARALLEL hint 로 나뉘어진다.
statement-level PARALLEL hint
-PARALLEL : PARALLEL 수행을 한다. degree 는 2 이상으로 수행한다.
-PARALLEL(DEFAULT) : 상동
-PARALLEL(AUTO) : degree 1 이상으로 수행한다. 1인 경우 일반 수행과 동일하다.
-PARALLEL(MANUAL) : object 에 대한 PARALLEL Setting 을 사용하게 된다.
-PARALLEL(integer) : 해당 integer 에 해당하는 degree 로 수행한다.
object-level PARALLEL hint
SQL 문장 Level 이 아니라 문장의 객체 레벨로 PARALLEL hint 를 사용한다.
ex) /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */, /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */
H. Hints for Query Transformations
i. NO_QUERY_TRANSFORMATION
ii. USE_CONCAT
iii. NO_EXPAND
iv. REWRITE and NO_REWRITE
v. MERGE and NO MERGE
vi. STAR_TRANSFORMATION and NO_START_TRANSFORMATION
vii. FACT and NO_FACT
viii. UNNEST and NO_UNNEST
Optimizer 의 Query Transformation 을 제어하는 Hint 이다.
NO_QUERY_TRANSFORMATION : 모든 Query Transformation 을 skip (위의 목록 확인)
USE_CONCAT : 사용시 or-expansion 한다.
NO_EXPAND : USE_CONCAT 과 반대의 의미로 사용.
REWRITE : MVIEW 와 관련해 Rewrite 가 가능한 경우 cost 산정없이 사용한다.
MERGE : inline view 에 대해 view merging 을 실시한다.
STAR_TRANSFORMATION : Star Transformation 을 사용.
FACT : Star transformation 에서 fact table 로 지정하기 위해 사용
UNNEST : Main Query 의 상수값을 sub query 에 전달.
I. Additional Hints
i. APPEND, APPEND_VALUES, and NOAPPEND
ii. CACHE and NOCACHE
iii. PUSH_PRED and NO_PUSH_PRED
iv. PUSH_SUBQ and NO_PUSH_SUBQ
v. QB_NAME
vi. CURSOR_SHARING_EXACT
vii. DRIVING_SITE
viii. DYNAMIC_SAMPLING
ix. MODEL_MIN_ANALYSIS
APPEND : direct-path INSERT 를 함.
CACHE : Full Scan 시 탐색된 Table 의 most recently used block block 을 버퍼캐시의 LRU list 의 끝에 위치시킨다.
PUSH_PRED : Predicate (조건절) 를 Inline View 의 조건으로 변형.
PUSH_SUBQ : not merged subquery 가 대상. Optimizer 는 Subquery 의 Merging 을 먼저 Cost 산정
QB_NAME : 해당 쿼리 블록에 사용자가 명명한다.
CURSOR_SHARING_EXACT : CURSOR_SHARING = EXACT 와 동일하다.
DRIVING_SITE : distributed query 에 사용 /*+ db link 사용하여 예제 제작 */
DYNAMIC_SAMPLING : /*+ DYNAMIC_SAMPLING(TABLE[_ALIAS] NUM ) */ num 은 0~ 10 사이의 Integer 를 사용한다. /*+ 자세한 설명은 다른 문서를 이용. */
MODEL_MIN_ANALYSIS : spreadsheet rule 의 최적화 타임의 일부를 생략한다.
4. References
A. Using Optimizer Hints | 11g Release 2 (11.2) |
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF005