9장. Optimizer 힌트 사용
학습 목표
이번 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.
① 힌트의 사용
② 다음과 같은 작업을 위해 힌트 사용
- Optimizer 모드
- 쿼리 변환
- 액세스 경로
- 조인 순서
- 조인 방법
Optimizer 힌트 : 개요
그림 9.1
힌트는 Optimizer의 판단에 영향을 미친다. 힌트는 Optimizer가 주어진 조건하에서 특정 쿼리 실행 계획을 사용하도록
지시하는 메커니즘을 제공한다.
예를 들어, 특정 쿼리의 경우, 특정 인덱스의 선택도가 높다는 사실을 알고 있을 수 있다. 이러한 정보를
Optimizer가 추천하는 실행 계획보다 더 효율적인 실행 계획을 사용하도록 지시 할 수도 있다.
그러한 경우, 힌트를 사용하여 Optimizer가 최적의 실행 계획을 사용하도록 지시한다. 그림의 예제에서는
데이터를 읽어 오기 위해 EMPFIRSTNAME_IDX 인덱스를 사용하도록 Optimizer에게 지시하는 경우이다.
보는 바와 같이 SQL 문장에 주석을 사용하여 Optimizer에게 지시를 내릴 수 있다.
(+) 기호를 사용하면 Optimizer는 주석을 힌트의 목록으로 사용한다.
(+) 기호는 반드시 주석 다음에 바로 기술하며, 공백을 사용하면 안된다.
힌트는 가끔 사용하여야 하며, 관련 테이블에 통계를 수집하고 힌트 없이 EXPLAIN PLAN 명령으로
Optimizer의 실행 계획을 평가한 다음, 사용하여야 한다. 데이터베이스 조건 및 데이터베이스 버전에
따른 향상 된 쿼리 성능은 힌트가 성능에 미치는 영향에도 변화를 준다. 또한, 힌트를 사용하면
관리, 확인, 제어해야 할 코드가 추가된다.
힌트의 종류
단일 테이블 : 단일 테이블 힌트는 하나의 테이블 또는 뷰에 지정된다. 예로 INDEX와 USE_NL이 있다.
다중 테이블 : 단일 테이블과 유사하지만, 힌트에 하나 이상의 테이블 또는 뷰를 지정 할 수 있다.
예로 LEADING이 있다.
쿼리 블록 : 쿼리 블록 힌트는 단일 쿼리 블록에 대해서만 동작한다 예로 STAR_TRANSFORMATION과 UNNEST가 있다.
문장 : 문장 힌트는 전체 SQL 문장에 적용된다. 예로 ALL_ROWS가 있다.
참고 : USE_NL(table1 table2)은 USE_NL(table 1)과 USE_NL(table2)라고 사용 할 수 있기 때문에 다중 테이블 힌트로 분류되지 않는다.
힌트 지정
그림 9.2
힌트는 지정된 해당 블록의 최적화에만 사용된다. 문장 블록이란 다음과 같다.
① 단일 MERGE, SELECT, INSERT, UPDATE, DELETE 문장
② 복잡한 문장의 부모 문장 또는 서브쿼리
③ 집합 연산자(UNION, MINUS, INTERSECT)를 사용한 복합 쿼리의 일부분
예를 들어, 두 개의 쿼리가 UNION 연산자로 결합된 복합 쿼리는 각 구성 요소들이 각각 하나의 쿼리이다.
이런 이유로 첫 번째 구성 요소 쿼리에 힌트를 적용하면 두 번째 구성 요소 쿼리의 최 적화에는 적용되지 않는다.
최적화 힌트 구문
힌트는 SQL 문장의 주석에 포함되며, 주석은 두 가지 형태로 사용 할 수 있다. 힌트 식별자 (+)는 주석 식별자
바로 뒤에 기술해야 하며, 만약, 공백을 포함하면 힌트의 주석이 인식되지 않는다.
힌트 사용 규칙
① SQL 문장 블록의 첫 번째 키워드(MERGE, SELECT, INSERT, DELETE, UPDATE) 바로 뒤에 힌트를 기술하여야 한다.
② 문장 블록은 힌트를 포함하는 오직 하나의 주석만 포함 할 수 있다. 그러나, 공백으로 분리하여 여러 개의 힌트를 주석 내부에 기술 할 수 있다.
③ 힌트는 해당 문장 블록에만 적용되며, 인스턴스 또는 세션 수준의 파라메터를 무시한다.
④ 만약, SQL 문장이 별칭을 사용한다면, 힌트도 테이블 이름 대신 반드시 별칭을 참조하여 야 한다.
오라클 서버는 잘 못 사용된 힌트는 무시하며, 다음과 같은 기본 사항을 기억해야 한다.
① 잘못 사용된 힌트에는 어떠한 오류 메시지도 리턴하지 않는다.
② 동일 주석의 올바른 다른 힌트는 인식된다.
③ 오라클 서버는 혼란스러운 힌트의 조합은 무시한다.
힌트 사용에 대한 권고 사항
① SQL 문장을 튜닝하는 경우, 힌트는 마지막 조치 방법으로 사용해야 한다.
② 힌트는 Optimizer가 최상의 실행 계획을 선택하지 못하도록 할 수도 있다.
③ 데이터베이스 구조 또는 내용이 변경되면 힌트는 덜 유효하거나 심지어는 무효가 될 수 있다.
Optimizer 힌트 구문 : 예제
그림 9.3
위 그림은 CBO가 인덱스를 사용하도록 지시하는 힌트를 사용한 예제이다. 실행 계획은 다음과 같다.
Execution Plan
-------------------------------------------------------------------
0 UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3 …)
1 0 UPDATE OF 'PRODUCTS'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (TABLE) (Cost…)
3 2 INDEX (RANGE SCAN) OF 'PRODUCTS_PROD_CAT_IX' (INDEX)(cost…)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (TABLE) (Cost…)
5 4 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (INDEX (UNIQUE))(Cost=0 …)
예제의 힌트는 PRODUCTS 테이블의 PROD_CATEGORY 컬럼에 정의된 PRODUCTS_PROD_CAT_IX
라는 이름을 가진 인덱스를 사용하도록 지시한다.
힌트의 종류
힌트는 다음과 같이 용도에 따라 분류되며, 힌트의 인자로서 테이블 이름과 인덱스 이름을 지정 한다.
① 최적화 목표 및 접근
② 액세스 경로
③ 쿼리 변환
④ 조인 순서
⑤ 조인 작업
⑥ 병렬 실행
⑦ 추가 힌트
최적화 목표 및 접근
ALL_ROWS : 이 힌트는 비용을 기반으로 가장 우수한 처리량(즉, 전체 자원을 최소로 소비)을 낼 수 있도록 문장 블록을 최적화한다.
역자 주 : 문장에서 리턴하는 모든 결과를 가장 빨리 리턴 할 수 있도록 최적화하는 것을 의미함.
FIRST_ROWS(n) : 이 힌트(n은 양의 정수)는 가장 빠른 응답 속도를 낼 수 있도록 개별 SQL 문장을
최적화한다. 최초 n개의 행을 가장 효율적으로 리턴하는 실행 계획을 선택하며, 역호환성 및 실행 계획
안정성을 위해 아직까지 남아 있다. Optimizer는 정렬 또는 그룹핑과 같은 블록킹(역 자 주 : 하나의
행을 읽어서 바로 리턴하지 않고, 전체 행을 읽은 다음에야 첫 번재 행을 출력) 작업이 포함된 SELECT
문장에서는 힌트를 무시한다. 그러한 문장은 첫 번째 행을 리턴하기 전에 해당 문장에 의해 액세스된
전체 행들이 먼저 리턴되어야 하기 때문에 가장 빠른 응답 시간을 위해 최적화 될 수 없다. 만약, 이 힌트를
이러한 문장에 지정하면, 데이터베이스는 가장 우수한 처리량을 낼 수 있도록 문장을 최적화한다.
SQL 문장에 ALL_ROWS 또는 FIRST_ROWS(n) 힌트를 사 용하였고, 데이터 딕셔너리에 해당 문장에 의해
액세스 되는 테이블에 통계가 없다면, Optimizer 는 디폴트 통계값을 가지고 누락된 통계를 예측한
다음, 실행 계획을 선택하게 된다.
만약, ALL_ROWS 또는 FIRST_ROWS(n)과 함께 액세스 경로 또는 조인 작업과 관련된 힌트를 지정 하면,
Optimizer는 힌트에 지정된 액세스 경로 및 조인 작업을 우선하게 된다.
참고 : FIRST_ROWS 힌트는 가장 유용한 힌트가 될 수 있다.
액세스 경로에 대한 힌트
이 힌트 중에서 하나를 지정하면 Optimizer는 지정된 액세스 경로를 선택하게 된다. 단, 인덱스가 존재하고
SQL 문장의 구조를 기반으로 해당 액세스 경로를 사용 할 수 있어야 한다. 만약, 힌트에 사용 불가능한 액세스
경로를 지정한다면, Optimizer는 이러한 힌트를 무시한다. 사용자는 문장에 나타나며 액세스 해야 할 테이블의
이름을 정확히 지정하여야 한다. 만약, 문장이 테이블 에 대하여 별칭을 사용한다면, 힌트에 테이블 이름 대신
해당 별칭을 사용하여야 한다. 문장의 테이블 이름에 스키마 이름이 포함되어 있는 경우에는 힌트에 스키마 이름을 포함하면 안된다.
FULL : 이 힌트는 지정된 테이블에 대하여 전체 테이블 스캔을 선택하도록 지시한다. 예를 들면, 다음과 같다.
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e WHERE last_name LIKE 'K%';
오라클 서버는 WHERE 절의 조건에 포함된 last_name 컬럼에 사용 가능한 인덱스가 존재하더라도 employees 테이블에 대하여 전체 테이블 스캔을 수행한다.
CLUSTER : 이 힌트는 지정된 테이블을 액세스 할 때 클러스터 스캔을 사용하도록 지시한다. 이 힌트는 클러스터 테이블에 대해서만 적용된다.
HASH : 이 힌트는 지정된 테이블을 액세스 할 때 해시 스캔을 사용하도록 지시한다. 힌트는 테이블 클러스터에 저장된 테이블에만 적용된다.
ROWID : 이 힌트는 지정된 테이블에 대하여 ROWID에 의한 테이블 스캔을 지시한다.
INDEX : 이 힌트는 지정된 테이블에 대하여 인덱스 스캔을 수행하도록 지시한다. 도메인 인덱스,
B*-트리 인덱스, 비트맵 인덱스, 비트맵 조인 인덱스에 사용 할 수 있다. 그러나, 비트맵 인덱스에
대하여 INDEX를 사용하는 대신에 INDEX_COMBINE을 사용해야 하는 경우라면, INDEX 힌트가
훨씬 기능이 많으므로 인덱스를 사용하는 편이 낫다. 이 힌트는 하나 이상의 인덱스를 지정 할 수 있다.
만약, 이 힌트에 사용 가능한 하나의 인덱스를 지정하였다면, Optimizer는 해당 인덱스를 사용하여 스캔을
수행한다. Optimizer는 해당 테이블의 전체 스캔이나 다른 인덱스를 이용한 스캔은 고려하지 않는다.
이 힌트가 사용 가능한 인덱스들의 목록을 지정하면, Optimizer는 목록 내의 각 인덱스에 대한 스캔 비용을
고려하여 가장 비용이 낮은 인덱스 스캔을 수행한다. 또한, Optimizer는 목록 내의 여러 인덱스를 머지하는
비용이 낮다면 이러한 방식을 선택 할 수도 있다. Optimizer는 전체 테 이블 스캔을 고려하지 않는다.
INDEX_ASC : 이 힌트는 지정된 테이블에 대하여 명시적으로 인덱스를 지정한다. 만약, 해당 문장 이 인덱스
범위 스캔을 사용한다면, 오라클 서버는 인덱스 값을 오름차 순으로 검색한다. 범위 스캔의 디폴트 동작 특성은
인덱스 값을 오름차 순으로 읽는 것이므로, INDEX 힌트에 아무 것도 지정 할 필요가 없다. 그러나, 명시적으로
오름차 순 범위 스캔을 지정하기 위해 INDEX_ASC 힌트를 사용 할 수도 있다.
INDEX_DESC : 이 힌트는 지정된 테이블에 대하여 내림차 순 인덱스 스캔을 수행하도록 옵티마이저에게 지시한다.
만약, 해당 문장이 인덱스 범위 스캔을 사용하고, 해당 인덱스가 오름차 순이라 면 시스템은 인덱스 값을
내림차순으로 검색한다. 파티션 인덱스에서는 각 파티션 별로 내림차 순으로 출력된다. 내림차순 인덱스의
경우, 이 힌트는 내림차 순이 아닌 오름차 순으로 인덱스 값을 검색한다.
INDEX_COMBINE : 이 힌트는 테이블에 대하여 비트맵 액세스 경로를 명시적으로 선택한다. 만약, INDEX_COMBINE
힌트에 어떠한 인덱스도 기술하지 않으면, Optimizer는 비용이 가장 낮은 비트맵 인덱스들의 불린(boolean) 조합 중에
가장 비용이 낮은 조합을 사용한다. 만약, 특정 인덱스가 인자로 제공되면 해당 인덱스들의 불린 조합을 사용하고자 시도한다.
예를 들면 다음과 같다.
SELECT /*+INDEX_COMBINE(customers cust_gender_bix cust_yob_bix)*/ * FROM customers WHERE cust_year_of_birth < 70 AND cust_gender = 'M';
참고 : INDEX, INDEX_FFS, INDEX_SS에 대하여 반대 동작을 하는 힌트인 NO_INDEX, NO_INDEX_FFS, NO_INDEX_SS가 있다.
INDEX_JOIN : 이 힌트는 액세스 경로로서 인덱스 조인을 사용하도록 지시한다. 이 힌트는 긍정적인 효과를 가지고 있으며,
적은 수의 인덱스들이 쿼리를 수행하는데 필요로 하는 모든 컬럼을 포 함하고 있어야 한다.
예를 들어, 다음 쿼리는 employee_id 컬럼과 department_id 컬럼에 액세스하기 위해 인덱스 조인을 사용한다.
employees 테이블의 각 컬럼에 인덱스가 존재하고 있다.
SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/
employee_id, department_id
FROM hr.employees WHERE department_id > 50;
INDEX_DESC : 이 힌트는 지정된 테이블에 대하여 인덱스 스캔을 수행하도록 지시한다. 만약, 인덱스 범위 스캔을
사용한다면, 오라클 서버는 인덱스 값을 내림차 순으로 검색한다. 파티션 인덱스의 경우, 각 파티션 내에서 내림차순으로 결과를 읽어 온다.
예를 들면, 다음과 같다.
SELECT /*+ INDEX_DESC(a ord_order_date_ix) */ a.order_date, a.promotion_id, a.order_id
FROM oe.orders a WHERE a.order_date < '01-jan-1985';
INDEX_FFS : 이 힌트는 전체 테이블 스캔 대신에 고속 전체 인덱스 스캔을 발생시킨다.
예를 들면, 다음과 같다.
SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*) FROM order_items l, orders o
WHERE l.order_id > 50 AND l.order_id = o.order_id;
INDEX_SS : 이 힌트는 지정한 인덱스를 이용하여 인덱스 스킵 스캔을 수행하도록 지시한다.
만약, 해당 문장이 인덱스 범위 스캔을 사용한다면, 시스템은 인덱스 값을 오름차 순으로 스캔한다.
파티션 인덱스의 경우, 각 파티션 내에서 오름차 순으로 인덱스 값을 읽어 온다. INDEX_SS_ASC와 INDEX_SS_DESC 힌트가 있다.
NO_INDEX : 이 힌트는 지정된 테이블에 대하여 인덱스를 사용하지 못하도록 지시한다.
① 만약, 단일 인덱스에 이 힌트를 지정하면, Optimizer는 이 인덱스의 스캔을 고려하지 않지만,
지정되지 않은 다른 인덱스는 여전히 고려한다.
② 만약 사용 가능한 인덱스들의 목록을 이 힌트에 지정하면, Optimizer는 이 지정된 인덱 스들의
스캔을 고려하지 않는다. 목록에 지정되지 않은 인덱스들은 여전히 고려한다.
③ 만약, 어떠한 인덱스도 이 힌트에 지정하지 않으면, Optimizer는 해당 테이블의 모든 인덱스 스캔을
고려하지 않는다. 이것은 NO_INDEX에 테이블에서 사용 가능한 모든 인덱스 들을 지정한 것과 동일하다.
NO_INDEX 힌트는 함수 기반 인덱스, B*-트리 인덱스, 비트맵 인덱스, 도메인 인덱스에 사용 가능 하다.
만약, NO_INDEX 힌트와 인덱스 힌트(INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, INDEX_FFS)를
동일한 인덱스에 같이 지정한 경우, 해당 인덱스에 지정된 두 힌트는 모두 무시되어 Optimizer는 해당 인덱스의 사용을 고려하게 된다.
예를 들면 다음과 같다.
SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees WHERE employee_id > 200;
AND_EQUAL : 이 힌트는 여러 개의 단일 컬럼 인덱스를 머지하는 액세스 경로를 실행 계획으로
사용하도록 지시한다. 여기서는 다음과 같은 항목을 지정 할 수 있다.
① 머지 할 인덱스와 관련된 테이블의 이름 또는 별칭
② 인덱스 스캔이 수행 될 인덱스들. 최소한 2개 이상의 인덱스를 지정하여야 하며 5개를 초과 할 수 없다.
INDEX_COMBINE 힌트 : 예제
그림 9.4
INDEX_COMBINE 힌트는 비트맵 인덱스 작업을 위해 고안되었다. 다음 사항을 기억해야 한다.
① 힌트의 인자로 특정 인덱스가 주어지면, Optimizer는 해당 비트맵 인덱스들의 몇몇 조합을 사용하기 위해 노력하게 된다.
② 만약 힌트에 인덱스가 지정되지 않으면, 모든 인덱스가 힌트에 포함된 것으로 가정한다.
③ Optimizer는 항상 힌트에 지정된 인덱스를 사용하려고 노력하며, 해당 인덱스의 비용이 효율적인지는 고려 대상이 아니다.
위 그림의 예제에서는 WHERE 절에서 참조되는 3개의 컬럼(CUST_MARITAL_STATUS, CUST_GENDER, CUST_YEAR_OF_BIRTH)에
비트맵 인덱스가 정의되었다고 가정한다. AUTOTRACE를 활성화하면 문장의 실행 계획은 다음과 같이 표시된다.
그림 9.5
위 예제에서는 다음과 같은 작업이 사용된다.
|
비트맵 행 원본 |
설명 |
|
BITMAP CONVERSION TO ROWIDS |
비트맵을 테이블을 액세스하기 위한 ROWID로 변환한다. COUNT : 실제 값이 필요하지 않은 엔트리의 개수를 리턴 한다. |
|
BITMAP OR |
두 비트맵에 대하여 비트 별 OR을 계산한다. |
|
BITMAP AND |
두 비트맵에 대하여 비트 별 AND를 계산한다. |
|
BITMAP INDEX |
SINGLE VALUE : 단일 키에 대한 비트맵를 검색한다. RANGE SCAN : 값 범위에 대하여 비트맵을 리턴한다. |
|
BITMAP MERGE |
범위 스캔에 의해 검색된 몇 개의 비트맵을 하나로 머지 한다(비트별 AND 연산자 사용). |
쿼리 변환 힌트
NO_QUERY_TRANSFORMATION : 이 힌트는 모든 쿼리 변환 기능을 Optimizer가 생략하도록 지시한다.
여기에는 OR-확장, 뷰 머지, Subquery unnesting, 스타 변환, 구체화 된 뷰 재 작성이 포함 된다.
USE_CONCAT : 이 힌트는 WHERE 절에 OR 조건들이 결합되어 있는 경우, UNION ALL 연산자를 사용하는
복합 쿼리로 변환하도록 지시한다. 일반적으로 이 변환은 이러한 변환 후 비용이 변환 전 비용보다 낮을 때만
수행된다. 이 힌트는 IN-목록 처리를 비활성화 시킨다.
NO_EXPAND : 이 힌트는 WHERE 절의 OR 조건 또는 IN 목록을 가진 쿼리에 대하여 OR 확장을 수행하지
않도록 비용 기반 Optimizer에게 지시한다. 이 힌트는 변환 후의 비용이 변환 전의 비 용보다 낮지 않은
경우에만 사용하여야 한다.
REWRITE : 이 힌트는 비용을 고려하지 않고, 가능하면 구체화 뷰의 관점으로 쿼리를 재 작성하도록 지시한다.
뷰 목록의 존재 여부와 상관 없이 REWRITE 힌트를 사용 할 수 있다.
UNNEST : UNNEST 힌트는 서브쿼리를 해당 쿼리를 포함하는 쿼리에 병합하도록 지시하며, 액세스 경로와 조인을 함께 평가한다.
MERGE : 이 힌트는 각 쿼리에 대한 뷰를 머지하도록 해준다. 만약, 뷰의 쿼리가 GROUP BY 절 또는 SELECT 뒤에
DISTINCT 연산자를 포함한다면 뷰의 쿼리를 뷰를 액세스하는 문장으로 머지 할 수 있다. 단, 복합 뷰 머지가 활성화되어
있어야 한다. 이 기능은 기본적으로 활성화 되어 있으므로 NO_MERGE 힌트를 사용하여 이 메커니즘을 비활성화
시킬 수 잇다. 복합 뷰 머지는 서브쿼리가 상관 관계가 아닌 IN 서브 쿼리를 병합하는데 사용 할 수도 있다.
이 힌트를 인자 없이 사용하면, 해당 뷰의 쿼리에 기술하여야 한다. 이 힌트에 인자를 지정하는 경우에는 쿼리를 둘러싼 위치에 지정하면 된다.
NO_MERGE : 이 힌트는 머지 할 수 있는 뷰를 머지하지 못하도록 한다. 이 힌트는 해당 뷰를 액세스 하는 방법에 많은 영향을 미친다.
NO_MERGE 힌트가 인자 없이 사용되면, 해당 쿼리에 기술 하여야 하며, 인자 없이 사용하는 경우에는 해당 쿼리를 둘러싼 위치에 지정하면 된다.
STAR_TRANSFORMATION : 이 힌트는 쿼리 변환이 사용된 실행 계획 중에 가장 우수한 실행 계 획을 사용하도록 지시한다.
힌트를 사용하지 않으면, 변환 된 쿼리의 가장 우수한 실행 계획 대신, 변환 없이 작성한 가장 우수한 실행 계획을 Optimizer가 선택하게 된다.
힌트가 지정되었더라도, 해당 변환이 반드시 발생하리라는 보장은 없다. Optimizer는 해당 변환 이 적절한 경우에만 서브 쿼리를
작성한다. 만약, 어떠한 서브쿼리도 작성되지 않고, 쿼리도 변환 되지 않으면, 힌트와 상관 없이 변환되지 않은 쿼리에 대하여 가장
우수한 실행 계획을 사용하게 된다.
FACT : FACT 힌트는 지정된 테이블을 스타 변환의 팩트 테이블로 사용할 때 사용한다.
NO_FACT : NO_FACT 힌트는 지정된 테이블을 스타 변환의 팩트 테이블로 사용하지 않을 때 지정 한다.
조인 순서에 대한 힌트
다음 힌트는 조인 순서를 제안하기 위해 사용되는 힌트들이다.
ORDERED : 이 힌트는 FROM 절에 나열된 순서대로 테이블을 조인한다. 만약, SQL 문장을 수행 할 때,
ORDERED 힌트를 생략하면 Optimizer는 조인 할 테이블의 순서를 선택한다. 만약, 사용자가 Optimizer가
모르는 정보. 즉, 각 테이블에서 리턴 할 행들의 개수를 알고 있다면 조인 순서를 지정하기 위해 ORDERED
힌트를 사용 할 수도 있다. 중첩 루프 예제에서 최상의 방법은 FROM 절의 테이블들이 인덱스를 이용하여
조인되어 마지막에는 대용량의 테이블과 연결되는 것 이다. 즉, 다음과 같이 힌트를 사용 할 수 있다.
/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */
여기서, facts는 테이블이며 fact_concat는 인덱스이다. 좀 더 일반적인 힌트는 STAR 힌트를 사용한 것이다.
LEADING : 이 힌트는 지정된 테이블들의 순서대로 조인한다. 만약, 지정된 테이블이 조인 그래프의 의존성으로
인하여 나열된 순서대로 조인 될 수 없다면 LEADING 힌트는 무시된다. 만약, 서로 다른 테이블들에 한해서
이 힌트를 두 개 이상 지정하면, 모든 힌트는 무시된다. 만약, ORDERED 힌트를 지정하였다면, 모든 LEADING 힌트는 무시된다.
조인 연산에 대한 힌트
다음 힌트는 테이블에 대한 조인 연산을 지시한다. 문장에서 표시되는 방식과 동일하게 테이블 이름을 힌트에
지정하여야 한다. 만약, 해당 문장이 테이블에 대하여 별칭을 사용한다면, 힌트 내 에 테이블 이름이 아닌 별칭을
반드시 기술하여야 한다. 그러나, 힌트 내의 테이블 이름은 문장 내에서 스키마 이름이 존재하더라도 스키마
이름을 포함하면 안된다. USE_NL과 USE_MERGE 힌트를 ORDERED 힌트와 함께 사용하도록 한다. 참조된 테이블이
조인 연산의 내부 테이블로 사용 되도록 지시하는 경우에도 이 힌트를 사용한다. 만약, 참조 테이블이
외부 테이블(External Table) 이라면 힌트는 무시된다.
USE_NL : 이 힌트는 오라클 서버가 지정된 테이블을 내부 테이블로 사용하며, 각 지정된 테이블을 중첩 루프 조인으로
다른 행 원본을 조인한다. 만약, 쿼리에 의해서 전체 행들이 리턴되는데 소요되는 시간 보다, 첫 번째 행이 리턴되는
시간을 최소화하도록 문장을 최적화하려면 USE_NL 힌트를 사용하여
Optimizer가 중첩 루프 조인을 선택하도록 지시 할 수 있다.
USE_NL_WITH_INDEX : 이 힌트는 USE_NL 힌트와 유사하다. 그러나, 인덱스가 지정되지 않으면, Optimizer는 최소한
하나 이상의 조인 컬럼을 포함하고 있는 일부 인덱스를 사용 할 수 있다. 만약, 인덱스가 지정되면, Optimizer는 최소한
하나 이상의 조인 컬럼을 포함하고 있는 해당 인덱스를 사용 할 수 있어야 한다.
NO_USE_NL : 이 힌트를 사용하면 Optimizer는 중첩 루프 조인을 고려 대상에서 배제한다.
그러나, 중첩 루프 조인만 가능한 경우에는 이 힌트를 무시한다.
대부분의 경우에 중첩 루프 조인은 소트 머지 조인보다 첫 번째 행이 빨리 리턴된다. 중첩 루프 조인은
하나의 테이블에서 읽어온 첫 번째 행과 일치되는 다른 테이블의 첫 번째 행을 리턴한다.
그러나, 소트 머지 조인은 두 테이블의 모든 행들을 읽고 정렬한 다음, 각 정렬된 행 원본의 첫 번째 행들을
연결 할 때까지 첫 번째 행을 리턴 할 수 없다.
다음 문장은 힌트에 의해 중첩 루프 조인이 발생한다. orders 테이블을 전체 테이블 스캔으로 읽고
l.order_id = h.order_id 필터 조건이 각 행에 적용된다. 필터 조건을 만족하는 각 행들에 대하여
orders_items가 order_id에 의해 읽혀진다.
SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM oe.orders h ,oe.order_items l
WHERE l.order_id = h.order_id;
쿼리에 INDEX 힌트를 추가하면 orders 테이블에 대한 전체 테이블 스캔을 방지 할 수 있다.
여기서 사용된 실행 계획이 특별히 효율적이지는 않지만 대규모 시스템에서 사용되는 문장과 유사하다.
USE_MERGE : 이 힌트는 다음 예제와 같이 지정된 각 테이블을 소트 머지 조인으로 각 행 원본을 조인한다.
SELECT /*+USE_MERGE(employees departments)*/ * FROM employees, departments
WHERE employees.department_id = departments.department_id;
NO_USE_MERGE : 이 힌트는 내부 테이블로 지정된 테이블을 사용하는 소트 머지 조인을 옵티마이저가 고려 대상에서 배제하도록 한다.
USE_HASH : 이 힌트는 다음 예제와 같이 해시 조인을 사용하여 각 지정된 테이블을 다른 행 원본과 조인하도록 지시한다.
SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id, l2.product_id, SUM(l2.unit_price*quantity)
FROM oe.orders l, oe.order_items l2
WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;
다음은 다른 예제이다.
SELECT /*+use_hash(employees departments)*/ * FROM hr.employees, hr.departments
WHERE employees.department_id = departments.department_id;
NO_USE_HASH : 이 힌트는 내부 테이블로 지정된 테이블을 사용하여 각 지정된 테이블을
다른 행 원본과 조인하는 해시 조인을 Optimizer가 고려 대상에서 배제하도록 한다.
DRIVING_SITE : 이 힌트는 지정된 데이터베이스가 아닌 다른 사이트에서 해당 쿼리를 실행하도록
지시한다. 이 힌트는 분산 쿼리 최적화를 사용하는 경우, 조인이 수행되어야 할 사이트를 결정 하는데 유용하다.
추가 힌트
APPEND : 이 힌트는 데이터베이스가 순차 모드(serial mode)로 운영되는 경우, 직접 경로(direct- path) INSERT를
수행 할 수 있도록 해준다. Enterprise Edition을 사용하고 있지 않으면 데이터베이스는 순차 모드이다. 순차 모드에서는
고전적인 INSERT가 디폴트이며, 병렬(parallel) 모드에서는 직접 경로 INSERT가 디폴트이다. 직접 경로 INSERT에서
데이터는 테이블에 현재 할당 된 기존 공간을 사용하는 대신 테이블의 말단에 추가된다. 그 결과, 직접 경로 INSERT는
고전적인 INSERT 보다 상당히 빠를 수 있다.
NOAPPEND : 이 힌트는 INSERT 문장이 수행되는 동안, 병렬 모드를 비활성화하여 직접 경로 INSERT를 비활성화
한다(고전적인 INSERT는 순차 모드에서 디폴트이고, 직접 경로 INSERT는 병 렬 모드에서 디폴트이다).
ORDERED_PREDICATES : 이 힌트는 조건절의 평가 순서를 보존하도록 지시한다. 단, 인덱스 키로 사용된 조건절은
제외된다. SELECT 문장의 WHERE 구문에 이 힌트를 사용한다.
만약, 이 힌트를 사용하지 않으면, 오라클 서버는 다음과 같은 순서로 모든 조건들을 평가한다.
1. WHERE 절에 지정된 순서대로 사용자 정의 함수, 타입 메서드, 서브쿼리가 없는 조건이 가장 먼저 평가된다.
2. 비용이 높은 순으로 사용자 계산 비용을 갖는 사용자 정의 함수와 타입 메서드를 가진 조건이 그 다음에 평가된다.
3. WHERE 절에 지정된 순으로 사용자 계산 비용을 가지지 않는 사용자 정의 함수와 타입 메서드를 가진 조건이 그 다음에 평가된다.
4. WHERE 절에 지정되지 않은 조건(예를 들어, Optimizer에 의해 이행적으로 만들어진 조 건)이 그 다음에 평가된다.
5. WHERE 절에 지정한 순서대로 서브 쿼리를 가진 조건절이 가장 마지막에 평가된다.
CURSOR_SHARING_EXACT : 오라클 서버는 SQL 문장 내의 리터럴을 바인드 변수로 교체 할 수 있다.
단, 교체 후의 문장은 안전하여야 한다. 이 기능은 CURSOR_SHARING 초기화 파라메터로 제어 된다. CURSOR_SHARING_EXACT
힌트는 이러한 기능을 비활성화 시킨다. 즉, 오라클 서버는 어떠한 리터럴도 바인드 변수로 교체하고자 하는 시도를 하지 않는다.
CACHE : CACHE 힌트는 전체 테이블 스캔을 수행 할 때, 테이블에서 읽어온 블록들을 버퍼 캐시 의 주요 부분에 저장한다.
이 힌트는 소용량의 룩업 테이블에 유용하다.
CACHE와 NOCACHE 힌트는 V$SYSSTAT 데이터 딕셔너리 뷰에서 보여지는
table scans (long tables)와 table scans (short tables) 시스템 통계에 영향을 미친다.
PUSH_PRED : 이 힌트는 뷰 내부로 조인 조건을 진입시킨다.
PUSH_SUBQ : 이 힌트는 머지가 불가능한 뷰를 실행 계획의 가능한 가장 빠른 단계에서 평가한다. 일반적으로 머지되지
않는 서브쿼리 들은 실행 계획의 가장 마지막 단계에서 실행된다. 만약, 서브쿼리의 수행 비용이 저련하고, 행들의 개수를
획기적으로 감소 시킬 수 있다면 해당 서브쿼리를 먼저 실행하는 것이 성능을 향상시키게 된다. 이 힌트는 서브쿼리가
원격 테이블을 대상으로 수행되거나 머지 조인을 이용하여 조인이 된다면, 아무런 영향을 주지 못한다.
DYNAMIC_SAMPLING : 이 힌트는 더욱 정확한 선택도와 카디널리티를 예측하여 서버의 성능을 향상시킬 수 있도록
동적 샘플링을 제어한다. 이 힌트는 0에서 10까지 지정 할 수 있으며, 값이 높아질수록 동적 샘플링에 많은 노력을 투입하고
좀 더 넓게 적용된다. 테이블을 지정하지 않는 한, 샘플링은 커서 수준으로 설정된다.
다음 예제를 살펴보자.
SELECT /*+ dynamic_sampling(1) */ * FROM …
이 예제는 다음과 같은 조건을 만족하면 동적 샘플링을 활성화한다.
① 쿼리에 하나 이상의 테이블이 존재한다.
② 최소한 하나 이상의 테이블이 분석되어 있지 않고, 인덱스도 존재하지 않는다.
③ Optimizer는 분석 되지 않는 테이블에 대하여 상대적으로 비용이 비싼 테이블 스캔이 필요하다고 결정한다.
MONITOR : 이 힌트는 문장의 수행 시간이 길지 않더라도, 쿼리에 대하여 실시간 SQL 모니터링을 수행한다. 이 힌트는
CONTROL_MANAGEMENT_PACK_ACCESS 파라메터를 DIAGNOSTIC+TUNING으로 지정하였을 때만 유효하다.
NO_MONITOR : 이 힌트는 해당 쿼리에 대하여 실시간 SQL 모니터링을 비활성화한다.
RESULT_CACHE : 이 힌트는 현재 쿼리의 결과 또는 쿼리의 일부를 메모리에 캐시하여 해당 쿼리 또는 쿼리 일부를
다음에 재실행 할 때 캐시된 결과를 사용하도록 해준다.
NO_RESULT_CACHE : RESULT_CACHE_MODE 초기화 파라메터가 FORCE로 설정되어 있다면 쿼리 결과를
결과 캐시(result cache)에 저장한다. 이 경우에 NO_RESULT_CACHE 힌트는 현재 쿼리에 대 하여 캐시 기능을 비활성화한다.
OPT_PARAM : 이 힌트는 현재 쿼리가 실행되는 동안에만 초기화 파라메터를 설정 할 수 있도록 해준다.
이 힌트는 다음 파라메터에 대해서만 유효하다.
OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ,
OPTIMIZER_SECURE_VIEW_MERGING, STAR_TRANSFORMATION_ENABLED
힌트와 뷰
뷰는 단일 컨텍스트 내에서 정의되고 다른 뷰에서 사용될 수 있기 때문에 뷰 정의에는 힌트를 사용하지 말아야 한다.
그러한 힌트 사용은 기대하지 못한 실행 계획을 발생 할 수 있다. 뷰 내의 힌트는 해당 뷰가 최상위 수준의 쿼리와
머지 가능한지 여부에 따라 다르게 처리된다.
뷰 최적화
일반적으로 문장은 뷰의 기본 테이블을 액세스하는 동일한 문장으로 변환된다.
Optimizer는 다음과 같은 기법 중 하나를 사용하여 문장을 변환한다.
① 뷰의 쿼리를 뷰에 액세스하는 문장의 쿼리 블록에 머지한다.
② 뷰를 참조하는 쿼리 블록의 조건절을 뷰 내부로 진입시킨다.
이러한 변환이 불가능하다면, 뷰의 쿼리는 실행 된 후, 그 결과를 테이블처럼 액세스한다. 이 과 정은 실행 계획에서 VIEW 단계로 표시된다.
머지 가능한 뷰
뷰 정의에 다음 항목을 포함하지 않는다면, 뷰는 뷰를 참조하는 쿼리 블록과 머지 될 수 있다.
① 집합 연산자(UNION, UNION ALL, INTERSECT, MINUS)
② CONNECT BY 절
③ ROWNUM 가상 컬럼
④ SELECT 절의 그룹 함수(AVG, COUNT, MAX, MIN, SUM)
힌트와 머지 가능한 뷰
Optimizer 접근 및 모드 힌트는 최상위 수준 쿼리 또는 뷰 내에 나타날 수 있다.
① 만약, 최상위 수준의 쿼리에 힌트가 존재한다면, 힌트는 뷰 내부의 힌트와 상관 없이 사용된다.
② 최상위 수준의 쿼리에 Optimizer 모드 힌트가 존재하지 않으면, 뷰 내의 모든 모드 힌트가 일치되는 경우,
참조되는 뷰 내의 모드 힌트가 사용된다.
③ 만약, 참조되는 뷰 내에 두 개 이상의 모드 힌트가 혼재한다면, 뷰 내의 모든 모드 힌트 는 취소되고 디폴트
또는 사용자가 정의한 세션 모드 힌트가 사용된다.
뷰가 단일 테이블(또는 단일 테이블로 정의된 다른 뷰를 참조)을 포함하지 않는 한, 참조되는 뷰 에 대한 액세스
방식과 조인 힌트는 무시된다. 그러한 단일 테이블 뷰의 경우, 해당 뷰에 대한 액 세스 방식 힌트 또는 조인 힌트는
뷰 내의 테이블에 적용된다.
액세스 방식과 조인 힌트는 뷰 정의 내에 나타날 수 있다.
① 만약, 뷰가 서브쿼리(즉, SELECT 문장의 FROM 절에 나타나는 경우)라면, 해당 뷰 내의 액세스 방식과
조인 힌트는 뷰가 최상위 수준의 쿼리와 머지되는 경우에 그대로 보존된 다.
② 서브 쿼리가 아닌 뷰의 경우, 해당 뷰 내의 액세스 방식과 조인 힌트는 최상위 수준의 쿼리가 다른 테이블
또는 뷰를 참조하지 않는 경우에만 보존된다(즉, SELECT 문장의 FROM 절에 오직 뷰만 포함).
힌트와 머지 불가능한 뷰
머지 불가능한 뷰의 경우, 뷰 내의 Optimizer 모드 힌트는 무시된다. 최상위 수준의 쿼리가 옵티마이저 모드를 결정한다.
머지 불가능한 뷰는 최상위 수준의 쿼리와 독립적으로 최적화되기 때문에 뷰 내의 액세스 방식과 조인 힌트는 언제나
보존된다. 같은 이유로 최상위 수준 쿼리 내의 뷰에 대한 액세스 방식 힌트는 무시된다. 그러나, 최상위 수준 내의 뷰에
대한 조인 힌트는 머지 불가능한 뷰가 테이블과 유사하기 때문에 보존된다.
전역 테이블 힌트
그림 9.6
테이블을 지정하는 힌트들은 일반적으로 해당 문장에 의해서 참조되는 뷰 내부의 테이블이 아닌 해당 힌트가 등장하는
DELETE, SELECT, UPDATE 쿼리 블록 내의 테이블을 참조한다. 뷰 내부에 등장하는 테이블에 대하여 힌트를 지정하고자
하는 경우에 뷰 내부에 힌트를 포함시키는 대신, 전역 힌트를 사용하도록 권장한다.
위 그림과 같이 뷰 이름에 테이블 이름을 포함시키는 확장 테이블 스펙 문법을 사용하면 테이블 힌트는 전역 힌트로 변환
될 수 있다. 또한, 쿼리 블록 이름을 지정하여 테이블 스펙보다 우선되도록 할 수 있다.
예를 들어, 전역 힌트 구조를 사용하여 뷰 내부에 인덱스 힌트를 지정하기 위해 뷰를 수정 할 필 요가 없다.
참고 : 만약, 전역 힌트가 동일한 쿼리 내에 두 번 이상 사용된 테이블 이름 또는 별칭을 참조한 다면, 힌트는 테이블 또는
별칭의 첫 번째 인스턴스에만 적용된다.
힌트에 쿼리 블록 지정
그림 9.7
힌트를 적용 할 쿼리 블록을 지정하기 위해 많은 힌트들에 쿼리 블록 이름을 지정 할 수 있다.
이 구문은 아웃터 쿼리에서 인라인 뷰에 적용할 힌트를 지정 할 수 있도록 해준다.
쿼리 블록 인자의 구문은 @queryblock 형태를 사용하고, 여기서 queryblock은 쿼리 내의 쿼리 블록에 지정된 식별자이다.
queryblock 식별자는 시스템에 의해 자동으로 설정되거나 사용자가 지 직접 지정 할 수 있다. 힌트가 적용될 쿼리 블록에
힌트를 지정하면, @queryblock 구문을 지정 할 필요가 없다.
위 예제와 같이 SELECT 문장은 인라인 뷰를 사용한다. 해당 쿼리 블록에는 QB_NAME 힌트를 통 해 strange라는 이름을 부여하였다.
위 예제에서는 Optimizer가 DEPT 테이블에 액세스 할 때 인덱스를 선택 할 수 있도록 DEPT 테이블의 DEPTNO 컬럼에
인덱스가 존재한다고 가정한다. 그러나, 메인 쿼리 블록 내의 FULL 힌트를 strange 쿼리 블록을 적용하였기 때문에
Optimizer는 해당 인덱스를 사용하지 않는다. 실행 계획에서 DEPT 테이블에 대하여 전체 테이블 스캔이 표시됨을
확인 할 수 있다. 또한, 실행 계획의 출력은 원본 쿼리의 각 쿼리 블록에 대하여 시스템이 생성한 이름도 명확히 표시해준다.
힌트의 전체 집합 지정
그림 9.8
힌트를 사용 할 때, 최적의 실행 계획을 보장하기 위해 힌트의 전체 집합을 지정해야 하는 경우도 있다.
예를 들어, 여러 테이블들을 조인하는 매우 복잡한 쿼리에 대하여 주어진 테이블에만 INDEX 힌트를 지정하면,
Optimizer는 사용 할 나머지 액세스 경로뿐만 해당 조인 방식을 결정 해야 할 필요가 있다. 그러므로,
INDEX 힌트를 지정하였더라도 Optimizer는 해당 힌트를 사용하지 않을 수도 있는데, 그 이유는 Optimizer가
선택한 조인 방식과 액세스 경로로 인하여 요청된 인덱스를 사용 할 수 없다고 결정을 내렸기 때문이다.
위 예제에서 LEADING 힌트는 사용할 정확한 조인 순서를 지정하였다. 서로 다른 테이블에 대해 사용할 조인 방식도 지정되었다.
'oracle11R2 > SQL Tuning 11g' 카테고리의 다른 글
| 11장. 자동 SQL 튜닝 (0) | 2011.06.23 |
|---|---|
| 10장. 애플리케이션 튜닝 (0) | 2011.06.23 |
| 08장. 바인드 변수의 사용 (0) | 2011.06.23 |
| 07장. 옵티마이저 통계 (0) | 2011.06.23 |
| 06장. 케이스 스터디 : 스타 변환 (0) | 2011.06.23 |
