> For the complete documentation index, see [llms.txt](https://docs.tibero.com/tibero-manuals/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tibero.com/tibero-manuals/7.2.6.manuals/tbpsm-reference-guide/dbms_xplan.md).

# DBMS\_XPLAN

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

## **개요**

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

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

GATHER\_SQL\_PLAN\_STAT의 기본값은 N이고 세션 단위로 변경할 수 있습니다. 이 파라미터가 꺼진 상태에서 수행된 쿼리는 플랜 구조와 optimizer 예측값(Cards, Cost)은 출력되지만 노드별 수행 정보(Rows, Starts, Elaps. Time 등)가 모두 0으로 출력됩니다.

**DBMS\_XPLAN**의 함수는 모두 `dbms_xplan_type_table` 타입을 반환합니다. 이 타입은 최대 65532바이트 VARCHAR의 컬렉션이므로 TABLE() 구문으로 조회했을 때 컬럼 이름은 `COLUMN_VALUE`이고, 한 행이 출력 한 줄에 대응합니다. 패키지 스펙에는 이 밖에 패키지 내부 처리에만 사용하는 레코드/컬렉션 타입(`plan_record`, `plan_table`, `plan_record2`, `plan_table2`, `plan_hash_value_table`, `sql_text_table`)이 함께 선언되어 있습니다.

format 파라미터는 공백이나 콤마로 구분된 출력 항목 이름의 목록입니다. 항목 표기 규칙은 다음과 같습니다.

* `ITEM` 또는 `+ITEM`: 해당 항목을 출력에 포함합니다.
* `-ITEM`: 해당 항목을 출력에서 제외합니다.
* 그룹 항목(BASIC, TYPICAL, ALL)은 `+`나 `-` 없이 이름만 사용합니다. `+TYPICAL`처럼 쓰면 유효하지 않은 항목으로 처리됩니다.
* 정의되지 않은 항목 이름이 하나라도 있으면 플랜을 출력하지 않고 `Error: input format '<format>' is not valid.` 한 줄만 반환합니다. 예외는 발생하지 않으므로 호출한 쿼리 자체는 성공합니다.
* SQL 항목과 OUTLINE 항목은 HEADER 항목을 함께 켭니다.

## **상수**

본 절에서는 DBMS\_XPLAN 패키지에서 제공하는 상수를 알파벳 순으로 설명합니다.

각 상수는 format 문자열의 출력 항목에 대응하는 비트 값입니다. format 파라미터는 문자열을 받으므로 일반적인 사용에서는 이 상수들을 직접 지정하지 않습니다.

<table><thead><tr><th width="200">상수</th><th width="120">값</th><th>설명</th></tr></thead><tbody><tr><td>FMT_ALL</td><td>524287</td><td>DISPLAY, DISPLAY_CURSOR의 ALL 그룹 항목<br><code>FMT_TYPICAL + FMT_ALLSTATS + FMT_SQL + FMT_OUTLINE</code></td></tr><tr><td>FMT_ALLSTATS</td><td>134912</td><td>ALLSTATS 그룹 항목<br><code>FMT_IOSTATS + FMT_MEMSTATS</code></td></tr><tr><td>FMT_ALL_TPR</td><td>264439</td><td>DISPLAY_TPR의 ALL 그룹 항목<br><code>FMT_TYPICAL_TPR + FMT_REMOTE + FMT_OUTLINE</code></td></tr><tr><td>FMT_BASIC</td><td>8327</td><td>DISPLAY, DISPLAY_CURSOR의 BASIC 그룹 항목<br><code>FMT_CARDS + FMT_COST + FMT_PART + FMT_ELAPTIME + FMT_LAST</code></td></tr><tr><td>FMT_BASIC_TPR</td><td>0</td><td>DISPLAY_TPR의 BASIC 그룹 항목. 추가 항목 없이 ID, Operation, Name만 출력</td></tr><tr><td>FMT_BUFGETS</td><td>2048</td><td>BUFGETS 항목</td></tr><tr><td>FMT_CARDS</td><td>1</td><td>CARDS 항목</td></tr><tr><td>FMT_COST</td><td>2</td><td>COST 항목</td></tr><tr><td>FMT_DISKREADS</td><td>131072</td><td>DISKREADS 항목</td></tr><tr><td>FMT_ELAPTIME</td><td>128</td><td>ELAPTIME 항목</td></tr><tr><td>FMT_FFFFFFFF</td><td>2147483647</td><td>항목 제외(<code>-ITEM</code>) 연산에 사용하는 전체 비트 마스크</td></tr><tr><td>FMT_HEADER</td><td>32768</td><td>HEADER 항목</td></tr><tr><td>FMT_IOSTATS</td><td>134656</td><td>IOSTATS 그룹 항목<br><code>FMT_TEMPREAD + FMT_TEMPWRITE + FMT_BUFGETS + FMT_DISKREADS</code></td></tr><tr><td>FMT_LAST</td><td>8192</td><td>LAST 항목</td></tr><tr><td>FMT_MEMSTATS</td><td>256</td><td>MEMSTATS 그룹 항목<br><code>FMT_USEDMEM</code></td></tr><tr><td>FMT_OUTLINE</td><td>262144</td><td>OUTLINE 항목</td></tr><tr><td>FMT_PART</td><td>4</td><td>PARTITION 항목</td></tr><tr><td>FMT_PE</td><td>8</td><td>PARALLEL 항목</td></tr><tr><td>FMT_PRECISE</td><td>16384</td><td>PRECISE 항목</td></tr><tr><td>FMT_PRED</td><td>16</td><td>PREDICATE 항목</td></tr><tr><td>FMT_REMOTE</td><td>32</td><td>REMOTE 항목</td></tr><tr><td>FMT_ROWS</td><td>64</td><td>ROWS 항목</td></tr><tr><td>FMT_SQL</td><td>65536</td><td>SQL 항목</td></tr><tr><td>FMT_STARTS</td><td>4096</td><td>STARTS 항목</td></tr><tr><td>FMT_TEMPREAD</td><td>512</td><td>TEMPREAD 항목</td></tr><tr><td>FMT_TEMPWRITE</td><td>1024</td><td>TEMPWRITE 항목</td></tr><tr><td>FMT_TYPICAL</td><td>61695</td><td>DISPLAY, DISPLAY_CURSOR의 TYPICAL 그룹 항목<br><code>FMT_BASIC + FMT_PE + FMT_ROWS + FMT_STARTS + FMT_PRED + FMT_REMOTE + FMT_HEADER + FMT_PRECISE</code></td></tr><tr><td>FMT_TYPICAL_TPR</td><td>2263</td><td>DISPLAY_TPR의 TYPICAL 그룹 항목<br><code>FMT_COST + FMT_CARDS + FMT_BUFGETS + FMT_ROWS + FMT_ELAPTIME + FMT_PART + FMT_PRED</code></td></tr><tr><td>FMT_USEDMEM</td><td>256</td><td>USEDMEM 항목</td></tr></tbody></table>

## **함수**

본 절에서는 DBMS\_XPLAN 패키지에서 제공하는 함수를 알파벳 순으로 설명합니다.

### **DISPLAY**

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

EXPLAIN PLAN FOR 구문의 결과는 세션 단위 임시 테이블인 PLAN\_TABLE에 저장되므로 EXPLAIN PLAN FOR 구문과 DISPLAY 함수는 같은 세션에서 수행해야 합니다. 세션의 PLAN\_TABLE이 비어 있으면 플랜 대신 `Error: cannot fetch last explain plan from PLAN_TABLE` 한 줄만 반환합니다.

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

* 프로토타입

```
DBMS_XPLAN.DISPLAY
(
    format    IN  VARCHAR2 DEFAULT 'BASIC LAST SQL'
)
RETURN dbms_xplan_type_table PIPELINED;
```

* 파라미터

<table><thead><tr><th width="141">파라미터</th><th width="120">IN / OUT</th><th>설명</th></tr></thead><tbody><tr><td>format</td><td>IN</td><td><ul><li>출력하고자 하는 항목을 명시</li><li>항목의 종류에는 개별 항목과 개별 항목 여러 개를 설정하는 그룹 항목이 있음</li><li>항목 이름 앞에 하이픈(-)를 붙여 해당 항목을 제외 가능함</li><li>NULL이나 빈 문자열을 명시하면 <code>BASIC LAST</code>가 적용됨(기본값: <code>BASIC LAST SQL</code>)</li></ul></td></tr></tbody></table>

* 반환값

<table><thead><tr><th width="240">반환값</th><th>설명</th></tr></thead><tbody><tr><td>dbms_xplan_type_table</td><td>플랜 출력 한 줄이 한 행에 대응하는 VARCHAR 컬렉션. TABLE() 구문으로 조회하면 컬럼 이름은 COLUMN_VALUE</td></tr></tbody></table>

* 개별 출력 항목

<table><thead><tr><th width="200">항목</th><th>설명</th></tr></thead><tbody><tr><td>CARDS</td><td>optimizer에서 예측한 해당 플랜 노드의 row 수</td></tr><tr><td>COST</td><td>optimizer에서 예측한 해당 플랜 노드의 cost</td></tr><tr><td>PARTITION</td><td>파티션 관련 정보</td></tr><tr><td>PARALLEL</td><td>parallel execution 관련 정보</td></tr><tr><td>PREDICATE</td><td>플랜 노드별 predicate 정보</td></tr><tr><td>REMOTE</td><td>플랜 노드별 데이터베이스 링크에서 수행한 쿼리 내용</td></tr><tr><td>ROWS</td><td>해당 플랜 노드에서 실제 수행된 row 수</td></tr><tr><td>ELAPTIME</td><td>해당 플랜 노드에서 실제 수행된 시간</td></tr><tr><td>USEDMEM</td><td>해당 플랜 노드에서 실제 사용된 메모리 양</td></tr><tr><td>TEMPREAD</td><td>해당 플랜 노드에서 실제 사용된 temp read 횟수</td></tr><tr><td>TEMPWRITE</td><td>해당 플랜 노드에서 실제 사용된 temp write 횟수</td></tr><tr><td>BUFGETS</td><td>해당 플랜 노드에서 실제 요청한 buffer get 횟수</td></tr><tr><td>STARTS</td><td>해당 플랜 노드가 실제 재시작된 횟수</td></tr><tr><td>LAST</td><td><p>수행 정보 값을 마지막 수행 정보 값으로 출력</p><p>지정하지 않은 경우 모든 수행에 대해 누적된 수행 정보 값</p></td></tr><tr><td>PRECISE</td><td>CARDS, ROWS에 대해 뒷자리수를 버림하지 않은 실제 값</td></tr><tr><td>HEADER</td><td>플랜의 기본 정보(sql id, child number, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행 시간 등)</td></tr><tr><td>SQL</td><td>플랜 생성에 사용된 쿼리문. HEADER 항목을 함께 켬</td></tr><tr><td>DISKREADS</td><td>해당 플랜 노드에서 실제 수행된 디스크 I/O 횟수</td></tr><tr><td>OUTLINE</td><td>플랜을 재현할 때 사용하는 힌트와 optimizer 파라미터 목록(Outlines 항목). HEADER 항목을 함께 켬</td></tr></tbody></table>

* 그룹 출력 항목

<table><thead><tr><th width="183">항목</th><th>설명</th></tr></thead><tbody><tr><td>IOSTATS</td><td>수행 정보 중 IO와 관련된 모든 항목<br><code>TEMPREAD + TEMPWRITE + BUFGETS + DISKREADS</code></td></tr><tr><td>MEMSTATS</td><td>수행 정보 중 메모리와 관련된 모든 항목<br><code>USEDMEM</code></td></tr><tr><td>ALLSTATS</td><td>수행 정보 중 IO, 메모리와 관련된 모든 항목<br><code>IOSTATS + MEMSTATS</code></td></tr><tr><td>BASIC</td><td><p>출력 기본 포맷</p><p>optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간<br><code>CARDS + COST + PARTITION + ELAPTIME + LAST</code></p></td></tr><tr><td>TYPICAL</td><td>BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보, parallel execution 정보, 플랜 기본 정보를 포함<br><code>BASIC + PARALLEL + ROWS + STARTS + PREDICATE + REMOTE + HEADER + PRECISE</code></td></tr><tr><td>ALL</td><td><p>TYPICAL 포맷에 추가로 ALLSTATS 항목과 쿼리문, Outlines 항목을 포함</p><p><code>TYPICAL + ALLSTATS + SQL + OUTLINE</code></p></td></tr></tbody></table>

* 예제

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

```
set pagesize 300
EXPLAIN PLAN FOR SELECT 'TEST_XPLAN' FROM DUAL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```

출력은 다음과 같습니다. SQL ID, CHILD NUMBER, HASH VALUE 값은 인스턴스마다 다릅니다. EXPLAIN PLAN FOR 구문은 쿼리를 실제로 수행하지 않으므로 EXECUTIONS와 FETCHES는 0이고, 수행 횟수가 0인 동안에는 AVG ELAPSED TIME, AVG BUFFER GETS 항목이 출력되지 않습니다.

```
COLUMN_VALUE
----------------------------------------------------------------------------------

SQL ID         : f25x262fpf1qm
CHILD NUMBER   : 24140
HASH VALUE     : 2639726291
PLAN HASH VALUE: 151428055
EXECUTIONS     : 0
FETCHES        : 0
LOADED AT      : 2026/07/31 12:37:10
TOT ELAPSED TIME: 00:00:00.0000
TOT BUFFER GETS: 0
SQL            :
 SELECT 'TEST_XPLAN' FROM DUAL

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


20 rows selected.
```

다음은 개별 항목만 지정하여 cost와 cardinality만 보는 예와, BASIC 그룹 항목에서 파티션 정보를 제외하고 보는 예입니다.

```
set pagesize 300
EXPLAIN PLAN FOR SELECT 'TEST_XPLAN' FROM DUAL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('CARDS COST'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('BASIC -PARTITION'));
```

format에 정의되지 않은 항목을 지정하면 플랜 대신 다음 한 줄이 반환됩니다.

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

COLUMN_VALUE
----------------------------------------------------------------------------------
Error: input format 'NOSUCHITEM' is not valid.

1 row selected.
```

### **DISPLAY\_CURSOR**

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

in\_sql\_id를 생략하면 V$SESSION의 PREV\_SQL\_ID, PREV\_CHILD\_NUMBER 값, 즉 현재 세션에서 직전에 수행한 쿼리의 플랜을 조회합니다. 세션이 아직 쿼리를 수행하지 않아 직전 SQL\_ID를 찾을 수 없으면 `ERROR: Last executed sql cannot be found` 한 줄만 반환합니다.

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

* 프로토타입

```
DBMS_XPLAN.DISPLAY_CURSOR
(
    in_sql_id       IN  VARCHAR2 DEFAULT NULL,
    in_child_no     IN  NUMBER   DEFAULT NULL,
    format          IN  VARCHAR2 DEFAULT 'BASIC LAST SQL'
)
RETURN dbms_xplan_type_table PIPELINED;
```

* 파라미터

<table><thead><tr><th width="200">파라미터</th><th width="120">IN / OUT</th><th>설명</th></tr></thead><tbody><tr><td>in_sql_id</td><td>IN</td><td><ul><li>조회하려는 플랜의 SQL_ID</li><li>이 값이 생략된 경우 해당 세션의 마지막 수행 쿼리의 SQL_ID 값을 사용하게 됨(기본값: NULL)</li></ul></td></tr><tr><td>in_child_no</td><td>IN</td><td><ul><li>조회하려는 플랜의 CHILD_NUMBER 값</li><li>이 값이 생략된 경우 입력된 SQL_ID 값과 일치하는 모든 플랜을 조회(기본값: NULL)</li><li>CHILD_NUMBER 값은 SQL_ID가 입력된 경우에만 지정될 수 있음</li></ul></td></tr><tr><td>format</td><td>IN</td><td><ul><li>출력하고자 하는 항목을 명시</li><li>항목의 종류에는 개별 항목과 개별 항목 여러 개를 설정하는 그룹 항목이 있음</li><li>항목 이름 앞에 하이픈(-)를 붙여 해당 항목을 제외 가능함</li><li>NULL이나 빈 문자열을 명시하면 <code>BASIC LAST</code>가 적용됨(기본값: <code>BASIC LAST SQL</code>)</li></ul></td></tr></tbody></table>

* 반환값

<table><thead><tr><th width="240">반환값</th><th>설명</th></tr></thead><tbody><tr><td>dbms_xplan_type_table</td><td>플랜 출력 한 줄이 한 행에 대응하는 VARCHAR 컬렉션. TABLE() 구문으로 조회하면 컬럼 이름은 COLUMN_VALUE</td></tr></tbody></table>

* 개별 출력 항목

<table><thead><tr><th width="200">항목</th><th>설명</th></tr></thead><tbody><tr><td>CARDS</td><td>optimizer에서 예측한 해당 플랜 노드의 row 수</td></tr><tr><td>COST</td><td>optimizer에서 예측한 해당 플랜 노드의 cost</td></tr><tr><td>PARTITION</td><td>파티션 관련 정보</td></tr><tr><td>PARALLEL</td><td>parallel execution 관련 정보</td></tr><tr><td>PREDICATE</td><td>플랜 노드별 predicate 정보</td></tr><tr><td>REMOTE</td><td>플랜 노드별 데이터베이스 링크에서 수행한 쿼리 내용</td></tr><tr><td>ROWS</td><td>해당 플랜 노드에서 실제 수행된 row 수</td></tr><tr><td>ELAPTIME</td><td>해당 플랜 노드에서 실제 수행된 시간</td></tr><tr><td>USEDMEM</td><td>해당 플랜 노드에서 실제 사용된 메모리 양</td></tr><tr><td>TEMPREAD</td><td>해당 플랜 노드에서 실제 사용된 temp read 횟수</td></tr><tr><td>TEMPWRITE</td><td>해당 플랜 노드에서 실제 사용된 temp write 횟수</td></tr><tr><td>BUFGETS</td><td>해당 플랜 노드에서 실제 요청한 buffer get 횟수</td></tr><tr><td>STARTS</td><td>해당 플랜 노드가 실제 재시작된 횟수</td></tr><tr><td>LAST</td><td><p>수행 정보 값을 마지막 수행 정보 값으로 출력</p><p>지정하지 않은 경우 모든 수행에 대해 누적된 수행 정보 값</p></td></tr><tr><td>PRECISE</td><td>CARDS, ROWS에 대해 뒷자리수를 버림하지 않은 실제 값</td></tr><tr><td>HEADER</td><td>플랜의 기본 정보(sql id, child number, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행 시간 등)</td></tr><tr><td>SQL</td><td>플랜 생성에 사용된 쿼리문. HEADER 항목을 함께 켬</td></tr><tr><td>DISKREADS</td><td>해당 플랜 노드에서 실제 수행된 디스크 I/O 횟수</td></tr><tr><td>OUTLINE</td><td>플랜을 재현할 때 사용하는 힌트와 optimizer 파라미터 목록(Outlines 항목). HEADER 항목을 함께 켬</td></tr></tbody></table>

* 그룹 출력 항목

<table><thead><tr><th width="200">항목</th><th>설명</th></tr></thead><tbody><tr><td>IOSTATS</td><td>수행 정보 중 IO와 관련된 모든 항목<br><code>TEMPREAD + TEMPWRITE + BUFGETS + DISKREADS</code></td></tr><tr><td>MEMSTATS</td><td>수행 정보 중 메모리와 관련된 모든 항목<br><code>USEDMEM</code></td></tr><tr><td>ALLSTATS</td><td>수행 정보 중 IO, 메모리와 관련된 모든 항목<br><code>IOSTATS + MEMSTATS</code></td></tr><tr><td>BASIC</td><td><p>출력 기본 포맷</p><p>optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간<br><code>CARDS + COST + PARTITION + ELAPTIME + LAST</code></p></td></tr><tr><td>TYPICAL</td><td><p>BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보, parallel execution 정보, 플랜 기본 정보를 포함</p><p><code>BASIC + PARALLEL + ROWS + STARTS + PREDICATE + REMOTE + HEADER + PRECISE</code></p></td></tr><tr><td>ALL</td><td>TYPICAL 포맷에 추가로 ALLSTATS 항목과 쿼리문, Outlines 항목을 포함<br><code>TYPICAL + ALLSTATS + SQL + OUTLINE</code></td></tr></tbody></table>

* 예제

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

```
set pagesize 300
SELECT COUNT(*) AS cnt FROM DUAL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
```

다음은 GATHER\_SQL\_PLAN\_STAT을 세션에 켜서 노드별 수행 정보를 함께 보는 예입니다. TYPICAL 포맷으로 전체 정보를 보고, 이어서 마지막 수행 시간·수행 결과 row·요청 buffer gets만 보도록 항목을 지정합니다. DISPLAY\_CURSOR 호출 자체도 세션의 마지막 수행 쿼리가 되므로, 같은 플랜을 다시 보려면 대상 쿼리를 다시 수행합니다.

```
set pagesize 300
CREATE TABLE xplan_emp (id NUMBER, dept NUMBER);
CREATE TABLE xplan_dept (dept NUMBER, dname VARCHAR2(30));
INSERT INTO xplan_emp SELECT LEVEL, MOD(LEVEL, 10) FROM DUAL CONNECT BY LEVEL <= 10000;
INSERT INTO xplan_dept SELECT LEVEL, 'D' || LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
COMMIT;

ALTER SESSION SET GATHER_SQL_PLAN_STAT = Y;

SELECT COUNT(*) AS cnt FROM xplan_emp e, xplan_dept d WHERE e.dept = d.dept AND e.id > 0;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));

