使用 AUTOTRACE 查看执行计划

我们利用 SQLPLUS 中自带的 AUTOTRACE 工具查看执行计划。AUTOTRACE 用法如下。

  1. set autot
  1. Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

方括号内的字符可以省略。

set autot on：该命令会运行 SQL 并且显示运行结果，执行计划和统计信息。

set autot trace：该命令会运行 SQL，但不显示运行结果，会显示执行计划和统计信息。

set autot trace exp：运行该命令查询语句不执行，DML 语句会执行，只显示执行计划。

set autot trace stat：该命令会运行 SQL，只显示统计信息。

set autot off：关闭 AUTOTRACE。

我们使用 set autot on 查看执行计划（基于 Oracle11gR2，Scott 账户）。

  1. conn scott/tiger

显示已连接。

  1. set lines 200 pages 200
  2. set autot on
  3. select count(*) from emp；
  1.  COUNT(*)
  2. ----------
  3.         14
  5.  执行计划 
  6. ----------------------------------------------------------
  7. Plan hash value: 1006289799
  9. ------------------------------------------------------------------------
  10. | Id  | Operation             | Name   | Rows  | Cost （%CPU）| Time     |
  11. ------------------------------------------------------------------------
  12. |   0 | SELECT STATEMENT      |        |     1 |     2   (0)| 00:00:01 |
  13. |   1 |  SORT AGGREGATE       |        |     1 |            |          |
  14. |   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |     2   (0)| 00:00:01 |
  15. ------------------------------------------------------------------------
  17. Note
  18. -----
  19.    - dynamic sampling used for this statement （level=2）
  20. 统计信息 
  21. ----------------------------------------------------------
  22.         233  recursive calls
  23.           0  db block gets
  24.          51  consistent gets
  25.          10  physical reads
  26.           0  redo size
  27.         430  bytes sent via SQL*Net to client
  28.         419  bytes received via SQL*Net from client
  29.           2  SQL*Net roundtrips to/from client
  30.           4  sorts （memory）
  31.           0  sorts （disk）
  32.           1  rows processed

使用set autot on查看执行计划会输出 SQL 运行结果，如果 SQL 要返回大量结果，我们可以使用set autot trace查看执行计划，set autot trace不会输出 SQL 运行结果。

  1. set autot trace
  2. select count(*) from emp；
  1. 执行计划 
  2. ----------------------------------------------------------
  3. Plan hash value: 1006289799
  5. ------------------------------------------------------------------------
  6. | Id  | Operation             | Name   | Rows  | Cost （%CPU）| Time     |
  7. ------------------------------------------------------------------------
  8. |   0 | SELECT STATEMENT      |        |     1 |     2   (0)| 00:00:01 |
  9. |   1 |  SORT AGGREGATE       |        |     1 |            |          |
  10. |   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |     2   (0)| 00:00:01 |
  11. ------------------------------------------------------------------------
  13. Note
  14. -----
  15.    - dynamic sampling used for this statement （level=2）
  16.  统计信息 
  17. ----------------------------------------------------------
  18.           0  recursive calls
  19.           0  db block gets
  20.           4  consistent gets
  21.           0  physical reads
  22.           0  redo size
  23.         430  bytes sent via SQL*Net to client
  24.         419  bytes received via SQL*Net from client
  25.           2  SQL*Net roundtrips to/from client
  26.           0  sorts （memory）
  27.           0  sorts （disk）
  28.           1  rows processed

笔者经常使用 set autot trace 命令查看执行计划。

利用 AUTOTRACE 查看执行计划会带来一个额外的好处，当 SQL 执行完毕之后，会在执行计划的末尾显示 SQL 在运行过程中耗费的一些统计信息。

recursive calls 表示递归调用的次数。一个 SQL 第一次执行就会发生硬解析，在硬解析的时候，优化器会隐含地调用一些内部 SQL，因此当一个 SQL 第一次执行，recursive calls 会大于 0；第二次执行的时候不需要递归调用，recursive calls 会等于 0。

