Hint of RBO & CBO
RBO와 CBO의 힌트
- RBO(Rule Base Optimization)
- CBO(Cost Base Optimization)
CBO의 목표 : 빠른 응답 시간(Best Response Time)과 최소의 자원 소비(Best Throughput)
OPTIMIZER_ MODE, ALTER SESSION의 OPTIMIZER_MODE 값과는 상관없이 오라클의 옵티마이저는 특정 접근 방법을 사용
Optimization Goals and Approaches
● RULE
RULE 힌트는 한 SQL문에 대해 Rule Base Optimization을 선택하도록 합니다.
SQL> SELECT /*+RULE */
EMPNO, ENAME
FROM EMP
WHERE EMPNO = 7788;
● ALL_ROWS
ALL_ROWS 힌트는 Best Throughput을 목표로 합니다. (즉, 총 자원 소비의 최소화) 가장 좋은 처리 성능을 목표로 SQL문 블록을 최적화하기 위해 CBO 접근 방법을 선택합니다.
SQL> SELECT /*+ ALL_ROWS */ EMPNO, ENAME
FROM EMP
WHERE EMPNO = 7788;
● FIRST_ROWS
FIRST_ROWS 힌트는 Best Response Time을 목표로 합니다. (즉, 첫 번째 행을 찾는 최소의 자원 사용) 가장 좋은 응답 시간을 목표로 SQL문 블록을 최적화하기 위해 CBO 접근 방법을 선택합니다.
SQL> SELECT /*+ FIRST_ROWS */ EMPNO, ENAME
FROM EMP
WHERE EMPNO = 7788;
<<특징>>
* Index Scan이 가능하다면 Optimizer가 Full Table Scan 대신 Index Scan을 선택합니다.
* Index Scan이 가능하다면 Optimizer가 Sort Merge Join보다 Nested Loop Join을 선택합니다.
* Order By 절에 의해 Index Scan이 가능하다면, Sort 과정을 피하기 위해 Index Scan을 선택합니다.
* Delete/ Update Block에서는 무시됩니다.
<<FIRST_ROWS 주의 사항>>
다음을 포함한 SELECT/ UPDATE/ DELETE 문에서는 FIRST_ROWS 힌트가 적용되지 않습니다. 그 이유는 오라클이 아래와 같은 SQL문의 경우에는 첫 번째 로우를 보여 주기 위해서는 모든 로우를 액세스해야 하기 때문입니다.
▷ 집합연산자(Union, Intersect, Minus)
▷ Group By
▷ Group 함수
▷ Distinct
● CHOOSE
액세스되는 테이블의 통계 정보 존재 여부에 따라 Optimizer로 하여금 Rule Based Approach와 Cost Based Approach 중 하나를 선택할 수 있게 합니다.
SQL> SELECT --+CHOOSE
EMPNO, ENAME
FROM EMP
WHERE EMPNO = 7788;
<<특징>>
* Data Dictionary가 해당 테이블들 중 최소한 하나의 테이블에 대한 통계 정보를 가지고 있다면 Optimizer는 Cost Based Approach를 선택하고, 그렇지 않다면 Rule Based Approach를 선택합니다. 이 때 통계 정보의 유무는 Analyze 작업 여부를 말합니다.
* 위 SQL문에서 만약 EMP 테이블에 대한 Statistics가 있다면 Optimizer는 Cost Based Approach를 사용하고, Data Dictionary에 EMP 테이블에 대한 어떤 Statistics도 존재하지 않는다면 Optimizer는 Rule Based Approach를 사용하게 됩니다.
이번 항에서는 테이블에 대한 접근 방법을 설명해 드리겠습니다. 이번 항에 언급될 힌트들은 지정된 Access Path가 SQL 문장과 클러스터 혹은 인덱스의 존재 여부에 기초하여 사용 가능할 경우에만 옵티마이저가 지정된 Access Path를 선택하게 됩니다.
만약에 지정된 힌트가 사용 가능하지 않은 Access Path라고 한다면, 옵티마이저는 이 힌트를 사용하지 않게 됩니다. 즉, 지정된 힌트가 테이블이나 해당 인덱스에서 사용될 수 있는 액세스 패스가 있는 경우에만 사용할 수 있습니다.
그리고 힌트 사용 시 주의 사항에서 언급했지만, 테이블에 대한 Alias를 사용할 수 있으며, 스키마 이름이 SQL문에 표현되어 있더라도 스키마 이름은 생략하여야 합니다.
Acess Method Hints
● FULL
이 힌트는 해당 테이블의 Full Table Scan을 유도합니다. FTS를 수행할 때는 Multi Block I/O를 하게 되므로 성능이 좋습니다. (관련된 초기 파라미터 : DB_FILE_ MULTIBLOCK_READ_COUNT) 그렇지만, FTS는 전체 테이블을 읽지 않고 Table의 10~15% 이하를 읽을 경우와 병렬 처리를 하지 않을 때는 애플리케이션의 응답속도를 떨어뜨리는 주범이므로 주의해야 합니다.
SQL> SELECT /*+ FULL(a) */ EMPNO, ENAME
FROM EMP a
WHERE EMPNO = 7788;
특징
* EMP 테이블에 EMPNO에 대한 인덱스가 있더라도 인덱스를 사용하지 않고, EMP 테이블을 Full Table Scan하게 유도합니다.
* 테이블 Alias가 있는 경우는 Alias를 사용합니다.
* FTS를 수행할 때 버퍼는 버퍼 캐시의 LRU(Least Recently Used)의 맨끝에 위치하기 때문에 메모리에서 빨리 사라지게 됩니다.
* Full Table Scan은 테이블 전체를 HWM(High Water Mark)까지 읽습니다.
HWM(High Water Mark)
▷ HWM은 데이터가 쓰여진 마지막 블록을 가리키게 되는데, 부분적으로 데이터가 삭제가 되더라도 FTS이 중단되지 않도록 하는 역할을 합니다.
▷ Delete를 할 경우에는 HWM은 변화가 없지만, Truncate를 하게 되면 HWM은 테이블의 처음 위치로 바뀌게 됩니다.
▷ 큰 테이블의 데이터를 전량 Delete 한 후에 Select를 하면 시간이 많이 걸리는 것으로 확인할 수 있습니다.
● ROWID
이 힌트는 지정된 테이블의 ROWID를 이용한 검색을 유도합니다.
SQL> SELECT /*+ ROWID(EMP) */ EMPNO, ENAME
FROM EMP
WHERE ROWID>'AAAAtkAABAAAFNTAAA';
● CLUSTER
이 힌트는 지정된 테이블 Access에 Cluster Scan을 유도합니다.
주의할 사항은 Cluster된 Objects에만 적용 가능하다는 것입니다.
SQL> SELECT /*+ CLUSTER(EMP) */ ENAME, DEPTNO
FROM EMP, DEPT
WHERE DEPTNO = 10
AND EMP.DEPTNO = DEPT.DEPTNO;
● INDEX
지정된 테이블 Access에 Index Scan을 유도합니다.
SQL> SELECT /*+ INDEX(EMP EMPNO_INDEX) */ EMPNO, ENAME
FROM EMP
WHERE DEPTNO=10;
특징
* 하나의 Index만 지정되면 옵티마이저는 해당 Index를 이용하게 됩니다.
(비록 다른 인덱스가 존재하더라도 지정된 Index Scan을 하게 됩니다.)
* 여러 개의 인덱스가 지정되면 Optimizer가 각 Index의 Scan시 Cost를 분석 한 후, 최소 비용이 드는 Index를 사용하게 됩니다.
* 경우에 따라 Optimizer는 여러 Index를 사용한 후 결과를 Merge하는 Access 방식을 선택하기도 합니다.
* Index가 지정되지 않으면 Optimizer는 테이블의 이용 가능한 모든 Index에 대해 Scan Cost를 고려 후 최저 비용이 드는 Index Scan을 선택합니다.
* 오라클에서 Bitmap Index의 경우는 INDEX 힌트보다는 INDEX_COMBINE 힌트를 사용할 것을 권장합니다.
● INDEX_ASC
INDEX HINT와 동일하게 Index Scan을 유도합니다. Oracle은 Index된 값들을 오름차순(Ascending)으로 Scan을 실시합니다.
SQL> SELECT /*+ INDEX_ASC(EMP EMPNO_INDEX) */ EMPNO, ENAME
FROM EMP
WHERE DEPTNO<10;
● INDEX_DESC
지정된 테이블의 지정된 Index를 내림차순(Descending)으로 Scan하고자 할 때 사용합니다.
SQL> SELECT /*+ INDEX_DESC(EMP EMPNO_INDEX) */ EMPNO, ENAME
FROM EMP
WHERE DEPTNO>10;
● INDEX_FFS
조회하는 칼럼들이 인덱스 내에 모두 있는 경우에 사용할 수 있습니다. FTS와 같이 Multi Block I/O를 하므로 성능이 좋습니다.
SQL> SELECT /*+ INDEX_FFS(EMP EMP_EMPNO) */ EMPNO
FROM EMP
WHERE EMPNO > 10;
● NO_INDEX
지정된 테이블의 인덱스 중에서 Access에 제외시킬 Index를 지정합니다.
SQL> SELECT /*+ NO_INDEX(EMP EMP_EMPNO) */ ENAME, DEPTNO
FROM EMP
WHERE EMPNO > 10;
특징
* 하나의 Index만 지정되면 Optimizer는 해당 Index를 사용하지 않습니다.
그리고, 지정되지 않은 인덱스들은 고려 대상이 됩니다.
* 여러 개의 인덱스가 지정되면 Optimizer가 지정된 각 인덱스들의 사용을 고려하지 않습니다.
* Index가 지정되지 않으면 Optimizer는 테이블에 인덱스 액세스를 고려하지 않습니다.
* NO_INDEX와 INDEX Hint를 같이 사용하게 되면 옵티마이저는 NO_INDEX 힌트를 무시합니다.
● AND_EQUAL
Single Column Index의 Merge를 이용한 Access Path를 선택합니다.
적어도 두 개 이상의 Index가 지정되어야 하며, 5개 이상의 인덱스는 지정할 수 없습니다.
SQL> SELECT /*+ AND_EQUAL(EMP JOB_INDEX DEPTNO_INDEX)*/ SAL, HIREDATE
FROM EMP
WHERE JOB = 'CLERK' AND DEPTNO = 30 ;
Join Order Hints
● ORDERED
From 절에 기술된 테이블 순서대로 Join이 일어나도록 유도합니다.
SQL문에서 ORDERED Hint를 생략하고 Join를 수행하면, Optimizer가 Table을 조인할 순서를 선택하기 때문에 각 테이블에서 Select하는 행의 수에 대해 알고 있다면, 의도적으로 드라이빙 테이블을 구성하여 Join 순서를 기술하는 ORDERED 힌트를 사용하는 것이 좋습니다.
SQL> SELECT /*+ORDERED */ TAB1.COL1,TAB2.COL2,TAB3.COL3
FROM TAB1,TAB2,TAB3
WHERE TAB1.COL1=TAB2.COL1
AND TAB2.COL1=TAB3.COL1;
Join Operation Hints
● USE_NL
JOIN SQL문의 경우 드라이빙 테이블의 각 ROW가 드리븐 테이블을 NESTED LOOP 형식으로 조인하는 NESTED LOOP 조인으로 유도하기 위해 USE_NL 힌트를 사용하며, ORDERD 힌트와 같이 사용하면 유용합니다. 대부분의 경우 SORT MERGE 조인보다 NESTED LOOP 조인이 첫 번째 로우를 가져오는 데는 빠릅니다.
SQL> SELECT /*+ ORDERED USE_NL(DEPT EMP) */ ENAME, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
● USE_MERGE
지정된 테이블들의 조인이 SORT MERGE 조인으로 처리되도록 유도합니다.
SQL> SELECT /*+ USE_MERGE(EMP DEPT) */ ENAME, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
● USE_HASH
각 테이블 간에 HASH JOIN이 일어나도록 유도합니다.
NESTED LOOP처럼 선행 테이블의 범위가 적은 것이 유리하므로 ORDERD와 같이 사용할 수 있습니다.
SQL> SELECT /*+ USE_HASH(EMP DEPT) */ ENAME, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
● LEADING
이 힌트는 조인 순서에서 지정된 테이블이 DRIVING 테이블이 되도록 합니다.
ORDERED 힌트와 같이 사용되는 경우는 LEADING 힌트는 무시됩니다.
사용법: /*+ LEADING(table_name) */
Parallel Execution Hints
● PARALLEL
PARALLEL 힌트는 그 연산을 위해 사용될 수 있는 동시의 Slave Process를 요구하는 수를 명시할 수 있습니다. 테이블 Scan 뿐만 아니라 DELETE, INSERT, UPDATE의 부분에도 적용될 수 있습니다.
이 힌트는 테이블 명 다음의 콤마(,)에 의해서 두 개의 인수 값을 가지게 되는데, 첫 번째는 주어진 테이블에 대한 패러렐 개수를 말하며, 두 번째 값은 어떻게 패러렐 서버 인스턴스 사이에 테이블을 나누어서 할당할 것인지를 지정하게 됩니다.
값을 표현하지 않거나 DEFAULT로 지정하게 되면, 초기 파라미터의 설정된 값을 가지게 됩니다.
SQL> SELECT /*+ FULL(EMP) PARALLEL(EMP, 5) */ ENAME
FROM EMP;
● NOPARALLEL
그 테이블이 PARALLEL 절로 새로 만들어졌다면 테이블의 병렬 검색을 사용하지 않게 합니다.
사용법: /*+ NOPARALLEL(table_name) */
● PARALLEL_INDEX
패러렐 인덱스 힌트는 파티션된 인덱스에 대한 Index Range Scan에 병렬 처리를 할 수 있도록 합니다. 이 힌트는 테이블 명과 인덱스 명 다음에 두 개의 인수값을 가지게 되는데, 첫 번째 값은 주어진 인덱스에 대한 패러렐 개수를 지정하는 것이며, 두 번째 값은 패러렐 서버 인스턴스에 인덱스를 어떻게 나누어서 할당할 것인지를 지정하는 값입니다. 값을 지정하지 않거나 DEFAULT로 값을 주면 초기 파라미터의 값을 갖게 됩니다.
다음은 2개의 인스턴스 각각에 3개의 Parallel 프로세스를 사용하는 예입니다.
사용법: /*+ PARALLEL_INDEX(TABLE1, INDEX1, 3, 2) */
● NOPARALLEL_INDEX
이 힌트는 인덱스에 대한 PARALLEL 처리를 무시하게 됩니다.
사용법: /*+ NOPARALLEL_INDEX(TABLE명 INDEX명) */
Query Transformation Hints
● MERGE
한 뷰의 쿼리에 GROUP BY나 DISTINCT를 포함하고 있다면, 옵티마이저는 뷰의 쿼리를 액세스하는 SQL에 MERGE하게 됩니다.
SQL> SELECT /*+ MERGE(v)*/ e1.ename, e1.sal, v.avg_sal
FROM emp e1,
(SELECT deptno, avg(sal) avg_sal
FROM emp e2
GROUP BY deptno) v
WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal;
● NO_MERGE
이 힌트는 Merge 가능한 뷰를 MERGE 하지 않도록 합니다.
사용법: /*+ NO_MERGE(table_name)*/
● USE_CONCAT
WHERE 절의 OR 조건을 UNION ALL 형식으로 변형 합니다.
일반적으로 이런 형태의 변형은 Concatenation이 비용 측면에서 더 효율적일 때 일어나게 됩니다.
SQL> SELECT /*+ USE_CONCAT*/ ENAME, DEPTNO
FROM EMP
WHERE EMPNO > 10 OR SAL < 5000;
● NO_EXPAND
WHERE 절에서 OR 절이나 IN-LIST 절의 전개를 CBO 모드에서 사용하지 않게 합니다.
SQL> SELECT /*+ NO_EXPAND*/ ENAME, DEPTNO
FROM EMP
WHERE EMPNO > 10 OR SAL < 5000;
Other Hints
● CACHE
전체 범위로 읽은 Table을 데이터베이스 버퍼의 LRU 리스트 앞쪽에 두어 버퍼에 오래 남아 있을 확률을 높이게 하는 힌트로 자주 액세스되는 적은 크기의 테이블에 유용합니다.
SQL> SELECT /*+ FULL(DEPT) CACHE(DEPT) */ ENAME
FROM DEPT;
● NOCACHE
전체 범위로 읽은 Table을 데이터베이스 버퍼의 LRU 리스트 뒤쪽에 두어 버퍼에서 제거되는 확률을 높이게 하는 힌트로 사용 빈도가 적고 큰 크기의 테이블에 사용합니다.
사용법: /*+ NOCACHE(table_name) */
| 
![[SPATIUM]WebMaster Mail](/image/bo_menu02.gif)