Get the Execution Plan from/using DBMS_XPLAN

The DBMS_XPLAN package is used to format the output of an explain plan. The DBMS_XPLAN package supplies five table functions:

• DISPLAY: To format and display the contents of a plan table
• DISPLAY_AWR: To format and display the contents of the execution plan of a stored SQL statement in the AWR.
• DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.
• DISPLAY_SQL_PLAN_BASELINE: To display one or more execution plans for the SQL statement identified by SQL handle.
• DISPLAY_SQLSET: To format and display the contents of the execution plan of statements stored in a SQL tuning set

An advantage of using the DBMS_XPLAN package table functions is that the output is
formatted consistently without regard to the source.


DISPLAY :

This table function displays the contents of the plan table. The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

• table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
• statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
• format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

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);

Example:

       SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

       SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','id','BASIC'));

DISPLAY_AWR :

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);

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 :

This table function displays the explain plan of any cursor loaded in the cursor cache.

Syntax: 

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id             IN  VARCHAR2  DEFAULT  NULL,

   child_number  IN  NUMBER     DEFAULT  NULL, 

   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

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 'atfwcg8anryp':

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anryp'));

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

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anryp', NULL, 'ALLSTATS LAST');

DISPLAY_SQL_PLAN_BASELINE:

This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

Syntax

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (

   sql_handle       IN VARCHAR2 := NULL,

   plan_name       IN VARCHAR2 := NULL,

   format            IN VARCHAR2 := 'TYPICAL')

 RETURN dbms_xplan_type_table;

Examples

Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab9af' using TYPICAL format

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

  FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

                 'SYS_SQL_b1d49f6074ab9af')) t;

Display all plans of one or more SQL statements containing the string 'HR2' using BASIC format

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

   FROM (SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines

         WHERE sql_text like '%HR2%') pb,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(pb.sql_handle, NULL,

                                                   'BASIC')) t;

DISPLAY_SQLSET Function:

This table function displays the execution plan of a given statement stored in a SQL tuning set.

Syntax

DBMS_XPLAN.DISPLAY_SQLSET(

   sqlset_name      IN  VARCHAR2, 

   sql_id               IN  VARCHAR2,

   plan_hash_value  IN NUMBER := NULL,

   format              IN  VARCHAR2  := 'TYPICAL', 

   sqlset_owner     IN  VARCHAR2  := NULL)

  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Examples

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqhqbf' and PLAN HASH 369369707 in the SQL Tuning Set called 'OLTP_optimization_0405":

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

       'OLTP_optimization_0405','gwp663cqhqbf', 369369707));

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

      'OLTP_optimization_0405','gwp663cqhqbf'));

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

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

      'OLTP_optimization_0405', 'gwp663cqhqbf', NULL, 'ALLSTATS LAST');