深入并行：从生产者到消费者模型深度理解Oracle的并行

陈焕生

Oracle Real-World Performance Group 成员，senior performance engineer，专注于 OLTP、OLAP 系统 在 Exadata 平台和 In-Memory 特性上的最佳实践。个人博客 http://dbsid.com 。

编辑手记：感谢陈焕生授权我们发布他的精品文章，Sidney撰写这个系列的文章时间跨度也有两年，下篇刚刚出炉，我们先从他去年投稿的第一篇开始。

Oracle 的并行执行

Oracle 的并行执行是一种分而治之的方法. 执行一个 SQL 时, 分配多个并行进程同时执行数据扫描,连接以及聚合等操作, 使用更多的资源, 得到更快的 SQL 响应时间。并行执行是充分利用硬件资源,处理大量数据时的核心技术。

在本文中, 在一个简单的星型模型上, 我会使用大量例子和 sql monitor 报告, 力求以最直观简单 的方式, 向读者阐述并行执行的核心内容:

• Oracle 并行执行为什么使用生产者-消费者模型.

• 如何阅读并行执行计划.

• 不同的数据分发方式分别适合什么样的场景.

• 使用 partition wise join 和并行执行的组合提高性能.

• 数据倾斜会对不同的分发方式带来什么影响.

• 由于生产者-消费者模型的限制, 执行计划中可能出现阻塞点.

• 布隆过滤是如何提高并行执行性能的.

• 现实世界中, 使用并行执行时最常见的问题.

术语说明:

• S: 时间单位秒.

• K: 数量单位一千.

• M: 数量单位一百万, 或者时间单位分钟.

• DoP: Degree of Parallelism, 并行执行的并行度.
  

• QC: 并行查询的 Query Coordinator.

• PX 进程: Parallel Execution Slaves.

• AAS: Average active session, 并行执行时平均的活动会话数.

• 分发: pq distribution method, 并行执行的分发方式, 包括 replicate, broadcast, hash 和 adaptive 分发等 4 种方式, 其中 adaptive 分发是 12c 引入的的新特性, 我将在本篇文章中一一阐述.

• Hash join 的左边: 驱动表, the build side of hash join, 一般为小表.

• Hashjoin的右边:被驱动表,theprobesideofhashjoin,一般为大表.

• 布隆过滤: bloom filter, 一种内存数据结构, 用于判断一个元素是否属于一个集合.

测试环境和数据

Oracle 版本为 12.1.0.2.2，两个节点的 RAC，硬件为 Exadata X3-8.

这是一个典型的星型模型，事实表 lineorder 有 3 亿行记录，维度表 part/customer 分别包含 1.2M 和 1.5M 行记录，3 个表都没有进行分区，lineorder 大小接近 30GB.

本篇文章所有的测试, 除非特别的说明, 我关闭了 12c 的 adaptive plan 特性, 参数optimizer_adaptive_features 被默认设置为 false. Adaptive 相关的特性如 cardinality feedback, adaptive distribution method, adaptive join 都不会启用. 如果检查执行计划的 outline 数据, 你会发 现 7 个优化器相关的隐含参数被设置为关闭状态. 事实上, 12c 优化器因为引入 adaptive plan 特 性, 比以往版本复杂得多, 剖析 12c 的优化器的各种新特性, 我觉得非常具有挑战性, 或许我会在另一篇文章里尝试一下。

并行初体验

串行执行

以下 sql 对 customers 和 lineorder 连接之后, 计算所有订单的全部利润. 串行执行时不使用parallel hint:

select /*+ monitor */ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;

串行执行时, sql 执行时间为 1.5 分钟, db time 为 1.5 分钟. 执行计划有 5 行, 一个用户进程工作完 成了对 customer, lineorder 两个表的扫描, hash join, 聚合以及返回数据的所有操作. 此时AAS(average active sessions)为 1, sql 执行时间等于 db time. 几乎所有的 db time 都为 db cpu, 72%的 cpu 花在了第二行的 hash join 操作. 因为测试机器为一台 Exadata X3-8, 30GB 的 IO 请求在一秒之内处理完成. Cell offload Efficiency 等于 87%意味着经过存储节点扫描, 过滤不需要的列, 最终返 回计算节点的数据大小只有 30GB 的 13%.

并行执行

使用 hint parallel(4), 指定 DoP=4 并行执行同样的 sql:

