행위

"XPLAN"의 두 판 사이의 차이

DB CAFE

(→‎DBMS_XPLAN)
146번째 줄: 146번째 줄:
 
- 수행횟수에 따라 누적된 값을 보여준다.
 
- 수행횟수에 따라 누적된 값을 보여준다.
  
−
'''ALLATATS LAST'''
+
'''ALLSTATS LAST'''
 
- 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
 
- 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  

2020년 11월 20일 (금) 10:26 판

thumb_up 추천메뉴 바로가기

1 DBMS_XPLAN 패키지[편집]

1.1 DIFF_PLAN 함수[편집]

플랜비교

1.2 DISPLAY 함수[편집]

플랜 내용 표시

1.3 DISPLAY_AWR 함수[편집]

AWR에 저장된 실행플랜 내용 표시

1.4 DISPLAY_CURSOR 함수[편집]

현재 커서 캐시에 있는 실행 플랜 표시

1.5 DISPLAY_PLAN 함수[편집]

CLOB 출력 유형을 사용하여 플랜 테이블의 내용을 다양한 형식으로 표시.

1.6 DISPLAY_SQL_PLAN_BASELINE 함수[편집]

SQL플랜 BASELINE의 지정된 SQL 핸들에 대한 하나 이상의 실행플랜 표시.

1.7 DISPLAY_SQLSET 함수[편집]

SQL튜닝 세트에 저장된 실행된 SQL문의 실행플랜 표시.

2 DBMS_XPLAN.DISPLAY[편집]

3 DBMS_XPLAN.DISPLAY_CURSOR[편집]

3.1 DBMS_XPLAN.DISPLAY_CURSOR 사용을 위한 권한 부여[편집]

GRANT SELECT ON V_$SESSION TO HR;
GRANT SELECT ON V_$SQL TO HR;
GRANT SELECT ON V_$SQL_PLAN TO HR;

3.2 플랜 확인 절차[편집]

3.2.1 선택 권한 부여[편집]

GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR;

3.2.2 STATISTICS_LEVEL 세션 적용[편집]

ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 현재 세션 변경 
ALTER SESSION SET CURRENT_SCHEMA = 스키마명;

SELECT * 
  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ADVANCED'));
  • 1번째 파라메터는 SQL_ID 임 , NULL 일경우 바로 전에 수행한 SQL
  • 2번째 파라메터는 CHILD_NUMBER 임
SELECT *
   FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))  
   -- ( NULL, NULL, 'ADVANCED ALLSTATS LAST' -rows )  '-rows 추가시 예측 rows 생략
  • 참고) 바로전 수행 한 SQL_ID 찾기
SELECT SQL_ID,CHILD_NUMBER,SQL_TEXT
   FROM V$SQL
  WHERE SQL_ID = (SELECT PREV_SQL_ID FROM V$SESSION 
                   WHERE AUDSID = USERENV('SESSIONID'))
    AND ROWNUM =1 ;
  • 참고) 메모리 Clear (※ 주의) 실제 운영 및 개발 환경에서 사용시 주의)
ALTER SYSTEM FLUSH BUFFER_CACHE; --데이터 버퍼 캐시 영역을 DBA에 의해 Flushing
ALTER SYSTEM FLUSH SHARED_POOL;  --공유 풀 영역을 Clear

4 DBMS_XPLAN.DISPLAY_CURSOR 결과 분석[편집]

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

4.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 설정이 자유로움

4.2 DBMS_XPLAN[편집]

- plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력

4.2.1 제공 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


 notifications_active[DBMS_XPLAN 사용하기 위해 필요한 권한]
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

4.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의 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으로 보여줄 지 결정하는 파라미터


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 정보를 보여준다.

- 수행횟수에 따라 누적된 값을 보여준다.

ALLSTATS LAST - 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.

- 마지막에 수행된 값을 보여준다.

ADVANCED ALLSTATS LSAT - DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.


4.4 DBMS_XPLAN.DISPLAY_CURSOR 사용방법[편집]

1) 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
2) STSTISTICS_LEVEL를 ALL로 설정

'_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정\ 

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

4.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 되는 컬럼의 정보.


4.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)
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=XPLAN&oldid=5030"