Table of Contents
This chapter briefly introduces the DBMS_XPLAN package, and describes how to use the procedures of the package.
DBMS_XPLAN provides the DISPLAY_CURSOR function to display plan information and plan executions using user specified items and in various formats.
DBMS_XPLAN uses a pipelined function to output query results. Each pipelined function uses TABLE() function to display the output. Since the output information can also be queried in V$SQL_PLAN and V$SQL_PLAN_STATISTICS, the GATHER_SQL_PLAN_STAT parameter must be enabled.
This section describes the function provided by the DBMS_XPLAN package.
Uses the SQL_ID value to display the execution plan registered in the physical plan cache. Since it is a pipelined function, the TABLE() function can be used to display the execution plan.
Details about the DISPLAY_CURSOR function are as follows:
Prototype
DBMS_XPLAN.DISPLAY_CURSOR
(
in_sql_id VARCHAR2 default NULL,
in_child_number NUMBER default NULL,
format VARCHAR2 default 'BASIC LAST SQL'
)
RETURN dbms_xplan_type_table pipelined;
Parameter
| Parameter | Description |
|---|---|
| in_sql_id | SQL_ID of the plan to display. If unspecified, the SQL_ID of the last executed query for the session is used. |
| in_child_number | CHILD_NUMBER of the plan to display. If unspecified, the CHILD_NUMBER of the last executed query for the session is used. |
| format | Items to display. Two types of items are available: individual and group. An item with a prefix of '-' is excluded. |
Individual Item
| Item | Description |
|---|---|
| CARDS | Number of rows of the plan node estimated by the optimizer. |
| COST | Cost information of the plan node estimated by the optimizer. |
| PARTITION | Partition related information. |
| PARALLEL | Parallel execution related information. |
| PREDICATE | Predicate information of the plan node. |
| REMOTE | Contents of the query executed on the database link of the plan node. |
| ROWS | Number of rows executed on the plan node. |
| ELAPTIME | Amount of time used by the plan node. |
| USEDMEM | Amount of memory used by the plan node. |
| TEMPREAD | Number of temporary reads used by the plan node. |
| TEMPWRITE | Number of temporary writes used by the plan node. |
| BUFGETS | Number of buffer gets requested by the plan node. |
| STARTS | Number of times the plan node has been restarted. |
| LAST | Option to display only the statistics of the last execution. If unspecified, the statistics of all executions on the cursor are displayed. |
| PRECISE | Displays the actual values of CARDS and ROWS, instead of the rounded up value. |
| HEADER | Basic information about the plan. (sql id, hash value, total execution time, total patch time, and plan execution time) |
| SQL | Query statement used to create the plan. |
Group Item
| Item | Description |
|---|---|
| IOSTATS | Displays all items related to IO. TEMPREAD + TEMPWRITE + BUFGETS |
| MEMSTATS | Displays all items related to memory. USEDMEM |
| ALLSTATS | Displays all items related to IO and memory. IOSTATS + MEMSTATS |
| BASIC | Basic output format. Includes the cardinality and cost estimated by the optimizer, and the last query execution time on each node. CARDS + COST + PART + ELAPTIME + LAST |
| TYPICAL | Includes information displayed at the BASIC level in addition to the number of rows, predicate, and remote SQL information used in the last query execution on each node. BASIC + PE + ROWS + STARTS + PRED + REMOTE + PRECISE |
| ALL | Includes information displayed at the TYPICAL level in addition to the ALLSTATS items. TYPICAL + ALLSTATS |
Example
The following is an example of displaying the plan information of the last executed query in the current session.
SQL> select * from table(dbms_xplan.display_cursor);
The following is an example of displaying the plan information of SQL_ID 11495 excluding the partition information.
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)
The following is an example of displaying the execution time, number of affected rows, and buffer gets result of the last query execution.
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)