DBMS_XPLAN.DISPLAY_CURSOR 결과 보는 법

출처 : http://www.bysql.net/index.php?document_srl=18171

1. 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점

• Query Block Name / Object Alias : 쿼리 블록 정보
• Outline Date : 오라클 내부 Hint
• Predicate Information : Access 조건 및 조인 조건, Filter 조건
• Column Projection Information : Operation Id 별로 Select된 컬럼 정보
• Format : 자신에게 맞는 Format 설정이 자유로움
  

2. DBMS_XPLAN

• plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력
  
• 제공 Function
  
• DISPLAY(9i) : 예상실행 계획
  
• DISPLAY_CURSOR(10g) : SHARED POOL에 올라가 있는 실제 실행계획
  
• DISPLAY_AWR(10g) : 과거의 실행계획이 저장
  
• DISPLAY_SQLSET(10g) : 튜닝된 실행계획을 보관
  
• DISPLAY_SQL_PLAN_BASELINE(11g) : 실행가능한 후보 실행계획
• DISPLAY_PLAN(11g) : Return the last plan, or a named plan, explained as CLOB
  
• BUILD_PLAN_XML(11g) : Return the last plan, or a named plan, explained as XML
  
• 사용하기위해 필요한 권한
• V$SESSION
• V$SQL_PLAN
• V$SQL(optional)
• V$SQL_PLAN_STATISTICS_ALL
  
• 참조
• 오라클 성능 고도화원리와 해법 CHAPTER3 - 04 DBMS_XPLAN 패키지
  
• 오라클클럽 문서(http://www.gurubee.net/pages/viewpage.action?pageId=4784265&)
• http://www.oracle-base.com/articles/9i/DBMS_XPLAN.php
  
• http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_xplan.htm
  

3. DBMS_XPLAN.DISPLAY_CURSOR

• Function Parameter
• FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
                          CURSOR_CHILD_NO INTEGER DEFAULT 0,
                          FORMAT VARCHAR2 DEFAULT 'TYPICAL')
• 파라미터 설명
  

파라미터	설명
SQL_ID	실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
	SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
	SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
CHILD_NUMBER	해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
	CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT	저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터

• FORMAT 옵션
  

  구분	FORMAT	내용
  예측 내용	BASIC	가장 기본적인 정보만 보여준다.
  	TYPICAL	FORMAT의 DEFAULT 값인 TYPICAL은 SQL 투닝에 필요한 NORMAL한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 PREDICATE INFORMATION이 제공된다.
  	ALL	TYPICAL  FORMAT에 QUERY BLOCK NAME과 COLUMN PROJECTION INFORMATION이 추가로 제공된다.
  	OUTLINE	TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
  	ADVANCE	ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
  실측 내용	ALLSTATS	실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		수행횟수에 따라 누적된 값을 보여준다.
  	ALLATATS LAST	실제 엑스스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		마지막에 수행된 값을 보여준다.
  	ADVANCED ALLSTATS LSAT	DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.

4. DBMS_XPLAN.DISPLAY_CURSOR 사용방법

• PLAN 정보 기록
• 'GATHER_PLAN_STATISTICS' HINT 사용

  SELECT /*+GATHER_PLAN_STATISTICS*/                  *   FROM (SELECT E.*           FROM EMPLOYEE E          WHERE E.DEPARTMENT_ID = 50          ORDER BY E.EMPLOYEE_ID)  WHERE ROWNUM <= 100

• STSTISTICS_LEVEL를 ALL로 설정
• '_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정\
• PLAN 정보 조회

  SELECT *    FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))

  
  

5. Plan 상의 항목들에 대한 설명

