제 4 장 시스템 진단과 튜닝

4.EXPLAIN_PLAN(실행계획)

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

EXPLAIN PLAN의 결과에 의해, 옵티마이져가 특정의 실행 계획(예를 들어 nested루프 결합)을 선택 할지 판단할 수 있습니다.
또, 옵티마이져의 결정(예를 들어 옵티마이져가 해시 결합이 아니고 nested 루프 결합을 선택한 이유)에 대한 이해 등 퍼포먼스를 알기위해 도움이 된다.

▶ 실행계획 실습

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}

기 능옵 션설 명
AGGREGATEGROUP BY그룹함수(sum, count 등)를 사용하여 한의 로우가 추출되도록 처리(7버전에서만 표시)
AND-EQUAL인덱스 머지(Merge)를 이용하는 경우
CONNECT BYCONNECT BY 를 사용하여 트리(Tree)구조로전개
CONCATENATION단위 액세스에서 추출한 로우들의 합집합을 생성
COUNTING테이블의 로우스를 센다
FILTER선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업
FIRST ROW조회 로우 중에 첫번째 로우만 추출한다.
FOR UPDATE선택된 로우에 LOCK을 지정한다.
INDEXUNION
RANGE SCAN
RANGE SCAN
DESCENDING
UNIQUE 인덱스를 사용한다. (단 한 개읠 로우 추출)
NON-RNIQUE한 인덱스를 사용한다. (한 개 이상의 로우)
RANGE SCAN하고 동일하지만 역순으로
로우를 추출한다.
INTERSECTION교집합의 로우를 추출한다. (같은 값이 없다.)
MERGE JOINOUTER먼저 자신의 조건만으로 액세스한 후 각각을 SORT하여 머지(Merge)해 가는 조인 outer join을 한다.
MINUSMINUS 함수를 사용한다.NESTED LOOPSOUTER먼저 어떤 드라이빙(Driving) 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인, outer join을 한다.
PROJECTION내부적인 처리의 일종
REMOTE다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해 DataBase Link를 사용하는 경우
SEQUENCESequence 를 액세스한다.
SORTUNIQUE
GROUP BY
JOIN
ORDER BY
같은 로우를 제거하기 위한 SORT
액세스 결과를 GROUP BY 하기 위한 SORT
MERGE JOIN을 하기 위한SORT
ORDER BY 를 위한 SORT
TABLE ACCESSFULL
CLUSTER
HASH
BY ROWID
전체 테이블 스캔한다.
CLUSTER를 액세스한다.
키값에 대한 해쉬 알고리즘을 사용(7버전에서만)
ROWID를 이용하여 테이블을 추출한다.
UNION두집합의 합집합을 구한다.(중복없음)
항상 전체범위처리를 한다.
UNION ALL두집합의 합집합을 구한다.(중복없음)
UNION 과는 다르게 부분범위 처리를 한다.
VIEW어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.VLDB:주로 서브쿼리(Subquery)에 의해 수행된 결과