DBMS_XPLAN包获取sql执行计划
来源:互联网 发布:相关系数矩阵怎么看 编辑:程序博客网 时间:2024/05/12 17:32
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
sql_id
Specifies the SQL_ID
of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the columnSQL_ID
in DBA_HIST_SQLTEXT
.
plan_hash_value
Specifies the PLAN_HASH_VALUE
of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID
.
db_id
Specifies the database_id
for which the plan of the SQL statement, identified by SQL_ID
should be displayed. If not supplied, the database_id
of the local database will be used, as shown in V$DATABASE
.
format
Controls the level of details for the plan. It accepts four values:
BASIC
: Displays the minimum information in the plan—the operation ID, the operation name and its option.TYPICAL
: 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 onlyPROJECTION
,ALIAS
andREMOTE
SQL
information (see below).SERIAL
: LikeTYPICAL
except that the parallel information is not displayed, even if the plan executes in parallel.ALL
: Maximum user level. Includes information displayed with theTYPICAL
level with additional information (PROJECTION
,ALIAS
and information aboutREMOTE
SQL
if the operation is distributed).
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 PARTITION
) or logical additions to the base plan table output (such as PREDICATE
). Format keywords must be separated by either a comma or a space:
ROWS
- if relevant, shows the number of rows estimated by the optimizerBYTES
- if relevant, shows the number of bytes estimated by the optimizerCOST
- if relevant, shows optimizer cost informationPARTITION
- if relevant, shows partition pruning informationPARALLEL
- if relevant, shows PX information (distribution method and table queue information)PREDICATE
- if relevant, shows the predicate sectionPROJECTION
-if relevant, shows the projection sectionALIAS
- if relevant, shows the "Query Block Name / Object Alias" sectionREMOTE
- if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)NOTE
- if relevant, shows the note section of the explain plan
Format keywords can be prefixed by the sign '-
' to exclude the specified information. For example, '-PROJECTION
' excludes projection information.
Usage Notes
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.
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
sqlset_name
Name of the SQL Tuning Set
sql_id
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 DBMS_SQLTUNE.SELECT_SQLSET
plan_hash_value
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.
format
Controls the level of details for the plan. It accepts four values:
BASIC
: Displays the minimum information in the plan—the operation ID, the operation name and its option.TYPICAL
: 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 onlyPROJECTION
,ALIAS
andREMOTE
SQL
information (see below).SERIAL
: LikeTYPICAL
except that the parallel information is not displayed, even if the plan executes in parallel.ALL
: Maximum user level. Includes information displayed with theTYPICAL
level with additional information (PROJECTION
,ALIAS
and information aboutREMOTE
SQL
if the operation is distributed).
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 PARTITION
) or logical additions to the base plan table output (such as PREDICATE
). Format keywords must be separated by either a comma or a space:
ROWS
- if relevant, shows the number of rows estimated by the optimizerBYTES
- if relevant, shows the number of bytes estimated by the optimizerCOST
- if relevant, shows optimizer cost informationPARTITION
- if relevant, shows partition pruning informationPARALLEL
- if relevant, shows PX information (distribution method and table queue information)PREDICATE
- if relevant, shows the predicate sectionPROJECTION
-if relevant, shows the projection sectionALIAS
- if relevant, shows the "Query Block Name / Object Alias" sectionREMOTE
- if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)NOTE
- if relevant, shows the note section of the explain planIOSTATS
- assuming that basic plan statistics are collected when SQL statements are executed (either by using thegather_plan_statistics
hint or by setting the parameter statistics_level to ALL), this format will show IO statistics forALL
(or only for theLAST
as shown below) executions of the cursor.MEMSTATS
- Assuming that PGA memory management is enabled (that is,pga_aggregate_target
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.ALLSTATS
- A shortcut for'IOSTATS MEMSTATS'
LAST
- By default, plan statistics are shown for all executions of the cursor. The keywordLAST
can be specified to see only the statistics for the last execution.
The following two formats are deprecated but supported for backward compatibility:
RUNSTATS_TOT
- Same asIOSTATS,
that is, displays IO statistics for all executions of the specified cursor.RUNSTATS_LAST
- Same asIOSTATS
LAST
, that is, displays the runtime statistics for the last execution of the cursor
Format keywords can be prefixed by the sign '-
' to exclude the specified information. For example, '-PROJECTION
' excludes projection information.
sqlset_owner
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');
- DBMS_XPLAN包获取sql执行计划
- dbms_xplan包-获取执行计划
- dbms_xplan.display_cursor 获取执行过的sql的执行计划
- dbms_xplan.display_cursor 获取执行过的sql的执行计划
- 使用dbms_xplan包查看执行计划
- [Oracle]如何查看SQL的执行计划 - DBMS_XPLAN Package
- DBMS_XPLAN.DISPLAY_CURSOR()看执行计划
- dbms_xplan.display_cursor 查看已执行SQL的执行计划(10g后)
- 使用dbms_xplan工具查看执行计划
- 使用dbms_xplan工具查看执行计划
- 获得执行计划方法六-dbms_xplan.diskplay_cursor
- dbms_xplan.display_cursor查看低效的执行计划
- 获得执行计划方法六-dbms_xplan.diskplay_cursor
- dbms_xplan.display_awr方式获取执行计划的实验和之前的误导
- dbms_xplan.display_cursor oracle 10g查看执行计划
- 通过dbms_xplan.display_cursor识别低效的执行计划
- Oracle10中如何用dbms_xplan分析执行计划
- 获取sql的执行计划总结!
- 支配值数目
- 杭电 1425
- java中设置文本框的滚动条到末尾
- 判断机器的大小端
- Tomcat性能调优方案
- DBMS_XPLAN包获取sql执行计划
- 面试题09 从二叉树的深度扩展到判断是否是二叉平衡树 【树】 Dserving thinking
- POJ-1328(贪心算法)
- Android中asset文件夹和raw文件夹区别
- 挑战编程 程序设计竞赛训练手册-1.6.4 液晶显示屏(LC-Display)
- 作为应届毕业生如何写个好简历
- ACM -- 魔兽传说
- 指针编程之道
- 杭电 2084