오라클사에서 배낀 문서 입니다....

http://www.oracle.com/kr/support/web_supports/bulletins/

EXPLAIN PLAN 이란 사용자들이 SQL 문의 액세스 경로를 확인하고
튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후
실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령이다.


1. Plan_table 생성

  Explain plan을 sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만
수립하여 plan_table에 저장해 둔다.
이 table을 생성하기 위한 script는 ?/rdbms/admin/utlxplan.sql 이다.


2. Index 생성

테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을
방지하기 위해  index를 생성한다.                                                
SQL> create unique index plan_index on plan_table(statement_id,id);            
                                                                              

3. SQL 문 사용

   for 뒷 부분에 확인하고자 하는 sql을 대치한다.
   EXPLAIN PLAN SET STATEMENT_ID='a1' FOR
                  SELECT /*+ index(free_idx free) */ * from free;


4. Plan_table 을 select 하는 SQL 문 (plan.sql이라고 작성)

Select lpad(operation,length(operation)+ 2*(level-1)) ||decode(id,0,'cost
estimate:' ||
      decode(position,'0','N/A',position),null) || ' ' ||options || decode(object_name,null,null,':') ||
      rpad(object_owner, length(object_name)+1,',') || object_name ||
      decode (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) ||
      decode(object_instance,null,null,'('||object_instance||')')
FROM PLAN_TABLE
START WITH ID= 0 and STATEMENT_ID = '&&id'
CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'


5. Explain plan 문의 'statement_id =' 에서 부여한 제목을 'id'에 지정하고
   다음처럼 실행한다.

   sql> def   id = a1
   sql> @plan


6. 다음은 SQL*Plus 에서 자동으로 Explain Plan을 Creation하는 방법이다.
   이 방법은 Explain을 실행하는 구문을 기억할 필요 없이 현재 수행하는
   SQL 문장을 쉽게 체크해 볼 수 있다.

이것을 수행하기 전에 plan_table이 생성되어 있지 않다면 사용하고자 하는 user로 sqlplus login한 후 $ORACLE_HOME/rdbms/admin/utlxplan.sql을 수행하여 plan_table을 생성한다.

다음이 script의 내용이다.
단, 이 때 --로 시작하는 comment로 인해 error가 발생하는 수도 있으므로,
오류가 발생하면 --로 시작하는 comment를 모두 제거하고 수행하면 된다.

save toto repl              -- Save current script
truncate table plan_table;  -- Clean Plan Table
get toto nolist             -- Restore script

1                           -- setup explain statement
                            -- 영문자 l이 아니고 숫자 1이다.
ch //explain plan set statement_id='MyTest' for /
/
                            -- Go!
select  lpad(' ',2*(level-1))|| operation||' '||
        options||' '||object_name||
        ' '||object_type||' '||object_instance||' '||
        decode(id,0,'Cost = '||position) "Query Plan"
from    plan_table
        start   with id = 0  --  start with statement_id = 'MyTest'
        connect by prior id = parent_id;

get toto nolist              -- Restore script



EXAMPLE:

위의 script를 'plan.sql' 이라는 화일로 저장한 후 SQL*Plus를 login 한다.

SQL) select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL) @plan
Wrote file toto

Table truncated.

  1* select * from dept
  1* explain plan set statement_id='MyTest' for select * from dept

Explained.

Query Plan
-------------------------------------------------------------------
SELECT STATEMENT     Cost = 1
  TABLE ACCESS FULL DEPT  1

Script는 plan.sql로 저장되어 있으므로 user는 buffer를 query해서 어느
정도의 수정을 가할 수 있다.

SQL)  l
  1* select * from dept
SQL)  i
  2  where deptno =20;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

SQL)  @plan
Wrote file toto

Table truncated.

  1* select * from dept
  1* explain plan set statement_id='MyTest' for select * from dept

Explained.


Query Plan
SELECT STATEMENT     Cost = 1
  TABLE ACCESS BY ROWID DEPT  1
    INDEX UNIQUE SCAN DEPT_PRIMARY_KEY UNIQUE