4.5.1. Understanding EXPLAIN PLAN

1. EXPAIN PLAN

• 명령어는 옵티마이져가 SELECT, UPDATE, INSERT, DELETE을 실행하기 위해 선택한 실행계획을 보여준다.
• 실행계획이란 옵티마이져가 특정 SQL문을 실행하기 위해 수행한 일련의 동작(OPERATIONS)을 트리형식으로 표현한 것이다.
• EXPAIN PLAN의 결과는 사용자로 하여금 옵티마이져가 왜 특정 실행계획을 선택했는지를 이해할 수 있게 해주고 쿼리문의 성능을 판단하는데 도움을 준다.

2. 실행계획은 다음과 같은 정보를 포함하고 있다.

• 1) 쿼리문이 참조한 테이블들의 순서
• 2) 쿼리문이 참조한 테이블들의 접근방법(ACESS PATH)
• 3) 조인에 의해 영향받는 테이블들의 조인방법(JOIN MEHTOD)
• 4) 데이터 조작방법(filter, sort, or aggregation,etc)

3. EXPLAIN PLAN 과 EXECUTION PLANS

• EXPLAIN PLAN 의 결과와 EXECUTION PLANS는 서로 다를 수 있다. 서로 다른 데이터베이스를 참조하거나, 같은 데이터베이스의 서로 다른
• OBJECTS를 참조하는 경우 차이가 발생한다. 설혹 같은 데이터베이스의 같은 OBJECT를 참조할지라도 다음과 같은 요인에 의해 달라질 수가 있다.

• 1) Data volume and statistics
• 2) Bind variable types and values
• 3) Initialization parameters - globally or session level

4. EXPLAIN PLAN는 사용자가 다음과 같은 피해야하는 항목들을 파악할 수 있게 해준다.

• 1) Full scans : 의도하지 않은 Full scans
• 2) Unselective range scans : 100건을 조회하기 위해 백만건을 스캔하는 경우
• 3) Late predicate filters : ?
• 4) Wrong join order : 잘못된 조인순서는 처리범위를 증가시킨다.
• 5) Late filter operations : 필터로 버릴 것이 있다면 조인 전에 필터하는 것이 좋다.

5. EXPLAIN PLAN 의 제약사항

• 1) 바이드변수에 대해서는 정확한 실제계획을 보여주지 못한다.
• 2) 암묵적인 형변환에 대해서도 정확한 예측을 하지 못한다.

6. EXPLAIN PLAN을 파악하는데 도움이 되는 시스템 뷰

• 1) V$SQL_PLAN : PLAN_TABLE과 거의 유사하다. PLAN_TABLE보다 좋은 점은 특정 쿼리문이 실행되는데 사용되는 컴파일 환경을 알 필요가 없다.
  • EXPLAIN PLAN의 경우 같은 플랜을 얻으려면 똑같은 환경을 조성해 주어야 한다.
• 2) V$SQL_PLAN_STATISTICS : 출력 로우수,경과시간과 같은 통계자료를 제공한다. 출력 로우수를 제외한 모든 통계값은 누적값이다.
  • 이 뷰의 데이터를 참조하기 위해서는 초기화 파라미터인 STATISTICS_LEVEL 이 ALL로 설정되어 있어야 한다.
• 3) V$SQL_PLAN_STATISTICS_ALL : 출력 로우수나 경과시간과 같은 통계자료를 하나씩 비교하는데 편리하다. V$SQL_PLAN과 V$SQL_PLAN_STATISTICS를 합쳐놓은 것이다.

4.5.2. The PLAN_TABLE Output Table

1. PLAN_TABLE 생성

• 1) 시스템마다 조금씩은 다르지만 $ORACLE_HOME/rdbms/admin directory/utlxplan.sql에 있는 쿼리문을 실행하여 만든다.
• 2) 플랜테이블이 칼럼이 달라질 수 있으므로 오라클 업그레이드시 테이블을 새로 만드는 것이 좋다.

4.5.3. Running EXPLAIN PLAN

1. PLAN_TALBE에 입력 (EXPLAIN PLAN 명령어는 DML이므로 암시적 COMMIT은 되지 않는다.)

EXPLAIN PLAN 
FOR SELECT last_name FROM employees;

• 쿼리문에 대한 실행계획이 PLAN_TALBE에 입력된다. 그러나 이럴경우 입력된 실행계획을 찾기 어려우므로 STATEMENT_ID를 정해준다.

EXPLAIN PLAN 
   SET STATEMENT_ID = 'st1' FOR
SELECT last_name FROM employees;

• 만일 다른 테이블을 플랜테이블로 사용하고 싶다면 INTO절을 사용하면된다. 단, my_plan_table 테이블은 PLAN_TABLE과 칼럼갯수,데이터타입이 일치해야 한다.

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table
FOR
SELECT last_name FROM employees;

4.5.4. Displaying PLAN_TABLE Output

1. $ORACLE_HOME/rdbms/admin directory/UTLXPLS.SQL : 가장 최근에 입력된 실행계획을 보여준다.

1) 입력

EXPLAIN PLAN 
   SET STATEMENT_ID = 'STID1' FOR
SELECT *
FROM EMP A,DEPT B
WHERE B.DEPTNO=10
AND A.DEPTNO=B.DEPTNO

2) 조회

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',NULL,'serial'));

3) 결과

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     5 |   270 |     3 |
|   1 |  NESTED LOOPS                |             |     5 |   270 |     3 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    21 |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT     |     4 |       |       |
|*  4 |   TABLE ACCESS FULL          | EMP         |     5 |   165 |     2 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."DEPTNO"=10)
   4 - filter("A"."DEPTNO"=10)

Note: cpu costing is off

2. $ORACLE_HOME/rdbms/admin directory/UTLXPLP.SQL

• 가장 최근에 입력된 실행계획을 보여준다. 쿼리가 병렬처리된다면 병렬처리 정보도 함께 보여준다.

1) 입력 : 상동

2) 조회 
set markup html preformat on; /*SQLPLUS에서만 작동됨*/
select * from table(dbms_xplan.display());

3) 결과 : 상동

3. DBMS_XPLAN.DISPLAY 프러시져

• 플랜테이블 이름,STATEMENT_ID,출력형식(BASIC, SERIAL, and TYPICAL, ALL)을 파라미터로 줄 수 있다.

1) 입력 : 상동

2) 조회 

* 파라미터 없슴
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

* 파라미터 있슴
SELECT PLAN_TABLE_OUTPUT 
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'STID1','TYPICAL'));


3) 결과 : 상동

4. PLAN_TABLE 직접 읽기

• 만일 ROWS나 COST 값이 NULL이라면 통계정보가 생성되지 않은 것이다.

SELECT 
   CARDINALITY "Rows"
   ,COST
   ,LPAD(' ',LEVEL-1)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME "Plan"
FROM PLAN_TABLE
CONNECT BY PRIOR ID = PARENT_ID
   AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = 0
   AND STATEMENT_ID = 'STID3'
ORDER BY ID;

4.5.5. PLAN_TABLE Columns

4.5.6. OPERATION and OPTIONS Values Produced by EXPLAIN PLAN

• 조인의 종류 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1181094

문서에 대하여

• 최초작성자 : 김강환
• 최초작성일 : 2009년 3월 27일
• 이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션1과 오라클 도큐먼트(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#PFGRF009)를 참고했습니다.