DBMS_XPLAN

DBMS_XPLAN 패키지의 기본 개념과 패키지 내의 프러시저와 함수를 사용하는 방법을 설명합니다.

개요

DBMS_XPLAN은 플랜 정보와 플랜의 수행 정보들에 대해 사용자가 원하는 항목을 선택해서 다양한 포맷으로 출력할 수 있는 기능을 제공합니다.

출력 방식은 pipelined function 기능을 이용하여 TABLE() 구문을 통해 쿼리의 결과로 해당 정보들을 출력합니다. 또한 출력 정보는 V$SQL_PLAN, V$SQL_PLAN_STATISTICS 뷰를 통해 얻기 때문에 GATHER_SQL_PLAN_STAT 파라미터가 켜져 있어야만 수행 정보를 정상적으로 출력할 수 있습니다.

함수

본 절에서는 DBMS_XPLAN 패키지에서 제공하는 프러시저와 함수를 알파벳 순으로 설명합니다.

DISPLAY

EXPLAIN PLAN FOR 구문을 사용하여 수행한 SQL 중 가장 나중에 수행한 SQL의 플랜을 조회하는 함수입니다. pipelined function이기 때문에 TABLE() 함수를 사용합니다.

DISPLAY 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_XPLAN.DISPLAY
(
    format VARCHAR2 default 'BASIC LAST SQL'
)
RETURN dbms_xplan_type_table pipelined;

• 파라미터

파라미터	IN / OUT	설명
format	IN	출력하고자 하는 항목을 명시 항목의 종류에는 개별 항목과 개별 항목 여러 개를 설정하는 그룹 항목이 있음 항목 이름 앞에 하이픈(-)를 붙여 해당 항목을 제외 가능함

• 개별 출력 항목

항목	설명
CARDS	optimizer에서 예측한 해당 플랜 노드의 row 수
COST	optimizer에서 예측한 해당 플랜 노드의 cost
PARTITION	파티션 관련 정보
PREDICATE	플랜 노드별 predicate 정보
REMOTE	플랜 노드별 데이터베이스 링크에 수행한 쿼리 내용
ROWS	해당 플랜 노드에서 실제 수행된 row 수
ELAPTIME	해당 플랜 노드에서 실제 수행된 시간
USEDMEM	해당 플랜 노드에서 실제 사용된 메모리 양
TEMPREAD	해당 플랜 노드에서 실제 사용된 temp read 횟수
TEMPWRITE	해당 플랜 노드에서 실제 사용된 temp write 횟수
BUFGETS	해당 플랜 노드에서 실제 요청한 buffer get 횟수
STARTS	해당 플랜 노드가 실제 재시작된 횟수
LAST	수행 정보 값을 마지막 수행 정보 값 지정하지 않은 경우 모든 수행에 대해 누적된 수행 정보 값
PRECISE	CARDS, ROWS에 대해 뒷 자리수들을 버림하지 않은 실제 값
HEADER	플랜의 기본 정보(sql id, child number, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행 시간 등)
SQL	플랜 생성에 사용된 쿼리문
DISKREADS	해당 플랜 노드에서 실제 수행된 디스크 I/O 횟수

• 그룹 출력 항목

항목	설명
IOSTATS	수행 정보 중 IO와 관련된 모든 항목 TEMPREAD + TEMPWRITE + BUFGETS + DISKREADS
MEMSTATS	수행 정보 중 메모리와 관련된 모든 항목 USEDMEM
ALLSTATS	수행 정보 중 IO, 메모리와 관련된 모든 항목 IOSTATS + MEMSTATS
BASIC	출력 기본 포맷 optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간 CARDS + COST + PART + ELAPTIME + LAST
TYPICAL	BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보를 포함 BASIC + ROWS + STARTS + PRED + REMOTE + PRECISE
ALL	TYPICAL 포맷에 추가로 ALLSTATS 항목을 포함 TYPICAL + ALLSTATS

• 예제

다음은 마지막으로 EXPLAIN PLAN FOR 구문으로 수행한 쿼리의 플랜 정보를 보는 예입니다.

SQL> EXPLAIN PLAN FOR SELECT 'TEST1_XPLAN' FROM DUAL;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------