如果 SQL 语句中有自定义函数，recursive calls 永远不会等于 0，自定义函数被调用了多少次，recursive calls 就会显示为多少次。

  1. create or replace function f_getdname(v_deptno in number) return varchar2 as
  2.    v_dname dept.dname%type;
  3.   begin
  4.     select dname into v_dname from dept where deptno = v_deptno;
  5.     return v_dname;
  6.   end f_getdname;
  1. Function created.

SQL 多次执行后的执行计划如下。

  1. select ename,f_getdname(deptno) from emp;
  1. 14 rows selected.
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 3956160932
  7. --------------------------------------------------------------------------
  8. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  9. --------------------------------------------------------------------------
  10. |   0 | SELECT STATEMENT  |      |    14 |   126 |     3   (0)| 00:00:01 |
  11. |   1 |  TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
  12. --------------------------------------------------------------------------
  14. Statistics
  15. ----------------------------------------------------------
  16.          14  recursive calls
  17.           0  db block gets
  18.          36  consistent gets
  19.           0  physical reads
  20.           0  redo size
  21.         769  bytes sent via SQL*Net to client
  22.         419  bytes received via SQL*Net from client
  23.           2  SQL*Net roundtrips to/from client
  24.           0  sorts (memory)
  25.           0  sorts (disk)
  26.          14  rows processed

SQL 一共返回了 14 行数据，每返回一行数据，就会调用一次自定义函数，所以执行计划中 recursive calls 为 14。

db block gets 表示有多少个块发生变化，一般情况下，只有 DML 语句才会导致块发生变化，所以查询语句中 db block gets 一般为 0。如果有延迟块清除，或者 SQL 语句中调用了返回 CLOB 的函数，db block gets 也有可能会大于 0，不要觉得奇怪。

consistent gets 表示逻辑读，单位是块。在进行 SQL 优化的时候，我们应该想方设法减少逻辑读个数。通常情况下逻辑读越小，性能也就越好。需要注意的是，逻辑读并不是衡量 SQL 执行快慢的唯一标准，需要结合 I/O 等其他综合因素共同判断。

怎么通过逻辑读判断一个 SQL 还存在较大优化空间呢？如果 SQL 的逻辑读远远大于 SQL 语句中所有表的段大小之和（假设所有表都走全表扫描，表关联方式为 HASH JOIN），那么该 SQL 就存在较大优化空间。动手能力强的读者可以据此编写一个 SQL，抓出 SQL 逻辑读远远大于语句中所有表段大小之和的 SQL 语句。

physical reads 表示从磁盘读取了多少个数据块，如果表已经被缓存在 buffer cache 中，没有物理读，physical reads 等于 0。

redo size 表示产生了多少字节的重做日志，一般情况下只有 DML 语句才会产生 redo，查询语句一般情况下不会产生 redo，所以这里 redo size 为 0。如果有延迟块清除，查询语句也会产生 redo。

bytes sent via SQL*Net to client 表示从数据库服务器发送了多少字节到客户端。

bytes received via SQL*Net from client 表示从客户端发送了多少字节到服务端。

SQL*Net roundtrips to/from client 表示客户端与数据库服务端交互次数，我们可以通过设置 arraysize 减少交互次数。

sorts （memory）和sorts （disk）分别表示内存排序和磁盘排序的次数。

rows processed 表示 SQL 一共返回多少行数据。我们在做 SQL 优化的时候最关心这部分数据，因为可以根据 SQL 返回的行数判断整个 SQL 应该是走 HASH 连接还是走嵌套循环。如果 rows processed 很大，一般走 HASH 连接；如果 rows processed 很小，一般走嵌套循环。

使用 EXPLAIN PLAN FOR 查看执行计划

使用 explain plan for 查看执行计划，用法如下。

  1. explain plan for SQL 语句；
  2. select * from table（dbms_xplan.display）；

