执行计划-1：获取执行计划

看懂执行计划是作为处理数据库性能问题的必备技能之一，接下来一系列的文章我 会告诉你怎么去做。

我们会从一些文章开始讨论几种获取执行计划的方法，并且评估不同来源的执行计划最适合哪种场景。 完成上述的内容后我们会继续深入解释简单执行计划的基础，之后会介绍一个用来解释大部分执行计划的规则 -需要小心使用该规则。 同时也会重点说明因为优化器改写我们的 sql导致执行计划与预期的不符的情况。

一些规则外的异常不可避免，所以在一些简单的计划后，我们会继续了解更复杂的计划，包括更新，删除，子查询分解和标量子查询。 之后继续学习分布式查询，分区表对于计划的影响，以及并行执行。

这些文章不会包含所有的执行计划里的操作和选项，但是会包含一些重要的足以处理大部分执行计划的操作。

什么是执行计划

当我们写了一条 SQL语句时，我们可能会思考 oracle会怎么产生结果。 执行计划是 Oracle将 SQL语句转变为一系列执行步骤，并且得出我们想要的结果的执行路径。 无论在语句执行前就生成的执行计划，还是真正执行时生成的计划，我们解释计划的方法都是一样的。 然而根据什么时候以及如何获取执行计划，详细程度 (甚至我们对正确执行计划的信心程度 )都会不同。 例如，有以下查询：

select
t1.v1, t2.v1
From
t1, t2
where
t1.n2= :b1
And t2.id= t1.id
And t2.n2 between :b2 and :b3;

这是简单的两表关联； 单列关联条件，有两个单表谓词过滤数据。 当我们查看这个语句时，我们希望能回答以下的问题 -这些问题不一定互相独立：

• Oracle 会先访问哪张表 ?

• Oracle 怎么访问那张表，通过索引或者表扫描 ?

• 它能获取多少数据？

• 接下来会以何种方式访问哪张表？

• Oracle 会用哪种方式连接两张表？

• 通过连接列谓词条件会生成多少数据？

• 在连接后应用谓词会有多少数据被过滤？

• Oracle 的预测以及真正执行结果会有显著的区别吗？

• 我们能不能快速确定为什么会发生 ( 上一条内容 ) ，以及有何影响？

• Oracle 的执行策略符合我们的预期吗？

• 我们可以找出为什么 Oracle 不执行我们认为更好的策略的原因吗？

这个例子中，我们假设 Oracle 利用 n2 上面的索引找到 t1 中的一小部分的行，接下来使用 nested loop 跟 t2 关联，通过 t2 上的主键索引与 t1 中的过滤后的每行数据进行匹配，获取匹配成功的行，最后丢弃大部分不符合 (t2.n2between :b2 and :b3) 条件的行。

这里有两个关于这个查询的执行计划，在我们开始解释执行计划前需要了解一些我们必须考虑的问题。 这些计划来自 11.2.0.4的数据库环境。

预测的执行计划

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    32 |   832 |    46   (3)| 00:00:01 |
|*  1 |  FILTER                       |       |       |       |            |          |
|*  2 |   HASH JOIN                   |       |    32 |   832 |    46   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | T1    |    32 |   416 |    24   (5)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |   500 |  6500 |    22   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | T2_N2 |    45 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate InfORMation (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
2 - access("T2"."ID"="T1"."ID")
3 - filter("T1"."N2"=TO_NUMBER(:B1))
5 - access("T2"."N2">=TO_NUMBER(:B2) AND "T2"."N2"<=TO_NUMBER(:B3))

真实的执行计划

----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |       |       |    33 (100)|          |
|*  1 |  FILTER                        |       |       |       |            |          |
|   2 |   NESTED LOOPS                 |       |    17 |   442 |    33   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |       |    17 |   442 |    33   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |    17 |   221 |    16   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_N2 |    17 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | T2_PK |     1 |       |     0   (0)|          |
|*  7 |    TABLE ACCESS BY INDEX ROWID | T2    |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B3>=:B2)
5 - access("T1"."N2"=:B1)
6 - access("T2"."ID"="T1"."ID")
7 - filter(("T2"."N2">=:B2 AND "T2"."N2"<=:B3))

