DBMS_XPLAN包获取sql执行计划

淩亂°似流年 2022-04-02 11:08 196阅读 0赞

Using DBMS\_XPLAN

Overview

Security Model

Examples

Overview

The DBMS\_XPLAN package supplies four table functions:

DISPLAY - to format and display the contents of a plan table.

DISPLAY\_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

DISPLAY\_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

DISPLAY\_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

Security Model

This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY\_CURSOR requires to have select privileges on the following fixed views: V$SQL\_PLAN, V$SESSION and V$SQL\_PLAN\_STATISTICS\_ALL.  
All these privileges are automatically granted as part of the SELECT\_CATALOG role.

Examples

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

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\_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,
       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 '`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\_AWR Function和 DISPLAY\_SQLSET Function

### 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 132-3 DISPLAY\_AWR Table Function Parameters

<table title="DISPLAY_AWR Table Function Parameters " style="max-width:70em; font-size:12px"> 
 <thead> 
 <tr> 
 <th align="left" style="text-align:left; vertical-align:bottom; font-weight:bold"> Parameter</th> 
 <th align="left" style="text-align:left; vertical-align:bottom; font-weight:bold"> Description</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">sql_id</code> </td> 
 <td align="left" style="vertical-align:top"> Specifies the&nbsp;<code style="font-family:monospace; font-size:12px">SQL_ID</code>&nbsp;of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column<code style="font-family:monospace; font-size:12px">SQL_ID</code>&nbsp;in&nbsp;<code style="font-family:monospace; font-size:12px">DBA_HIST_SQLTEXT</code>. </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">plan_hash_value</code> </td> 
 <td align="left" style="vertical-align:top"> Specifies the&nbsp;<code style="font-family:monospace; font-size:12px">PLAN_HASH_VALUE</code>&nbsp;of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given&nbsp;<code style="font-family:monospace; font-size:12px">SQL_ID</code>. </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">db_id</code> </td> 
 <td align="left" style="vertical-align:top"> Specifies the&nbsp;<code style="font-family:monospace; font-size:12px">database_id</code>&nbsp;for which the plan of the SQL statement, identified by&nbsp;<code style="font-family:monospace; font-size:12px">SQL_ID</code>&nbsp;should be displayed. If not supplied, the&nbsp;<code style="font-family:monospace; font-size:12px">database_id</code>&nbsp;of the local database will be used, as shown in&nbsp;<code style="font-family:monospace; font-size:12px">V$DATABASE</code>. </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">format</code> </td> 
 <td align="left" style="vertical-align:top"> Controls the level of details for the plan. It accepts four values: 
 <ul> 
 <li> <code style="font-family:monospace; font-size:12px">BASIC</code>: Displays the minimum information in the plan—the operation ID, the operation name and its option. </li> 
 <li> <code style="font-family:monospace; font-size:12px">TYPICAL</code>: 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&nbsp;<code style="font-family:monospace; font-size:12px">PROJECTION</code>,&nbsp;<code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;and<code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;<code style="font-family:monospace; font-size:12px">SQL</code>&nbsp;information (see below). </li> 
 <li> <code style="font-family:monospace; font-size:12px">SERIAL</code>: Like&nbsp;<code style="font-family:monospace; font-size:12px">TYPICAL</code>&nbsp;except that the parallel information is not displayed, even if the plan executes in parallel. </li> 
 <li> <code style="font-family:monospace; font-size:12px">ALL</code>: Maximum user level. Includes information displayed with the&nbsp;<code style="font-family:monospace; font-size:12px">TYPICAL</code>&nbsp;level with additional information (<code style="font-family:monospace; font-size:12px">PROJECTION</code>,&nbsp;<code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;and information about&nbsp;<code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;<code style="font-family:monospace; font-size:12px">SQL</code>&nbsp;if the operation is distributed). </li> 
 </ul> </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top">&nbsp;</td> 
 <td align="left" style="vertical-align:top"> 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&nbsp;<code style="font-family:monospace; font-size:12px">PARTITION</code>) or logical additions to the base plan table output (such as&nbsp;<code style="font-family:monospace; font-size:12px">PREDICATE</code>). Format keywords must be separated by either a comma or a space: 
 <ul> 
 <li> <code style="font-family:monospace; font-size:12px">ROWS</code>&nbsp;- if relevant, shows the number of rows estimated by the optimizer </li> 
 <li> <code style="font-family:monospace; font-size:12px">BYTES</code>&nbsp;- if relevant, shows the number of bytes estimated by the optimizer </li> 
 <li> <code style="font-family:monospace; font-size:12px">COST</code>&nbsp;- if relevant, shows optimizer cost information </li> 
 <li> <code style="font-family:monospace; font-size:12px">PARTITION</code>&nbsp;- if relevant, shows partition pruning information </li> 
 <li> <code style="font-family:monospace; font-size:12px">PARALLEL</code>&nbsp;- if relevant, shows PX information (distribution method and table queue information) </li> 
 <li> <code style="font-family:monospace; font-size:12px">PREDICATE</code>&nbsp;- if relevant, shows the predicate section </li> 
 <li> <code style="font-family:monospace; font-size:12px">PROJECTION</code>&nbsp;-if relevant, shows the projection section </li> 
 <li> <code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;- if relevant, shows the "Query Block Name / Object Alias" section </li> 
 <li> <code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;- if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL) </li> 
 <li> <code style="font-family:monospace; font-size:12px">NOTE</code>&nbsp;- if relevant, shows the note section of the explain plan </li> 
 </ul> Format keywords can be prefixed by the sign '<code style="font-family:monospace; font-size:12px">-</code>' to exclude the specified information. For example, '<code style="font-family:monospace; font-size:12px">-PROJECTION</code>' excludes projection information. </td> 
 </tr> 
 </tbody> 
