Oracle优化器之解释执行计划译文（一）

本文基于Oracle 12c优化器官方文档进行翻译，原文链接如下：
The Oracle Optimizer Explain the Explain Plan

介绍

  Oracle优化器的工作目标是为SQL查询确定最高效的执行计划。它会根据数据的统计信息并利用Oracle数据库的特性（如哈希连接、并行查询、分区等）做出决策。尽管Oracle优化器已经十分强大，它仍然会不时地为某些SQL语句生成次优执行计划。在存在比优化器生成的执行计划更好的替代计划的情况下，判断优化器为何选择次优计划的第一步就是查看执行计划。

  检查执行计划的不同特性（选择性、并行等），并了解应该从执行计划中获得哪些信息，即使对最有经验的DBA来说也是一项艰巨的任务。本文将对执行计划的各个方面进行详细的解释，并对CBO做出决策的原因进行深入的分析。

执行计划

  执行计划展示了Oracle执行SQL语句所需的详细步骤。这些步骤表现为一组使用和生成数据行的数据库操作符。这些操作符及其应用的顺序由查询优化器使用查询转换和物理优化技术的组合来决定。虽然执行计划通常以表格形式展示，但其实际上是树形结构。例如，对于下面这个基于Sales History Schema的SQL查询语句：

SELECT prod_category, AVG(amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;

上述查询的执行计划以表格形式展示如下：

图1 表格形式的执行计划

上述查询的执行计划以树形展示如下：

图2 树形执行计划

  表格形式是自上而下、从左到右的遍历执行计划树。而对于树形执行计划，我们阅读的顺序应该是从左下角开始，然后横向，最后向上。在上面的示例中，最先查看的是树的叶子。优化器对Products表和Sales表进行全表扫描，随后将全表扫描产生的数据行通过连接操作符进行连接。示例中的连接操作符是Hash Join（其他可选操作符包括Nested Loop和Sort Merge Join）。最后，优化器对Hash Join连接操作符的执行结果进行Group By操作，并将最终结果集返回。

显示执行计划

显示SQL语句执行计划最常用的两种方法是：

• EXPLAIN PLAN命令：在不实际执行SQL语句的情况下显示执行计划。
• V$SQL_PLAN：一个动态性能视图，显示已编译到游标并存储在游标缓存中的SQL语句的执行计划。

  在某些特殊的情况下，使用EXPLAIN PLAN时显示的执行计划可能与使用V$SQL_PLAN时显示的执行计划不同。例如，当SQL语句包含绑定变量时，使用EXPLAIN PLAN显示的执行计划将忽略绑定变量值，而V$SQL_PLAN中显示的执行计划将在计划生成过程中考虑绑定变量值。

  通过DBMS_XPLAN包可以很容易查看到执行计划。这个包提供了几个PL/SQL接口来显示不同来源的执行计划：

• EXPLAIN PLAN
• V$SQL_PLAN
• Automatic Workload Repository（AWR）
• SQL Tuning Set（STS）
• SQL Plan Baseline（SPM）

查看执行计划（使用EXPLAIN PLAN及DBMS_XPLAN.DISPLAY）

  下面的示例将演示如何使用DBMS_XPLAN包中提供的不同函数为原始SQL语句生成和显示执行计划。

图3 EXPLAIN PLAN设置为BASIC模式输出

DBMS_XPLAN.DISPLAY函数的参数设置：

• plan_table，存放执行计划的表名（默认值“PLAN_TABLE”）
• statement_id，SQL语句标识符（默认值NULL表示最近一条插入到执行计划表的SQL）
• format，格式化，执行计划展示信息的丰富程度（默认值“TYPICAL”）

  要使用EXPLAIN PLAN的功能，我们需要相应的权限来运行目标SQL语句。在Oracle中，每个用户都有一个默认的PLAN_TABLE，无需预先创建。

查看执行计划（使用DBMS_XPLAN.DISPLAY_CURSOR）

  此外，我们还可以使用DBMS_XPLAN包的DISPLAY_CURSOR函数生成和显示已执行的SQL语句的执行计划。下面的示例显示了之前在会话中执行的SQL语句的计划。

图4 BASIC格式展示SQL游标缓存中的执行计划

DBMS_XPLAN.DISPLAY_CURSOR函数的参数设置：

• SQL ID（默认值NULL，表示最近一次执行的SQL语句的SQL ID）
• child number（默认值0）
• format，格式化，执行计划展示信息的丰富程度（默认值“TYPICAL”）

  通过DBMS_XPLAN.DISPLAY_CURSOR函数查看执行计划除了需要执行SQL语句所需要的权限之外，还需要拥有V$SQL_PLAN、V$SQL_PLAN_DETAIL 以及SELECT_CATALOG_ROLE的SELECT权限。

执行计划的格式化

  DBMS_XPLAN包中函数的格式参数是高度可定制的，可以根据需要在执行计划输出中显示尽可能少的（高级）或尽可能多的（低级）细节。一共有三种预先定义好的的格式：

• BASIC，执行计划只展示ID、Operation和Object Name列
• TYPICAL，包含基本信息和其他与优化器相关的内部信息，如Cost（成本）、Cardinality（基数）预估等。这些信息出现在执行计划的每个操作中，它们表示优化器认为的每个操作的成本、生成的行数等。同时还会显示每个操作涉及到的谓词信息。谓词有两种类型：Access（访问）和Filter（筛选）。索引的访问谓词用于通过对适当的列应用搜索条件来获取相关块。在获取到数据块之后再应用筛选谓词对结果进行过滤。
• ALL，包含在TYPICAL中显示的全部信息，加上每个操作生成的表达式（列）列表、提示别名和操作所属的查询块名称。最后两条信息可以用作向SQL语句添加hints时的参数。

  在低级选项中我们可以包含或排除特定的细节，比如谓词和成本。图5中的示例展示了基本执行计划，并包含关于谓词和优化器成本列的信息。

SELECT plan_table_output 
FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic+predicate +cost'));

图5 自定义包含PREDICATE 和COST的BASIC格式化输出

  我们也可以使用低级的参数来排除执行计划中的信息。图6展示了不包含优化器成本和字节列的执行计划。

SELECT plan_table_output FROM
TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes'));

图6 自定义排除了COST和BYTES的TYPICAL格式化输出

Note（备注）

  除了展示计划步骤和谓词信息外，DBMS_XPLAN包还在NOTE部分显示其他信息，比如在查询优化过程中何时使用了动态采样，或者在查询中应用了星型转换等。在下面的例子中，SALES表上没有统计信息，因此优化器在查询优化期间使用了动态采样。执行计划显示这些信息的前提是在查询中使用+note：

SELECT plan_table_output FROM 
TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic**+note**'));

图7 自定义BASIC格式化输出显示动态采样的使用

  将格式选项设置为TYPICAL或ALL时，执行计划中将自动显示note部分的信息。有关DBMS_XPLAN包的更多信息可以在Oracle数据库PL/SQL包和类型参考指南中找到。