这两个执行计划明显不同 ——即使我是在相同的会话里分别产生的。 主要的不同体现在两方面； 第一，计划主体行数不同，一个 6行，另一个有 8行； 另一个就是 Predicate Information(谓词信息 )，一个明确显示了所有绑定变量的强制转换 (e.g. to_number(:B3))，另一个并没有这种信息。 如果我们要根据执行计划来帮助我们提高系统的效率，我们必须知道为什么会有这种自相矛盾现象发生，并且需要确定可以多大程度上相信 Oracle给我们的执行计划。 接下来一起看看常见的获取执行计划的方法以及每种方法的局限性。

获取执行计划

即使有许多可以通过图形化界面生成执行计划的工具，我更偏向于在 SQL命令行使用 Oracle的 dbms_xplan包。 不论你使用什么工具，得到的信息都是几乎一样的，但是如果你需要在不同的论坛或者会议上分享你的执行计划的话，那么通过 dbms_xplan生成的执行计划的格式是大部分人所熟悉和接受的。

解释计划

SQL*Plus 会话 :

explain plan for  (your select statement)
select * from table(dbms_xplan.display);

这是最简单也是最基本的方法获取到当你执行语句时， Oracle"预测 "的可能会采用的执行计划； 上述第一种执行计划就是通过这种方式得到的。 这种获取的方法存在一些问题，一些明显的问题上面已经阐述了，下面说些更深层的原因。

第一点，如果你的查询包含绑定变量时 (类似上面的例子 )， "explain plan"的特点是不知道绑定变量的数据类型。 它假设它们是 char类型的，所以我第一个执行计划中的 Predicate Information会显示强制转换 to_number()，这会使优化器不去考虑本来可以使用的索引，从而导致执行计划的巨大差距。

第二点， Oracle采用绑定变量窥探许多年了，当一条语句第一次被优化时，会获取到绑定变量真实的值。 但是 "explain plan"并不会去尝试； 它不会去获取真实的值。 它只会使用几种基本的规则去预估谓词中涉及到的绑定变量的选择性。 有一些规则是比较正确的，但是有些就是纯粹的猜测 ——通常用 1%或 5%作为选择性。 对选择性的错误预估会导致对于基数的错误语句最终导致错误的执行计划。

附注 :第一个执行计划就是猜测产生自相矛盾的例子 :在第 4行和第 5行， index range scan预估会有 45个 rowid会被获取到，但是 tableaccess预估返回 500行数据， 45个 rowid不可能对应 500行数据。 这是索引基于 range_based的预测，并使用了最小的选择性 0.45%，但是同等情况下表的最小选择性为 5%，所以导致了这种情况的发生。

还有更多的关于" explain plan "的细节你可能会碰到。调用时有一些额外的选项，在官方手册中记录如下 :

explain plan
set statement_id = ‘{string}’
into {schema}.{table}@{db_link}
for {statement};

statement_id默认为空，目标  table就是 plan_table(在新的版本中是全局临时表 sys.plan_table$的同义词 )。 可以使用 table和 statement_id参数来指定想要的语句的执行计划的输出，函数的声明如下：

dbms_xplan.display({plan_table},{statement_id},{formatoptions},{filter option})

如果无参数调用 dbms_xplan或者将前两个参数赋值为 NULL，则输出最近解释的语句。 就输出格式选项而言， plan table里有许多信息可供选择显示，我们会在后续的章节里介绍。 filter选项允许你限制 plan_table返回的行 —这几乎用不到。

Autotrace

这是嵌入到 SQL*PLUS 里的 "explain plan" 的特殊变体，可以使用 set 命令使 autotrace 选项生效：

