DBMS_XPLAN : Display Oracle Execution Plans

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.sqlCREATE 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/tigerEXPLAIN PLAN FORSELECT *FROM   emp e, dept dWHERE  e.deptno = d.deptnoAND    e.ename  = 'SMITH';

Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

SET LINESIZE 130SET PAGESIZE 0SELECT * 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' FORSELECT *FROM   emp e, dept dWHERE  e.deptno = d.deptnoAND    e.ename  = 'SMITH';SET LINESIZE 130SELECT * 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_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.

• child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.

• format - In addition to the setting available for the DISPLAY function, 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 SYSDBAGRANT 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/tigerSELECT *FROM   emp e, dept dWHERE  e.deptno = d.deptnoAND    e.ename  = 'SMITH';SET LINESIZE 130SELECT * 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$1Outline 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