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

튜닝 전

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

이 예제는 View 또는 인라인 뷰 안에 인덱스로 존재하는 조인컬럼이 가공되어 있어 성능이 나빠지는 사례이다.

SQL 구조를 보면 EMP_V 인라인 뷰에 DATE 타입으로 되어 있는 HIREDATE 컬럼에 시분초를 제거하고 싶어 'YYYYMMDD'로 표현을 하였다.

문제는 인덱스로 존재하는 HIREDATE 컬럼을 TO_CHAR()로 변경한 상태로 인라인 뷰 밖에서 해당 컬럼으로 조건을 주었기 때문에 조건이 인라인 뷰 안으로 침투가 안되서 FULL SCAN을 하고 있다.

이 문제는 사례 첫번째에서 본 Case와 유사한데 첫번째 내용은 데이터 타입이 서로 틀려 내부적 형변환을 오라클이 시도했지만 지금 Case는 View 구성 자체를 처음부터 잘 못 만들었기 때문에 외부적 형변환이라 볼 수 있다.

또한 Predicate Information의 Id 2번째 내용에서도 HIREDATE 컬럼을 가공하여 인덱스를 사용하지 못하고 있음을 확인할 수 있다.

 
VAR B1 VARCHAR2(100);
VAR B2 VARCHAR2(100);

EXEC :B1 := '20090101';
EXEC :B2 := '20090131';

-- SQL문장 실행
SELECT *
FROM   (SELECT EMPNO,
               JOB,
               TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE,
               DEPTNO       
        FROM   EMP) EMP_V
WHERE  EMP_V.HIREDATE BETWEEN :B1
                      AND     :B2;

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

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|*  1 |  FILTER            |      |      1 |        |    300 |00:00:29.34 |   44261 |  43950 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |  24979 |    300 |00:00:29.34 |   44261 |  43950 |
----------------------------------------------------------------------------------------------
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   1 - filter(:B1<=:B2)                                                                       
   2 - filter((TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYYMMDD')>=:B1 AND                     
              TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYYMMDD')<=:B2))

튜닝 후

결국 이 문제는 인라인 뷰 안에서 가공된 컬럼을 인라인 뷰 밖에서 조건으로 사용했기 때문에 발생된 문제인데, 왜 이런 비효율이 생긴것인지 유추를 해보면 다음과 같다.

개발자는 어플리케이션에서 HIREDATE 값을 문자타입(20100101)으로 보여주도록 프로그램을 구성하였고 이를 DB단계에서 매칭시키기 위해 해당 컬럼을 TO_CHAR() 함수를 통해 형변환을 시켰을 것이다.

하지만 이런 구현 방법으로 인해 DB 단계에서는 인덱스가 있음에도 불구하고 사용하지 못하고 FULL SCAN을 하여 수행속도가 오래 걸리는 원인이 되었던 것이다.

이제 원인이 나왔으니 해결방법 또한 찾을 수 있을 것이다.

즉, DB 단계(인라인 뷰)에서 인덱스를 사용할 수 있도록 HIREDATE를 그대로 사용하고 어플리케이션 단계에서 문자타입으로 조건값을 받는것이 아닌 날짜타입으로 조건값을 받으면 된다.

 
EXEC :B1 := '20090101 00:00:00';
EXEC :B2 := '20090131 23:59:59';

SELECT EMPNO,
       JOB,
       TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE,
       DEPTNO    
FROM   (SELECT EMPNO,
               JOB,
               HIREDATE,
               DEPTNO       
        FROM   EMP) EMP_V
WHERE  EMP_V.HIREDATE BETWEEN TO_DATE(:B1, 'YYYYMMDD HH24:MI:SS') 
                      AND     TO_DATE(:B2, 'YYYYMMDD HH24:MI:SS');

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

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                      |        |      1 |        |    310 |00:00:00.01 |      49 |      2 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  24979 |    310 |00:00:00.01 |      49 |      2 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |  44962 |    310 |00:00:00.01 |      25 |      2 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:B1,'YYYYMMDD HH24:MI:SS')<=TO_DATE(:B2,'YYYYMMDD HH24:MI:SS'))
   3 - access("HIREDATE">=TO_DATE(:B1,'YYYYMMDD HH24:MI:SS') AND
              "HIREDATE"<=TO_DATE(:B2,'YYYYMMDD HH24:MI:SS'))