SELECT COUNT(*) AS cnt FROM xplan_emp e, xplan_dept d WHERE e.dept = d.dept AND e.id > 0;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ROWS ELAPTIME +BUFGETS LAST'));

SELECT COUNT(*) AS cnt FROM xplan_emp e, xplan_dept d WHERE e.dept = d.dept AND e.id > 0;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS -TEMPREAD -TEMPWRITE LAST'));

ALTER SESSION SET GATHER_SQL_PLAN_STAT = N;

DROP TABLE xplan_emp;
DROP TABLE xplan_dept;
```

TYPICAL 포맷의 출력은 다음과 같습니다. Cards는 optimizer 예측값이고 Rows는 실제 처리한 row 수이므로 두 값의 차이로 예측 정확도를 확인할 수 있습니다.

```
COLUMN_VALUE
--------------------------------------------------------------------------------

SQL ID         : dpfqbpjm0aku2
CHILD NUMBER   : 429
HASH VALUE     : 1711622978
PLAN HASH VALUE: 2614744606
EXECUTIONS     : 1
FETCHES        : 1
LOADED AT      : 2026/07/31 14:28:37
TOT ELAPSED TIME: 00:00:00.0030
AVG ELAPSED TIME: 00:00:00.0030
TOT BUFFER GETS: 49
AVG BUFFER GETS: 49

