새로쓴 대용량 데이터베이스 솔루션 - 제 1부 액세스 영향요소의 이해3-3

3.3 실행계획의 제어

 

3.3.1 힌트(Hint)의 활용 기준

 

 

3.3.2 최적화 목표(Goal) 제어 힌트

- ALL_ROWS

  : 쿼리 전체 결과를 모두 수행하는 것에 대한 최적화를 목표로 최저 비용의 실행계획을 수립하도록 유도

  : Minimum total resource consumption

 

- CHOOSE

  : SQL에서 액세스하는 테이블에 대한 통계정보 유무에 따라 규칙, 비용기준을 적용하여 최적화를 수행

  : CHOOSE 모드에서 테이블의 통계정보를 참조할 수 있는 경우는 ALL_ROWS 방식으로 처리

 

- FIRST_ROWS

  : 최적 응답시간(Best response time)을 목표로 최저 비용의 실행계획을 수립하도록 유도

  : Minimum resource usage to return first to n rows)

 

- RULE

  : 조건구문에 사용된 컬럼들의 인덱스, 연산자 형태 (규칙기준 옵티마이저) 등에 의한 우선순위 규칙에 근거하여 실행계획을 수립하도록 유도

 

 

3.3.3 조인 순서 조정을 위한 힌트

- ORDERED

  : FROM 절에 기술된 테이블 순서대로 조인을 수행하도록 유도한다 (LEADING힌트와 함께 사용하면 LEADING 힌트는 무시됨)

  : 액세스 순서만 제시할 뿐 조인 방법과는 무관하므로 USE_NL, USE_MERGE 등의 조인방법 유도 힌트와 함께 사용하는 것이 일반적

 

- LEADING

  : FROM절에 기술한 테이블 순서와 상관없이 조인 순서를 제어하는 힌트 (FROM절을 변경할 필요 없이 사용 가능)

 

 

3.3.4 조인 방법 선택용 힌트

- USE_NL

  : Nested Loops 방식을 사용하여 조인을 수행하도록 유도하는 힌트 (적극적인 개입)

  : 대상 집합 간 조인 방식을 지칭하며 조인 순서에는 영향 X

 

- NO_USE_NL

  : Nested Loops 조인 방식을 제외한 다른 방식의 조인(해쉬, Sort Merge 조인 등)을 사용할 것을 제시 (소극적인 개입)

 

- USE_NL_WITH_INDEX

  : Nested loops 조인에서 선행 처리되는 외측 루프의 처리주관 인덱스를 지정할 때 사용하는 힌트

 

- USE_HASH

  : 해쉬조인 방식으로 조인이 수행되게 유도하는 힌트

  : 옵티마이저의 판단에 문제가 있거나 인라인뷰에서 가공한 결과 집합처럼 적절한 통계정보를 가질수 없는 경우 활용가치가 높음

 

- NO_USE_HASH

 

- USE_MERGE

  : Sort Merge 방식으로 조인을 수행하도록 유도하는 힌트 (필요한 경우 ‘ordered’ 힌트와 같이 사용할 것)

 

- NO_USE_MERGE

 

 

3.3.5 병렬 처리 관련 힌트

- PARALLEL

  : 대량의 데이터에 대한 테이블을 액세스 할 때와 DML을 처리할 때 SQL의 병렬처리를 지시

  : 일반적으로 병렬 스레드(Parallel threads)를 나타내는 숫자와 함께 사용

  : 만약 정의하지 않으면 PARALLEL_THREADS_PER_CPU 파라메터에 정의된 값을 이용하여 자동 계산 적용

  : PARALLEL을 지정하여 테이블을 정의했다면 힌트를 사용하지 않아도 병렬처리가 가능한 경우 이 병렬도를 적용

  : DML 문장을 병렬로 수행하기 위해서는 반드시 해당 세션을 ‘ALTER SESSION ENABLE PARALLEL DML’로 지정해야함

 

- NOPARALLEL

  : 힌트를 적용하면 PARALLEL 파라메터를 무시하고 병렬처리를 하지않는 실행계획을 수립

 

- PQ_DISTRIBUTE

  : 속도 향상을 위해 슬레이브 프로세스 – 생산자와 소비자 프로세스 – 사이에서 조인할 테이블 로우를 서로 주고받는 작업 할당 방법을 정의하는 힌트

  : ‘HASH’, ‘BROADCAST’, ‘PARTITION’, ‘NONE’

 

- PARALLEL_INDEX

  : 파티션 인덱스(Patitioned Index)에 대한 인덱스 범위 스캔을 병렬로 수행하기 위한 병렬도를 지정하는 힌트

 

- NOPARALLEL_INDEX

  : 인덱스 생성/변경에 의해 PARALLEL 파라메터가 적용되어 있는 경우, 옵티마이저는 해당 인덱스에 대한 PARALLEL 파라메터를 무시

  : 병렬 인덱스 범위 스캔 하지 않게 됨

 

 

3.3.6 액세스 수단 선택을 위한 힌트