select /*+ monitor parallel(4)*/ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;

SQL 执行时间为 21s, db time 为 1.4 分钟. DoP=4, 在两个实例上执行. 执行计划从 5 行增加为 9 行,从下往上分别多了’PX BLOCK ITERATOR’, ‘SORT AGGREGATE’, ‘PX SEND QC(RANDOM)’和’PX COORDINATOR’这四个操作.

其中 3 到 8 行的操作为并行处理, sql 的执行顺序为: 每个 PX 进程扫描维度表 customer(第 6 行),以数据块地址区间作为单位(第 7 行)扫描四分之一的事实表 lineorder(第 8 行), 接着进行 hash join(第 5 行), 然后对连接之后的数据做预先聚合(第 4 行), 最后把结果给 QC(第三行). QC 接收数 据(第 2 行)之后, 做进一步的汇总(第 1 行), 最后返回数据(第 0 行).

SQL 执行时间比原来快了 4 倍, 因为最消耗时间的操作, 比如对 lineorder 的全表扫描, hash join 和 聚合, 我们使用 4 个进程并行处理, 因此最终 sql 执行时间为串行执行的 1/4. 另一方面, db time并没有明显下降, 并行时 1.4m, 串行时为 1.5m, 从系统的角度看, 两次执行消耗的系统资源是一 样的.

DoP=4 时, 因为没有涉及数据的分发(distribution), QC 只需分配一组 PX 进程, 四个 PX 进程分别为 实例 1 和 2 的 p000/p0001. 我们可以从系统上查看这 4 个 PX 进程. 每个 PX 进程消耗大致一样的db time, CPU 和 IO 资源. AAS=4, 这是最理想的情况, 每个 PX 进程完成同样的工作量, 一直保持活 跃. 没有串行点, 没有并行执行倾斜。

AAS=4, 查看活动信息时, 为了更好的展示活动信息, 注意点掉”CPU Cores”这个复选框。

在 Linux 系统上显示这四个 PX 进程。

小结

本节的例子中, DoP=4, 并行执行时分配了 4 个 PX 进程, 带来 4 倍 的性能提升. SQL monitor 报告 包含了并行执行的总体信息和各种细节, 比如 QC, DoP, 并行执行所在的实例, 每个 PX 进程消耗 的资源, 以及执行 SQL 时 AAS. 下一节, 我们将深入讨论并行执行的生产者-消费者模型。

生产者-消费者模型

在上面并行执行的例子中, 每个 px 进程都会扫描一遍维度表 customer, 然后扫描事实表 lineorder进行 hash join. 这时没有数据需要进行分发, 只需要分配一组 px 进程. 这种 replicate 维度表的行为, 是 12c 的新特性, 由参数_px_replication_enabled 控制。

更常见情况是并行执行时, QC 需要分配两组 PX 进程, 互为生产者和消费者, 协同工作, 完成并行执行计划。架构图1如下:

Broadcast 分发，一次数据分发

为了举例说明两组 px 进程如何协作的, 设置_px_replication_enabled 为 false. QC 会分配两组 PX进程, 一组为生产者, 一组为消费者。

见下图，此时sql执行时间为23s，执行时间变慢了2s，dbtime 仍为1.5分钟。

最大的变化来自执行计划, 现在执行计划有 12 行. 增加了对 customer 的并行扫描’PX BLOCK ITERATOR’(第 8 行), 分发’PX SEND BROADCAST’和接收’PX RECEIVE’. 执行计划中出现了两组 PX 进 程, 除了之前蓝色的多人标志, 现在出现了红色的多人标志. 此时, SQL 的执行顺序为:

1. 4 个红色的 PX 进程扮演生产者角色, 扫描维度表 customer, 把数据通过 broadcast 的方式分 发给每一个扮演消费者的蓝色 PX 进程. 因为 DoP=4, 每一条被扫描出来的记录被复制了 4 份,从 sql monitor 的第 9 行, customer 全表扫描返回 1.5m 行数据, 第 8 行的分发和第 7 行的接受 之时, 变成了 6m 行记录, 每个作为消费者的蓝色 px 进程都持有了一份完整包含所有custome 记录的数据, 并准备好第 5 行 hash join 的 build table.

