by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

4.EXPLAIN_PLAN(실행계획)




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

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이다.

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)에 의해 수행된 결과]

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.