Oracle Tuning 강좌
Global Hint 사용 예제 0 1 12,086

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


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

  아래의 SQL은 인라인 뷰 LOCAL 안에 또 다른 인라인 뷰 DEPTH_1이 있는 2 DEPTH 구조이다.

  여기서 원하는 내용은 EMP 테이블이 FULL SCAN 하는것을 인덱스를 사용하도록 유도하려고 하는데 이 때 Local Hint를 사용하지 말고 Global Hint를 사용하여 핸들링하는 내용이다.

  Global Hint란 SQL 집합 구간에서 직접 힌트를 주지 않고 맨 밖에서 힌트를 사용하여 유도하고자 하는 집합의 플랜을 변경할 때 사용하는 방법이다.

튜닝 전

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM  (SELECT *
            FROM   (SELECT E.EMPNO, 
                           E.JOB,
                           D.DEPTNO
                    FROM   EMP  E, 
                           DEPT D
                    WHERE  E.DEPTNO = D.DEPTNO
                    ) DEPTH_1
           ) LOCAL
     WHERE LOCAL.DEPTNO = :B1;

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

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

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

  만약 Local Hint를 사용한다면, DEPTH_1 인라인뷰에 /*+ INDEX(E EMP_N1) */ 힌트를 사용하면 될 것이고 원하는 플랜도 얻을 수 있음에도 불구하고 왜 Global Hint를 사용해야 할까?

  그 이유는 Local Hint는 해당 SQL 집합(예를들어 View)에서는 최적화가 되지만 그 View를 사용하는 다른 SQL에서는 최적화가 되지 않을 수 있다.

  즉, View는 여러 프로그램에서 공용으로 사용이 가능한데 해당 View를 특정 프로그램에서 사용하는 조건에만 최적화를 시키기 위해 Local Hint를 사용할 경우 다른 프로그램에서는 오히려 그 Local Hint 때문에 성능이 더 나빠질 수 있기 때문이다.

  그러므로 공용으로 사용되는 View에는 가급적 Local Hint 사용을 지양하고 Global Hint를 사용하여 해당 View를 사용하는 각각의 프로그램에서 최적화를 시켜야 한다.

튜닝 후

  우리가 원하는 내용은 DEPTH_1 안의 EMP 테이블을 인덱스를 사용하도록 Global Hint를 사용하려고 하는데 Global Hint 사용방법은 아래와 같다.

 
- 기존의 Local Hint는 테이블 이름 또는 Alias를 사용하여 핸들링을 하는데 
  Global Hint는 이 테이블 이름 또는 Alias에 그 블록의 Alias를 Dot(.)를 사용하여 명시함.
    

  이제 Global Hint 사용방법을 알았으니 사용해보기로 하자.

  우선 Global Hint를 사용하기에 앞서 Local Hint를 구현하면 다음과 같다. /*+ INDEX(E EMP_N1) */

  이제 이 Local Hint를 가장 밖에서 사용을 할 것이기 때문에 밖에서 DEPTH_1의 E를 핸들링 하려면 Alias를 Dot(.)로 지정해 주면 되므로 'E'를 'LOCAL.DEPTH_1.E'로 변경해주면 되며 완성버전은 /*+ INDEX(LOCAL.DEPTH_1.E EMP_N1) */ 이 된다.

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

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"=TO_NUMBER(:B1))
   4 - access("E"."DEPTNO"=TO_NUMBER(:B1))
    

  위의 결과에서 보듯이 Global Hint로 DEPTH_1 안쪽의 EMP 테이블을 인덱스 사용하도록 유도할 수 있음을 알 수 있다.

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

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

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

by 참된신자 [2014.08.13 11:44:38]

감사합니다 :)

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