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

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

分区（Partitioning）

  分区技术可以将表、索引或索引组织表划分为若干小的集合，每一集合被称作一个分区。分区裁剪或分区消除是使用分区技术来提高性能的一种方法。例如，假设应用程序有一个ORDERS表，其中包含了过去2年内的所有订单记录。现在将这张表按照天来进行分区，那么查询一周内所有订单的请求就只需访问ORDERS表的7个分区，而不是730个分区（全部分区）。

  分区裁剪明细信息可以在执行计划的PSTART和PSTOP列中查看到。PSTART列包含将被访问的第一个分区的编号，PSTOP列包含将被访问的最后一个分区的编号。在图24的示例中，优化器访问了来自SALES表的4个分区，即分区9、10、11、12。

图24 一级分区表的分区裁剪示例

  图25展示了一个面向存在复合分区的表（RHP_TAB）的查询语句的执行计划，该表存在一个按天分区的一级分区，以及按照CUST_ID字段进行哈希分区的二级分区。在这种情况下，我们可以看到在PSTART和PSTOP列中存在多组数字。这些额外的数字意味着什么?

图25 复合分区表示例

  在使用复合分区技术时，Oracle会将每个分区从1到n（绝对分区编号）编号。对于仅存在一级分区的表，这些绝对值表示一级分区表在磁盘上的实际物理段。

  然而，对于复合分区表来说，分区只是逻辑实体，并不会在磁盘上有实际的段与其对应。每个分区会被细分为所谓的子分区，一个分区内的每个子分区的编号为从1到m（单个分区内的相对子分区编号）。最后，在一个复合分区表中的所有的（子）分区都会分配一个从1到（n*m）（此时是绝对子分区号）的编号。这些绝对值就表示复合分区表磁盘上的实际物理段，两者一一对应。

图26 分区表的编号模式

  因此，在图25所示的执行计划中，第4行PSTART和PSTOP列中的数字10表示磁盘上物理段的全局分区号。第2行PSTART和PSTOP列中的数字5表示分区号，而第3行PSTART和PSTOP列中的数字2表示分区内的相对子分区号。

  在某些情况下，执行计划中的PSTART和PSTOP列中出现的是单词或字母，而不是数字。例如，下图27所示的执行计划中显示的是单词KEY。这表明优化器在解析时还无法确定将访问哪些分区，但是优化器认为在执行时一定会使用到分区裁剪（动态裁剪）。

  动态裁剪会在几种情况下出现。例如，当分区键对应列上有一个包含函数的等式谓词，类似TIME_ID=SYSDATE。又如，在查询的分区键对应列上存在连接条件，且与分区表连接的表不会与所有分区连接（因为使用了Filter谓词）。分区裁剪是在执行时发生。在图27中的示例中，Where子句谓词位于表TIMES上，TIMES表通过TIME_ID与SALES表进行连接。在执行时会发生分区裁剪，具体来说就是在优化器对TIMES表应用了Where谓词条件并选择了适当的TIME_ID之后，优化器将在SALES表中再根据TIME_ID选择相应的分区。

图27 动态分区裁剪示例

  若执行计划中分区裁剪未按预期进行，我们需要检查分区键对应列上的谓词，确保谓词使用了与分区键相同的数据类型。在执行计划下方的Predicate Information部分可以看到类型信息。如果表上的分区是哈希分区，那么只有当分区键上的谓词是等式谓词或包含In的列表谓词时，才有可能出现分区裁剪。此外，如果表中存在多列哈希分区，那么只有在哈希分区中使用到的所有列上都存在谓词时，才会发生分区裁剪。

并行执行（Parallel Execution）

  Oracle数据库中的并行执行基于协调器（通常简称为查询协调器或QC，以下使用QC表示）和并行服务器进程。QC是发起并行SQL语句的会话，而并行服务器进程是指并行执行SQL的会话。QC将工作分配给并行服务器进程，并在某些时候需要执行少量的，主要是逻辑方面的，无法被并行执行的工作。例如，使用SUM()操作进行的并行查询需要QC将每个并行服务器进程计算出的各个数值相加。

