Parameter FORMAT
The value that is passed with the parameter
format determines the level of information that is shown by
dbms_xplan.display and can be set to one of
-
basic
-
typical
-
all
-
advanced (undocumented)
-
serial
The default value is typical
--
-- Minimal information: operation id, operation name, operation options
--
select * from dbms_xplan.display(format=>'basic');
--
-- Add #rows, #bytes, optimizer costs and, if applicable, pruning
-- parallel and predicate information
--
select * from dbms_xplan.display(format=>'typical');
--
-- Add projection, alias and, if a distributed query is displayed,
-- information about the remote SQL statement.
--
select * from dbms_xplan.display(format=>'all');
-- Don't show «parallel information», even if if the plan were
-- for a parallel execution. Otherwise the same as 'typical'
--
select * from dbms_xplan.display(format=>'serial');
More control on the displayed result set can be added with +xxx where xxx is
alias | Query block name / Object alias section |
bytes | number of bytes estimated by the optimizer |
cost | optimizer cost information |
note | note section |
outline | |
partition | partition pruning information (shows values of plan_table columns partition_start and partition_stop). |
parallel | PX information (distribution method and table queue information) |
predicate | predicate section |
projection | projection section |
qbregistry | Query block registry |
remote | information for distributed query (e.g. remote from serial distribution and remote SQL) |
rows | number of rows estimated by the optimizer |
The plus sign is optional, thus the following two statements are equivalent:
select * from dbms_xplan.display(format=>'basic +note');
select * from dbms_xplan.display(format=>'basic note');
Multiple options can be separated with a comma or by introducing each one with a plus (+):
select * from dbms_xplan.display(format=>'basic +cost +bytes');
select * from dbms_xplan.display(format=>'basic +cost, bytes');
Options can be turned off with a minus sign. Each option that is to be turned off must be prepended with a minus, otherwise, it's interpreted as a plus:
select * from dbms_xplan.display(format => 'typical -cost,-rows');