본문 바로가기
DBA

DBMS_XPLAN.DISPLAY_CURSOR 결과 보는 법

by 엘리후 2024. 9. 12.

출처 : 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
  • 사용하기위해 필요한 권한
  • 참조

3. DBMS_XPLAN.DISPLAY_CURSOR

  • Function Parameter
  • 파라미터 설명
  • 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 정보 기록
  • PLAN 정보 조회
     SELECT *

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
  • 1Mem : One-pass Execution에 필요한 Momory
  • O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
                O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 
                사용하지 않고 처리 되었다는 의미임. 
  • 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'로 설정
--------------------------------------------------------------------------------------------------------------------------------
| 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)
저작자표시

'DBA' 카테고리의 다른 글

접근 방법을 결정하는 힌트절? USE_NL, USE_HASH  (0) 2024.09.13
MySQL8 - Semi Join 과 Anti Join - 안티조인 세미조인  (1) 2024.09.12
접근 순서를 결정하는 힌트절 ? ORDERED, LEADING  (0) 2024.09.12
[TIL] MySQL Hint  (0) 2024.09.12
인라인뷰 그리고 페이징  (1) 2024.09.12

관련글

댓글

티스토리툴바