2. 4 个作为消费者的蓝色 PX 进程, 以数据块地址区间为单位扫描事实表 lineorder(第 10/11 行);同时和已经持有的 customer 表的数据进行 hash join(第 5 行), 然后对满足 join 条件的数据做 预聚合(第 4 行), 因为我们查询的目标是对所有 lo_revenue 求和, 聚合之后每个 PX 进程只需 输出一个总数。

3. 4 个蓝色的 PX 进程反过来作为生产者, 把聚合的数据发给消费者 QC(第 3 行和第 2 行). 由 QC对接收到 4 行记录做最后的聚合, 然后返回给用户。

4. 使用 broadcast 的分发方式, 只需要把 customer 的数据广播给每个消费者. Lineorder 的数不 需要重新分发. 因为 lineorder 的数据量比 customer 大的多, 应该避免对 lineorder 的数据进行 分发, 这种执行计划非常适合星型模型的数据。

观察 sql monitor 报告中 Parallel 标签下的信息, 红色的 PX 进程为实例 1、2 上的 p002/p003 进程,蓝色的 PX 进程为 p000/p001 进程, 因为蓝色的 PX 进程负责扫描事实表 lineorder, hash join 和聚 合, 所以消耗几乎所有的 db time.

生产者-消费者模型工作原理

并行查询之后, 可以通过视图 V$PQ_TQSTAT, 验证以上描述的执行过程.

1. 实例 1、2 上的 p002/p003 进程作为生产者, 几乎平均扫描 customer 的 1/4 记录, 把每一条记录广播给 4 个消费者 PX 进程, 发送的记录数之和为 6m 行. 通过 table queue 0(TQ_ID=0), 每个 作为消费者的 p000/p001 进程, 接收了完整的 1.5m 行 customer 记录, 接收的记录数之和为6m 行。

2. 实例 1、2 上的 p000/p0001 进程作为生产者, 通过 table queue 1(TQ_ID=1), 把聚合的一条结 果记录发给作为消费者的 QC. QC 作为消费者, 接收了 4 行记录。

那么，以上的输出中, DFO_NUMBER 和 TQ_ID 这两列表示什么意思呢?

1. DFO 代表 Data Flow Operator, 是执行计划中可以并行执行的操作. 一个 QC 代表一棵 DFO 树 (tree), 包含多个 DFO; 同一个 QC 中所有并行操作的 DFO_NUMBER 是相同的, 此例中, 所有DFO_NUMBER 为 1. 执行计划包含多个 QC 的例子也不少见, 比如使用 union all 的语句, union all 每个分支都是独立的 DFO 树, 不同的 DFO 树之间可以并行执行. 本篇文章仅讨论执行计划 只有一个 QC 的情况.

   
   

2. TQ 代表 table queue, 用以 PX 进程之间或者和 QC 通信连接. 以上执行计划中, table queue 0为 PX 进程之间的连接, table queue 1 为 PX 进程和 QC 之间的连接. 生产者通过 table queue分发数据, 消费者从 table queue 接收数据. 不同的 table queue 编号, 代表了不同的数据分发.通过 table queue, 我们可以理解 Oracle 并行执行使用生产者-消费者模型的本质:

• 同一棵 DFO 树中, 最多只有两组 PX 进程。每个生产者进程都存在一个和每个消费者 进程的连接, 每个 PX 进程和 QC 都存在一个连接. 假设 DoP=n, 连接总数为(n*n + 2*n), 随 着 n 的增长, 连接总数会爆炸型增长. Oracle 并行执行设计时, 采用生产者和消费者模型,考虑到连接数的复杂度, 每个 DFO 最多只分配两组 PX 进程. 假设 DoP=100 时, 两组 PX 进 程之间的连接总数为 10000. 假设可以分配三组 PX 进程一起完成并行执行计划, 那么三 组 PX 之间连接总数会等于 1 百万, 维护这么多连接, 是一个不可能的任务.

• 同一棵 DFO 树中, 两组 PX 进程之间，同一时间只存在一个活跃的数据分发。如果执行路径很长，数据需要多次分发, 两组 PX 进程会变换生产者消费者角色, 相互协作, 完成 所有并行操作. 每次数据分发, 对应的 table queue 的编号不同. 一个活跃的数据分发过程,需要两组 PX 进程都参与, 一组为生产者发送数据, 一组为消费者接收数据. 因为一个 DFO 里最多只有两组 PX 进程, 意味着, PX 进程之间, 同一时间只能有一个活跃的数据分发. 如果 PX 进程在执行计划中需要多次分发数据, 可能需要在执行计划插入一些阻塞点，比如 BUFFER SORT 和 HASH JOIN BUFFERED 这两个操作, 保证上一次的数据分发完成之后,才开始下一次分发. 在后面的章节, 我将会说明这些阻塞点带来什么影响.。这个例子中, table queue 0 和 1 可以同时工作是因为: table queue 0 是两组 PX 进程之间的链接, table queue1为PX进程和 QC之间的连接, tablequeue0与tablequeue1是相互独立的，因此可以同时进行.

  
  

