EXPLAIN PLAN(실행계획) 이란?

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 컬럼 설명

컬 럼 명	설 명
STATEMENT_ID	EXPLAIN PLAN문에서 사용자가 지정한 제목
TIMESTAMP	실행계획이 수립된 날짜와 시간
REMARKS	사용자가 부여한 주석(COMMENT)
OPERATION	아래 표에 자세히 설명 되어 있습니다.
OPTIONS	아래 표에 자세히 설명 되어 있습니다.
OBJECT_NODE	사용한 데이터베이스 링크
OBJECT_OWNER	해당 라인의 오브젝트를 생성한 사용자 그룹
OBJECT_NAME	테이블이나 인덱스, 클러스터등의 이름
OBJECT_INSTANCE	SQL의 FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호
OBJECT_TYPE	오브젝트의 종류(예 non-unique index)
ID	수립된 각 실행단계에 붙여진 일련번호
PARENT_ID	해당 ID의 부모가 가진 ID
POSITION	같은 부모 ID를 가지고 있는 자식 ID간의 처리 순서
OTHER	다른 필요한 텍스트를 지정하기 위한 필트

OPERATION의 종류와 OPTIONS에 대한 설명

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

문서에 대하여

• - 강좌 URL : http://www.oracleclub.com/lecture/1540
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.