-----------------------------------------------------------------------------------------------------------
| ID  | Operation               | Name       | Cost (%CPU)  | Cards   | Rows    | Elaps. Time  | Starts   |
-----------------------------------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION       |            |   26      (0)|       1 |       1 |00:00:00.0000 |       1  |
|   2 |  SORT AGGR              |            |   26      (0)|       1 |       1 |00:00:00.0002 |       1  |
|   3 |   HASH JOIN             |            |   26      (0)|   12134 |    9000 |00:00:00.0016 |       1  |
|   4 |    TABLE ACCESS (FULL)  | XPLAN_DEPT |   12      (0)|      10 |      10 |00:00:00.0001 |       1  |
|   5 |    TABLE ACCESS (FULL)  | XPLAN_EMP  |   13      (0)|   12134 |   10000 |00:00:00.0003 |       1  |
-----------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.0021

Predicate Information
-----------------------------------------------------------------------------------------------------------
   3 - access: ("E"."DEPT" = "D"."DEPT") (0.100)
   5 - filter: ("E"."ID" > 0) (1.000)

Note
-----------------------------------------------------------------------------------------------------------
   4 - dynamic sampling used for this table (13 blocks)
   5 - dynamic sampling used for this table (32 blocks)


35 rows selected.
```

두 번째 호출은 항목을 직접 지정했으므로 지정한 컬럼만 출력합니다.

```
COLUMN_VALUE
--------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| ID  | Operation               | Name       | Rows     | Elaps. Time  | BUFGETS  |
-----------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION       |            |       1  |00:00:00.0000 |       0  |
|   2 |  SORT AGGR              |            |       1  |00:00:00.0002 |       0  |
|   3 |   HASH JOIN             |            |    9000  |00:00:00.0016 |       0  |
|   4 |    TABLE ACCESS (FULL)  | XPLAN_DEPT |      10  |00:00:00.0001 |      16  |
|   5 |    TABLE ACCESS (FULL)  | XPLAN_EMP  |   10000  |00:00:00.0002 |      33  |
-----------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.0021
LAST BUFFER GET: 49