• PX 进程之间或者与 QC 的连接至少存在一个(单节点下至多三个, RAC 环境下至多四个)消 息缓冲区用于进程间数据交互, 该消息缓冲区默认在 Large pool 中分配(如果没有配置Large pool 则在 Shared pool 中分配). 多个缓冲区是为了实现异步通信, 提高性能.

• 每个消息缓冲区的大小由参数 parallel_execution_message_size 控制, 默认为 16k。

• 当两个进程都在同一个节点的时候，通过在 Large pool (如果没有配置 Large pool 则 Shared pool) 中传递和接收消息缓冲进行数据交互。当两个进程位于不同节点时。通过 RAC 心跳网络进行数据交互，其中一方接收的数据需要缓存在本地 Large pool (如果没有 配置 Large pool 则 Shared pool) 里面。

小结

为了说明并行执行的生产者-消费者模型是如何工作的, 我使用了 broadcast 分发, QC 分配两组 PX进程, 一组为生产者, 一组为消费者. QC 和 PX 进程之间, 两组 PX 进程之间通过 table queue 进行 数据分发, 协同完成整个并行执行计划. 视图 V$PQ_TQSTAT 记录了并行执行过程中, 数据是如何 分发的. 通过对 DFO, table queue 的描述, 我阐述生产者-消费者模型的工作原理和通信过程, 或许 有些描述对你来说过于突然, 不用担心, 后面的章节我会通过更多的例子来辅助理解.

如何阅读并行执行计划

Table queue 的编号代表了并行执行计划中, 数据分发的顺序. 理解执行计划中的并行操作是如何 被执行的, 原则很简单：跟随 Table queue 的顺序。

通过 sql monitor 报告判断 sql 的执行顺序, 需要结合 name 列的 table queue 名字比 如:TQ10000(代表 DFO=1, table queue 0),:TQ10001(代表 DFO=1, table queue 1), 还有 PX 进程的颜色,进行确定.

下面的例子为 dbms_xplan.display_cursor 的输出. 对于并行执行计划, 会多出来三列:

1. TQ 列: 为 Q1:00 或者 Q1:01, 其中 Q1 代表第一个 DFO, 00 或者 01 代表 table queue 的编号。

   1. ID 7~9 的操作的 TQ 列为 Q1,00, 该组 PX 进程, 作为生产者首先执行, 然后通过broadcast 的分发方式, 把数据发给消费者。

   2. ID 10~11, 3~6 的操作的 TQ 列为 Q1,01, 该组 PX 进程作为消费者接受 customer 的数 据之后, 扫描 lineorder, hash join, 聚合之后, 又作为生产者通过 table queue 2 把数据 发给 QC.
      

      
      

2. In-out 列: 表明数据的流动和分发。

• PCWC: parallel combine with child.

• PCWP: parallel combine with parent.

• P->P: parallel to parallel.

• P->S: parallel to Serial.

  
  

PQ Distribute 列: 数据的分发方式. 此执行计划中, 我们使用了 broadcast 的方式, 下面的章节 我会讲述其他的分发方式.

HASH 分发方式, 两次数据分发

除了 broadcast 分发方式, 另一种常见的并行分发方式为 hash. 为了观察使用 hash 分发时 sql 的 执行情况, 我对 sql 使用 pq_distribute hint.

使用 hash 分发方式时, sql 的执行时间为 29s, db time 为 2.6m. 相对于 broadcast 方式, sql 的执行 时间和 db time 都增加了大约 40%.

执行计划如下, 执行计划为 14 行, 增加了对 lineorder 的 hash 分发, 第 11 行的’PX SEND HASH’对 3亿行数据通过 hash 函数分发, 第 10 行的’PX RECEIVE’ 通过 table queue 1 接收 3 亿行数据, 这两个 操作消耗了 38%的 db cpu. 这就是为什么 SQL 执行时间和 db time 变长的原因. 此时, SQL 的执行 顺序为:

