Oracle Tuning 강좌
Local Hint 사용 예제 0 3 14,513

by 강정식 DBMS_XPLAN.DISPLAY DBMS_XPLAN Local Hint [2010.05.31]


튜닝 전

  두 번째 예제는 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 사용방법만 숙지를 하면 된다.

- 강좌 URL : http://www.gurubee.net/lecture/2123

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by shinbat [2010.07.03 12:52:59]
hint를 지우고 다시 실행시키니 hint 주었던것과 동일한 실행계획이 나오네요. 예측시간도 1초로 변경이 되었구요.

by 강정식 [2010.07.19 15:54:02]
네.. 힌트를 제거하였을 경우 실행계획이 동일하게 나올 수 있습니다.

또한 예측시간도 더 줄어들 수 있는데 이는 모두 예측이기 때문에 이 지표를 살펴보는것도 좋지만, 여기서는 Local Hint 사용하는 방법을 살펴 보시는게 더 좋을 것으로 보입니다.

by 참된신자 [2014.08.13 11:36:42]

감사합니다 :)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입