changes.
| | 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 함수를 사용한다. |
| | | |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)를 참고했습니다. |
개발자, DBA가 함께 만들어가는 구루비 지식창고!