Database Reference
In-Depth Information
The dbms_xplan Package
You saw earlier in this chapter that the
dbms_xplan
package can be used to display execution plans stored in several
places: among others, in the plan table, in the library cache, in AWR, and in the Statspack repository. The following
sections describe the functions available in the package for that purpose. To begin with, let's take a look at the output
they generate.
Output
The aim of this section is to explain the information contained in the output that's returned by some of the functions
in the
dbms_xplan
package. To do so, I use a sample output, generated by the
dbms_xplan_output.sql
script, that
contains most of the available sections. Because one book page isn't wide enough to show all the information, not
everything for each section is shown. I show key information only. If something is missing, I point it out. Also note that
for most of the information provided in this section, examples and further explanations are given either later on in this
chapter or in Part 4. The first section of output is as follows:
SQL_ID dwnnunj9nuztb, child number 0
-------------------------------------
SELECT t2.* FROM t1, t2 WHERE t1.n = t2.n AND t1.id > :t1_id AND
t2.id BETWEEN :t2_id_min AND :t2_id_max
This section gives the following information about the SQL statement:
sql_id
identifies the parent cursor. This information is available only when the output is
generated by the
display_cursor
and
display_awr
functions.
•
The
sql_id
, identifies the child cursor. This information is
available only when the output is generated by the
display_cursor
function.
•
The child number, along with the
•
The text of the SQL statement is available only when the output is generated by the
display_cursor
and
display_awr
functions.
The second section shows the hash value of the execution plan and, in a table, the execution plan itself. Here's
the excerpt:
Plan hash value: 2539808735
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 14 | 7756 | 15 (7)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 14 | 7392 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_PK | 14 | | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 876 | 22776 | 23 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
In the table, estimations and execution statistics for each operation are provided. The number of columns in the
table depends directly on the amount of available information. For example, information about partitioning, parallel
processing, or execution statistics is shown only when available. For this reason, two outputs generated by the same