1. 红色的 PX 进程作为生产者, 并行扫描 customer(第 8~9 行), 对于连接键 c_custkey 运用 hash函数, 根据每行记录的 hash 值, 通过 table queue 0, 发给 4 个蓝色消费者的其中一个(第 7 行). Hash 分发方式并不会复制数据, sql monitor 报告的第 6~9 行, actual rows 列都为 1.5m.

2. 红色的 PX 进程作为生产者, 并行扫描 lineorder(第 12~13 行), 对于连接键 lo_custkey 运用同 样的 hash 函数, 通过 table queue 1, 发给 4 个蓝色消费者的其中一个(第 11 行). 同样的 hash函数保证了 customer 和 lineorder 相同的连接键会发给同一个消费者, 保证 hash join 结果的 正确. 因为 3 亿行数据都需要经过 hash 函数计算, 然后分发(这是进程间的通信, 或者需要通 过 RAC 心跳网络通信), 这些巨大的额外开销, 就是增加 38% cpu 的原因.

3. 4 个蓝色的 PX 进程作为消费者接收了 customer 的 1.5M 行记录 (第 6 行), 和 lineorder 的 3 亿 行记录(第 10 行), 进行 hash join(第 5 行), 预聚合(第 4 行).

4. 4 个蓝色的 PX 进程反过来作为生产者,通过 table queue 2, 把聚合的数据发给消费者 QC(第 3行和第 2 行). 由 QC 对接收到 4 行记录做最后的聚合, 然后返回给用户(第 1 和 0 行).

观察 sql monitor 报告中 Parallel 标签下的信息, 红色的 px 进程为实例 1、2 上的 p002/p003 进程,蓝色的 PX 进程为 p000/p001 进程. 作为生产者的红色 PX 进程负责扫描事实表 lineorder, 对 3 亿 行数据进行 hash 分发, 占了超过 1/3 的 db time.

因为涉及 3 亿行数据的分发和接收, 作为生产者的红色 PX 进程和作为消费者的蓝色 PX 进程需要 同时活跃, SQL monitor 报告中的 activity 信息显示大部分时间, AAS 超过并行度 4, 意味这两组 PX进程同时工作. 不像 replicate 或者 broadcast 分发时, AAS 为 4, 只有一组 PX 进程保持活跃.

并行查询之后, 通过视图 V$PQ_TQSTAT, 进一步验证以上描述的执行过程. 并行执行过程涉及 3个 table queue 0/1/2, V$PQ_TQSTAT 包含 21 行记录。

1. 实例 1、2 上的 p002/p003 进程作为生产者, 平均扫描 customer 的 1/4 记录, 然后通过 table queue 0(TQ_ID=0), 发给作为消费者的 p000/p001 进程. 发送和接收的 customer 记录之和都为1.5m.

• 发送的记录数: 1500000 = 365658 + 364899 + 375679 + 393764

• 接收的记录数: 1500000 = 374690 + 374924 + 375709 + 374677
  

2. 实例 1、2 上的 p002/p0003 进程作为生产者，平均扫描 lineorder 的 1/4 记录，通过 table queue 1(TQ_ID=1)，发给作为消费者的 p000/p001 进程。发送和接收的 lineorder 记录之和都为300005811.

• 发送的记录数: 300005811 = 74987629 + 75053393 + 74979748 + 74985041

• 接收的记录数: 300005811 = 74873553 + 74968719 + 75102151 + 75061388

3. 实例 1、2 上的 p000/p0001 进程作为生产者, 通过 table queue 2(TQ_ID=2), 把聚合的一条结果记录发给作为消费者的 QC. QC 作为消费者, 接收了 4 行记录。

小结

我们观察 hash 分发时 sql 的并行执行过程. Hash 分发与 broadcast 最大的区分在于对 hash join 的 两边都进行分发. 这个例子中, 对 lineorder 的 hash 分发会增加明显的 db cpu. 下一节, 我将使用另 一个例子, 说明 hash 分发适用的场景。

Replicate, Broadcast 和 Hash 的选择

我们已经测试过 replicate, broadcast, 和 hash 这三种分发方式.

