View Source

h3. 4.5.1. Understanding EXPLAIN PLAN

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

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

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

h5. 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

h5. 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 : 필터로 버릴 것이 있다면 조인 전에 필터하는 것이 좋다.

h5. 5. EXPLAIN PLAN 의 제약사항

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

h5. 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를 합쳐놓은 것이다.


h3. 4.5.2. The PLAN_TABLE Output Table

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

h3. 4.5.3. Running EXPLAIN PLAN

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

{code:SQL}
EXPLAIN PLAN
FOR SELECT last_name FROM employees;
{code}

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

{code:SQL}
EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR
SELECT last_name FROM employees;
{code}

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

{code:SQL}
EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR
SELECT last_name FROM employees;
{code}

h3. 4.5.4. Displaying PLAN_TABLE Output

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

{code: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
{code}

h5. 2. $ORACLE_HOME/rdbms/admin directory/UTLXPLP.SQL
* 가장 최근에 입력된 실행계획을 보여준다. 쿼리가 병렬처리된다면 병렬처리 정보도 함께 보여준다.

{code:SQL}

1) 입력 : 상동

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

3) 결과 : 상동
{code}

h5. 3. DBMS_XPLAN.DISPLAY 프러시져
* 플랜테이블 이름,STATEMENT_ID,출력형식(BASIC, SERIAL, and TYPICAL, ALL)을 파라미터로 줄 수 있다.

{code:SQL}

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) 결과 : 상동
{code}

h5. 4. PLAN_TABLE 직접 읽기
* 만일 ROWS나 COST 값이 NULL이라면 통계정보가 생성되지 않은 것이다.

{code:SQL}
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;
{code}


h3. 4.5.5. PLAN_TABLE Columns

|| Column || Type || Description ||
|STATEMENT_ID|VARCHAR2(30)|EXPLAIN PLAN 명령실행시 입력한 STATEMENT_ID 파라미터|
|PLAN_ID|NUMBER|데이터베이스내에서 유일한 PLAN_TABLE 로우의 식별자(10G)|
|TIMESTAMP|DATE|EXPLAIN PLAN 이 생성된 날짜와 시간|
|REMARKS|VARCHAR2(80)|사용자입력 코멘트|
|OPERATION|VARCHAR2(30)|INDEX/TABLE ACCESS (4.5.6 참조)|
|OPTIONS|VARCHAR2(225)|UNIQUE SCAN/BY INDEX ROWID (4.5.6 참조)|
|OBJECT_NODE|VARCHAR2(128)|원격지의 객체를 참조하는 데이터베이스링크 이름. 병렬처리 로컬 쿼리의 경우는 출력의 순서를 의미|
|OBJECT_OWNER|VARCHAR2(30)|OBJECT를 담고 있는 스키마의 소유주 이름|
|OBJECT_NAME|VARCHAR2(30)|객체이름(EX:TABLE,INDEX)|
|OBJECT_INSTANCE|NUMERIC|본 쿼리문에 나열된 객체들을 좌에서 우,바깥에서 안쪽으로 순서를 부여한 번호|
|OBJECT_ALIAS|VARCHAR2(65)|객체 별칭|
|OBJECT_TYPE|VARCHAR2(30)|객체에 대한 설명적 정보 (EX:UNIQUE,NON-UNIQUE)|
|OPTIMIZER|VARCHAR2(255)|옵티마이져 모드|
|TIME|NUMBER(20,2)|옵티마이져에 의해 예상된 경과시간. RBO에서는 값이 없다.|
|ACCESS_PREDICATES|VARCHAR2(4000)|엑세스 조건 (EX:"B"."DEPTNO"=10)|
|FILTER_PREDICATES|VARCHAR2(4000)|체크조건 (EX:"A"."DEPTNO"=10)|
|TEMP_SPACE|NUMERIC|옵티마이져가 예측한 임시사용공간(BYTE)|
|COST|NUMERIC|옵티마이져가 예측한 실행비용. 비용은 테이블 엑세스 동작에만 한정된 것은 아니다. 이 칼럼의 값은 특별히 측정단위를 갖는 것은 아니다.
실행계획의 비용을 비교하기 위해 사용되는 가중치를 갖을뿐이다. CPU_COST와 IO_COST의 값 계산에 역할을 한다.|
|CARDINALITY|NUMERIC|옵티마이져가 예측한 엑세스될 로우 수|
|BYTES|NUMERIC|옵티마이져가 예측한 엑세스될 바이트 수|
|IO_COST|NUMERIC|옵티마이져가 예측한 IO COST. 읽혀진 데이터블럭 수의 비례치이다. RBO에서는 NULL이다.|
|CPU_COST|NUMERIC|옵티마이져가 예측한 CPU COST. 읽혀진 SYSTEM CYCLES 수의 비례치이다. RBO에서는 NULL이다.|

h3. 4.5.6. OPERATION and OPTIONS Values Produced by EXPLAIN PLAN

