The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the “utlxpls.sql” script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.
DBMS_XPLAN包是用来格式化执行计划输出的，其最初在9i的时候引入，用于替代用户执行utlxpls.sql脚本和查询计划表；在随后的ORACLE版本中增强了这个包的功能
Setup
If it is not already present create the SCOTT schema.
conn sys/password as sysdba @$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE if it does not already exist.
conn sys/password as sysdba @$ORACLE_HOME/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table; GRANT ALL ON sys.plan_table TO public;
DISPLAY Function
The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain a SQL statement.
CONN scott/tiger EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
18 rows selected.
SQL>
The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:
-
table_name – Name of the
PLAN_TABLE, default value ‘PLAN_TABLE’. -
statement_id – Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the
PLAN_TABLE. -
format – Controls the level of detail displayed, default value ‘TYPICAL’. Other values include ‘BASIC’, ‘ALL’, ‘SERIAL’. There is also an undocumented ‘ADVANCED’ setting.
table_name：指定计划表的名字，默认为PLAN_TABLE
statement_id：指定要显示的执行计划的statement_id；默认为null，意味着显示计划表中最新的执行计划
format：格式化定制输出执行计划，默认值为format；还有basic，all，serial以及阿斗advanced值
Note. From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with ‘-‘). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.
EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));
Plan hash value: 3625962092
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | EMP |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT |
------------------------------------------------
12 rows selected.
SQL>
DISPLAY_CURSOR Function
In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these.
ORACLE10.1之后的版本引入了一个新的display_cursor函数，这个函数用于显示存储在library cahce池中的真实的执行计划；而不是像display函数那样显示一个从plan_table评估出的执行计划。
display_cursor函数从动态视图v$sql_plan_statistics_all和v$sql_plan中获取信息，所以用户要具有这两个视图的执行权限
It accepts three optional parameters:
-
sql_id – The
SQL_IDof the statement in the cursor cache. TheSQL_IDas available from theV$SQLandV$SQLAREAviews, or from theV$SESSIONview using thePREV_SQL_IDcolumn. If omitted, the last cursor executed by the session is displayed. -
child_number – The child number of the cursor specified by the
SQL_IDparameter. If not specified, all cursors for the specifiedSQL_IDare diaplyed. -
format – In addition to the setting available for the
DISPLAYfunction, this function also has ‘RUNSTATS_LAST’ and ‘RUNSTATS_TOT’ to display the last and total runtime statistics respectively. These additional format options require “STATISTICS_LEVEL=ALL”.
sql_id：游标池中的sql_id(其实就是父游标号)，sql_id可以从v$sql和v$sqlarea视图中获取，也可以使用prev_sal_id列从v$session视图中获取；默认会取出会话最近执行的语句的游标信息
child_number：子游标号；如果没有指定，将会展示父游标下的所有子游标的执行计划
format：允许使用display函数的所有的format参数，还可以设置runstats-last和runstats_tot来获取最近一次的和全部的运行时统计信息；需要设置为statistics_level=all状态
The following example show the advanced output from a query on the SCOTT schema.
CONN / AS SYSDBA
GRANT SELECT ON v_$session TO scott;
GRANT SELECT ON v_$sql TO scott;
GRANT SELECT ON v_$sql_plan TO scott;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott;
CONN scott/tiger
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID gu62pbk51ubc3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename
= 'SMITH'
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],
"D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
67 rows selected.
SQL>
Other Functions
There are some other useful functions in the package, but I don’t find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.
-
DISPLAY_AWR– Introduced in Oracle 10g Release 1, this function displays an execution plan stored in the Advanced Workload Repository (AWR). -
DISPLAY_SQLSET– Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set. -
DISPLAY_SQL_PLAN_BASELINE– Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline. -
DISPLAY_PLAN– Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.
文章原文：http://oracle-base.com/articles/9i/dbms_xplan.php