1. Replicate: 每个 PX 进程重复扫描 hash join 的左边, buffer cache 被用来缓存 hash join 左边的小表, 减少重复扫描所需的物理读. 相对于 broadcast 分发, replicate 方式只需一组 PX 进程. 但 是 replicate 不能替换 broadcast 分发. 因为 replicate 仅限于 hash join 左边是表的情况, 如果hash join 的左边的结果集来自其他操作, 比如 join 或者视图, 那么此时无法使用 replicate.

2. Broadcast 分发: 作为生产者的 PX 进程通过广播的方式, 把 hash join 左边的结果集分发给每 个作为消费者的 PX 进程. 一般适用于 hash join 左边结果集比右边小得多的场景, 比如星型模型。

3. Hash 分发的本质: 把 hash join 的左边和右边(两个数据源), 通过同样 hash 函数重新分发, 切 分为 N 个工作单元(假设 DoP=N), 再进行 join, 目的是减少 PX 进程进行 join 操作时, 需要连接 的数据量. Hash分发的代价需要对hashjoin的两边都进行分发.对于customer连接lineorder 的例子, 因为维度表 customer 的数据量比事实表 lineorder 小得多, 对 customer 进 行 replicate 或者 broadcast 分发显然是更好的选择, 因为这两种方式不用对 lineorder 进行重 新分发. 如果是两个大表 join 的话, join 操作会是整个执行计划的瓶颈所在, hash 分发是唯一 合适的方式. 为了减低 join 的代价, 对 hash join 左边和右边都进行 hash 分发的代价是可以接受的。

Hash 分发, 有时是唯一合理的选择

我们使用 lineorder 上的自连接来演示, 为什么有时 hash 分发是唯一合理的选择. 测试的 SQL 如下:

SQL 执行时间为 2.4 分钟, db time 为 10.5 分钟。

优化器默认选择 hash 分发方式, 执行计划为 14 行, 结构与之前的 Hash 分发的例子是一致的. 不 同的是, 第 5 行的 hash join 消耗了 73%的 db time, 使用了 9GB 的临时表空间, 表空间的 IO 占 12%的 db time. 大约 15%的 db time 用于 Lineorder 的两次 hash 分发和接收, 相对上一个例子的占38% 比例, 这两次 HASH 分发的整体影响降低了一倍多。

红色的 PX 进程为实例 1、2 上的 p002/p003 进程, 蓝色的 PX 进程为 p000/p001 进程.作为生产者 的红色 PX 进程占总 db time 的 15%左右.

SQL 执行开始, 对 lineorder 两次 hash 分发时, AAS 大于 4, 分发完成之后, 只有蓝色的 PX 进程进行hash join 操作, AAS=4.

从 V$PQ_TQSTAT 视图可以确认, 对于 lineorder 的存在两次分发, 通过 table queue 0 和 1, 作为消 费者的 4 个 PX 进程接收到的两次数据是一样的, 保证重新分发不会影响 join 结果的正确性. 每个 蓝色 PX 进程需要 hash join 的左边和右边均为 3 亿行数据的 1/4, 通过 hash 分发, 3 亿行记录连接3 亿行记录的工作平均的分配四个独立 PX 进程各自处理, 每个 PX 进程处理 75M 行记录连接75M 行记录.

使用 broadcast 分发，糟糕的性能

对于 lineorder, lineorder 的自连接, 如果我们使用 broadcast 分发, 会出现什么情况呢? 我们测试一下:

使用 broadcase 分发, SQL 的执行时间为 5.9 分钟, db time 为 23.8 分钟. 相比 hash 分发, 执行时间 和 db time 都增加了接近 1.5 倍。

红色的 PX 进程作为生产者, 对 lineorder 进行并行扫描之后, 3 亿行记录通过 table queue 0 广播给4 个作为消费者的蓝色 PX 进程(第 6~9 行), 相当于复制了 4 份, 每个蓝色的 PX 进程都接收了 3 亿 行记录. 这次 broadcast 分发消耗了 11%的 db time, 因为需要每行记录传输给每个蓝色 PX 进程,消耗的 db cpu 比使用 hash 分发时两次 hash 分发所消耗的还多。

第 5 行的 hash join 的所消耗的临时表空间上升到 27GB, 临时表空间 IO 占的 db time 的 38%. 因为 每个蓝色 PX 进程进行 hash join 的数据变大了, hash join 的左边为 3 亿行数据, hash join 的右边为3 亿行记录的 1/4。

