DBMS_XPLAN (original) (raw)

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. Finally, it displays plans from a SQL plan baseline.

See Also:

• For more information on the EXPLAIN PLAN command, the AWR, and SQL tuning set, see Oracle Database SQL Tuning Guide.
• For more information on the V$SQL_PLAN fixed view, see Oracle Database Reference
• For more information on the V$SQL_PLAN_STATISTICS fixed view, see Oracle Database Reference

This chapter contains the following topics:

• Overview
• Security Model
• Examples
• Summary of DBMS_XPLAN Subprograms

207.1 DBMS_XPLAN Overview

The DBMS_XPLAN package supplies five table functions.

These functions are listed below:

• 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.

207.2 DBMS_XPLAN Security Model

This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requiresSELECT or READ privileges on the following fixed views: V$SQL_PLAN, V$SESSION andV$SQL_PLAN_STATISTICS_ALL. This function also requiresSELECT/READ permissions onV$SQL.

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

DISPLAY_SQLSET Function requires the user to have the SELECT or READ privilege onALL_SQLSET_STATEMENTS andALL_SQLSET_PLANS.

DISPLAY_SQL_PLAN_BASELINE Function requires the user to have the SELECT or READ privilege on DBA_SQL_PLAN_BASELINES as well as the privileges to execute the SQL statement for which the user is trying to get the plan.

The preceding privileges are granted automatically as part of SELECT_CATALOG_ROLE.

207.3 Examples

These examples show sample uses of DBMS_XPLAN.

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

This query produces the following output:

Plan hash value: 3693697075

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("E"."ENAME"='benoit')

15 rows selected.

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

This query produces the following output:

Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0

SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno AND e.empno=7369

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | | | |* 1 | HASH JOIN | | 1 | 16 | 6 (34)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (34)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("E"."EMPNO"=7369)

21 rows selected.

You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.

Run a query with a distinctive comment:

SELECT /* TOTO */ ename, dname FROM dept d join emp e USING (deptno);

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

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0

SELECT /* TOTO */ ename, dname FROM dept d JOIN emp e USING (deptno);

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 7 (100)| | | 1 | SORT GROUP BY | | 4 | 64 | 7 (43)| 00:00:01 | |* 2 | HASH JOIN | | 14 | 224 | 6 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (34)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 70 | 3 (34)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("E"."DEPTNO"="D"."DEPTNO")

Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:

Display the execution plan of all cursors matching the string 'TOTO':

SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib |

| 0 | SELECT STATEMENT | | 1 | 117 | 6 (50) | 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (ORDER) |:TQ10003 | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | PCWP | | | 4 | PX RECEIVE | | 1 | 117 | 5 (40) | 00:00:01 | Q1,03 | PCWP | | | 5 | PX SEND RANGE |:TQ10002 | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | P->P | RANGE | |* 6 | HASH JOIN | | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | PCWP | | | 7 | PX RECEIVE | | 1 | 87 | 2 (50) | 00:00:01 | Q1,02 | PCWP | | | 8 | PX SEND HASH |:TQ10001 | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | P->P | HASH | | 9 | PX BLOCK ITERATOR | | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWC | | |* 10| TABLE ACCESS FULL | EMP | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWP | | | 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | | | 12 | PX RECEIVE | | 4 | 120 | 3 (34) | 00:00:01 | Q1,02 | PCWP | | | 13 | PX SEND HASH |:TQ10000 | 4 | 120 | 3 (34) | 00:00:01 | | S->P | HASH | | 14 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (34) | 00:00:01 | | | |

Predicate Information (identified by operation id):

6 - access("E"."DEPTNO"="D"."DEPTNO") 10 - filter("E"."ENAME"='hermann')

When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).

By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN. However, you should purge the plan table regularly to ensure good performance in the execution of the DISPLAY table function. If no plan table is created, Oracle uses a global temporary table to store any plan information for individual users and preserves its content throughout the lifespan of a session. Note that you cannot truncate the content of a global temporary table.

For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAIN PLAN command:

Using a View to Display Last Explain Plan

define plan view

CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

display the output of the last explain plan command

SELECT * FROM PLAN;

207.4 Summary of DBMS_XPLAN Subprograms

This table lists the DBMS_XPLAN subprograms and briefly describes them.

207.4.1 DIFF_PLAN Function

This function compares plans.

Syntax

DBMS_XPLAN.DIFF_PLAN( plan1 IN SPC_SRC, plan2 IN SPC_SRC) RETURN VARCHAR2;

Parameters

Table 207-2 DIFF_PLAN Function Parameters

207.4.2 DISPLAY Function

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

Parameters

Table 207-3 DISPLAY Function Parameters

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='sales_query':

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

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

Parameters

Table 207-4 DISPLAY_AWR Table Function Parameters

Usage Notes

• To use the DISPLAY_AWR functionality, the calling user must have SELECT or READ 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%';

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

Parameters

Table 207-5 DISPLAY_CURSOR Function Parameters

Usage Notes

• To use the DISPLAY_CURSOR functionality, the calling user must have SELECT or READ 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');

207.4.5 DISPLAY_PLAN Function

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;

Parameters

Table 207-6 DISPLAY_PLAN Function Parameters

Return Values

Returns the requested report as CLOB

Usage Notes

Active reports have a rich, interactive user interface akin to that found in Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

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.

207.4.6 DISPLAY_SQL_PLAN_BASELINE Function

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;

Parameters

Table 207-7 DISPLAY_SQL_PLAN_BASELINE Function Parameters

Return Values

A PL/SQL type table

Usage Notes

Examples

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

SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b1d49f6074ab95af')) 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;

207.4.7 DISPLAY_SQLSET Function

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;

Parameters

Table 207-8 DISPLAY_SQLSET Function Parameters

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.

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