format
Controls the level of details for the plan. It accepts five values:
BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).
ADAPTIVE:
- Displays the final plan, or the current plan if the execution has not completed. This section includes notes about runtime optimizations that affect the plan, such as switching from a Nested Loops join to a Hash join.
- Plan lineage. This section shows the plans that were run previously due to automatic reoptimization. It also shows the default plan, if the plan changed due to dynamic plans.
- Recommended plan. In reporting mode, the plan is chosen based on execution statistics displayed. Note that displaying the recommended plan for automatic reoptimization requires re-compiling the query with the optimizer adjustments collected in the child cursor. Displaying the recommended plan for a dynamic plan does not require this.
- Dynamic plans. This summarizes the portions of the plan that differ from the default plan chosen by the optimizer.
For finer control on the display output, the following keywords can be added to the above three standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such as PREDICATE).
  
Format keywords must be separated by either a comma or a space:
ROWS - if relevant, shows the number of rows estimated by the optimizer
BYTES - if relevant, shows the number of bytes estimated by the optimizer
COST - if relevant, shows optimizer cost information
PARTITION - if relevant, shows partition pruning information
PARALLEL - if relevant, shows PX information (distribution method and table queue information)
PREDICATE - if relevant, shows the predicate section
PROJECTION -if relevant, shows the projection section
ALIAS - if relevant, shows the "Query Block Name / Object Alias" section
REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
NOTE - if relevant, shows the note section of the explain plan
IOSTATS - assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format shows IO statistics for ALL (or only for the LAST as shown below) executions of the cursor.
MEMSTATS - Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.
ALLSTATS - A shortcut for 'IOSTATS MEMSTATS'
LAST - By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution.
The following two formats are deprecated but supported for backward compatibility:
RUNSTATS_TOT - Same as IOSTATS, that is, displays IO statistics for all executions of the specified cursor.
RUNSTATS_LAST - Same as IOSTATS LAST, that is, displays the runtime statistics for the last execution of the cursor
Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.