------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                   |      1 |        |       |     4 (100)|          |     45 |00:00:00.01 |       3 |       |       |          | |*  1 |  COUNT STOPKEY                 |                   |      1 |        |       |            |          |     45 |00:00:00.01 |       3 |       |       |          | |   2 |   VIEW                         |                   |      1 |     45 |  5985 |     4  (25)| 00:00:01 |     45 |00:00:00.01 |       3 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY       |                   |      1 |     45 |  3105 |     4  (25)| 00:00:01 |     45 |00:00:00.01 |       3 |  6144 |  6144 | 6144  (0)| |   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      1 |     45 |  3105 |     3   (0)| 00:00:01 |     45 |00:00:00.01 |       3 |       |       |          | |*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      1 |     45 |       |     1   (0)| 00:00:01 |     45 |00:00:00.01 |       1 |       |       |          | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    4 - SEL$2 / E@SEL$2    5 - SEL$2 / E@SEL$2   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')       DB_VERSION('11.2.0.1')       ALL_ROWS       OUTLINE_LEAF(@"SEL$2")       OUTLINE_LEAF(@"SEL$1")       NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")       INDEX_RS_ASC(@"SEL$2" "E"@"SEL$2" ("EMPLOYEE"."DEPARTMENT_ID"))       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(ROWNUM<=100)    3 - filter(ROWNUM<=100)    5 - access("E"."DEPARTMENT_ID"=50)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "from$_subquery$_001"."EMPLOYEE_ID"NUMBER,22, "from$_subquery$_001"."FIRST_NAME"VARCHAR2,20, "from$_subquery$_001"."LAST_NAME"VARCHAR2,25,        "from$_subquery$_001"."EMAIL"VARCHAR2,25, "from$_subquery$_001"."PHONE_NUMBER"VARCHAR2,20, "from$_subquery$_001"."HIRE_DATE"DATE,7,        "from$_subquery$_001"."JOB_ID"VARCHAR2,10, "from$_subquery$_001"."SALARY"NUMBER,22, "from$_subquery$_001"."COMMISSION_PCT"NUMBER,22,        "from$_subquery$_001"."MANAGER_ID"NUMBER,22, "from$_subquery$_001"."DEPARTMENT_ID"NUMBER,22    2 - "from$_subquery$_001"."EMPLOYEE_ID"NUMBER,22, "from$_subquery$_001"."FIRST_NAME"VARCHAR2,20, "from$_subquery$_001"."LAST_NAME"VARCHAR2,25,        "from$_subquery$_001"."EMAIL"VARCHAR2,25, "from$_subquery$_001"."PHONE_NUMBER"VARCHAR2,20, "from$_subquery$_001"."HIRE_DATE"DATE,7,        "from$_subquery$_001"."JOB_ID"VARCHAR2,10, "from$_subquery$_001"."SALARY"NUMBER,22, "from$_subquery$_001"."COMMISSION_PCT"NUMBER,22,        "from$_subquery$_001"."MANAGER_ID"NUMBER,22, "from$_subquery$_001"."DEPARTMENT_ID"NUMBER,22    3 - (#keys=1) "E"."EMPLOYEE_ID"NUMBER,22, "E"."DEPARTMENT_ID"NUMBER,22, "E"."FIRST_NAME"VARCHAR2,20, "E"."LAST_NAME"VARCHAR2,25,        "E"."EMAIL"VARCHAR2,25, "E"."PHONE_NUMBER"VARCHAR2,20, "E"."HIRE_DATE"DATE,7, "E"."JOB_ID"VARCHAR2,10, "E"."SALARY"NUMBER,22,        "E"."COMMISSION_PCT"NUMBER,22, "E"."MANAGER_ID"NUMBER,22    4 - "E"."EMPLOYEE_ID"NUMBER,22, "E"."FIRST_NAME"VARCHAR2,20, "E"."LAST_NAME"VARCHAR2,25, "E"."EMAIL"VARCHAR2,25, "E"."PHONE_NUMBER"VARCHAR2,20,        "E"."HIRE_DATE"DATE,7, "E"."JOB_ID"VARCHAR2,10, "E"."SALARY"NUMBER,22, "E"."COMMISSION_PCT"NUMBER,22, "E"."MANAGER_ID"NUMBER,22,        "E"."DEPARTMENT_ID"NUMBER,22    5 - "E".ROWIDROWID,10, "E"."DEPARTMENT_ID"NUMBER,22

1) Basics 항목

• Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함
• Operation : 각각 실행되는 JOB
• Name : Operationdl 엑세스 하는 Table 및 Index

2) Query Optimizer Estimations 항목(예상치)

• E-Rows : 각 Operation이 끝났을 때 return 되는 건수.
• E-Bytes : 각 Operation이 Temporany Space를 사용한 양
• Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치.
• E-Time : 수행시간

3) Runtime Statistics 항목

• Starts : 각 Operation을 반복 수행한 건수
• A-Rows : 각 Operation이 Return 한 건수
• A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF).  Child Operation의 A-Time을 합친 누적치

4) I/O Statistics

• Buffers : 각 Operation이 memory에서 읽은 Block 수.
• Reads : 각 Operation이 Disk에서 Read한 Block 수.
• Writes : 각 Operation이 Disk에서 White한 Block 수.

5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)

• OMen : Optimal Execution에 필요한 Memory
• SQL 실행 메모리가 최적의 크기를 가졌을때의 메모리. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다.
• 1Mem : One-pass Execution에 필요한 Momory
• SQL 실행 메모리가 1 pass의 크기를 가졌을 때의 메모리. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다.
• O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
              O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 
              사용하지 않고 처리 되었다는 의미임. 
• multipass 횟수 : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 횟수.
• Used-mem : 마지막 실행 시 사용한 PGA -Memory
• Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함.
• Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.

 

6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.,

7) Outline Date : 오라클이 내부적으로 사용한 힌트.

8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출

9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보.

6. 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.

SELECT *   FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +PREDICATE'))

• Format : 'allstats last -rows +predicate'로 설정
• 예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력

-------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name              | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  1 |  COUNT STOPKEY                 |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |   2 |   VIEW                         |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY       |                   |      1 |     45 |00:00:00.01 |       3 |  6144 |  6144 | 6144  (0)| |   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      1 |     45 |00:00:00.01 |       1 |       |       |          | --------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(ROWNUM<=100)    3 - filter(ROWNUM<=100)    5 - access("E"."DEPARTMENT_ID"=50)

7.쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블럭과 힌트정보를 추가로 출력

SELECT *   FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +ALIAS +OUTLINE +PREDICATE'))

• Format : 'allstats last -rows +alias +outline +predicate'로 설정

• Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력
• +alias : 쿼리블록 추가
• +outline : 오라클리 내부적으로 사용한 힌트정보를 출력

-------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name              | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  1 |  COUNT STOPKEY                 |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |   2 |   VIEW                         |                   |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY       |                   |      1 |     45 |00:00:00.01 |       3 |  6144 |  6144 | 6144  (0)| |   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      1 |     45 |00:00:00.01 |       3 |       |       |          | |*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      1 |     45 |00:00:00.01 |       1 |       |       |          | --------------------------------------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    4 - SEL$2 / E@SEL$2    5 - SEL$2 / E@SEL$2   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')       DB_VERSION('11.2.0.1')       ALL_ROWS       OUTLINE_LEAF(@"SEL$2")       OUTLINE_LEAF(@"SEL$1")       NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")       INDEX_RS_ASC(@"SEL$2" "E"@"SEL$2" ("EMPLOYEE"."DEPARTMENT_ID"))       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(ROWNUM<=100)    3 - filter(ROWNUM<=100)    5 - access("E"."DEPARTMENT_ID"=50)