EXPLAIN PLAN, ORACLE에서 실행 계획 확인하기

<실행계획, SQL 작성 후 반드시 확인하자.>에서 언급했듯이 Oracle DBMS에서 SQL 문장의 실행 계획을 분석하는 방법을 살펴보겠습니다. Oracle DBMS는 실행 계획을 확인하는 방법을 여러 가지 제공하고 있습니다. 이번 시간에는 Oracle DBMS에서 실행 계획을 분석하기 위해 사용하는 도구 중 가장 간편하게 사용할 수 있는 EXPLAIN PLAN에 대해 알아보겠습니다.

EXPLAIN PLAN은 세션(SESSION) 레벨에서 실행 계획을 수집하고 분석합니다. 그러므로 다른 세션에서 같은 질의를 수행하더라도 서로 다른 실행 계획이 나타날 수 있음을 유의하셔야 합니다. 이뿐만 아니라, EXPLAIN PLAN에서 보이는 결과는 실제 수행된 결과가 아니라, Oracle DBMS 내부에 저장된 통계 정보를 활용하여 예측한 결과입니다. 그러므로 실제 질의문을 수행할 때, 예측된 실행 결과와 다른 형태로 수행될 수도 있습니다.

PLAN_TABLE

Oracle DBMS는 실행 계획을 생성한 후, 이 결과를 PLAN_TABLE에 저장합니다. 더 정확하게 EXPLAIN PLAN을 표현하면, SQL 문장을 분석 및 해석하여 실행 계획을 만들고, 이 결과를 PLAN_TABLE에 저장하는 도구입니다.

PLAN_TABLE의 대표적인 컬럼 목록

ORACLE 11gR2 이상 버전에서 PLAN_TABLE을 구성하는 컬럼은 모두 36개로 구성되어 있습니다^[각주:1]. 여기에서 모든 컬럼에 대해 설명하는 것은 불가능하며, 대표적인 컬럼에 대해서만 설명하겠습니다. 컬럼에 대해 정확하게 알고 싶으신 분은 다음 링크를 참조하시길 바랍니다.

PLAN_TABLE Columns

Column Name	Type	Description
Statement_Id	VARCHAR2(30)	EXPLAIN PLAN 문에서 사용자가 지정한 값
ID	NUMERIC	수립된 각 실행 단계에 붙여진 일련번호
Timestamp	DATE	실행 계획이 수립된 날짜와 시간
Object Owner	VARCHAR2(30)	객체(테이블, 인덱스)를 소유한 사용자 이름
Object_Name	VARCHAR2(30)	객체(테이블, 인덱스 등) 등의 이름
Object Type	VARCHAR2(30)	해당 라인의 객체의 종류 : 테이블, 인덱스 등

PLAN_TABLE 생성하기

Oracle에서 EXPLAIN PLAN을 이용하기 위해서는 PLAN_TABLE을 먼저 생성해야 합니다. 단, Oracle 10g 이상에서는 별도로 PLAN_TABLE 생성하지 않아도 됩니다. 왜냐하면, Oracle 10g 이상에서는 Oracle DBMS에 존재하는 SYS.PLAN_TABLE$를 사용하여 실행 계획을 저장하기 때문입니다.

Oracle DBMS에서 PLAN_TABLE을 생성하는 것은 매우 간단합니다. Oracle DBMS는 사용자가 PLAN_TABLE을 편리하게 생성할 수 있도록 스크립트를 제공하고 있습니다. PLAN_TABLE을 생성하기 위한 스크립트의 위치는 $ORACLE_HOME/RDBMS/ADMIN/에 있으며^[각주:2], 스크립트의 이름은 UTLXPLAN.SQL 입니다.

-- 윈도우 환경으로 $ORACLE_HOME%은 C:\ORACLE로 가정함

SQL> @C:\ORACLE\RDBMS\ADMIN\UTLXPLAN.SQL
SQL> SOURCE C:\ORACLE\RDBMS\ADMIN\UTLXPLAN.SQL

PLAN_TABLE INDEX 생성하기

PLAN_TABLE을 생성한 후, 질의 수행 속도를 높이고, 중복된 Statement_Id가 생성되는 것을 방지하기 위해 UNIQUE INDEX를 생성하는 것을 추천합니다. 인덱스를 생성하는 방법은 다음과 같습니다.

CREATE UNIQUE INDEX PLAN_TABLE_UIDX ON PLAN_TABLE(STATEMENT_ID, ID);

이상으로 PLAN_TABLE과 PLAN_TABLE에서 사용하는 INDEX를 생성하는 방법에 관해 알아보았습니다.

EXPLAIN PLAN 사용 방법

Oracle DBMS에서 EXPLAN PLAN을 사용하는 방법은 매우 쉽습니다.

기본 문법

EXPLAIN PLAN [SET STATEMENT_ID = 'OOO'] FOR
QUERY EXPRESSION;