SQL ID           : 0mrct16s5m35r
CHILD NUMBER     : 12578
HASH VALUE       : 2958658743
PLAN HASH VALUE  : 56788869
EXECUTIONS       : 0
FETCHES          : 0
LOADED AT        : 2025/04/21 13:32:55
TOT ELAPSED TIME : 00:00:00.0000
TOT CR GETS BLOCK: 0
TOT BUFFER GETS  : 0
SQL              :
SELECT 'TEST1_XPLAN' FROM DUAL

--------------------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name     | Cost (%CPU) | Cards | Elaps. Time |
--------------------------------------------------------------------------------------------------------------------------------
|  1 | DPV       | _VT_DUAL |     1  (0)  |    1  |00:00:00.0000|
--------------------------------------------------------------------------------------------------------------------------------

21 rows selected.

DISPLAY_CURSOR

Physical Plan Cache에 등록되어 있는 플랜에 대해 SQL_ID 값을 통해 조회하는 함수입니다. pipelined function이기 때문에 TABLE() 함수를 사용합니다.

DISPLAY_CURSOR 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_XPLAN.DISPLAY_CURSOR
(
    in_sql_id       VARCHAR2 default NULL,
    in_child_no     NUMBER default NULL,
    format          VARCHAR2 default 'BASIC LAST SQL'
) 
RETURN dbms_xplan_type_table pipelined;

• 파라미터

파라미터	IN / OUT	설명
in_sql_id	(IN)	조회하려는 플랜의 SQL_ID 이 값이 생략된 경우 해당 세션의 마지막 수행 쿼리의 SQL_ID 값을 사용하게 됨
in_child_no	(IN)	조회하려는 플랜의 CHILD_NUMBER 값 이 값이 생략된 경우 입력된 SQL_ID 값과 일치하는 모든 플랜을 조회 CHILD_NUMBER 값은 SQL_ID 가 입력된 경우에만 지정될 수 있음
format	(IN)	출력하고자 하는 항목을 명시 항목의 종류에는 개별 항목과 개별 항목 여러 개를 설정하는 그룹 항목이 있음 항목 이름 앞에 하이픈(-)를 붙여 해당 항목을 제외 가능함

• 개별 출력 항목

항목	설명
CARDS	optimizer에서 예측한 해당 플랜 노드의 row 수
COST	optimizer에서 예측한 해당 플랜 노드의 cost
PARTITION	파티션 관련 정보
PARALLEL	parallel execution 관련 정보
PREDICATE	플랜 노드별 predicate 정보
REMOTE	플랜 노드별 데이터베이스 링크에 수행한 쿼리 내용
ROWS	해당 플랜 노드에서 실제 수행된 row 수
ELAPTIME	해당 플랜 노드에서 실제 수행된 시간
USEDMEM	해당 플랜 노드에서 실제 사용된 메모리 양
TEMPREAD	해당 플랜 노드에서 실제 사용된 temp read 횟수
TEMPWRITE	해당 플랜 노드에서 실제 사용된 temp write 횟수
BUFGETS	해당 플랜 노드에서 실제 요청한 buffer get 횟수
STARTS	해당 플랜 노드가 실제 재시작된 횟수
LAST	수행 정보 값을 마지막 수행 정보 값 지정하지 않은 경우 모든 수행에 대해 누적된 수행 정보 값
PRECISE	CARDS, ROWS에 대해 뒷 자리수들을 버림하지 않은 실제 값
HEADER	플랜의 기본 정보(sql id, child number, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행 시간 등)
SQL	플랜 생성에 사용된 쿼리문
DISKREADS	해당 플랜 노드에서 실제 수행된 디스크 I/O 횟수

• 그룹 출력 항목

항목	설명
IOSTATS	수행 정보 중 IO와 관련된 모든 항목 TEMPREAD + TEMPWRITE + BUFGETS + DISKREADS
MEMSTATS	수행 정보 중 메모리와 관련된 모든 항목 USEDMEM
ALLSTATS	수행 정보 중 IO, 메모리와 관련된 모든 항목 IOSTATS + MEMSTATS
BASIC	출력 기본 포맷 optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간 CARDS + COST + PART + ELAPTIME + LAST
TYPICAL	BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보를 포함 BASIC + ROWS + STARTS + PRED + REMOTE + PRECISE
ALL	TYPICAL 포맷에 추가로 ALLSTATS 항목을 포함 TYPICAL + ALLSTATS

