Oracle Explain Plan : Part 1

• You can also include the syntax ‘explain plan for’ in front of your SQL statement and run it in SQL*Plus.  

• SQL*Plus will reply with ‘Explained’ and the PLAN_TABLE will be populated.  

• The ‘select * from table(dbms_xplan.display)’ will format and display the current contents of this PLAN_TABLE.

select * from table(dbms_xplan.display);

DBMS_XPLAN.Display_Cursor takes into account the SQL_ID, an identifier for each executed SQL  and will produce an explain plan out of the V$SQL dictionary view and is useful for SQL that has been recently executed 


DBMS_XPLAN.Display_awr also takes the SQL_ID identifier but looks in the Automated Workload Repository (AWR) and reports back explain plans that the SQL had also executed with but over the prior seven days .

Subprogram	Description
DISPLAY Function	Displays the contents of the plan table
DISPLAY_AWR Function	Displays the contents of an execution plan stored in the AWR
DISPLAY_CURSOR Function	Displays the execution plan of any cursor in the cursor cache
DISPLAY_PLAN Function	Displays the contents of the plan table in a variety of formats with CLOB output type
DISPLAY_SQL_PLAN_BASELINE Function	Displays one or more execution plans for the specified SQL handle of a SQL plan baseline
DISPLAY_SQLSET Function	Displays the execution plan of a given statement stored in a SQL tuning set

 

DISPLAY Function

This table function displays the contents of the plan table.

In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the specified table to select rows of the plan to display.

Syntax

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL,

   format        IN  VARCHAR2  DEFAULT  'TYPICAL',

   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Parameter	Description
table_name	Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command. If NULL is specified it also defaults to PLAN_TABLE.
statement_id	Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.If no statement_id is specified, the function shows you the plan of the most recent explained statement.
format	Controls the level of details for the plan. It accepts four 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). 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 Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information. If the target plan table (see table_name parameter) also stores plan statistics columns (for example, it is a table used to capture the content of the fixed view V$SQL_PLAN_STATISTICS_ALL), additional format keywords can be used to specify which class of statistics to display when using the DISPLAY Function. These additional format keywords are IOSTATS, MEMSTATS, ALLSTATS and LAST (see the DISPLAY_CURSOR Function or the DISPLAY_SQLSET Function for a full description of these four keywords).
filter_preds	SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. When value is NULL (the default), the plan displayed corresponds to the last executed explain plan. For example: filter_preds=>'plan_id = 10' Can reference any column of the table where the plan is stored and can contain any SQL construct (for example, sub-query, function calls (see WARNING under Usage Notes)

Usage Notes

Here are some ways you might use variations on the format parameter:

• Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.
• Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.
• Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.
• Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

WARNING:

Application developers should expose the filter_preds parameter to end-users only after careful consideration because this could expose the application to SQL injection. Indeed, filter_preds can potentially reference any table or execute any server function for which the database user invoking the table function has privileges.

Examples

To display the result of the last EXPLAIN PLAN command stored in the plan table:

SELECT * FROM table (DBMS_XPLAN.DISPLAY);

To display from other than the default plan table, "my_plan_table":

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));

To display the minimum plan information:

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

To display the plan for a statement identified by 'foo', such as statement_id='foo':

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

---

DISPLAY_AWR Function

This table function displays the contents of an execution plan stored in the AWR.

Syntax

DBMS_XPLAN.DISPLAY_AWR(

   sql_id            IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id             IN      NUMBER DEFAULT NULL,

   format            IN      VARCHAR2 DEFAULT TYPICAL);

Parameter	Description
sql_id	Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
plan_hash_value	Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function returns all stored execution plans for a given SQL_ID.
db_id	Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, the database_id of the local database is used, as shown in V$DATABASE.
format	Controls the level of details for the plan. It accepts four 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).
	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 Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.

Usage Notes

• To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE, otherwise it shows an appropriate error message.
• Here are some ways you might use variations on the format parameter:
• Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.
• Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.
• Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.
• Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

Examples

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

To display all execution plans of all stored SQL statements containing the string 'TOTO':

SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table

    (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf

 WHERE ht.sql_text like '%TOTO%';

---

 

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id           IN  VARCHAR2  DEFAULT  NULL,

   cursor_child_no  IN  NUMBER    DEFAULT  0,

   format           IN  VARCHAR2  DEFAULT  'TYPICAL');

Parameter	Description
sql_id	Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session is displayed.
cursor_child_no	Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
format	Controls the level of details for the plan. It accepts four 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). 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.

Usage Notes

• To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it shows an appropriate error message.
• Here are some ways you might use variations on the format parameter:
• Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.
• Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.
• Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.
• Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

Examples

To display the execution plan of the last SQL statement executed by the current session:

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

To display runtime statistics for the cursor included in the preceding statement:

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');