图28 Oracle数据库并行执行基本概念

  我们可以很容易的在执行计划中定位到QC（执行计划中可以直接看到QC的字样）。在图29所示的执行计划ID#1行中，我们可以看到PX COORDINATOR操作。执行计划中出现在这条线以上的所有操作都是由QC完成的。由于这是一个单独的进程，所以由QC执行的操作都是串行进行。因此，在通常情况下，我们需要尽量的减少QC的操作。在PX COORDINATOR下进行的所有操作都是由并行服务器进程完成的。

图29 并行执行的执行计划示例

粒度（Granule）

  粒度是并行服务器进程能够处理的最小工作单元。为了能够在并行服务器进程之间实现工作的均匀分配，待处理的粒度数量通常远远高于并行度（并行数量）。每个并行服务器进程将只在它自己被分配的粒度上工作，当一个粒度被处理完成后，QC将给它分配另一个粒度，直到所有的粒度都被处理完毕。Oracle数据库在并行执行中分配工作是基于磁盘块的区间分配或基于块的粒度分配。在执行计划中，我们可以在Operation列中看到优化器是如何将粒度分配给并行服务器进程的。下图30所示执行计划的第7行中所显示的操作PX BLOCK ITERATOR表示并行服务器进程将遍历生成的区间内的每一个基于块的粒度以完成全表扫描。

图30 块粒度处理执行计划示例

  基于块的粒度是最常见的，但有些操作也可以利用分区表的底层数据结构以提高效率。在这种情况下，分区成为了工作时的最小单元（即粒度）。使用基于分区的粒度，一个并行服务器进程将在单个分区中执行所有工作。如果在实际操作中需要访问的（子）分区的数量大于并行度，那么优化器将考虑基于分区的粒度（若各个分区的大小存在偏差，则优化器考虑基于分区的粒度的理想情况是分区的数量远大于并行度）。在图31的第6行中可以看到基于分区的粒度的示例PX PARTITION RANGE ALL代表着每个并行服务器进程将仅在表中的一个分区上工作。

图31 基于分区的粒度示例

  Oracle数据库将根据SQL语句和并行度，基于最终成本考虑来决定使用基于块还是基于分区的粒度，我们无法影响数据库的这种选择。

生产者和消费者（Producers & Consumers）

  为了高效地并行执行语句，并行服务器进程实际上是以集合的形式协同工作：一个集合产生数据行（生产者），另一个集合消费数据行（消费者）。例如，在图32所示执行计划中，SALES表和CUSTOMERS表之间的并行连接使用了两组集合的并行服务器进程。第5-7和第9-11行所示的生产者扫描两张表，应用所有Where子句谓词并将结果数据行发送给消费者。确定谁是生产者的方法非常简单。在下图中第5行和第9行中可以看到PX SEND的标识，在这一标识下方的操作可以被认定为是生产者的操作。第2-4行和第8行的消费者完成实际的哈希连接操作并将结果发送给QC。识别消费者的方法也很简单，在执行计划中，通过PX RECEIVE（第4和第8行）操作我们可以明确识别消费者，消费者会通过PX SEND QC（第2行）操作在完成数据行消费将结果发送给QC。

图32 并行执行时生产者和消费者示例

  我们也可以在TQ列中查看类似的信息。如下图33所示，它展示了执行计划的一组并行服务器进程，以及执行计划中的执行步骤。在下图的执行计划中Q1,00组并行服务器进程（生产者）首先扫描了CUSTOMERS表。随后生产者将结果数据行发送（第5行操作）给消费者Q1,02。执行完这一步后，Q1,00组并行服务器进程变为Q1,01组并行服务器进程（也是生产者）。Q1,01扫描SALES表并将结果数据行发送（第9行操作）给消费者。最后Q1,02并行服务器进程（消费者）接受（第4和第8行操作）来自生产者的数据行，完成哈希连接（第3行操作）并将最终结果发送（第2行操作）给QC。