- FULL

  : 힌트 내 정의된 테이블을 FULL TABLE SCAN 방식으로 유도

 

- HASH

  : 해쉬 클러스터 테이블을 액세스 할 때 HASH SCAN 방식으로 액세스 하도록 유도

 

- CLUSTER

  : 클러스터링 테이블을 액세스할 때 클러스터 인덱스를 통해 스캔하도록 유도

 

- INDEX

  : 인덱스 범위 스캔에 의한 테이블 액세스를 유도

  : 뷰를 액세스하는 쿼리의 경우 뷰 쿼리 내에서 있는 테이블에 대한 스캔을 지정할 수 있음

 

- NO_INDEX

  : 지정 인덱스는 제외하고 다른 액세스 방법을 고려하도록 유도

  : INDEX, INDEX_ASC, INDEX_COMBINE, INDEX_FFS 등에 사용하면 옵티마이저는 두 힌트를 모두 무시

  : 튜닝 테스트 시 기존 힌트를 지우지 않고 잠시 무시하거나 뷰쿼리에 삽입한 힌트를 무시하고자 할 때 적용 가능

 

- INDEX_ASC (INDEX_DESC)

  : 인덱스 경유하여 테이블 액세스를 할 때 힌트에 지정한 인덱스를 컬럼값의 오름차순(내림차순)으로 범위스캔 하도록 유도

 

- INDEX_COMBINE

  : 2개 이상의 인덱스를 비트맵 인덱스로 변경/결합하여 테이블을 액세스하는 방식으로 유도

  : 일반, 비트맵 인덱스 모두 가능하며 서로 다른 타입의 인덱스에서도 변경/결합 가능

  : 테이블 이름만 부여하고 인덱스 이름을 명시하지 않으면 옵티마이저가 가장 적합한 인덱스를 선택하여 처리

 

- INDEX_FFS

  : 전체 테이블 스캔 방식과 유사하게 인덱스 전체범위를 스캔하는 방식으로 유도

  : 인덱스를 스캔하나 다중블록을 스캔

 

- NO_INDEX_FFS

 

- INDEX_JOIN

  : 2개 이상의 인덱스들만으로 조인을 수행하도록 유도

  : 인덱스를 구성하는 컬럼이 해당 질의에서 필요로 하는 모든 컬럼을 반드시 포함해야함

  : 테이블 액세스 전혀 하지 않고 인덱스들만으로 쿼리를 처리할 수 있어야함

  : 2개 이상의 인덱스를 범위 스캔한 결과를 ROWID로 해쉬 조인을 수행하여 결과를 얻음

 

- INDEX_SS

  : 인덱스 스킵 스캔 방식으로 인덱스를 액세스하도록 유도

 

- NO_INDEX_SS

 

- INDEX_SS_ASC (INDEX_SS_DESC)

  : 인덱스 스킵 스캔 방식으로 범위스캔하는 경우 오름차순(내림차순)으로 인덱스를 읽도록 유도

 

 

3.3.7 쿼리형태 변형(Query Transformation)을 위한 힌트

- USE_CONCAT

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

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

 

- NO_EXPAND

  : USE_CONCAT의 반대 개념으로 조건절의 연산자 OR, IN 조건을 연결 실행계획으로 처리되지 않게 유도

 

- REWRITE

  : 원래 테이블 액세스 방법과 실체뷰를 액세스하는 방법 중 유리한 것을 선택하도록 쿼리를 변형하는 것

  : 실체뷰(Materialized View) 대량의 데이터를 대상으로 하는 조인이나 그룹함수로 가공 집합을 얻고자 할 때 수행속도 향승을 위해 미리 생성된 뷰

  : 힌트 내 특정 실체뷰가 지정되어 있으면 비용에 상관없이 해당 실체뷰를 사용하며 지정하지 않는다면 적용가능한 실체뷰를 비용 상관없이 사용함

 

- NOREWRITE

  : QUERY_REWRITE_ENABLED 파라메터가 TRUE로 정의되어 있더라도 이를 무시하고 쿼리 블록에 대해 재생성을 하지 않도록 유도

 

- MERGE

  : 뷰병합 => 뷰/인라인뷰의 액세스를 최적화하기 위해서는 뷰쿼리에 사용된 원래의 테이블을 최적으로 액세스 하도록 문장을 변형시킴

 

- NO_MERGE

  : 뷰쿼리 병합이 일어나지 않도록 유도

  : 주어진 조건이나 처리형태에 따라 뷰 병합을 하지 않고 먼저 뷰를 수행한 결과를 이용해 다음 처리를 하는 것이 유리한 경우

 

- STAR_TRANSFORMATION

  : 스타 변형 조인(Star transformation join)을 수행하도록 유도

  : 소량의 데이터를 가진 여러개의 디멘전 테이블과 팩트 테이블의 개별 비트맵 인덱스를 이용하여 처리범위를 줄이는 조인방식

 

- NO_STAR_TRANSFORMATION

 