示例（Oracle11gR2，Scott 账户）如下。

  1. explain plan for select ename, deptno
  2.     from emp
  3.    where deptno in (select deptno from dept where dname = 'CHICAGO');
  1. Explained.
  1. select * from table(dbms_xplan.display);
  1. PLAN_TABLE_OUTPUT
  2. -------------------------------------------------------------------------------------
  4. Plan hash value: 844388907
  6. -------------------------------------------------------------------------------------
  7. | Id  |Operation                    | Name    | Rows | Bytes | Cost(%CPU)| Time     |
  8. -------------------------------------------------------------------------------------
  9. |   0 |SELECT STATEMENT             |         |    5 |   110 |    6  (17)| 00:00:01 |
  10. |   1 | MERGE JOIN                  |         |    5 |   110 |    6  (17)| 00:00:01 |
  11. |*  2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |    2   (0)| 00:00:01 |
  12. |   3 |   INDEX FULL SCAN           | PK_DEPT |    4 |       |    1   (0)| 00:00:01 |
  13. |*  4 |  SORT JOIN                  |         |   14 |   126 |    4  (25)| 00:00:01 |
  14. |   5 |   TABLE ACCESS FULL         | EMP     |   14 |   126 |    3   (0)| 00:00:01 |
  15. -------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  20.    2 - filter("DNAME"='CHICAGO')
  21.    4 - access("DEPTNO"="DEPTNO")
  22.        filter("DEPTNO"="DEPTNO")
  24. 19 rows selected.

查看高级（ADVANCED）执行计划，用法如下。

  1. explain plan for SQL 语句；
  2. select * from table（dbms_xplan.display(NULL, NULL, 'advanced -projection')）；

示例（Oracle11gR2，Scott 账户）如下。

  1. explain plan for select ename, deptno
  2.    from emp
  3.    where deptno in (select deptno from dept where dname = 'CHICAGO');
  1. Explained.
  1. select * from table(dbms_xplan.display(NULL, NULL, 'advanced -projection'));
  1. PLAN_TABLE_OUTPUT
  2. -------------------------------------------------------------------------------------
  4. Plan hash value: 844388907
  6. -------------------------------------------------------------------------------------
  7. | Id  |Operation                    | Name    | Rows | Bytes | Cost(%CPU)| Time     |
  8. -------------------------------------------------------------------------------------
  9. |   0 |SELECT STATEMENT             |         |    5 |   110 |    6  (17)| 00:00:01 |
  10. |   1 | MERGE JOIN                  |         |    5 |   110 |    6  (17)| 00:00:01 |
  11. |*  2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |    2   (0)| 00:00:01 |
  12. |   3 |   INDEX FULL SCAN           | PK_DEPT |    4 |       |    1   (0)| 00:00:01 |
  13. |*  4 |  SORT JOIN                  |         |   14 |   126 |    4  (25)| 00:00:01 |
  14. |   5 |   TABLE ACCESS FULL         | EMP     |   14 |   126 |    3   (0)| 00:00:01 |
  15. -------------------------------------------------------------------------------------
  17. Query Block Name / Object Alias (identified by operation id):
  18. -------------------------------------------------------------
  20.    1 - SEL$5DA710D3
  21.    2 - SEL$5DA710D3 / DEPT@SEL$2
  22.    3 - SEL$5DA710D3 / DEPT@SEL$2
  23.    5 - SEL$5DA710D3 / EMP@SEL$1
  25. Outline Data
  26. -------------
  28.   /*+
  29.       BEGIN_OUTLINE_DATA
  30.       PX_JOIN_FILTER(@"SEL$5DA710D3" "EMP"@"SEL$1")
  31.       USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$1")
  32.       LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "EMP"@"SEL$1")
  33.       FULL(@"SEL$5DA710D3" "EMP"@"SEL$1")
  34.       INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
  35.       OUTLINE(@"SEL$2")
  36.       OUTLINE(@"SEL$1")
  37.       UNNEST(@"SEL$2")
  38.       OUTLINE_LEAF(@"SEL$5DA710D3")
  39.       ALL_ROWS
  40.       DB_VERSION('11.2.0.1')
  41.       OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
  42.       IGNORE_OPTIM_EMBEDDED_HINTS
  43.       END_OUTLINE_DATA
  44.   */
  46. Predicate Information (identified by operation id):
  47. ---------------------------------------------------
  49.    2 - filter("DNAME"='CHICAGO')
  50.    4 - access("DEPTNO"="DEPTNO")
  51.        filter("DEPTNO"="DEPTNO")
  53. 48 rows selected.

