꿈꾸는 개발자, DBA 커뮤니티 구루비

튜닝 전

두 번째 예제는 Local Hint를 통해 성능 개선을 보여주는 사례이다.

아래의 내용을 보면, EMP, DEPT 테이블이 DEPTNO로 조인되어 있고, DEPT 테이블에 DEPTNO 조건이 들어가고 있으므로 DEPT 테이블이 드라이빙(시작)이 될 것이라고 쉽게 예측이 가능하다.

여기서 원하는 내용은 DEPT 테이블을 먼저 드라이빙 한 뒤 EMP 테이블과 조인할 때 DEPTNO 컬럼으로 인덱스가 있으므로 인덱스를 사용하길 원하는데, 아래의 실행계획에서는 TABLE ACCESS FULL을 하고 있다.

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO
     AND   D.DEPTNO = :B1;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  1998K|    83M| 12387   (4)| 00:02:29 |
|   1 |  NESTED LOOPS                |         |  1998K|    83M| 12387   (4)| 00:02:29 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |  1998K|    49M| 12386   (4)| 00:02:29 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=TO_NUMBER(:B1))
4 - filter("E"."DEPTNO"=TO_NUMBER(:B1))

이를 EMP 테이블에 EMP_N1(DEPTNO) 인덱스를 사용하도록 변경하고 싶은데 이 때 사용하는 방법이 Local Hint이다.

Local Hint란, 하나의 SQL 집합구간에서만 힌트로 핸들링이 가능하다고 해서 Local이라는 이름이 들어갔으며, C나 Java 언어의 지역변수와 전역변수 관계를 떠올리면 이해하기가 쉽다.

힌트 사용 방법은 아래와 같다.

- SQL 블록의 첫 키워드 바로 뒤에 입력 - 각 블록에는 하나의 Hint 주석만 있어야 하지만, 하나의 Hint 주석은 여러 개의 Hint 포함 가능 - Hint는 해당 블록에만 적용 - 문장에 alias를 사용하는 경우 힌트는 그 alias를 참조해야 함.

튜닝 후

이제 Local Hint를 사용하여 EMP_N1(DEPTNO) 인덱스를 유도해보자. 방법은 /*+ INDEX(E EMP_N1) */ 힌트를 사용하면 된다.

 
SQL> EXPLAIN PLAN FOR
     SELECT /*+ INDEX(E EMP_N1) */ *
     FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO
     AND   D.DEPTNO = :B1;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  1998K|    83M| 17838   (1)| 00:03:35 |
|   1 |  NESTED LOOPS                |         |  1998K|    83M| 17838   (1)| 00:03:35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |  1998K|    49M| 17837   (1)| 00:03:35 |
|*  5 |    INDEX RANGE SCAN          | EMP_N1  |  2162K|       |  4270   (2)| 00:00:52 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPTNO"=TO_NUMBER(:B1))
   5 - access("E"."DEPTNO"=TO_NUMBER(:B1))

위의 결과처럼 힌트를 사용할 경우 옵티마이저가 TABLE FULL SCAN을 하지 않고 인덱스를 사용한것을 알 수 있다.

하지만 예측시간(Time)은 오히려 전보다 증가가 됐는데, 이는 예측시간이고 바인드 변수에 대한 평균치이므로 크게 신경쓸 필요는 없으며 여기서는 Local Hint 사용방법만 숙지를 하면 된다.