DBMS_XPLAN

The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views
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.

The table function DISPLAY_CURSOR requires SELECT or READ privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

The DISPLAY_AWR Function requires the user to have SELECT or READ privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.

The DISPLAY_SQLSET Functionrequires the user to have the SELECT or READ privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS.

The DISPLAY_SQL_PLAN_BASELINE Function requires the user to have the SELECT or READ privilege on DBA_SQL_PLAN_BASELINES

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY

Execute an explain plan command on a SELECT statement:

EXPLAIN PLAN FOR

SELECT * FROM emp e, dept d

   WHERE e.deptno = d.deptno

   AND e.ename='benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR

By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. For example:

SELECT ename  FROM  emp e, dept d

   WHERE   e.deptno = d.deptno 

   AND   e.empno=7369;

ENAME

----------

SMITH

To display the execution plan of the last executed statement for that session:

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

Get sql_id and child_number for the preceding statement:

SELECT sql_id, child_number

FROM v$sql

WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER

----------     -----------------------------

gwp663cqh5qbf   0

Display the execution plan for the cursor:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

Displaying a Plan Table with Parallel Information

By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel.

ALTER TABLE emp PARALLEL;

EXPLAIN PLAN for

SELECT * FROM emp e, dept d

   WHERE e.deptno = d.deptno

   AND e.ename    ='hermann'

   ORDER BY e.empno;

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 3693697345

DIFF_PLAN Function

This function compares plans.

Syntax

DBMS_XPLAN.DIFF_PLAN(

   plan1    IN   SPC_SRC,

   plan2    IN   SPC_SRC)

 RETURN VARCHAR2;

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

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

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

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

DISPLAY_PLAN Function

This table function displays the contents of the plan table in a variety of formats with CLOB output type.

Syntax

DBMS_XPLAN.DISPLAY_PLAN (

   table_name       IN    VARCHAR2   DEFAULT 'PLAN_TABLE',

   statement_id     IN    VARCHAR2   DEFAULT NULL,

   format           IN    VARCHAR2   DEFAULT 'TYPICAL',

   filter_preds     IN    VARCHAR2   DEFAULT NULL,

   type             IN    VARCHAR2   DEFAULT 'TEXT')

  RETURN CLOB;

DISPLAY_SQL_PLAN_BASELINE Function

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;

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 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

       'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));

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','gwp663cqh5qbf'));

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

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

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