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

튜닝 전

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

첫 번째 예제는 데이터타입이 틀릴 경우 인덱스를 사용하지 못하는 내용이다.

아래의 내용을 보면, EMP 테이블에 EMPNO_VARCHAR 컬럼으로 조건값 111이 들어가며, EMP 테이블에는 EMPNO_VARCHAR 컬럼으로 인덱스가 있기 때문에 빠르게 액세스를 할 수 있을 것이다.

그러나, 플랜을 확인한 결과 인덱스가 있음에도 불구하고 TABLE ACCESS FULL을 하며 예상 시간(Time)은 13.92초나 나왔다.

 
SET SERVEROUTPUT OFF
SET LINESIZE 2000
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT *
FROM   EMP E
WHERE  E.EMPNO_VARCHAR = 111;

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

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:13.92 |   44242 |  43323 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                          
   1 - filter(TO_NUMBER("E"."EMPNO_VARCHAR")=111)

튜닝 후

결국 내부적 데이터 형변환 때문에 인덱스를 사용하지 못하고 있으므로, 인덱스로 생성되어 있는 컬럼의 데이터타입에 맞추어 조건값을 넣으면 된다.

아래와 같이 데이터 타입이 맞게 조건절을 수정 한 후 Predicate Information을 확인해 보자

 
SELECT *
FROM   EMP E
WHERE  E.EMPNO_VARCHAR = '111';

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       5 |      3 |
|*  2 |   INDEX RANGE SCAN          | EMP_N3 |      1 |      1 |      1 |00:00:00.01 |       4 |      2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."EMPNO_VARCHAR"='111')

데이터 타입에 맞추어 조건값을 111 숫자가 아닌 '111'로 넣고 XPLAN을 확인한 결과 EMP_N3 인덱스를 사용하였고 A-Time 또한 예측 정보와 동일하게 00:01초밖에 안걸리는 것으로 확인되었다.

이처럼, 데이터 타입이 틀릴 경우 내부적 형변환으로 인해 인덱스를 사용하지 못 할 수 있으므로 반드시 Predicate Information 절을 확인해야 한다.