제 4 장 시스템 진단과 튜닝

4.EXPLAIN_PLAN(실행계획)

EXPLAIN PLAN 문은 SELECT,UPDATE, INSERT 및 DELETE 문에 대해 Oracle 옵티마이져가 선택한 실행 계획을 표시합니다.
문자의 실행 계획이랑 Oracle 가 그 문장을 실행하기 위해 실시하는 일련의 처리입니다.

▶ 실행계획 실습

1.Plan_table 생성

{section}{column:width=33%}

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

sctipt : ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql  실행


create table PLAN_TABLE (
        statement_id       varchar2(30),      // EXPLAIN PLAN문에서 사용자가 지정한  제목
        plan_id            number,            
        timestamp          date,              // 실행계획이 수립된 날짜와 시간
        remarks            varchar2(4000),    // 사용자가 부여한 주석(Comments)
        operation          varchar2(30),      // 
        options            varchar2(255),
        object_node        varchar2(128),     // 사용한 데이터베이스 링크(DataBase Link)
        object_owner       varchar2(30),      // 해당 라인의 오브젝트(Object)를 생성한 사용자 그룹(Owner)
        object_name        varchar2(30),      // 테이블이나 인덱스, 클러스터 등의 이름
        object_alias       varchar2(65),
        object_instance    numeric,           // SQL의 FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호
        object_type        varchar2(30),      // 오브젝트의 종류(예:non-unique index)
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,           // 해당 ID의 부모의 단계가 가진 ID
        parent_id          numeric,
        depth              numeric,
        position           numeric,           // 같은 부모 ID를 가지고 있는 자식 ID 간의 처리순서
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,               // 다른 필요한 텍스트를 저장하기 위한 필드
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

{column}{section}

2. Index 생성

{section}{column:width=33%}

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

{column}{section}

3. SQL문 사용

{section}{column:width=33%}

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

{column}{section}

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

{section}{column:width=33%}

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'

{column}{section}

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

{section}{column:width=33%}

   sql> def   id = a1
sql> @plan

{column}{section}

6. EXPLIN PLAN 을 통해 확인한다.

{section}{column:width=33%}

*EXECUTION PLAN*
-------------------------------------------------------------------------------
SELECT STATEMENT
   SORT  (GROUP BY)
     FILTER
        NESTED LOOPS
           TABLE ACCESS (FULL) OF 'TABLE1'
           TABLE ACCESS (BY ROWID) OF  'TABLE2'
              INDEX (RANGE SCAN) OF 'TABLE1_PK' (UNIQUE)
        TABLE ACCESS (FULL) OF 'TABLE3'

{column}{section}