DBMS_XPLAN : Display Oracle Execution Plans

发布时间：2020-06-30 00:13:06 作者：MeredithMrh
来源：网络阅读：798

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_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 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

DBMS_XPLAN : Display Oracle Execution Plans

Setup

DISPLAY Function

DISPLAY_CURSOR Function

Other Functions

相关阅读