蓝色 PX 进程为消费者负责 hash join, 所消耗的 db time 都大幅增加了。

hash join 时, 临时表空间读等待事件’direct path read temp’明显增加了.

V$PQ_TQSTAT 的输出中, 实例 1、2 上的 p000/p001 进程作为消费者, 都接收了 3 亿行数据, 造成 后续 hash join 的急剧变慢. Broadcast 分发对 hash join 左边进行广播的机制, 决定了它不适合 hash join 两边都为大表的情况。

小结，Broadcast 和 Hash 分发的陷阱

通过前一节和本节的例子, 我们知道, 如果选择了不合理的分发方式, SQL 执行时性能会明显下降。

1. 对于 broadcast 分发: 只对 hash join 的左边进行分发, 但是采用广播分发, hash join 时左边的数据量并没有减少, 如果 hash join 左边的包含大量数据, 并行对 hash join 性能改善有限. 对大 量数据的 broadcast 分发也会消耗额外的 db cpu, 比如本节中 lineorder 自连接的例子. Replicate 同理。

2. 对于 hash 分发: 对 hash join 的两边都进行分发, 使每个 PX 进程进行 hash join 时, 左边和右边 的数据量都为原始的 1/N, N 为并行度. Hash 分发的潜在陷阱在于:

• 两次分发, 尤其对大表的分发, 可能带来明显的额外开销, 比如前一节 customer 连接 lineorder 的例子. 使用 Partition wise join 可以消除分发的需要, 后面会举例说明。

• 如果数据存在倾斜, 连接键上的少数值占了大部分的数据, 通过 hash 分发, 同一个键值的 记录会分发给同一个 PX 进程, 某一个 PX 进程会处理大部分数据的 hash join, 引起并行执行倾斜. 我会在后面的章节说明这种情况和解决方法。

SQL 解析时，优化器会根据 hash join 左边和右边估算的 cardinality，并行度等信息，选择具体何种分发方式。维护正确的统计信息，对于优化器产生合理的并行执行计划是至关重要的。

Partition Wise Join, 消除分发的额外开销

无论对于 broadcast 或者 hash 分发, 数据需要通过进程或者节点之间通信的完成传输, 分发的数 据越多, 消耗的 db cpu 越多. 并行执行时, 数据需要分发, 本质上是因为 Oracle 采用 share- everything 的集中存储架构, 任何数据对每个实例的 PX 进程都是共享的. 为了对 hash join操作分而治之, 切分为 N 个独立的工作单元(假设 DoP=N), 必须提前对数据重新分发, 数据 的分发操作就是并行带来的额外开销。

使用 full 或者 partial partition wise join 技术, 可以完全消除分发的额外开销, 或者把这种开销降到 最低. 如果 hash join 有一边在连接键上做 hash 分区, 那么优化器可以选择对分区表不分发, 因为hash 分区已经对数据完成切分, 这只需要 hash 分发 hash join 的其中一边, 这是 partial partition wise join. 如果 hash join 的两边都在连接键上做了 hash join 分区, 那么每个 PX 进程可以独立的处 理对等的 hash 分区, 没有数据需要分发, 这是 full partition wise join. hash 分区时, hash join 的工作 单元就是对等 hash 分区包含的数据量, 应该控制每个分区的大小, hash join 时就可能消除临时表 空间的使用, 大幅减少所需的 PGA.

Partition Wise Join, 不需要数据分发。

如果在 lineorder 的列 lo_orderkey 上做 hash 分区，分区数为 32 个。每个分区的大小接近 1G.

使用 lo_orderkey 连接时, lineorder 不需要再分发. 我们继续使用自连接的 sql, 演示 full partition wise join.

此时 sql 执行时间为 1.6 分钟, db time 6 分钟; 不分区使用 hash 分发时, 执行时间为 2.4 分钟, db time 10.5 分钟. 使用 Partition Wise join 快了三分之一. 执行计划中只有一组蓝色的 PX 进程, 不需 要对数据进行分发. 因为 lineorder_hash32 的 3 亿行数据被切分为 32 个分区. 虽然并行度为 4, 每 个 PX 进程 hash join 时, 工作单元为一对匹配的 hash 分区, 两边的数据量都为 3 亿的 1/32. 更小 的工作单元, 使整个 hash join 消耗的临时表空间下降为 448MB. 每个 PX 进程消耗 8 对 hash 分区,可以预见, 当我们把并行度提高到 8/16/32, 每个 PX 进程处理的 hash 分区对数, 应该分别为 4/2/1, sql 执行时间会线性的下降。

