本文介绍Oracle数据库中DBMS_XPLAN包的功能及使用方法。该包提供四个表格函数，用于显示从计划表、游标、AWR及SQL调优集获取的执行计划。文章通过实例展示了如何使用这些函数来分析SQL执行效率。
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_AWRfunctionality, the calling user must haveSELECTprivilege 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
formatparameter:-
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');
扫一扫
2886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
