【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？

小麦苗DBA宝典

发布于 2019-09-29 16:01:24

5400

发布于 2019-09-29 16:01:24

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在Oracle中，获取执行计划有哪几种方法？

♣

答案部分

一般来说，有如下几种获取执行计划的方式：

1、AUTOTRACE方式

AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*Plus会自动收集执行过的SQL语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。

DBA用户可以直接使用AUTOTRACE功能，但是如果用户没有DBA权限，那么需要在SYS用户下执行plustrce.sql脚本，自动创建PLUSTRACE角色，再把PLUSTRACE权限赋给普通用户即可。

$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO USER_LHR;

另外，若启用AUTOTRACE报“SP2-0611”的错误，则可以执行utlxplan.sql脚本来创建表PLAN_TABLE，如下所示：

SQL> set autot on
SP2-0613: 无法验证 PLAN_TABLE 格式或实体
SP2-0611: 启用EXPLAIN报告时出错
SQL> @?/rdbms/admin/utlxplan.sql

在执行如下脚本后，每个用户（包括以后新建的用户）都可以使用AUTOTRACE命令：

@?/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
GRANT ALL ON PLAN_TABLE TO PUBLIC;
@?/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO PUBLIC;

AUTOTRACE的语法如下所示：

SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS]

其中，AUTOTRACE可简写为AUTOT，TRACEONLY可简写为TRACE，EXPLAIN可简写为EXP，STATISTICS可简写为STAT。

SQL> SET AUTOT ON
SQL> SELECT COUNT(*) FROM PLAN_TABLE;
  COUNT(*)
----------
        68
Execution Plan
----------------------------------------------------------
Plan hash value: 1751138260
--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| PLAN_TABLE$ |    68 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

AUTOTRACE STATISTICS含义见下表：

序号	列名	解释
1	recursive calls	递归调用，表示执行SQL的时候的产生的递归调用的次数。Oracle在执行SQL的时候，有时候会生成很多额外的SQL语句，这个就称为递归调用。这个参数和访问数据字典的次数有很大的关系，一般来说，这个参数值不会很大。
2	db block gets	DB块取，表示当前读。在发生INSERT、DELETE、UPDATE和SELECT FOR UPDATE的时候，数据库缓冲区中的数据库块的个数。在SELECT语句中一般为0。
3	consistent gets	一致性读，表示除了SELECT FOR UPDATE的时候，从数据库缓冲区中读取的数据块的个数（注意，实际上并不是块的个数），可能会读取回滚段的信息，一般来说，逻辑读（Logical Reads） = 当前读（db block gets） + 一致性读（consistent gets）。
4	physical reads	物理读，在执行SQL的过程中，从硬盘上读取的数据块个数。
5	redo size	SQL语句在执行过程中产生的Redo的字节数。
6	bytes sent via SQL*Net to client	服务器利用SQL*Net发送到客户端的字节数。
7	bytes received via SQL*Net from client	服务器利用SQL*Net从客户端接收的字节数。
8	SQL*Net roundtrips to/from client	从客户端发送和接收的SQL*Net消息的总数，包括从多行的结果集中提取的往返消息。
9	sorts (memory)	在内存执行的排序次数。
10	sorts (disk)	在磁盘上执行的排序次数，如果内存空间不足，那么会使用磁盘空间。
11	rows processed	更改或选择返回的行数。

2、EXPLAIN PLAN FOR方式

SQL> EXPLAIN PLAN FOR SELECT * FROM T017_LHRO;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3200443156
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1363 |   177K|     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T017_LHRO |  1363 |   177K|     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------

3、DBMS_XPLAN.DISPLAY_CURSOR方式

SYS@RAC2LHR1> SELECT * FROM V$VERSION WHERE ROWNUM<2; 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 
  2   FROM SCOTT.EMP E,SCOTT.DEPT D 
  3  WHERE E.DEPTNO = D.DEPTNO 
  4  AND E.EMPNO = 7788; 
ENAME      DNAME          LOC
---------- -------------- -------------
SCOTT      RESEARCH       DALLAS

如果不传递任何参数给DISPLAY_CURSOR函数，那么默认显示当前会话最后一条SQL语句的执行计划，如下所示：

SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL)); 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  315xan8zgvtbm, child number 0
-------------------------------------
SELECT ENAME,DNAME,LOC  FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =
D.DEPTNO AND E.EMPNO = 7788
Plan hash value: 1674520956
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |         |     1 |    33 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)|          |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."EMPNO"=7788)
   5 - access("E"."DEPTNO"="D"."DEPTNO")
