실행계획(Execution Plan)

SQL에서 실행계획이란 사용자가 작성한 SQL이 요구한 데이터를 추출하기 위해 옵티마이저가 작업의 방법과 순서를 결정하는 것을 말합니다.

ORACLE DB의 경우, 옵티마이저가 실행한 실행계획 그리고 그 실행계획에서 사용된 비용에 대한 정보를 다음과 같은 3가지 방법으로 알 수 있습니다.

EXPLAIN PLAN - 실행계획만을 확인할 수 있음

SET AUTOTRACE - 실행계획과 I/O 관련정보를 확인할 수 있음

tkprof - 실행계획과 I/O 관련정보 뿐만 아니라 CPU 및 실제소요시간과 관련된 정보도 상세히 확인 가능

SQL 문장을 해석하고 실행계획을 수립하여 실행계획 테이블에 저장해주는 명령으로 PLAN 테이블이 만들어져 있어야합니다.

EXPLAIN PLAN 

SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE FOR

SELECT /*+ USE_NL(A B) */

       A.ENAME, A.DEPTNO, B.DNAME 

  FROM EMP A, DEPT B

 WHERE A.DEPTNO = B.DEPTNO;

그리고 이 PLAN_TABLE의 내용을 조회하는 SQL문은 다음과 같습니다.

SELECT ID||' '||PARENT_ID||' '||LPAD(' ',2 * (LEVEL-1))||

       OPERATION||' '||OPTIONS||' '||OBJECT_NAME as "Execution Plan"

  FROM PLAN_TABLE

 START WITH ID = 0 AND STATEMENT_ID = 'TEST1'

 CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEST1';

그러나 이 방법은 매번 SQL 문장을 EXPLAIN PLAN 명령어와 함께 사용하고, STATEMENT_ID를 지정해야하는 불편함이 있습니다. 

따라서 위의 작업을 자동적으로 해주는 SET AUTOTRACE 기능이 오라클의 경우 7.3버전부터 지원되고 있습니다.

이 SET AUTOTRACE 기능을 이용하기 위해서는 DBA와 함께 다음의 절차를 거쳐야합니다.

1. PLAN TABLE 생성 ( @는 start와 같은 SQL*PLUS 명령어)

SQL > @$ORACLE_HOME\rdbms\admin\utlxplan.sql

*** PLAN TABLE은 global temporary table로서 EXPLAIN PLAN 구문이 만드는 출력값을 저장하기 위해 만들어집니다. PLAN_TABLE이 자동적으로 만들어지도록 각 유저에게 셋업되면, utlxplan.sql을 통해서 자동적으로 local PLAN_TABLE을 생성할 수 있습니다.

2. DB USER에 PLUSTRACE ROLE을 할당받아야 함( SYS유저로 접속해야하므로 DBA의 도움이 필요하다 )

- SYS 유저로 접속

- @ORACLE_HOME\sqlplus\admin\plustrce.sql 실행 PLUSTRACE ROLE 생성

- GRANT PLUSTRACE TO hr을 실행 권한을 부여

SET AUTOTRACE ON

그리고 SET AUTOTRACE는 다음과 같은 옵션이 있습니다.

ON, OFF, TRACEONLY

ON, OFF는 AUTOTRACE기능을 켜고 끄는 기능을 합니다. TRACEONLY는 SQL문의 결과량이 많아서 디스플레이 시간이 많이 소요된다거나 이미 결과를 알고 있는 경우에 실행계획만 보고 싶을 때 사용합니다.

*** Predicate Information에 나타나는 INTERNAL_FUNCTION은 암묵적인 DATATYPE CONVERSION을 행하는 특별한 함수입니다.

ex) 예를 들어 A, B 컬럼이 date type일 때 where a = b 쿼리를 주었을 때 내부적으로는 where to_date(a) = b로 비교한다는 뜻이다.

*** DBMS_XPLAN은 EXPLAIN_PLAN 명령의 출력을 볼 때 쉬운 방법을 제공하는 패키지입니다.

*** 그냥 SET AUTOTRACE ON을 하는 것이 더 좋을 듯

*** ROWNUM은 슈도 칼럼이다.