실행 계획을 확인하고 싶은 질의의 앞 부분에 EXPLAIN PLAIN FOR를 기술하면 됩니다. 만약 STATEMENT_ID를 설정하고 싶다면, 추가로 SET STATEMENT_ID 명령을 입력하여 설정할 수 있습니다. SET STATEMENT_ID 명령을 통해 STATEMENT_ID를 지정하면, 지정한 ID를 통해 실행 계획의 결과를 호출할 수 있습니다. 만약 STATEMENT_ID를 생략하면 가장 마지막에 실행된 문장의 결과를 보여줍니다.

사용 예제

EXPLAIN PLAN FOR
SELECT A.EMPLOYEE_ID, B.DEPARTMENT_NAME, A.FIRST_NAME
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

실행 계획 확인하기 (방법1)

-- *****************************************************************
-- PARAMETERS:
--   1) STATEMENT ID
-- *****************************************************************

SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF

COLUMN PLAN             FORMAT A50
COLUMN OBJECT_NAME      FORMAT A30
COLUMN OBJECT_TYPE      FORMAT A15
COLUMN BYTES            FORMAT 9999999999
COLUMN COST             FORMAT 9999999
COLUMN PARTITION_START  FORMAT A20
COLUMN PARTITION_STOP   FORMAT A20

SELECT LPAD(' ', 2 * (LEVEL - 1)) ||
       DECODE (LEVEL,1,NULL,LEVEL-1 || '.' || PT.POSITION || ' ') ||
       INITCAP(PT.OPERATION) ||
       DECODE(PT.OPTIONS,NULL,'',' (' || INITCAP(PT.OPTIONS) || ')') PLAN,
       PT.OBJECT_NAME,
       PT.OBJECT_TYPE,
       PT.BYTES,
       PT.COST,
       PT.PARTITION_START,
       PT.PARTITION_STOP
  FROM PLAN_TABLE PT
 START WITH PT.ID = 0
   AND PT.STATEMENT_ID = '&1'
 CONNECT BY PRIOR PT.ID = PT.PARENT_ID
   AND PT.STATEMENT_ID = '&1';

이 스크립트는 DBA-ORACLE.COM에서 제공하는 질의입니다.

실행 계획 확인하기 (방법 2)

앞의 방법대로 PLAN_TABLE을 직접 질의할 수 있지만, 사용 방법이 꽤 복잡합니다. Oracle DBMS는 사용자에게 PLAN_TABLE을 쉽게 조회할 수 있도록 스크립트를 제공하고 있으며, 스크립트를 수행하는 방법은 다음과 같습니다.

SQL> @?/RDBMS/ADMIN/UTLXPLS
SQL> @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLS.SQL

UTLXPLS 스크립트는 여전히 Oracle DBMS의 최신 버전에도 설치되어 있습니다. 그러나 위와 같은 복잡한 질의 대신 DBMS_XPLAN 패키지를 이용하여 실행 계획을 표시하고 있습니다.

EXPLAIN PLAN EXAMPLE

지금부터 EXPLAIN PLAN을 활용하는 예제를 한 단계씩 따라가며 살펴보겠습니다.

앞 단락에선 다룬 EXPLAIN PLAN 사용 방법을 정독하신 분이라면 쉽게 따라하실 수 있을 것입니다. 예제에서 사용할 질의는 다음과 같습니다.

SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL;

예제의 실행 계획을 확인하기 위해 질의의 내용에 다음 내용을 추가합니다.

EXPLAIN PLAN FOR
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL;

이 예제의 실행 계획을 확인하기 위해 앞 단락에서 알아본 방법 중 Oracle DBMS에서 제공하는 스크립트를 활용하여 실행 계획을 확인해보겠습니다.

SQL> @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLS.SQL

스크립트를 수행한 결과는 다음과 같습니다.

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |     6  (67)| 00:00:01 |
|   1 |  UNION-ALL       |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   3 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   4 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

스크립트를 수행한 후, Oracle DBMS에서 생성한 결과를 확인하실 수 있습니다. 실행 계획을 제대로 도출했어도 해석 방법을 모르면, 어떤 내용인지 이해할 수 없습니다. 실행 계획을 해석하는 방법은 다음 시간에 별도의 글로 찾아뵙겠습니다.

마치면서

Oracle DBMS에서 실행 계획을 확인하는 데, 가장 쉽고 편리하게 활용할 수 있는 EXPLAIN PLAN에 대해 간단하게 살펴봤습니다. EXPLAIN PLAN에 대한 더 자세한 내용은 Oracle에서 제공하는 문서를 참조하시면 도움을 받을 수 있습니다[관련링크].

ORACLE 12cR1 버전까지 확인했습니다. [본문으로]
윈도우 환경은 %ORACLE_HOME%/RDBMS/ADMIN/ [본문으로]