• 예제

다음은 현재 세션에서 마지막으로 수행한 쿼리의 플랜 정보를 보는 예입니다.

select * from table(dbms_xplan.display_cursor);

다음은 기본 포맷에서 파티션 정보를 제외하고 SQL_ID 'cp2tt6ptgqws0', CHILD_NUMBER 16449인 플랜 정보를 보는 예입니다.

set pagesize 300
set linesize 400
drop table t1;
drop table t2;
drop table t3;

create table t1 as select level as a from dual connect by level<=300000;
create table t2 as select level as a from dual connect by level<=300000;
create table t3 as select level as a from dual connect by level<=300000;

ALTER SESSION SET GATHER_SQL_PLAN_STAT =Y;

set autot traceonly planstat
select t1.a, t2.a from t1,t2 where t1.a = t2.a union select t2.a, t3.a from t2,t3 where t2.a=t3.a;
set autot off

select * from table(dbms_xplan.display_cursor('cp2tt6ptgqws0', 16449, 'SQL +HEADER'));

COLUMN_VALUE                                                                                      
--------------------------------------------------------------------------------------------------

SQL ID         : cp2tt6ptgqws0
CHILD NUMBER   : 16449
HASH VALUE     : 1929081600
PLAN HASH VALUE: 2145193847
EXECUTIONS     : 1
FETCHES        : 69
LOADED AT      : 2025/11/18 10:37:33
TOT ELAPSED TIME: 00:00:00.5723
AVG ELAPSED TIME: 00:00:00.5723
TOT CR GETS BLOCK: 2003
AVG CR GETS BLOCK: 2003
TOT BUFFER GETS: 2003
AVG BUFFER GETS: 2003
SQL            :
select t1.a, t2.a from t1,t2 where t1.a = t2.a union select t2.a, t3.a from t2,t3 where t2.a=t3.a
                                                                               
----------------------------------------
| ID  | Operation               | Name |
----------------------------------------
|   1 | UNION                   |      |
|   2 |  DISTINCT (SORT)        |      |
|   3 |   HASH JOIN             |      |
|   4 |    TABLE ACCESS (FULL)  | T2   |
|   5 |    TABLE ACCESS (FULL)  | T1   |
|   6 |  DISTINCT (SORT)        |      |
|   7 |   HASH JOIN             |      |
|   8 |    TABLE ACCESS (FULL)  | T3   |
|   9 |    TABLE ACCESS (FULL)  | T2   |
----------------------------------------

Note                                                                           
----------------------------------------
   4 - dynamic sampling used for this table (55 blocks)
   5 - dynamic sampling used for this table (55 blocks)
   8 - dynamic sampling used for this table (52 blocks)

38 rows selected.

다음은 마지막 수행 시간, 수행 결과 row, 요청 buffer gets 값을 보는 예입니다.

select * from table(dbms_xplan.display_cursor('cp2tt6ptgqws0',16449,'ROWS ELAPTIME +BUFGETS'));

COLUMN_VALUE                                                                 
-----------------------------------------------------------------------------
                                                                             
-----------------------------------------------------------------------------
| ID  | Operation               | Name | Rows     | Elaps. Time  | BUFGETS  |
-----------------------------------------------------------------------------
|   1 | UNION                   |      |     300K |00:00:00.0406 |       0  |
|   2 |  DISTINCT (SORT)        |      |     300K |00:00:00.1015 |       0  |
|   3 |   HASH JOIN             |      |     300K |00:00:00.1513 |       0  |
|   4 |    TABLE ACCESS (FULL)  | T2   |     300K |00:00:00.0068 |     505  |
|   5 |    TABLE ACCESS (FULL)  | T1   |     300K |00:00:00.0074 |     505  |
|   6 |  DISTINCT (SORT)        |      |     300K |00:00:00.1011 |       0  |
|   7 |   HASH JOIN             |      |     300K |00:00:00.1497 |       0  |
|   8 |    TABLE ACCESS (FULL)  | T2   |     300K |00:00:00.0013 |     486  |
|   9 |    TABLE ACCESS (FULL)  | T3   |     300K |00:00:00.0085 |     507  |
-----------------------------------------------------------------------------
 
