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
).
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.
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);
Table 132-3 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 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 ' |
-
To use the
DISPLAY_AWR
functionality, the calling user must haveSELECT
privilege onDBA_HIST_SQL_PLAN
.DBA_HIST_SQLTEXT
, andV$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.
-
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.
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;
Table 132-5 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. |
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.
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');