高级执行计划比普通执行计划多了 Query Block Name /Object Alias 和 Outline Data。

当需要控制半连接/反连接执行计划的时候，我们就可能需要查看高级执行计划。有时候我们需要使用 SQL PROFILE 固定执行计划，也可能需要查看高级执行计划。

Query Block Name表示查询块名称，Object Alias 表示对象别名。

Outline Data 表示 SQL 内部的 HINT。一条 SQL 语句可能会包含多个子查询，每个子查询在执行计划内部就是一个 Query Block。为什么会有 Query Block 呢？比如一个 SQL 语句包含有多个子查询，假如每个子查询都要访问同一个表，不给表取别名，这个时候我们怎么区分表属于哪个子查询呢？所以 Oracle 会给同一个 SQL 语句中的子查询取别名，这个名字就是 Query Block Name，以此来区分子查询中的表。Query Block Name 默认会命名为 SEL$1，SEL$2，SEL$3 等，我们可以使用 HINT：qb_name（别名）给子查询取别名。

查看带有 A-TIME 的执行计划

查看带有 A-TIME 的执行计划的用法如下。

  1. alter session set statistics_level=all;

或者在 SQL 语句中添加 hint:/*+ gather_plan_statistics */

在SQLPLUS中查看

运行完 SQL 语句，然后执行下面的查询语句就可以获取带有 A-TIME 的执行计划。

  1. select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

示例（Oracle11gR2，Scott 账户）如下。

  1. select /*+ gather_plan_statistics full(test) */ count(*) from test 
  2. where owner='SYS';
  1.   COUNT(*)
  2. ----------
  3.      30808
  1. select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
  1. PLAN_TABLE_OUTPUT
  2. -------------------------------------------------------------------------------------
  4. SQL_ID  fswg73p1zmvqu, child number 0
  5. -------------------------------------
  7. select /*+ gather_plan_statistics full(test) */ count(*) from test
  8. where owner='SYS'
  10. Plan hash value: 1950795681
  12. -------------------------------------------------------------------------------------
  13. | Id |Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
  14. -------------------------------------------------------------------------------------
  15. |  0 |SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    1037 | 1033 |
  16. |  1 | SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    1037 | 1033 |
  17. |* 2 |  TABLE ACCESS FULL| TEST |      1 |   2518 |  30808 |00:00:00.01 |    1037 | 1033 |
  18. -------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  23.    2 - filter("OWNER"='SYS')
  25. 20 rows selected.

  • Starts 表示这个操作执行的次数。

  • E-Rows 表示优化器估算的行数，就是普通执行计划中的 Rows。

  • A-Rows 表示真实的行数。

  • A-Time 表示累加的总时间。与普通执行计划不同的是，普通执行计划中的 Time 是假的，而 A-Time 是真实的。

  • Buffers 表示累加的逻辑读。

  • Reads 表示累加的物理读。

    在developer中查看

  1. 获取sql_id ```sql select /+ gather_plan_statistics full(test) / count(*) from test where owner = ‘SYS’;

select sql_id,sql_text from v$sqlarea where sql_text like ‘%’||’owner = ‘|| chr(39)||’SYS’||chr(39)||’%’;—注意这里’只能用chr(39)转移字符

  1. ![Screen Shot 2021-11-06 at 12.43.05 PM.png](https://cdn.nlark.com/yuque/0/2021/png/22382307/1636173798840-4f8a936e-0b8d-45ec-84a5-d13ff129de31.png#clientId=u9fceffce-eec1-4&from=ui&id=uf815b275&margin=%5Bobject%20Object%5D&name=Screen%20Shot%202021-11-06%20at%2012.43.05%20PM.png&originHeight=190&originWidth=1326&originalType=binary&ratio=1&size=143676&status=done&style=none&taskId=u63174260-8929-4433-9063-4beec14ef5d)
  3. - 获取child_number
  4. ```sql
  5. select sql_id,child_number,sql_text from v$sql where sql_id='burf59jjhvw5u';