LAST ELAPSED TIME: 00:00:00.5682
LAST CR GET BLOCK: 2003

다음은 수행 중 메모리 사용량과 디스크 I/O 값을 보는 예입니다.

SQL> SELECT * from table(dbms_xplan.display_cursor('cp2tt6ptgqws0',16449,'ALLSTATS -TEMPREAD -TEMPWRITE'));

COLUMN_VALUE
-------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| ID  | Operation               | Name | BUFGETS  | Used Mem |  Reads   |
-------------------------------------------------------------------------
|   1 | UNION                   |      |       0  |       0K |       0  |
|   2 |  DISTINCT (SORT)        |      |       0  |      45M |       0  |
|   3 |   HASH JOIN             |      |       0  |      24M |       0  |
|   4 |    TABLE ACCESS (FULL)  | T2   |     505  |       0K |     454  |
|   5 |    TABLE ACCESS (FULL)  | T1   |     505  |       0K |     454  |
|   6 |  DISTINCT (SORT)        |      |       0  |      45M |       0  |
|   7 |   HASH JOIN             |      |       0  |      24M |       0  |
|   8 |    TABLE ACCESS (FULL)  | T2   |     486  |       0K |       0  |
|   9 |    TABLE ACCESS (FULL)  | T3   |     507  |       0K |     454  |
-------------------------------------------------------------------------
 
LAST CR GET BLOCK: 2003

DISPLAY_TPR

TPR에 등록되어 있는 플랜에 대해 SQL_HASH_VALUE 또는 SQL_ID 값을 통해 조회하는 함수입니다. pipelined function이기 때문에 TABLE() 함수를 사용합니다.

DISPLAY_TPR 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_XPLAN.DISPLAY_TPR
(
    sql_hash_value  NUMBER,
    plan_hash_value NUMBER default NULL,
    instance#       NUMBER default NULL,
    format          VARCHAR2 default 'TYPICAL'
)
RETURN dbms_xplan_type_table pipelined;

DBMS_XPLAN.DISPLAY_TPR
(
    sql_id          VARCHAR,
    plan_hash_value NUMBER default NULL,
    instance#       NUMBER default NULL,
    format          VARCHAR2 default 'TYPICAL'
)
RETURN dbms_xplan_type_table pipelined;

• 파라미터

파라미터	IN / OUT	설명
sql_hash_value	(IN)	조회하려는 플랜의 SQL_HASH_VALUE
sql_id	(IN)	조회하려는 플랜의 SQL_ID
plan_hash_value	(IN)	조회하려는 플랜의 PLAN_HASH_VALUE 이 값이 생략된 경우 입력된 SQL_HASH_VALUE 값과 일치하는 모든 플랜을 조회
instance#	(IN)	조회하려는 플랜이 생성된 인스턴스 번호 이 값이 생략된 경우 V$DATABASE의 로컬 instance_id를 사용
format	(IN)	아래 3가지 포맷을 지원 BASIC : OPERATION ID, NAME만 출력하고 추가적으로 옵션 사용 가능 TYPICAL :기본적으로 OPERATION ID, NAME, CARDS, COST를 출력하고, 해당 내용이 있을 경우 BUFGETS, ROWS, ELAPTIME, PARTITION, PREDICATE 정보도 출력 (기본값) ALL : TYPICAL, REMOTE를 출력

• 개별 출력 항목

항목	설명
CARDS	optimizer에서 예측한 해당 플랜 노드의 row 수
COST	optimizer에서 예측한 해당 플랜 노드의 cost
BUFGETS	해당 플랜 노드에서 실제 요청한 buffer get 횟수
ROWS	해당 플랜 노드에서 실제 수행된 row 수
ELAPTIME	해당 플랜 노드에서 실제 수행된 시간
PARTITION	파티션 관련 정보
PREDICATE	플랜 노드별 predicate 정보
REMOTE	플랜 노드별 데이터베이스 링크에 수행한 쿼리 내용

• 예제

다음은 기본 포맷에서 SQL HASH VALUE 1929081600를 통해 플랜 정보를 보는 예입니다.

select * from table(dbms_xplan.display_tpr(1929081600));

COLUMN_VALUE                                                     
-------------------------------------------------------------------------------------------------

