EXPLAIN PLAN

• EXPLAIN PLAN은 주어진 SQL 쿼리를 지금(현재의 세션에서 현재의 설정으로) 실행한다면 이 쿼리의 쿼리 계획이 어떻게 될지를 알 수 있는 SQL 명령이다.
• EXPLAIN PLAN은 주어진 쿼리를 실행하기 위하여 실제로 사용하였던 계획이 무엇인지를 말해 줄 수 없다.

EXPLAIN PLAN 설치

• $ORACLE_HOME/rdbms/admin
• utlxplan.sql (UTiLity eXplain) : PLAN_TABLE이라 명명된 테이블의 CREATE TABLE 문을 포함
• utlxplp.sql (UTiLity eXplain PLan Parallel) : 병렬 쿼리 계획에 특화된 정보를 포함하여 계획 테이블의 내용을 보여 준다.
  • select * from table(dbms_xplan.display());
• utlxpls.sql(UTiLity eXplain PLan Serial) : 일반적인 직렬(비병렬) 계획에 대한 계획 테이블의 내용을 보여준다.
  • select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

• EXPLAIN PLAN을 설정하기 위해서는 먼저 계획 테이블 자체를 만들어야 한다.
  • @?/rdbms/admin/utlxplan
• UTILS 혹은 TOOLS라는 스키마를 생성 후 "ON COMMIT PRESERVE ROWS"옵션을 가진 글로벌 임시 테이블 생성 후 모든권한(ALL privilege)을 일반(public)에 부여한 후 일반 동의어(public synonym) PLAN_TABLE을 생성하여 계획 테이블을 공유할 수 있다.
• 10G부터 GLOBAL TEMPORARY TABLE인 PLAN_TABLE$을 제공한다. (PUBLIC SYNONYM PLAN_TABLE, CRUD 권한 부여)

EXPLAIN PLAN을 사용하라

explain plan
  [set statement_id = 'text']
  [into [owner.]table_name]
for statement;

• statement_id는 여러 계획을 계획 테이블에 저장할 수 있도록 해 준다.
• owner.table_name은 PLAN_TABLE이 아닌 다른 테이블을 사용할 수 있도록 해 준다.

• Pstart와 Pstop 값을 출력해 줌으로서 테이블을 통째로 읽지 않고 오직 한 파티션만 액세스될 것이라는 것을 알려준다.
• Oracle9i 릴리스 2 이상에서만 쿼리 계획의 각 단계에 적용할 술어의 정확한 부분을 나타낸다.

쿼리 계획을 읽는 방법

• 실행 계획을 읽는 방법 : 위에서 아래로, 안쪽에서 바깥쪽으로

EXPLAIN PLAN의 함정 회피

• EXPLAIN PLAN은 주어진 쿼리를 현재의 환경에서 즉시 실행해야 하는 경우 이 SQL 쿼리의 쿼리 계획을 얻기 위한 수단이지 이 쿼리르르 어제 수행하였을 때 어떤 계획이 사용되었는지 또는 앞으로 다른 세션을 이용하여 이 쿼리를 수행할 때 어떤 계획이 사용될지를 보여 주지는 못한다.
• 9i 이후 버젼에서는 이미 실행된 쿼리의 실제 계획을 얻기가 어렵지 않다(V$SQL_PLAN 뷰를 통해 조회 가능)
• p.153 ~ p.155 참조

DBMS_XPLAN과 V$SQL_PLAN의 사용

• DBMS_XPLAN.DISPLAY : 보고서의 말미에 상당한 양의 정보를 포함(Oracle9i 릴리스 2부터 도입)하고 있고, 절차에 따라 일정한 형식을 갖춘 EXPLAIN PLAN의 출력을 반환하는 함수

select * from table(dbms_xplan.display);

• V$SQL_PLAN 동적 성능 뷰와 함께 이기능을 사용하면 이미 실행된 문의 쿼리 계획을 쉽게 얻을 수 있다.
• p.157참조
• http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#CACICEDJ