24 rows selected.

传递SQL_ID以及FORMAT参数给DISPLAY_CURSOR函数，并配合修饰符控制执行计划的输出，如下所示：

SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('315XAN8ZGVTBM',NULL,'ALL')); 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  315xan8zgvtbm, child number 0
-------------------------------------
SELECT ENAME,DNAME,LOC  FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =
D.DEPTNO AND E.EMPNO = 7788
Plan hash value: 1674520956
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |         |     1 |    33 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)|          |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / E@SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."EMPNO"=7788)
   5 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "ENAME"[VARCHAR2,10], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
   2 - "ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]
   3 - "E".ROWID[ROWID,10]
   4 - "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
   5 - "D".ROWID[ROWID,10]
42 rows selected.

利用STATISTICS_LEVEL或/*+ GATHER_PLAN_STATISTICS*/可以知道表访问的次数，也可以查看真实执行计划并获得统计信息。如下所示：

SET SERVEROUTPUT OFF
ALTER SESSION SET STATISTICS_LEVEL=ALL; 
执行SQL语句
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>'',CURSOR_CHILD_NO =>1,FORMAT => 'ADVANCED ALLSTATS'));

其中参数SQL_ID为父游标，如果为NULL，那么表示显示该会话之前的SQL执行计划。CURSOR_CHILD_NO为子游标的序号，默认为0，如果设定为NULL，那么所有该父游标下所有的子游标的执行计划都将返回。参数FORMAT指定要显示哪些信息，常用的有：IOSTATS（I/O信息显示）、ALLSTATS（I/O信息显示+PGA信息）、ADVANCED（显示所有统计信息）、IOSTATS LAST或ALLSTATS LAST（只显示最后一次执行的统计信息）。默认值TYPICAL只能显示一个普通的执行计划，不能显示出实际返回的行。

☞ 这种方式也是SQL调优中常用的方法，但使用该方法的前提是如下两个条件必须同时满足：

①　一般在会话级别设置参数STATISTICS_LEVEL为ALL，也可以使用/*+ GATHER_PLAN_STATISTICS*/提示。

②　若DBMS_XPLAN.DISPLAY_CURSOR中的入参SQL_ID输入值为NULL的话，则SERVEROUTPUT必须设置为OFF（SET SERVEROUTPUT OFF），否则会报类似如下的错误：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

若为具体SQL_ID的值的话，则无论SERVEROUTPUT的值如何都可以正常执行。

示例如下所示：

SYS@RAC2LHR1> SHOW PARAMETER STATISTICS_LEVEL 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SYS@RAC2LHR1> ALTER SESSION SET STATISTICS_LEVEL=ALL; 
Session altered.
SYS@RAC2LHR1> SHOW SERVEROUTPUT
serveroutput OFF
SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 
  2   FROM SCOTT.EMP E,SCOTT.DEPT D 
  3   WHERE E.DEPTNO = D.DEPTNO 
  4    AND E.EMPNO = 7369; 
ENAME      DNAME          LOC
---------- -------------- -------------
SMITH      RESEARCH       DALLAS
SYS@RAC2LHR1> SET PAGESIZE 0 
SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS LAST -PREDICATE -NOTE'));
SQL_ID  g3mx9hdyrhus7, child number 0
-------------------------------------
SELECT ENAME,DNAME,LOC  FROM SCOTT.EMP E,SCOTT.DEPT D  WHERE E.DEPTNO =
D.DEPTNO   AND E.EMPNO = 7369
Plan hash value: 1674520956
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   3 |    INDEX UNIQUE SCAN         | PK_EMP  |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|   5 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------
SYS@RAC2LHR1> SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC 
  2   FROM SCOTT.EMP E,SCOTT.DEPT D 
  3   WHERE E.DEPTNO = D.DEPTNO 
  4   AND E.EMPNO = 7369; 
SMITH      RESEARCH       DALLAS
SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC LAST ALLSTATS'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC  FROM SCOTT.EMP
E,SCOTT.DEPT D  WHERE E.DEPTNO = D.DEPTNO  AND E.EMPNO = 7369
Plan hash value: 1674520956
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."EMPNO"=7369)
   5 - access("E"."DEPTNO"="D"."DEPTNO")

4、其它跟踪方法

除了上述方法外，还可以通过其它一些途径获取到语句的执行计划，例如10046，10053事件等，但在这些方法所产生的数据里，执行计划通常仅是辅助解决问题的一个部分，而非重点。

5、第三方工具

