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.
 
 
Search WWH ::




Custom Search