EXPLAIN PLAN(실행계획) 이란?

  SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여 실행계획을 수립한 후 실행계획을 PLAN_TABLE에 저장하도록 해주는 명령 이다.

  SQL Trace 없이 사용 할 수 있다.

  ORACLE_HOME/rdbms/admin/utlxplan.sql실행 하여 PLAN_TABLE을 생성한다.

  statement_id컬럼에 인덱스를 생성해주는것이 수행속도를 향상시켜주고 id값이 중복되는 것을 막을 수 있다.

문법

• - statement_id = 'identifiedr' : 1-30자로 부여할 수 있는 해당 실행문의 제목
• - INTO tablename : 출력문을 저장하는 테이블명 PLAN_TABLE을 사용하지 않을경우 사용
• - FOR statement : 실행계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)

1. Plan_table 생성

  Explain plan을 sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여 plan_table에 저장 한다.

  이 table을 생성하기 위한 script는 $ORACLE_HOME/rdbms/admin/utlxplan.sql 이다.

  SQL> @C:\oracle\ora81\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문을 입력 실행한다.

 
SQL> EXPLAIN PLAN SET STATEMENT_ID='a1' FOR 
     SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0;
    

4. PLAN_TABLE 을 SELECT 하는 SQL 문을 실행

 
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||')')  "Explain Plan"
       FROM PLAN_TABLE
       START WITH ID= 0 and STATEMENT_ID = '&&id'
       CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'

-- a1을 입력하면 아래와 같은 실행계획을 볼 수 있다.
 Explain Plan
----------------------------------------------- 
SELECT STATEMENTcost  estimate:1
  TABLE ACCESS BY INDEX ROWID:TESTEMP(1)
    INDEX RANGE SCAN:TEST,,,EMP_PK    
    

PLAN_TABLE 컬럼 설명

OPERATION의 종류와 OPTIONS에 대한 설명