图33 通过TQ（Table Queue）列查看生产者和消费者示例

数据重分发（Data Redistribution）

  在上面的示例中，两个大表CUSTOMERS和SALES进行了连接操作。为了并行处理这两个大表的连接，需要在生产者和消费者之间进行数据行的重新分发。生产者以块为粒度在区间内（表的一部分）扫描表并应用所有的Where子句谓词，然后将结果数据行发送给消费者，消费者再完成最终的连接。执行计划的最后两列IN-OUT和PQ Distrib显示优化器如何在生产者和消费者之间重新分配数据的相关信息。PQ Distrib列包含的信息最为明确，它在一定程度上取代了IN-OUT列。

以下五种数据重分发方法是最常见的，我们可以在执行计划的PQ Distrib列中看到相关信息。

• HASH
    哈希重分发在并行执行中很常见。哈希重分发可以使并行服务器进程之间实现相对均衡的任务分配。在哈希重分发中，将哈希函数应用于连接列，根据函数结果确定哪个并行服务器进程（消费者）可以消费该数据行。

• BROADCAST
    广播重分发往往会出现在连接操作的两个结果集中，有一个结果集的行数远小于另一个结果集的情形。在广播再分发这种模式下，小结果集的数据行会被分发给所有的消费者，此时所有并行服务进程都拥有了小结果集的完整数据副本，在进一步操作大结果集时每一个并行服务器进程都可完整的进行连接操作。

• RANGE
    区间重分发通常用于需要排序的并行操作。单一并行服务器进程在一个数据区间内工作，这样QC就无需进行任何额外的排序（因为每一个单一数据区间已经排好序），QC只需以正确的顺序显示单一并行服务器进程的处理结果即可。

• KEY
    键值重分发可以确保同一键值的结果集聚集在一起。这是一种主要用于Partial Partition-Wise连接的重分发方法，用以确保连接双方中只有一方需要重分发。

• ROUND ROBIN
    在将数据发送到请求进程之前，轮询重分发可能是最后一步操作。在无需重分发约束的情况下，轮询重分发也可以用于查询的早期阶段。

  在Oracle RAC中，我们可能会在PQ Distrib列看到LOCAL字母后缀LOCAL。LOCAL重分发是RAC中的一种优化方法，它用于最小化并行查询时RAC节点间的互连资源消耗。在这种重分发模式中，数据行将仅仅被分发给同一RAC节点上的消费者。

  在图34中的执行计划示例中，生产者使用哈希重分发向消费者发送数据。

图34 并行处理时的哈希重分发示例

  上图34所示执行计划中对应数据重分发的列，IN-OUT列中的值要么是P->P（第5和第9行)，要么就是P->S（第2行）。P->P表示将数据从一个并行操作发送到另一个并行操作。P->S表示将数据从并行操作发送到串行操作。在第2行中，数据行被发送给QC，这是一个单一的处理过程，因此该列显示的是P->S。但是，如果我们在实际中发现P->S操作发生在执行计划中的较低位置（即早期阶段），则表明在该执行计划中可能存在串行点，这值得我们关注。串行点的出现可能是由于查询中涉及的一个或者多个对象没有设置并行度导致，它会影响并行处理的性能。

结尾

  Oracle优化器的目标是为查询确定最有效的执行计划。优化器根据所知的数据统计信息并利用Oracle数据库的特性（哈希连接、并行查询和分区等）做出最终决策。

  到目前为止，执行计划是我们所能使用的帮我们理解优化器为何会做出当前决策的最有效的工具。通过分解执行计划并关注基数估计、访问路径、连接方法和连接顺序这四个关键元素，我们就可以确定当前优化器选择的执行计划是否是最佳执行计划。