본 장에서는 DBMS_XPLAN 패키지의 기본 개념과 패키지 내의 프러시저와 함수를 사용하는 방법을 설명한다.
DBMS_XPLAN은 플랜 정보와 플랜의 수행 정보들에 대해 사용자가 원하는 항목을 선택해서 다양한 포맷으로 출력할 수 있는 기능을 제공한다.
출력 방식은 pipelined function 기능을 이용하여 TABLE() 구문을 통해 쿼리의 결과로 해당 정보들을 출력한다. 또한 출력 정보는 V$SQL_PLAN, V$SQL_PLAN_STATISTICS 뷰를 통해서 구하기 때문에 GATHER_SQL_PLAN_STAT 파라미터가 켜져 있어야 수행 정보를 구할 수 있다.
본 절에서는 DBMS_XPLAN 패키지에서 제공하는 프러시저와 함수를 알파벳 순으로 설명한다.
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_sql_id | 조회하려는 플랜의 SQL_ID 값이다. 입력을 생략한 경우에는 해당 세션의 마지막 수행 쿼리의 SQL_ID 값을 사용하게 된다. |
| in_child_no | 조회하려는 플랜의 CHILD_NUMBER 값이다. 입력을 생략한 경우에는 해당 세션의 마지막 수행 쿼리의 CHILD_NUMBER 값을 사용하게 된다. |
| format | 출력하고자 하는 항목을 명시할 수 있다. 항목의 종류에는 개별 항목과 개별 항목 여러 개를 설정하는 그룹 항목이 있다. 항목 이름 앞에 하이픈(-)를 붙이면 해당 항목을 제외할 수 있다. |
개별 출력 항목
| 항목 | 설명 |
|---|---|
| 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, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행 시간)를 보여준다. |
| SQL | 플랜 생성에 사용된 쿼리문을 보여준다. |
그룹 출력 항목
| 항목 | 설명 |
|---|---|
| IOSTATS | 수행 정보 중 IO와 관련된 모든 항목을 보여준다. TEMPREAD + TEMPWRITE + BUFGETS |
| MEMSTATS | 수행 정보 중 메모리와 관련된 모든 항목을 보여준다. USEDMEM |
| ALLSTATS | 수행 정보 중 IO, 메모리와 관련된 모든 항목을 보여준다. IOSTATS + MEMSTATS |
| BASIC | 출력 기본 포맷으로 optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간을 출력한다. CARDS + COST + PART + ELAPTIME + LAST |
| TYPICAL | BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보를 출력한다. BASIC + PE + ROWS + STARTS + PRED + REMOTE + PRECISE |
| ALL | TYPICAL 포맷에 ALLSTATS 항목을 보여준다. TYPICAL + ALLSTATS |
예제
다음은 현재 세션에서 마지막으로 수행한 쿼리의 플랜 정보를 보는 예이다.
SQL> select * from table(dbms_xplan.display_cursor);
다음은 기본 포맷에서 파티션 정보를 제외하고 11495 SQL_ID의 플랜 정보를 보는 예이다.
SQL> set pagesize 0
SQL> set linesize 200
SQL> select * from table
(dbms_xplan.display_cursor(11495, 'BASIC LAST SQL -PARTITION'));
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL ID : 11495
PLAN HASH VALUE: 897223689
EXECUTIONS : 3
FETCHES : 18
LOADED AT : 2014/07/22 17:55:44
SQL : select p_name, p_mfgr, ps_supplycost, p_type,
min (ps_supplycost) over (partition by ps_partkey) min_supplycost,
p_comment, ps_comment
from part, partsupp
where p_partkey = ps_partkey
and p_size = :v_size
and p_brand = :v_brand
and p_type like '%' || :v_type
---------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU)| Cards | Elaeps. Time |
---------------------------------------------------------------------------------
| 1 | COLUMN PROJECTION | |61069 (.02)| 94 |00:00:00.0001 |
| 2 | WINDOW | |61069 (.02)| 94 |00:00:00.0003 |
| 3 | GROUP BY | |61069 (.02)| 94 |00:00:00.0000 |
| 4 | ORDER BY (SORT) | |61069 (.02)| 94 |00:00:00.0009 |
| 5 | PARTITION HASH | |61069 (.02)| 94 |00:00:00.0039 |
| | (ALL PART) | | | | |
| 6 | HASH JOIN | |61069 (.02)| 94 |00:00:00.0905 |
| 7 | TABLE ACCESS | PART |12517 (.02)| 4 |00:00:00.2713 |
| | (FULL) |
| 8 | TABLE ACCESS | PARTSUPP |48545 (0)| 706K |00:00:00.1580 |
| | (FULL) | | | | |
---------------------------------------------------------------------------------
Note
---------------------------------------------------------------------------------
7 - dynamic sampling used for this table (88 blocks)
8 - dynamic sampling used for this table (88 blocks)
다음은 마지막 수행 시간, 수행 결과 row, 요청 buffer gets 값을 보는 예이다.
SQL> set pagesize 0
SQL> set linesize 200
SQL> select * from table
(dbms_xplan.display_cursor(11495, 'ROWS ELAPTIME +BUFGETS'));
COLUMN_VALUE
------------------------------------------------------------------------------
SQL ID : 11495
PLAN HASH VALUE: 897223689
EXECUTIONS : 3
FETCHES : 18
LOADED AT : 2014/07/22 17:55:44
------------------------------------------------------------------------------
| ID | Operation | Name | Rows | Elaps. Time | CR Gets |
------------------------------------------------------------------------------
| 1 | COLUMN PROJECTION | | 156 |00:00:00.0001 | 0 |
| 2 | WINDOW | | 156 |00:00:00.0003 | 0 |
| 3 | GROUP BY | | 156 |00:00:00.0000 | 0 |
| 4 | ORDER BY (SORT) | | 156 |00:00:00.0009 | 0 |
| 5 | PARTITION HASH | | 156 |00:00:00.0039 | 0 |
| | (ALL PART) | | | | |
| 6 | HASH JOIN | | 156 |00:00:00.0905 | 0 |
| 7 | TABLE ACCESS | PART | 39 |00:00:00.2713 | 28754 |
| | (FULL) | | | | |
| 8 | TABLE ACCESS | PARTSUPP | 212296 |00:00:00.1580 | 30377 |
| | (FULL) | | | | |
------------------------------------------------------------------------------
Predicate Information
--------------------------------------------------------------------------------
6 - access: ("PART"."P_PARTKEY" = "PARTSUPP"."PS_PARTKEY") (0.000)
7 - filter: ("PART"."P_SIZE" = :0) AND ("PART"."P_TYPE" LIKE CONCAT('%',:2))
AND ("PART"."P_BRAND" = :1) (0.018 * 0.025 * 0.062)
Note
--------------------------------------------------------------------------------
7 - dynamic sampling used for this table (88 blocks)
8 - dynamic sampling used for this table (88 blocks)