출 처 : http://www.oracleclub.com/oracle/lecture/LectureInclude.jsp?lectureID=1260&lectureType=TUNINGTIP
제 목 : [ORACLE][자주] 오라클 HINT
날 짜 : 2002-04-02
2008/07/15 08:29
기 타 :
/*+ ALL_ROWS */
ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택 합니다.
Goal : Best Throughput
SQL>SELECT /*+ ALL_ROWS */ ename, hiredate FROM emp WHERE ename like '%%%'
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=5 Bytes=80)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=80)
예 : SELECT /*+ALL_ROWS */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655;
/*+ CHOOSE */
Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost Based Optimization) 인지를 선택 합니다.
만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용 합니다.
Goal : Acess되는 테이블에 통계치 존재여부에 따라 Optimizer로 하여금 Rule-Based Approach와 Cost-Based Approach중 하나를 선택할수 있게 한다.
용도 : Data Dictionary가 해당테이블에 대해 통계정보를 가지고 있다면 Optimizer는 Cost-Based Approach를 선택하고, 그렇지 않다면 Rule-Based Approach를 선택한다.
예 : SELECT /*+CHOOSE */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655;
/*+ FIRST_ROWS */
Full Table Scan보다는 index scan을 선호하며 Interactive Application인 경우 best response time을 제공 합니다.
또한 sort merge join보다는 nested loop join을 선호 합니다.
조건에 맞는 첫번째 row를 리턴하기 위한 Resource 소비를 최소화 시키기위한 힌트. Cost-Based 접근방식.
Goal : Best Response Time
SQL>SELECT /*+ FIRST_ROWS */ ename FROM emp WHERE empno=7876
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=1)
예 : SELECT /*+FIRST_ROWS */ EMPNO,ENAME
FROM EMP
WHERE EMPNO = 7655;
Rule Based 접근 방식을 사용하도록 지정 합니다.
예 : SELECT /*+RULE */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655;
----- Access Methods - 접근 방법 -----
/*+ CLUSTER(table_name) */
Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.
예 : SELECT /*+CLUSTER(EMP) */ ENAME,DEPTNO
FROM EMP,DEPT
WHERE DEPTNO = 10
AND EMP.DEPTNO = DEPT.DEPTNO;
/*+ FULL(table_name) */
Table을 Full Scan하길 원할 때 사용 합니다.
예 : SELECT /*+FULL(EMP) */ EMPNO,ENAME
FROM EMP
WHERE EMPNO = 7655;
* 테이블 Alias 가 있는경우는 Alias사용. Schema Name은 사용안함(From 에 SCOTT.EMP 라고 기술해도 hint에는 EMP사용).
/*+ HASH(table) */
Hash scan을 선택하도록 지정한다.
이 hint는 HASHKEYS parameter를 가지고 만들어진 cluster내에 저장된 table에만 적용이 됩니다.
/*+ INDEX(table_name index_name) */
지정된 index를 강제적으로 쓰게끔 지정 합니다.
용도 : 지정된 테이블Access에 Index Scan 유도.
* 하나의 index만 지정되면 optimizer는 해당index를 이용.
* 여러개의 인덱스가 지정되면 optimizer가 각 index의 scan시 cost를 분석 한 후 최소비용이 드는 index사용.
경우에 따라 optimizer는 여러 index를 사용한 후 결과를 merge하는 acees방식도 선택.
* index가 지정되지 않으면 optimizer는 테이블의 이용가능한 모든 index에 대해 scan cost를 고려후 최저비용이 드는 index scan을 선택한다.
예 : SELECT /*+INDEX(EMP EMPNO_INDEX) */ EMPNO,ENAME
FROM EMP
WHERE DEPTNO=10
/*+ INDEX_ASC(table_name index_name) */
지정된 index를 오름차순으로 쓰게끔 지정 합니다. Default로 Index Scan은 오름차순 입니다
/*+ INDEX_DESC(table_name index_name) */
지정된 index를 내림차순으로 쓰게끔 지정 합니다.
SQL>SELECT /*+ index_desc(emp pk_emp) */ empno FROM emp WHERE rownum = 1 ;
위 문장은 제일 큰 것 하나만 조회되므로, max function의 기능을 대신할 수 있습니다.
/*+ INDEX_FFS(table index) */
Full table scan보다 빠른 Full index scan을 유도 합니다.
/*+ ROWID(table) */
Rowid로 Table Scan을 하도록 지정 합니다.
/*+INDEX_COMBINE(table index) */
INDEX명이 주어지지 않으면 OPTIMIZER는 해당 테이블의 best cost 로 선택된 Boolean combination index 를 사용한다.
index 명이 주어지면 주어진 특정 bitmap index 의 boolean combination 의 사용을 시도한다.
조건절의 OR 를 Union ALL 형식으로 변형한다. 일반적으로 변형은 비용측면에서 효율적일때만 일어난다.
----- JOIN 순서를 결정하는 Hints -----
/*+ ORDERED */
From절에 기술된 테이블 순서대로 join이 일어나도록 유도 합니다.
예 : SELECT /*+ORDERED */ TAB1.COL1,TAB2.COL2,TAB3.COL3
FROM TAB1,TAB2,TAB3
WHERE TAB1.COL1=TAB2.COL1
AND TAB2.COL1=TAB3.COL1;
/*+STAR */
STAR QUERY PLAN이 사용가능하다면 이를 이용하기위한 HINT.
STAR PLAN은 규모가 가장큰 테이블이 QUERY에서 JOIN ORDER상 마지막으로 위치하게 하고 NESTED LOOP 으로 JOIN이 일어나도록
유도한다.
적어도 3개 테이블 이상이 조인에 참여해야하며 LARGE TABLE의 CONCATENATED INDEX는 최소 3컬럼 이상을 INDEX에 포함해야한다.
테이블이 ANALYZE 되어 있다면 OPTIMIZER가 가장효율적인 STAR PLAN을 선택한다.
----- JOIN OPERATION을 결정하는 HINTS. -----
/*+USE_NL(inner_table) */
테이블의 JOIN 시 테이블의 각 ROW가 INNER 테이블을 NESTED LOOP 형식으로 JOIN 한다.
예 : SELECT /*+ORDERD USE_NL(CUSTOMER) */
FROM ACCOUNT.BALANCE,CUSTOMER.LAST_NAME,CUSTOMER.FIRST_NAME
WHERE ACCOUNT.CUSTNO = CUSTOMER.CUSTNO;
/*+ USE_HASH (table_name) */
각 테이블간 HASH JOIN이 일어나도록 유도 합니다.
/*+ USE_MERGE (table_name) */
지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도 합니다.
/*+DRIVING_SITE(table) */
QUERY의 실행이 ORACLE에 의해 선택된 SITE가 아닌 다른 SITE에서 일어나도록 유도.
예 : SELECT /*+DRIVING_SITE(DEPT) */
FROM EMP,DEPT@RSITE
WHERE EMP.DEPTNO = DEPT.DEPTNO;
DRIVING_SITE 힌트를 안쓰면 DEPT의 ROW가 LOCAL SITE로 보내져 LOCAL SITE에서 JOIN이 일어나지만,
DRIVING_SITE 힌트를 쓰면 EMP의 ROW들이REMOTE SITE로 보내져 QUERY가 실행된후 LOCAL SITE로 결과가 RETURN된다.
----- Parallel Execution -----
/*+ NOPARALLEL(table_name) */
NOPARALLEL hint를 사용하면, parallel query option을 사용하지 않도록 할 수 있다.
SQL>SELECT /*+ NOPARALLEL */ * FROM emp;
/*+ PARALLEL(table_name, degree) */
PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있습니다.
예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을 실행하도록 할 수 있습니다. 이 때 parallel이란 글자와 괄호( '(' )사이에 blank를 넣지 않도록 주의해야 합니다.
SQL> SELECT /*+ PARALLEL(emp, 4) */ * FROM emp;
* DEGREE의 의미 및 결정
Parallel Query에서 degree란 하나의 operation 수행에 대한 server process의 개수 입니다.
이러한 degree 결정에 영향을 주는 요인들에는 다음과 같은 것들이 있습니다.
(1) system의 CPU 갯수
(2) system의 maximum process 갯수
(3) table이 striping되어 있는 경우, 그 table이 걸쳐있는 disk의 갯수
(4) data의 위치 (즉, memory에 cache되어 있는지, disk에 있는지)
(5) query의 형태 (예를 들어 sorts 혹은 full table scan)
한 사용자만이 parallel query를 사용하는 경우, sorting이 많이 필요한 작업과 같은 CPU-bound 작업의 경우는 CPU 갯수의 1 ~ 2배의 degree가 적당하며, sorting보다는 table scan과 같은 I/O bound 작업의 경우는 disk drive 갯수의 1 ~ 2배가 적당합니다.
동시에 수행되는 parallel query가 많은 경우에는 위의 각 사용자의 degree를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.
[펌] [ORACLE][자주] 오라클 HINT|작성자 ㅇㅈㅇ
