oracle并行比不并行慢,陈焕生：深入理解Oracle 的并行执行(二)

如何阅读并行执行计划

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

通过sqlmonitor报告判断sql的执行顺序，需要结合name列的tablequeue名字比如：TQ10000(代表DFO=1，tablequeue0)，：TQ10001(代表DFO=1，tablequeue1)，还有PX进程的颜色，进行确定。

下面的例子为dbms_xplan。display_cursor 的输出。对于并行执行计划，会多出来三列：

1. TQ列：为Q1：00或者Q1：01，其中Q1代表第一个DFO，00或者01代表tablequeue的编号。

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

b. ID10~11，3~6的操作的TQ列为Q1，01，该组PX进程作为消费者接受customer的数据之后，扫描lineorder，hashjoin，聚合之后，又作为生产者通过tablequeue2把数据 发给QC。

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

•  PCWC：parallelcombinewithchild。

•  PCWP：parallelcombinewithparent。

•  P-­‐>P：  paralleltoparallel。

•  P-­‐>S：  paralleltoSerial。

3. PQDistribute 列：数据的分发方式。此执行计划中，我们使用了broadcast 的方式，下面的章节

我会讲述其他的分发方式。

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

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

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

执行计划如下，执行计划为14行，增加了对lineorder的hash分发，第11行的’PXSENDHASH’对3亿行数据通过hash函数分发，第10行的’PXRECEIVE’通过tablequeue1接收3亿行数据，这两个操作消耗了38%的dbcpu。这就是为什么SQL执行时间和dbtime变长的原因。此时，SQL的执行顺序为：

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

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

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

4个蓝色的PX进程反过来作为生产者，通过table queue2，把聚合的数据发给消费者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

个tablequeue0/1/2，V$PQ_TQSTAT包含21行记录。

1.　实例1、2上的p002/p003进程作为生产者，平均扫描customer的1/4记录，然后通过tablequeue0(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 queue1(TQ_ID=1) ，发给作为消费者的p000/p001进程。发送和接收的lineorder 记录之和都为300005811。

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

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

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

小结

数组大小m，可以把错误判断的几率控制在很小的范围之内。

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

Replicate，Broadcast和Hash的选择

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

Replicate ：每个PX进程重复扫描hashjoin 的左边，buffercache 被用来缓存hashjoin 左边的小表，减少重复扫描所需的物理读。相对于broadcast 分发，replicate 方式只需一组PX进程。但是repli cate不能替换br oadcast分发。因为repli cate仅限于hashj oin左边是表的情况，如果hashjoin的左边的结果集来自其他操作，比如j oin或者视图，那么此时无法使用repli cate。

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

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

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

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

SQL执行时间为2.4分钟，dbtime为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 queue0和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亿行记录通过tablequeue0广播给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执行时性能会明显下降

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

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

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

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

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