||기 능|| 옵 션|| 설 명||
|AGGREGATE|GROUP BY|그룹함수(sum, count 등)를 사용하여 한의 로우가 추출되도록 처리(7버전에서만 표시)|
|AND-EQUAL| |인덱스 머지(Merge)를 이용하는 경우 주로나타난다. 복수개의 ROWID집합을 받아들일 때, 집합들간의 교집합을 반환할 때
,중복로우를 제거할 때 나타나는 OPERATION이다.|
|BITMAP|CONVERSION TO ROWIDS
CONVERSION FROM ROWIDS
CONVERSION COUNT
INDEX SINGLE VALUE
INDEX RANGE SCAN
INDEX FULL SCAN
MERGE
MINUS
OR
AND
KEY ITERATION|비트맵 표현식을 실제 ROWID로 변환한다.
ROWID를 비트맵 표현식으로 변환한다.
ROWID 갯수를 반환한다.
인덱스에서 싱클키에 대한 비트맵을 검색한다.
키값 범위에 맞는 비트맵을 검색한다.
비트맵을 풀스캔한다
범위스캔의 결과로 나온 복수개의 비트맵을 하나의 비트맵으로 합친다.
비트맵의 비트값을 빼는 연산을 한다. 부정형 조건이 기술된 경우 나타난다.
두개의 비트맵에 대해 OR연산을 한다.
두개의 비트맵에 대해 AND연산을 한다.
테이블에서 각각의 로우를 취해 비트맵 인덱스에서 대응되는 비트맵을 찾은 후 BITMAP MERGE 에 의해 하나의 비트맵으로 만든다|
|CONNECT BY| | CONNECT BY 를 사용하여 트리(Tree)구조로전개|
|CONCATENATION| |단위 액세스에서 추출한 로우들의 합집합을 생성|
|COUNTING| | 테이블의 로우스를 센다|
|FILTER| |선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업|
|FIRST ROW| |조회 로우 중에 첫번째 로우만 추출한다.|
|FOR UPDATE| |선택된 로우에 LOCK을 지정한다.|
|INDEX|RANGE SCAN
RANGE SCAN DESCENDING
FULL SCAN
FULL SCAN DESCENDING
FAST FULL SCAN
SKIP SCAN|인덱스의 일정범위 ROWID를 오름차순 검색.
인덱스의 일정범위 ROWID를 내림차순 검색
인덱스에 있는 모든 ROWID를 오름차순 검색한다.
인덱스에 있는 모든 ROWID를 내림차순 검색한다.
멀티블럭 I/O를 통해 인덱스된 칼럼만을 대상으로 모든 ROWID 검색한다. CBO에서만 가능.
결합인덱스의 첫번째 칼럼의 값이 없을때 인덱스의 ROWID를 검색한다. 9i. CBO에서만 가능|
|INTERSECTION| |교집합의 로우를 추출한다. (같은 값이 없다.)|
|MERGE JOIN|.
OUTER
ANTI
SEMI
CARTESIAN|두개의 집합이 각각 특정 값으로 정렬되어 있으며,한 집합의 로우는 매칭되는 다른 집합의 로우와 결합시킨 결과를 반환한다.
먼저 자신의 조건만으로 액세스한 후 각각을 SORT하여 머지(Merge)해 가는 조인 outer join을 한다.
머지안티조인
머지세미조인
조인조건 없는 머지조인|
|MINUS| | MINUS 함수를 사용한다. |
|NESTED LOOPS|OUTER|먼저 어떤 드라이빙(Driving) 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인, outer join을 한다.|
|PROJECTION| |내부적인 처리의 일종|
|REMOTE| |다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해 DataBase Link를 사용하는 경우|
|SEQUENCE| |Sequence 를 액세스한다.|
|SORT|AGGREGATE
UNIQUE
GROUP BY
JOIN
ORDER BY|선택된 로우들을 그룹함수를 이용하여 하나의 로우로 만들때
결과집합 정렬하여 중복로우를 제거할 때
결과집합을 정렬하여 GROUP BY 절을 사용하여 그룹핑할 때
결과집합을 정렬하여 MERGE JOIN할 때
ORDER BY 를 위한 SORT|
|TABLE ACCESS\\(물리뷰의 경우 \\MAT_VIEW REWITE ACCESS
\\로 나타난다)|FULL
SAMPLE
CLUSTER
HASH
BY ROWID RANGE
SAMPLE BY ROWID RANGE
BY USER ROWID
BY INDEX ROWID
BY GLOBAL INDEX ROWID
BY LOCAL INDEX ROWID|테이블의 모든 로우를 검색한다.
테이블에서 샘플로우만을 검색한다.
indexed cluster key 값에 근거하여 로우를 검색한다.
hash cluster key 값에 근거하여 로우를 검색한다.
ROWID 범위검색
ROWID 범위로 샘플데이터 검색
테이블의 로우가 사용자가 제공한 ROWID에 위치하고 있을 때 사용
파티션되지 않은 테이블에 인덱스를 이용하여 로우 검색
파티션된 테이블에서 글로벌인덱스를 이용하여 로우검색
파티션된 테이블에서 로컬인덱스를 이용하여 로우검색 |
|UNION| | 두집합의 합집합을 구한다.(중복없음)
항상 전체범위처리를 한다.|
|UNION ALL| |두집합의 합집합을 구한다.(중복없음)
UNION 과는 다르게 부분범위 처리를 한다.|
|VIEW| |어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.[주로 서브쿼리(Subquery)에 의해 수행된 결과]


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

h2. 문서에 대하여

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