Note
-----------------------------------------------------------------------------------
   4 - dynamic sampling used for this table (13 blocks)
   5 - dynamic sampling used for this table (32 blocks)


19 rows selected.
```

세 번째 호출은 메모리 사용량과 디스크 I/O 값을 출력합니다.

```
COLUMN_VALUE
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| ID  | Operation               | Name       | BUFGETS  | Used Mem |  Reads   |
-------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION       |            |       0  |       0K |       0  |
|   2 |  SORT AGGR              |            |       0  |      66K |       0  |
|   3 |   HASH JOIN             |            |       0  |      78K |       0  |
|   4 |    TABLE ACCESS (FULL)  | XPLAN_DEPT |      16  |       0K |       0  |
|   5 |    TABLE ACCESS (FULL)  | XPLAN_EMP  |      33  |       0K |       0  |
-------------------------------------------------------------------------------

LAST BUFFER GET: 49

Note
-------------------------------------------------------------------------------
   4 - dynamic sampling used for this table (13 blocks)
   5 - dynamic sampling used for this table (32 blocks)


18 rows selected.
```

위 두 예제는 LAST 항목을 함께 지정했으므로 마지막 수행 한 번의 값만 출력합니다. LAST를 빼면 같은 플랜을 여러 번 수행한 만큼 누적된 값이 출력되므로, 수행 횟수가 2회일 때 Rows는 위 값의 2배로 나타납니다.

다음은 SQL\_ID와 CHILD\_NUMBER를 직접 지정하여 조회하는 예입니다. 두 값은 인스턴스마다 다르므로 V$SESSION에서 직전 수행 쿼리의 값을 얻어 사용합니다.

```
set serveroutput on
SELECT COUNT(*) AS cnt FROM DUAL;

