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.