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

세 번째 예제는 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 테이블을 인덱스 사용하도록 유도할 수 있음을 알 수 있다.