DECLARE
    v_sql_id    VARCHAR2(13);
    v_child_no  NUMBER;
BEGIN
    SELECT prev_sql_id, prev_child_number INTO v_sql_id, v_child_no
      FROM v$session
     WHERE sid = USERENV('SESSID');

    DBMS_OUTPUT.PUT_LINE('SQL_ID = ' || v_sql_id || ', CHILD = ' || v_child_no);

    FOR rec IN (SELECT column_value txt
                  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(v_sql_id, v_child_no,
                                                       'BASIC'))) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.txt);
    END LOOP;
END;
/
```

### **DISPLAY\_INTERNAL**

DISPLAY, DISPLAY\_CURSOR 함수가 내부적으로 사용하는 함수입니다. 사용자가 직접 호출하지 않습니다.

플랜 조회에는 DISPLAY 또는 DISPLAY\_CURSOR 함수를 사용합니다.

### **DISPLAY\_TPR**

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

DISPLAY, DISPLAY\_CURSOR 함수와 달리 V$SQL\_PLAN이 아니라 TPR 저장소를 조회합니다. 따라서 Physical Plan Cache에서 이미 밀려난 플랜도 조회할 수 있지만, TPR 저장소에 수집되지 않은 값을 지정하면 행을 반환하지 않습니다. TPR 저장소의 플랜 수행 통계 수집 여부는 TPR\_SQL\_PLAN\_STAT 파라미터로 결정되며 기본값은 N입니다.

format 파라미터가 받는 항목은 DISPLAY, DISPLAY\_CURSOR보다 좁습니다. LAST, HEADER, SQL, PRECISE, STARTS, USEDMEM, TEMPREAD, TEMPWRITE, DISKREADS, IOSTATS, MEMSTATS, ALLSTATS 항목은 지정할 수 없고, 지정하면 `Error: input format '<format>' is not valid.` 한 줄만 반환합니다.

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

* 프로토타입

```
DBMS_XPLAN.DISPLAY_TPR
(
    sql_hash_value  IN  NUMBER,
    plan_hash_value IN  NUMBER   DEFAULT NULL,
    instance#       IN  NUMBER   DEFAULT NULL,
    format          IN  VARCHAR2 DEFAULT 'TYPICAL'
)
RETURN dbms_xplan_type_table PIPELINED;
```

```
DBMS_XPLAN.DISPLAY_TPR
(
    sql_id          IN  VARCHAR,
    plan_hash_value IN  NUMBER   DEFAULT NULL,
    instance#       IN  NUMBER   DEFAULT NULL,
    format          IN  VARCHAR2 DEFAULT 'TYPICAL'
)
RETURN dbms_xplan_type_table PIPELINED;
```

* 파라미터

<table><thead><tr><th width="200">파라미터</th><th width="120">IN / OUT</th><th>설명</th></tr></thead><tbody><tr><td>sql_hash_value</td><td>IN</td><td><ul><li>조회하려는 플랜의 SQL_HASH_VALUE</li><li>NULL을 명시하면 행을 반환하지 않음</li></ul></td></tr><tr><td>sql_id</td><td>IN</td><td><ul><li>조회하려는 플랜의 SQL_ID</li><li>NULL을 명시하면 행을 반환하지 않음</li></ul></td></tr><tr><td>plan_hash_value</td><td>IN</td><td><ul><li>조회하려는 플랜의 PLAN_HASH_VALUE</li><li>이 값이 생략된 경우 입력된 SQL_HASH_VALUE 또는 SQL_ID 값과 일치하는 모든 플랜을 조회(기본값: NULL)</li></ul></td></tr><tr><td>instance#</td><td>IN</td><td><ul><li>조회하려는 플랜이 생성된 인스턴스 번호</li><li>이 값이 생략된 경우 V$INSTANCE의 INSTANCE_NUMBER를 사용(기본값: NULL)</li></ul></td></tr><tr><td>format</td><td>IN</td><td><p>아래 3가지 그룹 항목을 지원하며, 개별 항목을 함께 지정할 수 있음(기본값: <code>TYPICAL</code>)</p><ul><li>BASIC : OPERATION ID, NAME만 출력하고 추가적으로 옵션 사용 가능</li><li>TYPICAL :기본적으로 OPERATION ID, NAME, CARDS, COST를 출력하고, 해당 내용이 있을 경우 BUFGETS, ROWS, ELAPTIME, PARTITION, PREDICATE 정보도 출력 (기본값)</li><li>ALL : TYPICAL에 추가로 REMOTE, OUTLINE을 출력</li></ul><p>NULL이나 빈 문자열을 명시하면 <code>TYPICAL</code>이 적용됨</p></td></tr></tbody></table>

* 반환값

<table><thead><tr><th width="240">반환값</th><th>설명</th></tr></thead><tbody><tr><td>dbms_xplan_type_table</td><td>플랜 출력 한 줄이 한 행에 대응하는 VARCHAR 컬렉션. TABLE() 구문으로 조회하면 컬럼 이름은 COLUMN_VALUE</td></tr></tbody></table>

* 개별 출력 항목

<table><thead><tr><th width="200">항목</th><th>설명</th></tr></thead><tbody><tr><td>CARDS</td><td>optimizer에서 예측한 해당 플랜 노드의 row 수</td></tr><tr><td>COST</td><td>optimizer에서 예측한 해당 플랜 노드의 cost</td></tr><tr><td>BUFGETS</td><td>해당 플랜 노드에서 실제 요청한 buffer get 횟수</td></tr><tr><td>ROWS</td><td>해당 플랜 노드에서 실제 수행된 row 수</td></tr><tr><td>ELAPTIME</td><td>해당 플랜 노드에서 실제 수행된 시간</td></tr><tr><td>PARTITION</td><td>파티션 관련 정보</td></tr><tr><td>PARALLEL</td><td>parallel execution 관련 정보</td></tr><tr><td>PREDICATE</td><td>플랜 노드별 predicate 정보</td></tr><tr><td>REMOTE</td><td>플랜 노드별 데이터베이스 링크에 수행한 쿼리 내용</td></tr><tr><td>OUTLINE</td><td>플랜을 재현할 때 사용하는 힌트와 optimizer 파라미터 목록(Outlines 항목)</td></tr></tbody></table>

* 그룹 출력 항목

<table><thead><tr><th width="200">항목</th><th>설명</th></tr></thead><tbody><tr><td>BASIC</td><td>ID, Operation, Name만 출력. 추가 항목 없음</td></tr><tr><td>TYPICAL</td><td>optimizer 예측값과 수집된 수행 정보, predicate 정보를 포함(기본값)<br><code>COST + CARDS + BUFGETS + ROWS + ELAPTIME + PARTITION + PREDICATE</code></td></tr><tr><td>ALL</td><td>TYPICAL 포맷에 추가로 remote sql 정보와 Outlines 항목을 포함<br><code>TYPICAL + REMOTE + OUTLINE</code></td></tr></tbody></table>

* 예제

다음은 기본 포맷에서 SQL HASH VALUE 1929081600를 통해 플랜 정보를 보는 예입니다. SQL\_HASH\_VALUE와 PLAN\_HASH\_VALUE에 해당하는 플랜이 여러 개면 플랜별로 나누어 출력합니다. 예제의 SQL\_HASH\_VALUE, SQL\_ID, PLAN\_HASH\_VALUE 값은 인스턴스마다 다르며, TPR 저장소에 수집되지 않은 값을 지정하면 행이 반환되지 않습니다.

```
SQL> 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)
```

SQL\_ID로 조회하면 첫 줄이 `SQL HASH VALUE` 대신 `SQL ID`로 출력됩니다.

```
SQL> select * from table(dbms_xplan.display_tpr('0rxmgdn014b5a'));

COLUMN_VALUE
-------------------------------------------------------------------------------------------------
SQL ID          : 0rxmgdn014b5a
PLAN HASH VALUE : 3839639941
SQL TEXT        :
...
```

{% hint style="warning" %}
**주의**

첫 번째 파라미터 값으로 `'1929081600'`처럼 사용하면 VARCHAR 타입인 SQL\_ID로 인식됩니다.

SQL HASH VALUE를 입력하려면 따옴표 없이 사용하거나, `sql_hash_value => 1929081600`처럼 명시적으로 파라미터를 지정합니다.
{% endhint %}