EXPLAIN PLAN 사용법
===================

PURPOSE
-------
EXPLAIN PLAN에 대해서 알아본다.

Explanation
-----------

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

-----------------------------------------------------------------------


Reference Document
------------------