set autotrace on
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace off

在 SQL*PLUS中开启 autotrace后，可以输出执行的任意 SQL语句的执行计划和执行统计信息。 你可以限制只输出执行计划，只输出执行统计信息，或者全部输出，你还可以不输出语句的执行结果 (使用 traceonly选项 )。 下面是一个我使用 set autotrace to traceonly statistics的输出例子：

1 row selected.
Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
   36  consistent gets
   0  physical reads
   0  redo size
   471  bytes sent via SQL*Net to client 
   415  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

这对于只想知道执行一条语句 Oracle 做了多少工作量来说十分方便，因为我不需要获取结果集，或者将结果集存储在客户端。

对于获取真实的执行计划来说， autotrace依然做不到，它只是简单的在后台执行 "explain plan"并且调用 "dbms_xplan.display"； 此外，如果你设置 "setautotrace traceonly explain"并且执行一条查询语句，该选项因为并不返回真实的结果，所以 Oracle根本不会执行这条语句。 但是如果是 insert、 update、 delete或者 merge语句，会真实执行，并输出影响的行数，提醒你是否需要回滚。

Dbms_xplan.display_cursor()

这是文章中唯一提及的，可以在语句执行后从内存中获取真实执行计划的函数的选项。 该函数的定义如下：

dbms_xplan.display_cursor({sql_id},{child_number},{formatoptions})

如果不带参数调用，则会返回最近一次执行的 SQL 执行计划。有许多原因会导致获取不到执行计划，有可能是游标不可用了，不过最常见的原因是没有设置 "set serveroutput off", 这个获取失败的执行计划是针对跟在执行的语句后的对 dbms_output(BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;) 的调用，可以看到如下信息 :

SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  b3s1x9zqrvzvc, child number 0
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
NOTE: cannot fetch plan for SQL_ID: b3s1x9zqrvzvc, 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)
8 rows selected.

我调用 dbms_xplan.display_cursor()来获得上述的第二个真实的执行计划。 这是 Oracle根据我绑定变量的值、涉及对象的统计信息以及会话的环境所真正执行的路径。 但是它依然只是返回优化器每一步预估的返回的行数，并不是在执行时真正获取的行数。 我们会在接下来的话题中继续讨论。

虽然还有许多关于 dbms_xplan.display_cursor要说的，也有很多使用它的方式。 但是介绍先到此，并且有个小提醒。 虽然它会根据你提供的绑定变量输出真正的执行计划 (大部分情况 )，但是不同环境下不能保证是一致的，或者同样的环境下始终都是一样的执行计划。

如果不了解最后的用户做了什么，就会有很多原因导致你被执行计划欺骗。 在生产系统中，最常见的包括 :

•  真实的绑定变量值

•   优化器环境和对象统计信息

•  名称解析

虽然 "dbms_xplan.display_cursor()" 的调用结果相对于调用 "explain plan" 以及 "dbms_xplan.display()" 的结果来说会真实很多，但是如果你想确保没有获取到错误的执行计划，仍然需要一些明智的判断。

结论

通过这篇文章我们了解了获取执行计划其实非常简单，但是计划会有两种类别 —预测的和真实的。 也了解到如果语句中存在绑定变量的话，预测的执行计划更倾向于是一个错误的执行计划。

一种普遍且相当准确的观点认为，在生产库上的执行计划会与在自己环境执行语句后获取的真正的执行计划一致，但这只能取决于你的环境是否与生产库最后用户执行该语句时的环境十分相似。

我们从真实的执行计划中获取到的关于 " 体积 "(rows ， bytes) 的信息仍然是通过预估得出的，下一章节我们会获取到真实的 " 体积 " 数据，这也会帮助我们判断为什么优化器的选择与我们预期的不符。

原文链接： https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/

原文作者： Jonathan Lewis

| 译者简介

林锦森·沃趣科技数据库技术专家

沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验