Database Reference
In-Depth Information
The display Function
The
display
function returns execution plans stored in a plan table. The return value is an instance of the
dbms_xplan_type_table
collection. The elements of the collection are instances of the
dbms_xplan_type
object type.
The only attribute of this object type, named
plan_table_output
, is of type
VARCHAR2(300)
. The function has the
following input parameters:
table_name
specifies the name of the plan table. The default value is
plan_table
. If
NULL
is
specified, the default value is used.
•
statement_id
specifies the SQL statement name, optionally given as a parameter, when the
EXPLAIN PLAN
statement is executed. The default value is
NULL
. If the default value is used, the
execution plan most recently inserted into the plan table is displayed (provided the
filter_preds
parameter isn't specified).
•
format
specifies which information is provided in the output. There are primitive values
(
basic
,
typical
,
serial
,
all
, and
advanced
) and, for finer control, additional modifiers
(
adaptive
,
alias
,
bytes
,
cost
,
note
,
outline
,
parallel
,
partition
,
peeked_binds
,
predicate
,
projection
,
remote
,
report
, and
rows
) that can be added to them. If information
should be added, modifiers are optionally prefixed by the
+
character (for example,
basic
+predicate
). If information should be removed, modifiers have to be prefixed by
the
-
character (for example,
typical -bytes
). Multiple modifiers can be specified at the
same time (for example,
typical +alias -bytes -cost
). Table
10-2
and Table
10-3
fully
describe the primitive values and the modifiers, respectively. The default value is
typical
.
•
Table 10-2.
Primitive Values Accepted by the
format
Parameter
Value
Description
basic
Displays only the minimum amount of information, basically only the operations and the objects
on which they're executed.
typical
Displays the most common information, basically everything except for aliases, outline, peeked
bind variables, subplans, column projection, and reporting mode information.
serial
Like
typical
, except that information about parallel processing isn't displayed.
all
Displays all available information except the outline, the peeked bind variables, subplans, and
reporting mode information.
advanced
Displays all available information except for subplans and reporting mode information.
Table 10-3.
Modifiers Accepted by the
format
Parameter
Value
Description
adaptive
Controls the display of subplans. This section isn't shown in the previous examples. Refer to
the “Adaptive Execution Plans” section later on for an example. This modifier is available from
version 12.1 onward only.
alias
Controls the display of the section containing query block names and object aliases.
bytes
Controls the display of the
Bytes
column in the execution plan table.
cost
Controls the display of the
Cost
column in the execution plan table.