Screen Shot 2021-11-06 at 12.44.03 PM.png

  • 获取A-Time执行计划
    1. select * from table(dbms_xplan.display_cursor('burf59jjhvw5u',0,'ALLSTATS LAST'));
    Screen Shot 2021-11-06 at 12.44.25 PM.png

上面介绍了 3 种方法查看执行计划。使用 AUTOTRACE 或者 EXPLAIN PLAN FOR 获取的执行计划来自于 PLAN_TABLE。PLAN_TABLE 是一个会话级的临时表，里面的执行计划并不是 SQL 真实的执行计划，它只是优化器估算出来的。真实的执行计划不应该是估算的，应该是真正执行过的。SQL 执行过的执行计划存在于共享池中，具体存在于数据字典 V$SQL_PLAN 中，带有 A-Time 的执行计划来自于 V$SQL_PLAN，是真实的执行计划，而通过 AUTOTRACE、通过 EXPLAIN PLAN FOR 获取的执行计划只是优化器估算获得的执行计划。有读者会有疑问，使用 AUTOTRACE 查看执行计划，SQL 是真正运行过的，怎么得到的执行计划不是真实的呢？原因在于 AUTOTRACE 获取的执行计划来自于 PLAN_TABLE，而非来自于共享池中的 V$SQL_PLAN。

查看正在执行的 SQL 的执行计划

有时需要抓取正在运行的 SQL 的执行计划，这时我们需要获取 SQL 的 SQL_ID 以及 SQL 的 CHILD_NUMEBR，然后将其代入下面 SQL，就能获取正在运行的 SQL 的执行计划。

  1. select * from table(dbms_xplan.display_cursor('sql_id',child_number));

示例（Oracle11gR2，Scott 账户）如下。

先创建两个测试表 a，b。

  1. create table a as select * from dba_objects;
  1. Table created.
  1. create table b as select * from dba_objects;
  1. Table created.

然后在一个会话中执行如下 SQL。

  1. select count(*) from a,b where a.owner=b.owner;

在另外一个会话中执行如下 SQL，结果如图 3-1 所示。

  1. select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_text
  2.   from v$session a, v$sql b
  3.  where a.sql_address = b.address
  4.    and a.sql_hash_value = b.hash_value
  5.    and a.sql_child_number = b.child_number
  6.  order by 1 desc;

获取执行计划常用方法 - 图3

图 3-1

接下来我们将 SQL_ID 和 CHILD_NUMBER 代入以下 SQL。

  1. select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));
  1. PLAN_TABLE_OUTPUT
  2. ------------------------------------------------------------------------------------
  4. SQL_ID  czr9jwxv0xra6, child number 0
  5. -------------------------------------
  6. select count(*) from a,b where a.owner=b.owner
  8. Plan hash value: 319234518
  10. ------------------------------------------------------------------------------------
  11. | Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  12. ------------------------------------------------------------------------------------
  13. |   0 | SELECT STATEMENT    |      |       |       |       |  2556 (100)|          |
  14. |   1 |  SORT AGGREGATE     |      |     1 |    34 |       |            |          |
  15. |*  2 |   HASH JOIN         |      |   400M|    12G|  1920K|  2556  (78)| 00:00:31 |
  16. |   3 |    TABLE ACCESS FULL| B    | 67547 |  1121K|       |   187   (1)| 00:00:03 |
  17. |   4 |    TABLE ACCESS FULL| A    | 77054 |  1279K|       |   187   (1)| 00:00:03 |
  18. ------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  23.    2 - access("A"."OWNER"="B"."OWNER")
  25. Note
  26. -----
  27.    - dynamic sampling used for this statement (level=2)