Database Reference
In-Depth Information
Table 10-3.
(
continued
)
Value
Description
note
Controls the display of the section containing the notes.
outline
Controls the display of the section containing the outline.
parallel
Controls the display of parallel processing information, specifically, the
TQ
,
IN-OUT
, and
PQ
Distrib
columns in the execution plan table. These columns aren't shown in the
previous examples.
partition
Controls the display of partitioning information, specifically the
Pstart
and
Pstop
columns in
the execution plan table. These columns aren't shown in the previous examples.
peeked_binds
Controls the display of the section containing peeked bind variables.
predicate
Controls the display of the section containing filter, access and storage predicates.
projection
Controls the display of the section containing column projection information.
remote
Controls the display of SQL statements executed remotely. This section isn't shown in the
previous examples.
report
Controls the activation of the reporting mode. When enabled, additional information about
adaptive and reoptimized execution plans is displayed. 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.
rows
Controls the display of the
Rows
column in the execution plan table.
filter_preds
specifies a restriction applied while querying the plan table. The restriction
is a regular SQL predicate based on one of the columns of the plan table (for example,
statement_id = 'test3'
). The default value is
NULL
. If the default value is used, the
execution plan most recently inserted into the plan table is displayed.
To use the
display
function, the caller requires only the
EXECUTE
privilege on the package and the
SELECT
privilege on the plan table.
The following queries, which display the same execution plan, show the main differences between the primitive
values
basic
,
typical
, and
advanced
. Here's an excerpt of the output generated by the
display.sql
script:
•
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, '
basic
'));
PLAN_TABLE_OUTPUT
-----------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