SQL HASH VALUE  : 1929081600                                     
PLAN HASH VALUE : 103011050                                      
SQL TEXT        :                                                
                                                                                                 
select t1.a, t2.a from t1,t2 where t1.a = t2.a union select t2.a, t3.a from t2,t3 where t2.a=t3.a
                                                                 
-----------------------------------------------------------------
| ID  | Operation               | Name | Cards   | Cost   (%CPU)|
-----------------------------------------------------------------
|   1 | UNION                   |      |  67039  |   95   (7.37)|
|   2 |  DISTINCT (SORT)        |      |  33520  |   45   (2.22)|
|   3 |   HASH JOIN             |      |  33520  |   44      (0)|
|   4 |    TABLE ACCESS (FULL)  | T2   |  26442  |   22      (0)|
|   5 |    TABLE ACCESS (FULL)  | T1   |  26442  |   22      (0)|                                
|   6 |  DISTINCT (SORT)        |      |  33520  |   45   (2.22)|                                
|   7 |   HASH JOIN             |      |  33520  |   44      (0)|
|   8 |    TABLE ACCESS (FULL)  | T3   |  26442  |   22      (0)|
|   9 |    TABLE ACCESS (FULL)  | T2   |  26442  |   22      (0)|
-----------------------------------------------------------------
                                                                 
Predicate Information                                            
-----------------------------------------------------------------
    3 - access: ("T1"."A" = "T2"."A") (0.000)                    
    7 - access: ("T2"."A" = "T3"."A") (0.000)                    
                                                                                                 
Note                                                             
-----------------------------------------------------------------
    4 - dynamic sampling used for this table (52 blocks)         
    5 - dynamic sampling used for this table (52 blocks)         
    8 - dynamic sampling used for this table (52 blocks)         


SQL HASH VALUE  : 1929081600                                                                     
PLAN HASH VALUE : 265950847                                                                      
SQL TEXT        :                                                                                
                                                                                                 
select t1.a, t2.a from t1,t2 where t1.a = t2.a union select t2.a, t3.a from t2,t3 where t2.a=t3.a
                                                                                                 
-----------------------------------------------------------------                                
| ID  | Operation               | Name | Cards   | Cost   (%CPU)|                                
-----------------------------------------------------------------                                
|   1 | UNION                   |      |  67039  |   95   (7.37)|                                
|   2 |  DISTINCT (SORT)        |      |  33520  |   45   (2.22)|
|   3 |   HASH JOIN             |      |  33520  |   44      (0)|
|   4 |    TABLE ACCESS (FULL)  | T2   |  26442  |   22      (0)|
|   5 |    TABLE ACCESS (FULL)  | T1   |  26442  |   22      (0)|
|   6 |  DISTINCT (SORT)        |      |  33520  |   45   (2.22)|
|   7 |   HASH JOIN             |      |  33520  |   44      (0)|
|   8 |    TABLE ACCESS (FULL)  | T3   |  26442  |   22      (0)|
|   9 |    TABLE ACCESS (FULL)  | T2   |  26442  |   22      (0)|
-----------------------------------------------------------------
  
Predicate Information
-----------------------------------------------------------------
    3 - access: ("T1"."A" = "T2"."A") (0.000)
    7 - access: ("T2"."A" = "T3"."A") (0.000)
  
Note
-----------------------------------------------------------------
    4 - dynamic sampling used for this table (52 blocks)
    5 - dynamic sampling used for this table (52 blocks)
    8 - dynamic sampling used for this table (52 blocks)

...--------------------------------
3 - access: ("T1"."A" = "T2"."A") (0.000)
7 - access: ("T2"."A" = "T3"."A") (0.000)

Note
--------------------------------------------------------------------------------
4 - dynamic sampling used for this table (98 blocks)
5 - dynamic sampling used for this table (98 blocks)
9 - dynamic sampling used for this table (98 blocks)

주의

첫 번째 파라미터 값으로 ＇1929081600＇처럼 사용 시 VARCHAR 타입인 SQL_ID로 인식됩니다.

SQL HASH VALUE를 입력하고자 하면 ＇ ＇ 없이 사용하거나, sql_hash_value=>'1929081600'와 같이 명시적으로 파라미터를 지정합니다.