Oracle Database TIP
EXPLAIN PLAN(실행계획) 40 1 63,257

by 김정식 EXPLAIN PLAN 실행계획 PLAN_TABLE [2002.09.09]


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.gurubee.net/lecture/1540

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 거부기 [2009.03.23 22:06:45]
잘보고 갑니다. 좋은 자료네요... CARTESIAN에 대한 정보가 없어서 아쉽네요...
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입