ORACLE HINT 사용

Hint란?

select문을 실행시키면 DB의 옵티마이져가 조건절 또는 join절 등을 고려하여 액세스 경로를 결정한다. 이때 옵티마이저에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 액세스 경로를 선택할 수 있도록 하는 것이 Hint이다.

힌트의 사용 방법

힌트를 사용하는 방법은 "/*+ */"와 "--+"의 두 가지 방법이 있다.

/*+ */	여러 라인에 걸쳐 기술할 때 사용.
--+	오직 한 라인에만 기술할 수 있고 칼럼은 반드시 다음 라인에 기술해야 한다.

예제 : kkaok이라는 테이블이 있고 kkaok_indx라는 인덱스를 힌트에 사용한다고 가정한다.

SELECT /*+ INDEX(kkaok kkaok_indx) */ name,content FROM kkaok WHERE rownum<=2

SELECT --+ INDEX(kkaok kkaok_indx) name,content FROM kkaok WHERE rownum<=2

힌트의 접근방법

힌트의 접근방법에는 여러 가지가 있다. 이중에 자주 사용되어지는 몇가지만 알아보겠다.

/*+ CLUSTER(table_name) */

Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용된다.

/*+ INDEX(table_name index_name) */

지정된 index사용하도록 지정한다.

/*+ INDEX_ASC(table_name index_name) */

지정된 index를 오름차순으로 사용하도록 지정한다. Default로 Index Scan은 오름차순이다

/*+ INDEX_DESC(table_name index_name) */

지정된 index를 내림차순으로 사용하도록 지정한다.

힌트를 사용한 성능향상 테스트

50000만 건을 입력하고 전체 카운터를 가져오는 테스트를 해보겠다.

여기서의 소요시간은 서버환경이나 측정하는 방법에 따라 달라 질 수 있다. 하지만 상대적으로 비교해 볼 수는 있는 것이니 어떤 효과가 있는지를 알기에는 충분하다고 생각한다.

1. select count(idx) idx from 테이블명

- 소요시간 : 203ms

2. select /*+ index(테이블명 인덱스명) */ count(idx) idx from 테이블명

- 소요시간 : 15ms

카운터는 집계함수이지만 hint를 사용하면 처리 속도가 훨씬 빠른 것을 볼 수 있다. 오라클이 최적의 경로로 처리할 거라고 너무 믿지 말자. 힌트를 사용하면 훨씬 나은 결과를 얻을 수 있는 것이다.

HINT [ 힌트 ] 힌트란 사용자가 액세스 경로의 변경을 위해서 SQL내에 요구사항을 기술하면 옵티마이져가 액세스 경로를 결정할때 이를 참조하도록 하는 사용자 인터페이스를 말합니다. 옵티마이저에게 모든것을 맡기지 않고 사용자가 원하는 보다 좋은 액세스 경로를 직접 선택할 수 있도록 하므로 보다 쉽게 최적의 튜닝을 할 수 있게 도와 줍니다. ◈ 힌트의 사용 방법   힌트를 사용하는 방법은 '/*+ */'와 '--+'의 두가지 방법이 있습니다. /*+ */  : 여러 라인에 걸쳐 기술 할 때 사용합니다. --+ : 오직 한 라인에만 힌트를 기술할 수 있고 컬럼은 반드시 다음 라인에 기술해야 합니다. [힌트의 사용 예] SELECT /*+ INDEX(emp ename_index)           중복된 이름이 거의 없음 */        empno, ename, deptno FROM emp WHERE ename like ‘%A%’   AND deptno = 30     SELECT --+ INDEX(emp ename_index)            empno, ename, deptno FROM emp WHERE ename like ‘%A%’   AND deptno = 30 <>   /*+ ALL_ROWS */     ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 default로   ALL_ROWS를 선택 합니다.                    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)       /*+ CHOOSE */   Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost Based Optimization)   인지를 선택 합니다.    만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용 합니다.       /*+ FIRST_ROWS */    Full Table Scan보다는 index scan을 선호하며    Interactive Application인 경우 best response time을 제공 합니다.    또한 sort merge join보다는 nested loop join을 선호 합니다.      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)       /*+ RULE */    Rule Based 접근 방식을 사용하도록 지정 합니다. <>     /*+ CLUSTER(table_name) */   Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.     /*+ FULL(table_name) */   Table을 Full Scan하길 원할 때 사용 합니다.   /*+ HASH(table) */   Hash scan을 선택하도록 지정한다.   이 hint는 HASHKEYS parameter를 가지고 만들어진 cluster내에 저장된 table에만 적용이 됩니다.     /*+ INDEX(table_name index_name) */   지정된 index를 강제적으로 쓰게끔 지정 합니다.       /*+ 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을 하도록 지정 합니다. <> /*+ ORDERED */   From절에 기술된 테이블 순서대로 join이 일어나도록 유도 합니다. <>     /*+ USE_HASH (table_name) */   각 테이블간 HASH JOIN이 일어나도록 유도 합니다.     /*+ USE_MERGE (table_name) */   지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도 합니다. <>     /*+ 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를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.   출처 : http://yuleum.dnip.net/tc/gtkim/2