- FACT

  : 스타변형조인에서 팩트 테이블을 지정하기 위해 사용

  : 팩트 테이블 선정에 오류로 원치 않는 실행계뢱이 나타났을 때 사용

 

- NO_FACT

  : STAR TRANSFORMATION 상황에서 옵티마이저가 지정한 테이블을 FACT 테이블로 고려하지 않도록 유도

 

- UNNEST

  : 서브쿼리와 메인쿼리를 합쳐 조인 형태로 변형하도록 하는 실행계획을 생성하도록 유도

 

 

3.3.8 기타 힌트

- APPEND

  : INSERT 문에서 사용하는 힌트 DIRECT-PATH 방식으로 입력 작업을 수행하여 SGA를 거치지 않고 직접 저장공간으로 입력하므로 매우 빠른 처리

  : 반드시 최고 수위점(High water mark) 다음 위치에 데이터를 저장

 

- NOAPPEND

  : CONVENTIONAL-PATH 방식으로 수행하도록 유도. 기본적으로 직렬모드(Serial mode)로 수행되며 DIRECT-PATH 방식은 병렬 모드로 수행

 

- CACHE

  : 전체 테이블 스캔 방식으로 읽혀진 블록을 DB의 버퍼 캐쉬를 관리하는 LRU 리스트의 최근 사용위치에 두어 계속 메모리 내에 머물게 하도록 유도

  : 크기가 작은 테이블에 유용하며 이 힌트를 사용하면 옵티마이저는 테이블에 이미 정의된 기본 캐쉬 정의를 무효화함

 

- NOCACHE

  : LRU 리스트의 끝에 전체테이블스캔으로 읽혀진 블록을 두어 메모리 내에서 우선적으로 제거가 되도록 함

  : 데이터베이스 버퍼 캐쉬에서 옵티마이저가 블록을 관리하는 일반적인 방법

 

- CARDINALITY

  : 옵티마이저에게 해당 쿼리 전체나 일부 구성에 대한 카디널리티 예상 값을 제시하여 실행계획 수립에 참조하도록 하는 힌트

  : 힌트에 테이블을 지정하지 않으면 이때의 카디널리티는 전체 쿼리를 수행한 결과로 얻어진 총 건수로 함

 

- CURSOR_SHARING_EXACT

  : CURSOR_SHARING 초기화 파라메터가 EXACT로 지정되어 있다면 리터럴 값을 바인드 변수로 변경하지 않고 있는 그대로 파싱

  : FORCE / SIMILAR를 지정하면 조건절에 상수값을 지정했더라도 옵티마이저는 변수로 인정하여 실행계획 수립 -> 공유비율 증가

  : 경우에 따라 ‘ALTER SESSION ...’ 명령을 이용하여 파라메터를 조정

 : SQL에서 지정한 상수값에 따라 미묘하게 달라지는 실행계획을 얻기 위해 CURSOR_SHARING 파라메터를 EXACT로 지정하여 사용

 

- DRIVING_SITE

  : 원격(REMOTE) 테이블과의 조인(분산쿼리)을 할 때 쿼리가 수행될 사이트를 지정하여 분산쿼리를 최적화 하는데 적용하는 힌트

 

- DYNAMIC_SAMPLING(동적 표본화)

  : 동적 표본화(Dynamic sampling) => 통계정보를 생성해두지 않았더라도 언제나 비용기준으로 작동할 수 있게 하기 위함

  : Dynamic sampling 기법을 이용하여 CBO 모드로 실행계획이 수립되도록 유도 (샘플링 레벨 범위 : 0~10)

 

- PUSH_PRED

  : 뷰/인라인뷰 외부의 조인 조건을 뷰 쿼리 내로 삽입

 

- PUSH_SUBQ

  : MERGE되지 않은 서브쿼리를 최대한 먼저 수행할 수 있다록 실행계획을 수립하도록 요구

  : MERGE된 서브쿼리는 먼저 수행되어 메인쿼리의 처리 범위를 줄이는 ‘제공자’ 역할을 하지만 머지가 불가능하면 ‘확인자’ 역할을 위해 마지막에 수행

  : 서브쿼리가 원격 테이블이거나 SORT MERGE 조인의 일부로 수행될 때는 아무런 역할도 하지 못함

 

- NO_PUSH_SUBQ

  : MERGE되지 않은 서브쿼리를 가장 나중에 수행되도록 함

  : 다른 조건을 이용, 처리범위를 최대한 줄인 후 수행되도록 함. ‘확인자’의 역할

 

- QB_NAME

  : 쿼리 블록에 이름을 부여하여 해당 쿼리 블록 외부의 다른 힌트에서 지정한 쿼리 블록을 참조할 수 있도록 유도

 

- REWRITE_ON_ERROR

  : 적합 실체뷰가 존재하지 않아 옵티마이저가 쿼리 재생성을 실행할 수 없는 경우 ORA-30393 에러를 유발하여 쿼리 수행을 중단시키도록 하는 힌트