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

튜닝 전

다섯 번째 예제는 View 또는 인라인 뷰 안에 Local Hint가 존재하여 성능이 나빠지는 사례이다.

세 번째 사례에서도 살펴보았듯이 Local Hint가 존재할 경우 해당 블록의 우선순위가 가장 높게 되어 인라인 뷰 밖에서 조건을 넣게 되도 인덱스를 사용할 수 없다.

이번 사례 또한 인라인 뷰 밖에서 EMPNO 조건을 넣었음에도 불구하고 인라인 뷰 안에 TABLE FULL SCAN을 하도록 힌트 /*+ FULL(EMP) */가 들어가 있어 Unique 인덱스인 EMP_U1을 사용할 수 없게 된 것이다.

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM  (SELECT /*+ FULL(EMP) */
                    EMPNO, JOB, HIREDATE, DEPTNO
            FROM   EMP) EMP_V
     WHERE  EMP_V.EMPNO = :B1         

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    18 |  9343   (4)| 00:01:53 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    18 |  9343   (4)| 00:01:53 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=TO_NUMBER(:B1))

튜닝 후

그러므로 반드시 공용 View 일 경우에는 Local Hint가 들어가지 말도록 해야 하며, 이 경우에도 Local Hint만 제거할 경우 EMPNO 조건이 인라인 뷰 안으로 잘 들어가는 것을 확인할 수 있다.

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM   (SELECT EMPNO, JOB, HIREDATE,  DEPTNO
             FROM   EMP) EMP_V
     WHERE  EMP_V.EMPNO = :B1 ;

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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    18 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    18 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_U1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=TO_NUMBER(:B1))