DBMS XPLAN 사용법

DBMS_XPLAN

아래 뷰에 대한 SELECT 권한 필요
1. DBA_HIST_SQL_PLAN
2. DBA_HIST_SQLTEXT
3. V$DATABASE

DBMS_XPLAN.DISPLAY

plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력
예상 수행 플랜 정보 제공

DBMS_XPLAN.DISPLAY_CURSOR

실제 실행 후 패치된 플랜정보

DBMS_XPLAN.DISPLAY_CURSOR 필요권한

V$SQL_PLAN
V$SESSION
V$SQL_PLAN_STATISTICS_ALL

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

실제 현장에서 튜너의 SQL 튜닝시 진행 절차

선택 권한 부여

GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR;

STATISTICS_LEVEL 세션 적용

ALTER SESSION SET STATISTICS_LEVEL = ALL;

현재 세션(스키마) 변경

ALTER SESSION SET CURRENT_SCHEMA = 스키마명;

플랜 조회

-- SQL 실행후 바로 플랜 조회 시 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,FORMAT => 'ALLSTATS LAST -ROWS'));

-- sql_id 와 child_number로 플랜 조회 시 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',child_number,FORMAT => 'ALLSTATS LAST -ROWS'));

-- 다양한 옵션들
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last -rows +alias +outline +predicate'));

1번째 파라메터는 SQL_ID 임 , NULL 일경우 바로 전에 수행한 SQL
2번째 파라메터는 CHILD_NUMBER 임

참고) 바로전 수행 한 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

파라미터 상세 설명

CHILD_NUMBER

해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
SQL_ID 값이 명시 될 경우에만 CHILD NUMBER 값을 지정할수 있다.

FORMAT

저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터

BASIC

가장 기본적인 정보만 보여준다. operation ID, the operation name 과 option.

TYPICAL

기본값 임.
계획에서 가장 관련성이 높은 정보를 표시합니다 (operation id, name and option, #rows, #bytes and optimizer cost).
Pruning, parallel and predicate information 는 적용 가능한 경우에만 표시.
PROJECTION, ALIAS 및 REMOTE SQL 정보는 제외 됨.

SERIAL

TYPICAL 같음 (다른점은 parallel information is not displayed, even if the plan executes in parallel.)

ALL

최대 사용자 레벨
TYPICAL 레벨과 추가 정보 (PROJECTION, ALIAS ,REMOTE SQL 정보)

ROWS

관련이있는 경우 옵티마저가 추정한 ROW수 표시
-ROWS 플랜상 E-ROWS 항목 생략

BYTES

관련이있는 경우 옵티마저가 추정한 BYTE수 표시

COST

관련이있는 경우 옵티마이져 COST 정보 출력

OUTLINE

TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.

PARTITION

partition pruning information 출력

PARALLEL

PX information (distribution method and table queue information)

PREDICATE

predicate section 출력

PROJECTION

projection section

ALIAS

"Query Block Name / Object Alias" section

REMOTE

distributed query (for example, remote from serial distribution and remote SQL)

NOTE

note section of the explain plan

IOSTATS

SQL 실행시 기본 플랜 통계가 수집된다고 가정 한다면(gather_plan_statistics 힌트를 사용하거나 statistics_level 매개 변수를 ALL로 설정하여),
이 포맷은 커서를 수행된 ALL의 IO 통계정보를 보여준다. (혹은 아래와 같이 LAST 만 )

MEMSTATS

PGA 메모리 관리가 활성화 된 경우 (즉, pga_aggregate_target 매개 변수가 0이 아닌 값으로 설정 됨)이 형식을 사용하면 메모리 관리 통계
(예 : 운영자의 실행 모드, 사용 된 메모리 양, 유출 된 바이트 수)를 표시 할 수 있습니다. 디스크 등).
이러한 통계는 해시 조인, sort 또는 일부 bitmap operator 와 같은 메모리 집약적 인 작업에만 적용됩니다.

ALLSTATS

'IOSTATS MEMSTATS' 단축키

LAST

기본값,, 플랜통계는 커서의 모든 실행결과를 보여줌. 마지막 실행된 통계정보 출력

ADVANCE

ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.

ALLSTATS

실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
수행횟수에 따라 누적된 값을 보여준다.

ALLSTATS LAST

실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
LAST는 마지막 실행된 통계정보 출력

ADVANCED ALLSTATS LAST

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

SQL Plan 실전 예시

SQL 플랜 조회 결과

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

(실전예시) 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.

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)

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

 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)

(실전예시) 병렬쿼리 플랜정보 조회

SELECT *
  FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE'))