213 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. 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:
213.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.
213.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
requires
SELECT
or READ
privileges on the following fixed
views: V$SQL_PLAN
, V$SESSION
and
V$SQL_PLAN_STATISTICS_ALL.
This function also requires
SELECT
/READ
permissions on
V$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 on
ALL_SQLSET_STATEMENTS
and
ALL_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
.
213.3 DBMS_XPLAN Data Structures
The DBMS_XPLAN
package defines a TABLE
type.
Table Types
213.3.1 DBMS_XPLAN PLAN_OBJECT_LIST Table Type
This type allows for a list of generic objects as input to the COMPARE_PLANS
function.
Syntax
TYPE plan_object_list IS TABLE OF generic_plan_object;
The generic object abstracts the common attributes of plans from all plan sources. Every plan source is a subclass of the plan_object_list
superclass. The following table summarizes the different plan sources. Note that when an optional parameter is null, it can correspond to multiple objects. For example, if you do not specify a child number for cursor_cache_object
, then it matches all cursor cache statements with the specified SQL ID.
Table 213-1 Plan Sources for PLAN_OBJECT_LIST
Plan Source | Specification | Description |
---|---|---|
Plan table |
|
The parameters are as follows:
|
Cursor cache |
|
The parameters are as follows:
|
AWR |
|
The parameters are as follows:
|
SQL tuning set |
|
The parameters are as follows:
|
SQL plan management |
|
The parameters are as follows:
|
SQL profile |
|
The |
Advisor |
|
The parameters are as follows:
|
213.4 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 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 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, 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 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;
213.5 Summary of DBMS_XPLAN Subprograms
This table lists the DBMS_XPLAN subprograms and briefly describes them.
Table 213-2 DBMS_XPLAN Package Subprograms
Subprogram | Description |
---|---|
Compares each plan in a list with a reference plan and returns the report |
|
Compares plans |
|
Displays the contents of the plan table |
|
Displays the contents of an execution plan stored in the AWR |
|
Displays the execution plan of any cursor in the cursor cache |
|
Displays the contents of the plan table in a variety of formats with |
|
Displays one or more execution plans for the specified SQL handle of a SQL plan baseline |
|
Displays the execution plan of a given statement stored in a SQL tuning set |
213.5.1 COMPARE_PLANS Function
This function compares each plan in a list with a reference plan and returns the report.
Syntax
DBMS_XPLAN.COMPARE_PLANS(
reference_plan IN generic_plan_object,
compare_plan_list IN plan_object_list,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL',
section IN VARCHAR2 := 'ALL')
RETURN CLOB;
Parameters
Table 213-3 COMPARE_PLANS Function Parameters
Parameter | Description |
---|---|
|
The reference plan. This plan should always evaluate to a single plan. |
|
List of plans to compare with reference plan. The |
|
Type of the report. Possible values are:
|
|
Format of the report. Possible values are:
|
|
A particular section in the report. Possible values are:
|
Example 213-1 Examples
The following examples illustrate the usage of COMPARE_PLANS Function.
var report clob; exec :report := dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), - plan_object_list (cursor_cache_object(‘8mkxm7ur07za0’, 4))); print report
The above example compares the plan of child cursor number 2 for the SQL ID ‘8mkxm7ur07za0’ with that of the child cursor number 4 for the same SQL ID. Returns the report in the text format (default).
var report clob; exec :report := dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), plan_object_list( spm_object(‘SQL_024d0f7d21351f5d’, ‘SQL_PLAN_sdfjkd’))); print report
The above example compares the plan of child cursor number 2 for the SQL ID ‘8mkxm7ur07za0’ with that of the plan baseline captured by SPM for query whose SQL handle is ‘SQL_024d0f7d21351f5d’ and plan name is ‘SQL_PLAN_sdfjkd’. Returns the report in the text format (default).
var report clob; exec :report = dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), plan_object_list( cursor_cache_object(‘8mkxm7ur07za0’), sqlset_object(‘SH’, ‘SQLT_WORKLOAD’, ‘ 6vfqvav0rgyad’), awr_object(‘6vfqvav0rgyad’, 5), spm_object(‘SQL_024d0f7d21351f5d’, ‘SQL_PLAN_ sdfjkd’), plan_table_object(‘SH’, ‘plan_table’, NULL, 38), sql_profile_object(‘pe3r3ejsfd’), a dvisor_object(‘TASK_1228’, ‘EXEC_1928’, ‘8mkxm7ur07za0’)), type => ‘XML’, level => ‘ALL’, section => ‘SUMMARY’);
The above example compares the plan of child cursor number 2 for the SQL ID ‘8mkxm7ur07za0’ with each of the plans in the following list:
-
cursor_cache_object(‘8mkxm7ur07za0’): All the plans in the cursor cache that are generated for the SQL ID ‘8mkxm7ur07za0’.
-
sqlset_object(‘SH’, ‘SQLT_WORKLOAD’, ‘6vfqvav0rgyad’): All the plans generated in the SQL tuning set SH. SQLT_WORKLOAD for the SQL ID ‘6vfqvav0rgyad’.
-
awr_object(‘6vfqvav0rgyad’, 5): All the plans in AWR that are captured for database ID 5 and SQL ID ‘6vfqvav0rgyad’.
-
spm_object (‘SQL_024d0f7d21351f5d, ‘SQL_PLAN_sdfjkd’): The plan baseline for the query with SQL handle ‘SQL_024d0f7d21351f5d’ with name ‘SQL_PLAN_sdfjkd’.
-
plan_table_object(‘SH’, ‘plan_table’, NULL, 38): Plan stored in SH.plan_table identified by plan_id=38.
-
sql_profile_object(‘pe3r3ejsfd’): Plan identified by the SQL profile name ‘pe3r3ejsfd’.
-
advisor_object(‘TASK_1228’, ‘EXEC_1928’, ‘8mkxm7ur07za0’)): All the plans stored in SQL advisor identified by task name ‘TASK_1228’, execution name ‘EXEC_1928’ and SQL ID ‘8mkxm7ur07za0’.
213.5.2 DIFF_PLAN Function
This function compares two sql plans, the reference plan and the target plan. This function returns a task_id
that can be used to retrieve the report of findings.
Syntax
DBMS_XPLAN.DIFF_PLAN( sql_text IN CLOB, outline IN CLOB, user_name IN VARCHAR2 := 'NULL') RETURN VARCHAR2;
Parameters
Table 213-4 DIFF_PLAN Function Parameters
Parameter | Description |
---|---|
|
The text of the SQL statement. |
|
Used to generate the target plan. |
|
The parsing schema name default to current user. |
213.5.3 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);
Parameters
Table 213-5 DISPLAY Function Parameters
Parameter | Description |
---|---|
|
Specifies the table name where the plan is stored. This parameter defaults to |
|
Specifies the |
|
Controls the level of details for the plan. It accepts the following values:
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
Format keywords can be prefixed by the sign ' If the target plan table (see |
|
SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. When value is 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 |
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'));
213.5.4 DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in AWR.
Note:
This function is deprecated. Use DISPLAY_WORKLOAD_REPOSITORY
instead. DISPLAY_AWR
only works with snapshots for the local DBID, whereas DISPLAY_WORKLOAD_REPOSITORY
supports all snapshots inside AWR, including remote and imported snapshots.
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 213-6 DISPLAY_AWR Table Function Parameters
Parameter | Description |
---|---|
|
Specifies the |
|
Specifies the |
|
Specifies the |
|
Controls the level of details for the plan. It accepts four values:
For finer control on the display output, the following keywords can be added to the above four standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as
Format keywords can be prefixed by the sign ' |
Usage Notes
-
To use the
DISPLAY_AWR
functionality, the calling user must haveSELECT
orREAD
privilege onDBA_HIST_SQL_PLAN
,DBA_HIST_SQLTEXT
, andV$DATABASE
, otherwise it shows an appropriate error message. -
The following examples show different ways of using the
format
parameter:-
Use '
BASIC ROWS
' to display basic information with the additional number of rows estimated by the optimizer. -
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 and the predicate section.
-
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%';
213.5.5 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 213-7 DISPLAY_CURSOR Function Parameters
Parameter | Description |
---|---|
|
Specifies the |
|
Child number of the cursor to display. If not supplied, the execution
plan of the |
|
Controls the level of details for the plan. It accepts five values:
For finer control on the display output, you can add the following keywords to the preceding format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as Format keywords must be separated by either a comma or a space:
The following formats are deprecated but supported for backward compatibility:
You can prefix format keywords with the sign ' |
Usage Notes
-
To use the
DISPLAY_CURSOR
functionality, the calling user must haveSELECT
orREAD
privilege on the fixed viewsV$SQL_PLAN_STATISTICS_ALL
,V$SQL
andV$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');
213.5.6 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;
Parameters
Table 213-8 DISPLAY_PLAN Function Parameters
Parameter | Description |
---|---|
|
Specifies the table name where the plan is stored. This parameter defaults to |
|
Specifies the |
|
SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. When value is 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 |
|
Controls the level of details for the plan. It accepts five values:
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
Format keywords can be prefixed by the sign ' If the target plan table (see |
|
Output type, one of: |
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.
213.5.7 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;
Parameters
Table 213-9 DISPLAY_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
SQL statement handle. It identifies a SQL statement whose plans are to be displayed. |
|
Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed. |
|
Format string determines what information stored in the plan displayed. The following format values are possible, each representing a common use case: |
Return Values
A PL/SQL type table
Usage Notes
This function uses plan information stored in the plan baseline to explain and display the plans. The plan_id
stored in the SQL management base may not match the plan_id
of the generated plan. A mismatch between the stored plan_id
and generated plan_id
means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.
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;
213.5.8 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 213-10 DISPLAY_SQLSET Function Parameters
Parameter | Description |
---|---|
|
Name of the SQL Tuning Set |
|
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 |
|
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown. |
|
Controls the level of details for the plan. It accepts four values:
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
The following two formats are deprecated but supported for backward compatibility:
Format keywords can be prefixed by the sign ' |
|
The owner of the SQL tuning set. The default is the current user. |
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');
213.5.9 DISPLAY_WORKLOAD_REPOSITORY Function
This table function displays the contents of an execution plan stored in AWR.
Note:
This function replaces DISPLAY_AWR
, which is deprecated.
Syntax
DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL'
dbid IN NUMBER DEFAULT NULL,
con_dbid IN NUMBER DEFAULT NULL,
awr_location IN VARCHAR2 DEFAULT 'AWR_ROOT'
);
Parameters
Table 213-11 DISPLAY_WORKLOAD_REPOSITORY Table Function Parameters
Parameter | Description |
---|---|
|
Specifies the You can retrieve the appropriate value for the SQL statement of interest by querying the column |
|
Specifies the This parameter is optional. If omitted, the table function returns all stored execution plans for a given |
|
Controls the level of details for the plan. It accepts four values:
For finer control on the display output, the following keywords can be added to the above four standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as
Format keywords can be prefixed by the sign ' |
|
Identifies the plans for a specific database. If this parameter is omitted, then the value defaults to the DBID of the AWR repository pointed to by the initialization parameter |
|
Identifies the plans for a specific container. If this parameter is omitted, then the value defaults to |
|
Specifies the location of the AWR repository. Supported values are:
|
Example 213-2 Querying an AWR Plan
Assume that you log in as an administrator and issue the following query:
select count(*) from sh.sAleS
You create an AWR snapshot as follows:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
You query joint DBA_HIST_SQLTEST
to the function output as follows:
SET LINESIZE 150
SET PAGESIZE 5000
SELECT t.*
FROM DBA_HIST_SQLTEXT ht,
TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY
(ht.sql_id, null, '-PREDICATE +ALIAS',null,null,'AWR_ROOT')) t
WHERE ht.SQL_TEXT LIKE '%sAleS%';
SQL_ID 2f4cx9qjnqd70
--------------------
select count(*) from sh.sAleS
Plan hash value: 1123225294
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 27 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE ALL | | 918K| 27 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION COUNT | | 918K| 27 (0)| 00:00:01 | | |
| 4 | BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX | | | | 1 | 28 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / "SALES"@"SEL$1"