蓝色的 PX 进程为、的 p000/p001 进程. 每个 PX 进程消耗的 db time 是平均的, 每个 PX 进程均处 理了 8 对分区的扫描和 hash join.

AAS 绝大部分时间都为 4.

唯一的数据连接为 table queue 0, 每个 PX 进程向 QC 发送一行记录.

当 DoP 大于分区数时, Partition Wise Join 不会发生

当并行执行的 DoP 大于 hash 分区数时, partition wise join 不会发生, 这时优化器会使用 broadcast local 的分发。使用 DoP=64 执行同样的 sql:

DoP=64, 查询执行时间为 15 秒, db time 为 11.3 分钟。

执行计划中出现了两组 PX 进程. 优化器选择对 hash join 的右边进行 broadcast local 分发. 如果hash join 的左边比较小的话, broadcast local 会发生在 hash join 的左边. 因为 DoP 是分区数的两倍, hash join 两边的 lineorder_hash64 的每个分区, 由 2 个 PX 进程共同处理。处理一对匹配分区的两 个蓝色的 PX 进程和两个红色的 PX 进程, 会处在同一个实例上. 数据只会在同一个实例的 PX 进程之间, 不会跨实例传输, 降低数据分发成本, 这是 broadcast local 的含义。SQL 的执行顺序如下:

1. 以数据库地址区间为单位, 蓝色的 PX 进程并行扫描 hash join 左边的 lineorder_hash32(第 7 行), 因为 DoP 是分区数的两倍, 每个分区由两个蓝色 PX 进程共同扫描, 这两个 PX 进程在同 一个实例上. 每个蓝色的 PX 进程大约扫描每个分区一半的数据, 大约 4.7M 行记录, 并准备好 第 5 行 hash join 的 build table.

2. 红色的 PX 进程并行扫描 hash join 右边的 lineorder_hash32, 每个红色的 PX 进程大概扫描4.7M 行记录, 然后 table queue 0, 以 broadcast local 的方式, 分发给本实例两个红色的 PX 进 程(数据分发时, 映射到本实例某些 PX 进程, 避免跨节点传输的特性, 称为 slaves mapping, 除 了 broadcast local, 还有 hash local, random local 等分发方式). 通过 broadcast local 分发, 数据 量从 300M 行变成 600M 行。

3. 每个蓝色的 PX 进程通过 table queue 0 接收了大概 9.4M 行数据, 这是整个匹配分区的数据量.然后进行 hash join, 以及之后的聚合操作. 每个蓝色的 PX 进程 hash join 操作时, 左边的数据 量为 lineorder_hash32 的 1/64(=1/DoP), 右边的数据为 lineorder_hash32 的 1/32(=1/分区数).如果继续提高 DoP, 只有 hash join 左边的数据量减少, 右边的数据量并不会减少; 同时, 更多 的 PX 进程处理同一个分区, 会提高 broadcast 分发成本. 所以当 DoP 大于分区数时, 并行执行 的随着 DoP 的提高, 扩展性并不好。

   
   

   
   

查看一个蓝色的 PX 进程, 实例 1 p005 进程的执行信息, 可以确认 hash join 的左边为lineorder_hash32 的 1/64, hash join 的右边为 lineorder_hash32 的 1/32.

小结

数据仓库设计时, 为了取得最佳的性能, 应该使用 partition wise join 和并行执行的组合. 在 大表最常用的连接键上, 进行 hash 分区, hash join 时使优化器有机会选择 partition wise join. Range-hash 或者 list-hash 是常见的分区组合策略, 一级分区根据业务特点, 利用时间范围或者列 表对数据做初步的切分, 二级分区使用 hash 分区. 查询时, 对一级分区裁剪之后, 优化器可以选择partition wise join.

设计 partition wise join 时, 应该尽可能提高 hash 分区数, 控制每个分区的大小. Partition wise join时, 每对匹配的分区由一个 PX 进程处理, 如果分区数据太多, 可能导致 join 操作时使用临时空间,影响性能. 另一方面, 如果分区数太少, 当 DoP 大于分区数时, partition wise join 会失效, 使用更大 的 DoP 对性能改善非常有限.

本文出自数据和云公众号，原文链接