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

튜닝 전

DBMS_XPLAN.DISPLAY 강좌 예제와 동일한 예문이지만, DBMS_XPLAN.DISPLAY_CURSOR를 사용하였다.

해당 SQL을 보면 개발자가 순위를 부여하기 위해 EMP_V 인라인 뷰 안에 ROWNUM을 넣었고 인라인 뷰 밖에서 DEPTNO가 10인 대상들만 가져오도록 구성을 하였다.

문제는 'DEPTNO = 10' 조건으로 DEPT 테이블을 액세스한 뒤 조인키인 DEPTNO 컬럼이 EMP_V 인라인 뷰 안으로 침투되어 해당 데이터들만 가져온 뒤에 ROWNUM을 부여하는 것이 좋다.

하지만 ROWNUM이 존재하게 되면 오라클의 특성 상 ROWNUM이 존재하는 집합을 먼저 만들고 인라인 뷰 밖의 조건(DEPTNO 조인키)을 필터시키기 때문에 문제가 된다.

문제가 되는 이유는 EMP 테이블에 천만건의 데이터가 있는데 이 천만건을 모두 액세스한 뒤 9건밖에 존재하지 않는 'DEPTNO = 10' 데이터를 필터링 시키기 때문이며, 실행계획에서도 이러한 문제를 Id 3번를 보면 access가 아닌 filter로 처리된다는 것을 확인할 수 있다.

 
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT EMP_V.*
FROM   (SELECT ROWNUM,
               EMPNO,
               JOB,
               HIREDATE,
               DEPTNO       
        FROM   EMP) EMP_V,
       DEPT D
WHERE  EMP_V.DEPTNO = D.DEPTNO
AND    D.DEPTNO     = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS        |         |      1 |   9991K|      9 |00:00:25.47 |   44243 |   7482 |
|*  2 |   INDEX UNIQUE SCAN  | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|*  3 |   VIEW               |         |      1 |   9991K|      9 |00:00:25.47 |   44242 |   7482 |
|   4 |    COUNT             |         |      1 |        |     10M|00:00:10.00 |   44242 |   7482 |
|   5 |     TABLE ACCESS FULL| EMP     |      1 |   9991K|     10M|00:00:10.00 |   44242 |   7482 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                                
   2 - access("D"."DEPTNO"=10)
   3 - filter("EMP_V"."DEPTNO"=10)

튜닝 후

이를 해결하기 위해서는 결국 문제가 되는 ROWNUM을 인라인 뷰 밖으로 가져오면 되며, 실제 그렇게 변경할 경우 아래처럼 조인조건이 인라인 뷰 안으로 침투가 되어 인덱스를 사용하는 것을 확인할 수 있다.

 
SELECT ROWNUM,
       EMP_V.*
FROM   (SELECT EMPNO,
               JOB,
               HIREDATE,
               DEPTNO       
        FROM   EMP) EMP_V,
       DEPT D
WHERE  EMP_V.DEPTNO = D.DEPTNO
AND    D.DEPTNO     = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  COUNT                        |         |      1 |        |      9 |00:00:00.01 |       7 |
|   2 |   NESTED LOOPS                |         |      1 |      1 |      9 |00:00:00.01 |       7 |
|*  3 |    INDEX UNIQUE SCAN          | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      1 |      9 |00:00:00.01 |       6 |
|*  5 |     INDEX RANGE SCAN          | EMP_N1  |      1 |      1 |      9 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------

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