힌트 종류
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 Oracle 힌트
- 1.1 힌트의 종류 별 분류
- 1.2 힌트의 설명 및 사용법
- 1.2.1 ALL_ROWS
- 1.2.2 AND_EQUAL
- 1.2.3 APPEND HINT
- 1.2.4 CACHE HINT
- 1.2.5 CHOOSE HINT
- 1.2.6 CLUSTER HINT
- 1.2.7 CURSOR_SHARING_EXACT
- 1.2.8 DRIVING_SITE
- 1.2.9 DYNAMIC_SAMPLING
- 1.2.10 EXPAND_GSET_TO_UNION
- 1.2.11 FACT HINT
- 1.2.12 FIRST_ROWS
- 1.2.13 FULL HINT
- 1.2.14 HASH HINT
- 1.2.15 HASH_AJ
- 1.2.16 INDEX
- 1.2.17 INDEX_ASC
- 1.2.18 INDEX_COMBINE
- 1.2.19 INDEX_DESC
- 1.2.20 INDEX_FFS
- 1.2.21 LEADING_HINT
- 1.2.22 MERGE
- 1.2.23 MERGE_AJ
- 1.2.24 MERGE_SJ
- 1.2.25 NL_AJ
- 1.2.26 NL_SJ
- 1.2.27 NOAPPEND
- 1.2.28 NOCACHE
- 1.2.29 NO_EXPAND
- 1.2.30 NO_FACT
- 1.2.31 NO_INDEX
- 1.2.32 NO_MERGE
- 1.2.33 NOPARALLEL
- 1.2.34 NOPARALLEL_INDEX
- 1.2.35 NO_PUSH_PRED
- 1.2.36 NO_PUSH_SUBQ
- 1.2.37 NOREWRITE
- 1.2.38 NO_UNNEST
- 1.2.39 ORDERED
- 1.2.40 ORDERED_PREDICATE
- 1.2.41 PARALLEL
- 1.2.42 PARALLEL_INDEX
- 1.2.43 PQ_DISTRIBUTE
- 1.2.44 PUSH_PRED
- 1.2.45 PUSH_SUBQ
- 1.2.46 REWRITE
- 1.2.47 ROW_ID
- 1.2.48 RULE
- 1.2.49 STAR
- 1.2.50 STAR_TRANSFORMATION
- 1.2.51 UNNEST
- 1.2.52 USE_CONCAT
- 1.2.53 USE_HASH
- 1.2.54 USE_MERGE
- 1.2.55 USE_NL
1 Oracle 힌트[편집]
1.1 힌트의 종류 별 분류[편집]
1.1.1 Optimization Goals and Approaches[편집]
- ALL_ROWS 혹은 FIRST_ROWS
- CHOOSE
- RULE
1.1.2 Acess Method Hints[편집]
AND_EQUAL
CLUSTER
FULL
HASH
INDEX 혹은 NO_INDEX
INDEX_ASC 혹은 INDEX_DESC
INDEX_COMBINE
INDEX_FFS
ROWID
1.1.3 Join Order Hints[편집]
ORDERED
STAR
1.1.4 Join Operation Hints[편집]
DRIVING_SITE
HASH_SJ, MERGE_SJ 혹은 NL_SJ
LEADING
USE_HASH 혹은 USE_MERGE
USE_NL
1.1.5 Parallel Execution Hints[편집]
PARALLEL 혹은 NOPARALLEL
PARALLEL_INDEX
PQ_DISTRIBUTE
NOPARALLEL_INDEX
1.1.6 Query Transformation Hints[편집]
EXPAND_GSET_TO_UNION
FACT 혹은 NOFACT
MERGE
NO_EXPAND
NO_MERGE
REWIRTE 혹은 NOREWRITE
STAR_TRANSFORMATION
USE_CONCAT
1.1.7 Other Hints[편집]
APPEND 혹은 NOAPPEND
CACHE 혹은 NOCACHE
CURSOR_SHARED_EXACT
DYNAMIC_SAMPLING
NESTED_TABLE_GET_REFS
UNNEST 혹은 NO_UNNEST
ORDERED_PREDICATES
1.2 힌트의 설명 및 사용법[편집]
1.2.2 AND_EQUAL[편집]
/*+ AND_EQUAL (table index index [index] [index] [index] ) */
- 복수의 단일 컬럼을 스캔하여 머지 방식으로 처리하게 합니다.
1.2.3 APPEND HINT[편집]
/*+ APPEND */
- 직렬 모드 데이터베이스에서 Direct INSERT를 실행하게 합니다.
- Enterprise Edition 이 아닌 데이터베이스의 기본 모드는 직렬 모드입니다. 이러한 직렬 모드 데이터 베이스에서의 INSERT 작업은 Conventional를 기본값으로 하고 병렬 처리 시에는 Direct INSERT를 기본값으로 합니다.
1.2.4 CACHE HINT[편집]
/*+ CACHE (table) +/
- 풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 MRU 쪽에 위치시킵니다. 작은 테이블의 사용 시 유용합니다.
1.2.5 CHOOSE HINT[편집]
/*+ CHOOSE +/
- Rule-Based 와 Cost-Based 방식 간의 선택을 유도합니다. 선택 기준은 사용 객체의 분석 정보 존재 여부이며, 사용되는 객체들중 하나라도 분석 정보가 존재한다면 Cost-Based 방식을 사용하게 됩니다.
1.2.7 CURSOR_SHARING_EXACT[편집]
/*+ CURSOR_SHARING_EXACT +/
- 바인드 변수 값의 교체를 불가능하게 합니다.
- 기본적으로 CURSOR_SHARING 파라미터를 사용하여, 안전하다고 판단될 시 SQL 내의 바인드 변수 값을 교체할 수 있게 되어 있습니다.
1.2.8 DRIVING_SITE[편집]
/*+ DRIVING_SITE (table) +/
- 오라클이 선택한 SITE 대신, 지정한 SITE를 사용하여 쿼리를 실행합니다. Rule-Based 와 Cost-Based, 두 모드 다 사용 가능합니다.
1.2.9 DYNAMIC_SAMPLING[편집]
/*+ DYNAMIC_SAMPLING ( [table] n ) +/
- 해당 객체의 Selectivity 와 Cardinality 에 대한 보다 자세한 정보를 자동으로 생성시켜 실행합니다.
- 값은 0 부터 10 까지 지정할 수 있으며, 높을 수록 보다 자세한 정보를 생성하게 됩니다. 테이블에 해당 값을 지정하지 않았을 경우, 기본 값은 CURSOR 레벨의 값이 쓰여집니다.
1.2.10 EXPAND_GSET_TO_UNION[편집]
/*+ EXPAND_GSET_TO_UNION +/
- GROUP BY GROUPING SET 혹은 GROUP BY ROLLUP 등과 같은 구문을 포함하는 쿼리에 사용할 수 있습니다.
- 이 힌트는 기존의 쿼리를 개별적인 그룹 생성 후, UNION ALL 방식으로 실행되게 유도합니다.
1.2.15 HASH_AJ[편집]
/*+ HASH_AJ */
- EXISTS 구문 뒤에 오는 서브 쿼리에 사용되며 HASH_SJ, MERGE_SJ 혹은 NL_SJ 등을 사용할 수 있습니다.
- HASH_SJ 은 hash semi-join 이고, MERGE_SJ 은 sort merge semi-join 이며 NL_SJ 은 nested loop semi-join 입니다.
1.2.16 INDEX[편집]
/*+ INDEX (table index [index] [index] ... ) */
- 지정한 테이블의 인덱스 스캔을 실행하도록 유도합니다.
- Domain, B-tree, bitmap, bitmap join 인덱스 등이 사용될 수 있으나, bitmap 인덱스 들의 사용 시, INDEX 힌트보다는 INDEX_COMBINE 힌트 사용이 추천됩니다.
1.2.17 INDEX_ASC[편집]
/*+ INDEX_ASC (table [index] [index] ... ) +/
- 해당 테이블의 인덱스를 순차적 방식으로 스캔하게 합니다.
- 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 값의 순차적 방식으로 읽게 됩니다.
1.2.18 INDEX_COMBINE[편집]
/*+ INDEX_COMBINE (table [index] [index] ... ) +/
- 해당 테이블에 Bitmap 인덱스의 존재 시, Bitmap 인덱스를 통한 액세스를 유도합니다.
- 힌트 내에 인덱스의 이름이 쓰여지지 않을 시, 해당 인덱스의 Boolean 값을 사용하여 최적의 Cost를 산출하여 실행하게 됩니다.
1.2.19 INDEX_DESC[편집]
/*+ INDEX_DESC (table [index] [index] ... ) +/
- 지정한 인덱스에 대해 인덱스 스캔을 역순으로 실행합니다.
- 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 컬럼의 값을 사용하여 역순으로 실행합니다.
- 파티션 인덱스에서는 파티션 별 개별적인 실행이 이루어집니다.
1.2.20 INDEX_FFS[편집]
/*+ INDEX_FFS (table [index] [index] ... ) +/ /*+ NO_INDEX_FFS */
- Full 테이블 스캔 대신에 빠른 인덱스 패스트 풀스캔의 실행을 유도합니다.
- 멀티 블럭 I/O로 처리함
- 고려할 사항
인덱스 Fast Full Scan은 인덱스에 의해 정렬된 데이터가 추출되지 않는다. 인덱스 Fast Full Scan만 병렬 프로세싱이 가능하다.
- 언제 사용하는지?
인덱스로만 원하는 데이터를 모두 추출하는 경우 해당 테이블의 데이터 중 대부분을 추출하는 경우
- BETWEEN,부등호 연산, COUNT, MAX, MIN 질의를 할 때 성능상의 이점
- 정렬이 불필요한 경우
1.2.21 LEADING_HINT[편집]
/*+ LEADING (table) +/
- 테이블 간의 조인 시에 지정한 테이블을 먼저 수행하도록 유도합니다.
- 두 개 이상의 LEADING 힌트의 사용 시, 힌트 자체가 사용되어 지지 않습니다.
- ORDERED 힌트와 더불어 사용시, LEADING 힌트는 사용되지 않습니다.
1.2.22 MERGE[편집]
/*+ MERGE (table) +/
- 각 쿼리의 결과값을 머지합니다.
- 해당 쿼리 내에 GROUP BY 절의 사용 이나 SELECT 구문에 DISTINCT 가 사용되었을 시, 머지의 실행이 가능할 경우에만 힌트가 실행됩니다.
- IN 과 서브 쿼리의 사용 시, 서브 쿼리와 상위 쿼리 간의 상호 관계가 없을 때에만 머지의 실행이 가능합니다.
- 이 힌트는 Cost-based 가 아닙니다. 따라서 액세스하는 실행 쿼리 블럭에 MERGE 힌트가 반드시 명시되어야만 합니다. 그렇지 않을 경우 옵티마이저는 다른 실행 계획을 수립합니다.
1.2.23 MERGE_AJ[편집]
HASH_AJ 를 참조하십시요.
1.2.24 MERGE_SJ[편집]
HASH_AJ 를 참조하십시요.
1.2.25 NL_AJ[편집]
HASH_AJ 를 참조하십시요.
1.2.26 NL_SJ[편집]
HASH_AJ 를 참조하십시요.
1.2.27 NOAPPEND[편집]
/*+ NOAPPEND +/
- 병럴 모드에서의 INSERT 작업을 Conventional 방식으로 수행합니다.
- 병렬 모드에서는 Direct-path INSERT 가, 직렬 모드에서는 Conventional INSERT가 기본값입니다.
1.2.28 NOCACHE[편집]
/*+ NOCACHE (table) +/
- 풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 LRU 쪽에 위치시킵니다. 기본 모드입니다.
1.2.29 NO_EXPAND[편집]
/*+ NO_EXPAND +/
- 실행 쿼리 내에 OR 나 WHERE 절의 IN 이 사용되었을 시, Cost-Based 옵티마이저가 쿼리 처리를위해 OR 를 사용한 확장을 사용하는 것을 방지합니다.
- 일반적으로 옵티마이저는 위와 같은 경우 OR – 확장의 가격이 확장을 사용하지 않는 것보다 적을 시, 확장 방식으로 수행합니다.
1.2.33 NOPARALLEL[편집]
/*+ NOPARALLEL (table) +/
- 지정한 테이블의 병렬 처리를 방지합니다.
- 테이블의 지정된 PARALLEL 값에 대해서 우선권을 가집니다.
- 중첩 테이블에 대해서는 병렬 처리를 할 수 없습니다.
1.2.34 NOPARALLEL_INDEX[편집]
/*+ NOPARALLEL_INDEX (table [index] [index] ... ) +/
- 인덱스 스캔 작업의 병렬 처리를 방지합니다.
- 인덱스에 지정된 PARALLEL 값에 우선권을 가집니다.
1.2.36 NO_PUSH_SUBQ[편집]
/*+ NO_PUSH_SUBQ +/
- 서브 쿼리의 결과값을 머지하지 않는 실행 계획이 실행 계획 설립 단계에서 제일 마지막으로 참조되는 것을 방지합니다.
- 일반적으로 서브 쿼리의 Cost 가 높거나, 처리 로우의 갯수를 크게 줄여주지 못할 때에는 서브 쿼리를 마지막에 참조하는 것이 성능 향상에 도움이 됩니다.
1.2.37 NOREWRITE[편집]
/*+ NOREWRITE +/
- 해당 쿼리 블럭의 쿼리 재생성의 실행을 방지합니다.
- QUERY_REWRITE_ENALBE 파라미터에 대해 우선권을 가집니다.
- NOREWRITE 힌트의 사용 시, Function-Based 인덱스의 사용이 금지됩니다.
1.2.40 ORDERED_PREDICATE[편집]
/*+ ORDERED_PREDICATE +/
- 옵티마이저에 의한 조인 관계의 Cost를 산출하기 위해 미리 정해둔 조인 관계 별 실행 순서의 사용을 방지합니다.
n 인덱스 키를 사용한 조인 관계들은 제외됩니다.
- 이 힌트는 쿼리의 WHERE 절에 사용하십시요.
1.2.41 PARALLEL[편집]
/*+ PARALLEL (table [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/
- 병렬 처리에 사용될 서버 프로세스의 갯수를 설정.
- 병렬 처리 조건에 위배될 시, 힌트는 적용되지 않음.
- TEMP테이블에 대한 PARALLEL_HINT 사용 시에는 적용되지 않음.
1.2.42 PARALLEL_INDEX[편집]
/*+ PARALLEL_INDEX (table [ [index] [, index]...] [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/
- 파티션 인덱스의 인덱스 범위 스캔 작업의 병렬 처리에 할당될 서버 프로세스의 갯수를 지정합니다.
1.2.43 PQ_DISTRIBUTE[편집]
/*+ PQ_DISTRIBUTE (table [,] outer_distribution, inner_distribution) +/
- 병렬 조인 시, Producer 프로세스와 Consumer 프로세스 간의 데이터 전달 방식을 지정합니다.
1.2.44 PUSH_PRED[편집]
/*+ PUSH_PRED (table) +/ /*+ NO_PUSH_PRED */
- 결과값에 대한 조인 방식 서술의 강제적 수행을 실행합니다.
- 메인쿼리에서 서브쿼리를 제어
- 메인쿼리에 기술
- 조인조건을 인라인뷰 안으로 PUSH 하는 힌트
SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
FROM employees e,
( SELECT manager_id
FROM employees
) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;1.2.45 PUSH_SUBQ[편집]
/*+ PUSH_SUBQ +/ /*+ NO_PUSH_SUBQ */
- 서브쿼리 조인조건을 메인쿼리에 먼저 조인처리
- 실행 계획에서 가능한 빠른 단계에서 노머지(nomerge)된 서브 쿼리를 평가하기 위해 옵티마이저에 지시
- 머지가 불가능한 서브 쿼리들의 우선 실행 계획을 실행 계획 수립시 먼저 참조하도록 지시
- 서브 쿼리가 Remote 테이블,머지 조인의 사용 시 힌트는 실행되지 않음.
1.2.46 REWRITE[편집]
/*+ REWRITE [ ( [materialized_view] [materialized_view]...) ] +/
- 실행 계획의 가격에 상관없이 Materialized View 를 사용하여 쿼리 재생성을 하도록 합니다.
- Materialized View 를 지정할 시, 지정한 Materialized View 의 가격에 상관없이 무조건 쿼리 재생성을 실행합니다.
- Materialized View 를 지정하지 않을 시, 오라클은 사용 가능한 모든 Materialized View 를 참조하여 그 중 가장 가격이 낮은 Materialized View 를 사용하여 쿼리 재생성을 합니다.
- Materialized View 를 지정하지 않는 힌트의 사용이 권장됩니다.
1.2.48 RULE[편집]
/*+ RULE +/
- 실행 계획을 Rule-Based 방식으로 실행하게 합니다.
- 해당 쿼리 블럭에 다른 힌트 또한 사용되었을 경우, 다른 힌트들은 사용되지 않습니다.
1.2.49 STAR[편집]
/*+ STAR +/
- Star 쿼리 계획이 사용 가능하다면, 실행하게 합니다.
- Star 쿼리 계획이란 가장 큰 테이블이 마지막 순서로 조인되며, 조인될 시 가장 큰 테이블 내의 Concatenated 인덱스에 대해 Nested Loop 조인 방식으로 실행되는 것을 말합니다.
- 최소한 세개 이상의 테이블이 사용되며, 제일 큰 테이블의 Concatenated 인덱스의 생성에 최소한 세 개 이상의 컬럼이 사용되어야 하며, 액세스나 조인 방식에 충돌이 없어야만 이 힌트는 사용됩니다.
1.2.50 STAR_TRANSFORMATION[편집]
/*+ STAR_TRANSFORMATION +/
- 옵티마이저가 Star 변형 작업에 최적화된 실행 계획을 수립, 실행하도록 합니다.
- 힌트를 사용하지 않을 시, 옵티마이저는 일반적인 작업에 최적화된 실행 계획을 수행합니다.
- 힌트를 사용하였어도 변형 작업에 맞추어진 실행 계획을 실행한다는 보장은 없습니다. 다른 일반적인 힌트의 사용과 마찬가지로 비교 분석 후, 오라클의 판단에 따라 다른 실행 계획이 실행될 수 있습니다.
1.2.51 UNNEST[편집]
- UNNEST 는 둥지(집)에서 몰아내다의 의미 로 서브쿼리를 메인쿼리로 몰아 내서 조인토록 유도하는 하는 힌트
- 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
- WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어
메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다. 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
- 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에
Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.
/*+ UNNEST +/
- 서브 쿼리에 기술
- 서브 쿼리 블럭에 대해 인증성 만을 검사하게 합니다.
- 인증이 되었다면 그 이상의 검증 작업없이 서브쿼리에 대한 UNNESTING 의 설정을 가능하게 합니다.
select *
from emp
where deptno in (
select /*+ unnest */ deptno
from dept
);/*+ NO_UNNEST +/
1.2.52 USE_CONCAT[편집]
/*+ USE_CONCAT +/
- WHERE 절의 OR 조인 을 UNION ALL 로 변경하여 수행하게 합니다.
- 일반적으로 이러한 변경은 결과값의 병합 수행의 가격이 수행하지 않을 시의 가격 보다 낮을 때에만 실행됩니다.
1.2.53 USE_HASH[편집]
/*+ USE_HASH (table [table]...) +/ /*+ NO_USE_HASH */
- Hash 조인 방식으로 각 테이블을 조인.
- EQUAL JOIN 에서만 가능
- 작은테이블과 큰테이블 조인시 작은테이블이 드라이빙 테이블에 좋음
- ORDERED힌트를 사용하여 조인순서 조정하면 좋음
- HASH AREA SIZE 확인: 작은테입블사이즈 * 1.6 적당
- 각 테이블 에 대해 1 번만 찾음,NL 조인이 이너테이블을 여러번 중복으로 탐색하는것과는 다름.
- ALTER SESSION SET HASH_AREA_SIZE= 104857600; -* 세션에 해쉬 사이즈 증가로 속도 개선(배치,ETL처리시)
1.2.54 USE_MERGE[편집]
/*+ USE_MERGE (table [table]...) +/ /*+ NO_USE_MERGE */
- Sort-Merge 방식으로 각 테이블을 조인하게 합니다.
- 참조테이블이 아우터/드라이빙 테이블 이면 적용되지 않음
- LEADING and ORDERED 와 함께 사용할것을 권장함
- 메모리 필요,SORT_AREA_SIZE 비용이 필요
- 양쪽 테이블 이 모두 sort 한후 merge,별도로 SORT 할필요 없음
- 넓은범위처리
- 환경설정
ALTER SESSION SET SORT_AREA_SIZE= 104857600; ALTER SESSION SET SORT_AREA_RETAINED_SIZE= 104857600; (같이 준다) ALTER SESSION SET SORT_MULTIBLOCK_READ_COUNT=128;
- 정렬 메모리의 크기
(= Target rows×(total selected column’s bytes) ×2) 이상
PGA Memory Allocation Error가 발생하지 않는 범위
1.2.55 USE_NL[편집]
/*+ USE_NL (table [table]...) +/ /*+ NO_USE_NL */
- Nested-Loop 방식으로 각 테이블을 조인.
- LEADING and ORDERED 와 함께 사용할것을 권장함
- 참조테이블이 OUTER 테이블이면 힌트는 무시 됨.
- 드라이빙 테이블이 중요함
- CPU 사용하지 않음
- join시 이너테이블로 강제로 지정해버림(중첩되도록)
- 드라이빙 테이블은 조회조건으로 집합구성(WHERE절의 관련 컬럼의 인덱스가 중요)
- 드라이빙 집합으로 후행테이블이 조인키+조회조건 인덱스를 탄다
- 두테이블에 적용되는 인덱스에 따라 자동 정렬