利用第三方工具，如PL/SQL DEV、TODO等开发工具，在PL/SQL DEV中选定SQL后，按F5即可查看执行计划：

此外，还可以通过写脚本从V$SQL_PLAN、DBA_HIST_SQL_PLAN、V$SQL_PLAN_MONITOR等视图中来获取执行计划。

下表对这几种获取执行计划的方法给予总结：

方法		简介	SQL语句是否真实执行过	是否真实执行计划	物理读、逻辑读、递归调用	运行时间	处理行数	表访问次数	等待事件	解析时间
set autotrace	SET AUTOTRACE OFF	此为默认值，即关闭AUTOTRACE
	SET AUTOTRACE ON	包含SQL语句的执行结果、SQL语句执行结果的数量、执行计划和统计信息内容	是	不确定	有	有	有	无	无	无
	SET AUTOTRACE ON EXPLAIN	包含SQL语句的执行结果、SQL语句执行结果的数量和执行计划			无	有	有	无	无	无
	SET AUTOTRACE ON STATISTICS	包含SQL语句的执行结果、SQL语句执行结果的数量和统计信息内容			有	有	有	无	无	无
	SET AUTOTRACE TRACEONLY	包含SQL执行结果的数量、执行计划和统计信息内容，但不显示SQL语句的执行结果			有	有	有	无	无	无
	SET AUTOTRACE TRACEONLY EXPLAIN	同EXPLAIN PLAN命令，对于SELECT语句不会执行，只显示目标SQL的执行计划，但是对于DML语句还是会执行的，而且显示SQL语句执行结果的数量和执行计划	否		无	有	有	无	无	无
	SET AUTOTRACE TRACEONLY STATISTICS	显示SQL语句执行结果的数量和统计信息，不显示执行计划和SQL执行结果	是		有	有	有	无	无	无
DBMS_XPLAN	EXPLAIN PLAN FORDBMS_XPLAN.DISPLAY	EXPLAIN PLAN FOR SQL语句;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED'));	否	不确定	无	无	无	无	无	无
	STATISTICS_LEVEL=ALLSELECT /+ GATHER_PLAN_STATISTICS/ ...DBMS_XPLAN.DISPLAY_CURSOR	ALTER SESSION SET STATISTICS_LEVEL=ALL ;执行SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',0,'BASIC LAST ALLSTATS ADVANCED'));	是	是	无	有	有	有	无	无
	DBMS_XPLAN.DISPLAY_CURSOR	没有设置STATISTICS_LEVEL=ALL或没有使用/+ GATHER_PLAN_STATISTICS/的Hint：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID')); --从内存得到执行计划	是	是	无	无	无	无	无	无
	DBMS_XPLAN.DISPLAY_AWR	DISPLAY_AWR函数显示存储在AWR历史数据的执行计划。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQLID'));	是	是	无	无	无	无	无	无
	DBMS_XPLAN.DISPLAY_SQLSET	DISPLAY_SQLSET函数显示存储在一个SQL调优集中的语句的执行计划，SQL调优集查询DBA_SQLSET_STATEMENTS，查询执行计划的SQL语句为：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('&SQLSET','&SQLID',NULL,'BASIC ALLSTATS ADVANCED'));	是	是	无	无	无	无	无	无
	DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE	DISPLAY_SQL_PLAN_BASELINE函数显示存储在数据字典当中SQL执行计划基线的计划。执行计划基线所属SQL的句柄名称（SQL_HANDLE）可以通过视图DBA_SQL_PLAN_BASELINES查询，查询执行计划的SQL语句为：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => ));	是	是	无	无	无	无	无	无
SQL_TRACE、事件10046、10053跟踪		ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'; 执行SQL语句ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';TKPROF格式化TRACE文件	是	是	有	有	有	无	有	有
awrsqrpt.sql		@?/rdbms/admin/awrsqrpt.sqlSELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(V_DBID,V_INST_ID,V_MIN_SNAP_ID,V_MAX_SNAP_ID,V_SQLID));	是	是	有	有	有	无	有	有
SQL实时监控特性：DBMS_SQLTUNE.REPORT_SQL_MONITOR		SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('&SQLID') FROM DUAL ;SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'&SQLID',TYPE=>'ACTIVE',REPORT_LEVEL=>'ALL') AS REPORT FROM DUAL;	是	是	有	有	有	有	有	有
其它工具		利用第三方工具，如PL/SQL DEV、TODO等开发工具，在PL/SQL DEV中选定SQL后，按F5即可查看执行计划	否	不确定	无	无	无	无	无	无