用hash分发模式解决并行优化器bug

背景

最近给项目做性能优化，遇到一个并行优化器bug，场景是简单的2张300W表做内关联，开的8个并行，走的hash连接，但是性能特别差，拿到执行计划是走了broadcast分发模式，这明显不对，应该走hash分发模式。当时以为是统计信息不准确导致，收集完统计信息以后，执行计划并没有改变，猜测是oracle优化器的Bug。

broadcast广播分发

通俗来讲就是将其中一张表全量结果集传输到其他拥有另一张表部分结果集的多个进程上做关联，非常试用于小表与大表的分发场景

hash分发

将两张表采用关联键进行hash运算分发，这样每个并行服务器上只有各自一部分不重复结果集关联，适合大表与大表的分发场景，这种分发模式可以手工实现优化超级大表的关联。

实验

1.创建2个测试表

create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;

2.复现broadcast分发模式

select/*+ optimizer_features_enable('11.2.0.4')  parallel(4) pq_distribute(a broadcast none)*/ *
from test1 a, test2 b
where a.object_id=b.object_id
--执行计划
----------------------------------------------------------
Plan hash value: 1728626078

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 71566 |    65M|   215   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 71566 |    65M|   215   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |          | 71566 |    65M|   215   (2)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND BROADCAST  | :TQ10000 | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR |          | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| TEST2    | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX BLOCK ITERATOR   |          | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL  | TEST1    | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint


统计信息
----------------------------------------------------------
         47  recursive calls
          0  db block gets
       3490  consistent gets
       1105  physical reads
          0  redo size
    8448172  bytes sent via SQL*Net to client
      53574  bytes received via SQL*Net from client
       4826  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      72364  rows processed

3.用hash分发调整执行计划

select/*+ optimizer_features_enable('11.2.0.4')  parallel(4) pq_distribute(a hash hash)*/ *
from test1 a, test2 b
where a.object_id=b.object_id
--执行计划
----------------------------------------------------------
Plan hash value: 2213618322

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 71566 |    65M|   215   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 | 71566 |    65M|   215   (2)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          | 71566 |    65M|   215   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| TEST2    | 71566 |    32M|   107   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| TEST1    | 92106 |    42M|   107   (1)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint


统计信息
----------------------------------------------------------
         47  recursive calls
          0  db block gets
       3486  consistent gets
        992  physical reads
          0  redo size
   10065498  bytes sent via SQL*Net to client
      53569  bytes received via SQL*Net from client
       4826  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      72364  rows processed

4.分析

使用optimizer_features_enable('11.2.0.4')这个hint是复现当时场景，本地安装的是19c版本使用自适应的并行分发方法，hybrid hash分发模式，这里不过多介绍。
简单从逻辑读consistent gets观察，二者区别不大，但是区别在于广播大表的消耗与做hash分发消耗，以及笔者认为最重要的是hash table的大小是否能完全容纳于内存，从而导致大量临时表空间读写消耗。

总结

要理解并发每种分发模式原理，这样即使遇到oracle自身Bug我们也可以游刃有余。