</table>

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 will show 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\_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;

Parameters

Table 132-5 DISPLAY\_SQLSET Function Parameters

<table title="DISPLAY_SQLSET Function Parameters " style="max-width:70em; font-size:12px"> 
 <thead> 
 <tr> 
 <th align="left" style="text-align:left; vertical-align:bottom; font-weight:bold"> Parameter</th> 
 <th align="left" style="text-align:left; vertical-align:bottom; font-weight:bold"> Description</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">sqlset_name</code> </td> 
 <td align="left" style="vertical-align:top"> Name of the SQL Tuning Set </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">sql_id</code> </td> 
 <td align="left" style="vertical-align:top"> Specifies the sql_id value for a SQL statement having its plan stored in the SQL tuning set. You can find all stored SQL statements by querying table function&nbsp;<code style="font-family:monospace; font-size:12px">DBMS_SQLTUNE.SELECT_SQLSET</code> </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">plan_hash_value</code> </td> 
 <td align="left" style="vertical-align:top"> Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown. </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">format</code> </td> 
 <td align="left" style="vertical-align:top"> Controls the level of details for the plan. It accepts four values: 
 <ul> 
 <li> <code style="font-family:monospace; font-size:12px">BASIC</code>: Displays the minimum information in the plan—the operation ID, the operation name and its option. </li> 
 <li> <code style="font-family:monospace; font-size:12px">TYPICAL</code>: 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&nbsp;<code style="font-family:monospace; font-size:12px">PROJECTION</code>,&nbsp;<code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;and<code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;<code style="font-family:monospace; font-size:12px">SQL</code>&nbsp;information (see below). </li> 
 <li> <code style="font-family:monospace; font-size:12px">SERIAL</code>: Like&nbsp;<code style="font-family:monospace; font-size:12px">TYPICAL</code>&nbsp;except that the parallel information is not displayed, even if the plan executes in parallel. </li> 
 <li> <code style="font-family:monospace; font-size:12px">ALL</code>: Maximum user level. Includes information displayed with the&nbsp;<code style="font-family:monospace; font-size:12px">TYPICAL</code>&nbsp;level with additional information (<code style="font-family:monospace; font-size:12px">PROJECTION</code>,&nbsp;<code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;and information about&nbsp;<code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;<code style="font-family:monospace; font-size:12px">SQL</code>&nbsp;if the operation is distributed). </li> 
 </ul> </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top">&nbsp;</td> 
 <td align="left" style="vertical-align:top"> 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&nbsp;<code style="font-family:monospace; font-size:12px">PARTITION</code>) or logical additions to the base plan table output (such as&nbsp;<code style="font-family:monospace; font-size:12px">PREDICATE</code>). Format keywords must be separated by either a comma or a space: 
 <ul> 
 <li> <code style="font-family:monospace; font-size:12px">ROWS</code>&nbsp;- if relevant, shows the number of rows estimated by the optimizer </li> 
 <li> <code style="font-family:monospace; font-size:12px">BYTES</code>&nbsp;- if relevant, shows the number of bytes estimated by the optimizer </li> 
 <li> <code style="font-family:monospace; font-size:12px">COST</code>&nbsp;- if relevant, shows optimizer cost information </li> 
 <li> <code style="font-family:monospace; font-size:12px">PARTITION</code>&nbsp;- if relevant, shows partition pruning information </li> 
 <li> <code style="font-family:monospace; font-size:12px">PARALLEL</code>&nbsp;- if relevant, shows PX information (distribution method and table queue information) </li> 
 <li> <code style="font-family:monospace; font-size:12px">PREDICATE</code>&nbsp;- if relevant, shows the predicate section </li> 
 <li> <code style="font-family:monospace; font-size:12px">PROJECTION</code>&nbsp;-if relevant, shows the projection section </li> 
 <li> <code style="font-family:monospace; font-size:12px">ALIAS</code>&nbsp;- if relevant, shows the "Query Block Name / Object Alias" section </li> 
 <li> <code style="font-family:monospace; font-size:12px">REMOTE</code>&nbsp;- if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL) </li> 
 <li> <code style="font-family:monospace; font-size:12px">NOTE</code>&nbsp;- if relevant, shows the note section of the explain plan </li> 
 <li> <code style="font-family:monospace; font-size:12px">IOSTATS</code>&nbsp;- assuming that basic plan statistics are collected when SQL statements are executed (either by using the<code style="font-family:monospace; font-size:12px">gather_plan_statistics</code>&nbsp;hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for&nbsp;<code style="font-family:monospace; font-size:12px">ALL</code>&nbsp;(or only for the&nbsp;<code style="font-family:monospace; font-size:12px">LAST</code>&nbsp;as shown below) executions of the cursor. </li> 
 <li> <code style="font-family:monospace; font-size:12px">MEMSTATS</code>&nbsp;- Assuming that PGA memory management is enabled (that is,&nbsp;<code style="font-family:monospace; font-size:12px">pga_aggregate_target</code>&nbsp;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. </li> 
 <li> <code style="font-family:monospace; font-size:12px">ALLSTATS</code>&nbsp;- A shortcut for&nbsp;<code style="font-family:monospace; font-size:12px">'IOSTATS MEMSTATS'</code> </li> 
 <li> <code style="font-family:monospace; font-size:12px">LAST</code>&nbsp;- By default, plan statistics are shown for all executions of the cursor. The keyword&nbsp;<code style="font-family:monospace; font-size:12px">LAST</code>&nbsp;can be specified to see only the statistics for the last execution. </li> 
 </ul> The following two formats are deprecated but supported for backward compatibility: 
 <ul> 
 <li> <code style="font-family:monospace; font-size:12px">RUNSTATS_TOT</code>&nbsp;- Same as&nbsp;<code style="font-family:monospace; font-size:12px">IOSTATS,</code>&nbsp;that is, displays IO statistics for all executions of the specified cursor. </li> 
 <li> <code style="font-family:monospace; font-size:12px">RUNSTATS_LAST</code>&nbsp;- Same as&nbsp;<code style="font-family:monospace; font-size:12px">IOSTATS</code>&nbsp;<code style="font-family:monospace; font-size:12px">LAST</code>, that is, displays the runtime statistics for the last execution of the cursor </li> 
 </ul> Format keywords can be prefixed by the sign '<code style="font-family:monospace; font-size:12px">-</code>' to exclude the specified information. For example, '<code style="font-family:monospace; font-size:12px">-PROJECTION</code>' excludes projection information. </td> 
 </tr> 
 <tr> 
 <td align="left" style="vertical-align:top"> <code style="font-family:monospace; font-size:12px">sqlset_owner</code> </td> 
 <td align="left" style="vertical-align:top"> The owner of the SQL tuning set. The default is the current user. </td> 
 </tr> 
 </tbody> 
</table>

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