|
HASH JOIN BUFFERED，连续hash分发时执行计划中的阻塞点
到目前为止，所有的测试只涉及两个表的连接。如果多于两个表，就需要至少两次的hash join，数据分发次数变多，生产者消费者的角色可能互换，执行计划将不可避免变得复杂。执行路径变长，为了保证并行执行的正常进行，执行计划可能会插入相应的阻塞点，在hash join时，把符合join条件的数据缓存到临时表，暂停数据继续分发。本节我使用一个三表连接的sql来说明连续hash join时，不同分发方式的不同行为。
使用Broadcast分发，没有阻塞点。
测试三个表连接的sql如下，加入part表，使用hint让优化器两次hash join都使用broadcast分发。Replicate SQL查询性能类似。
SQL执行时间为42秒，dbtime为2.6分钟。
AAS=(sql db time)/(sql 执行时间)=(2.6*60)/42=3.7，接近4，说明4个PX进程基本一直保持活跃。
执行计划是一颗完美的右深树，这是星型模型查询时执行计划的典型形式。生产者对两个维度进行broadcast分发，消费者接受数据之后准备好两次hash join的build table， 最后扫描事实表，并进行hash join。我们通过跟随table queue顺序的原则，阅读这个执行计划。
- 红色PX进程作为生产者并行扫描part，通过tablequeue0广播给每个蓝色的消费者PX进程 (第7~9行)。每个蓝色的PX进程接收part的完整数据(第6行)，1.2M行记录，并准备好第5行hash join的build table。
- 红色PX进程作为生产者并行扫描customer，通过tablequeue1广播broadcast给每个蓝色的 消费者PX进程(第12~14行)。每个蓝色的PX进程接收customer的完整数据(第11行)，1.5M行记录，并准备好第10行hash join的build table。
- 蓝色的PX进程并行扫描事实表lineorder，对每条符合扫描条件(如果sql语句包含对lineorder的过滤条件)的3亿行记录，进行第10行的hash join，对于每一条通过第10行的 hash join的记录，马上进行第5行的hash join，接着再进行聚合。从sql monitor报告的 Timeline列信息，对lineorder的扫描和两个hash join操作是同时进行的。执行计划中没有阻塞点，数据在执行路径上的流动不需要停下来等待。大部分的db cpu消耗在两次hash join操作。最优化的执行计划，意味着经过每个hash join的数据越少越好。对于这类执行计划，你需要确保优化器把最能过滤数据的join，放在最接近事实表的位置执行。
连续hash分发，执行计划出现阻塞点
使用以下hints，强制SQL使用hash分发。
SQL执行时间为1.5分钟，dbtime为8.1分钟。相对于增加了14GB的IO操作。
连续两次hash join都使用HASH分发，每次hash join左右两边都需要分发，PX进程之间发生4次数据分发。执行计划中最显著的地方来自第12行的HASH JOIN BUFFERED，这是一个阻塞性的操作。下面，我们依然通过跟随table queue顺序的原则，阅读执行计划，并解析为什么出现HASH JOIN BUFFERED这个阻塞操作，而不是一般的HASH JOIN。
1. 蓝色的PX进程作为生产者，并行扫描customer，通过tablequeue0，hash分发给作为消费者的红色PX进程(第14~16行)。每个红色的PX进程接收了1/4的customer的数据(第13行)， 大约为370k行记录，并准备好第12行‘HASH JOIN BUFFERED’的build table。与broadcast分发区别的是，此时执行计划是从第16行，扫描靠近lineorder的customer开始的，而不是从第一个没有’孩子’的操作(第9行扫描part)开始的。这是hash分发和串行执行计划以及broadcast分发不同的地方。
2. 蓝色的PX进程作为生产者，并行扫描lineorder，通过tablequeue1，hash分发作为消费者的红色PX进程(第18~20行)。每个红色PX进程接收了1/4的lineorder数据(第17行)，大约75M行记录。每个红色PX进程在接收通过tablequeue1接收数据的同时，进行第12行的hash join，并把join的结果集在PGA中作缓存，使数据暂时不要继续往上流动。如果结果集过 大的话，需要把数据暂存到临时空间，比如我们这个例子，用了7GB的临时空间。你可以理解为把join的结果集暂存到一个临时表。那么，为什么执行计划需要在这里插入一个阻塞点，阻止数据继续往上流动呢?
这里涉及生产者消费者模型的核心：同一棵DFO树中，最多只能有两组PX进程，一个数据分发要求两组PX进程协同工作; 这意味着同一时刻，两组PX进程之间，最多只能存在一个活跃的数据分发，一组作为生产者发送数据，一组作为消费者接收数据，每个PX进程只能扮演其中一种角色，不能同时扮演两种角色。当红色的PX进程通过tablequeue1向蓝色的PX进程分发lineorder数据，同时，蓝色的PX进程正在接收lineorder数据，并进行hash join。观察timeline列的时间轴信息，第12，17~20行是同时进行的。 但是此时红色的PX进程不能反过来作为生产者，把hash join的结果分发给蓝色进程，因为此时有两个限制：
• 蓝色的PX进程作为生产者，正忙着扫描lineorder;此时，无法反过来作为消费者，接收来自红色PX进程的数据。
• 第5行hash jon操作的build table还没准备好，这时表part甚至还没被扫描。
所以Oracle需要在第12行hash join这个位置插入一个阻塞点，变成HASH JOIN BUFFER操作，把join的结果集缓存起来。当蓝色的PX进程完成对lineorder的扫描和分发，红色的PX进程完成第12行的hash join并把结果完全暂存到临时空间之后。Tablequeue2的数据分发就开始了。
3. 红色的PX进程作为生产者， 并行扫描part，通过tablequeue2，分发给作为消费者的蓝色PX进程(第7~9行)。每个蓝色PX进程接收了1/4的part数据(第6行)，大概300k行记录，并准备好第5行hash join的build table。
4. 红色的PX进程作为生产者，把在第12行”HASH JOIN BUFFERED”操作，存在临时空间的对于customer和lineorder连接的结果集，读出来，通过table queue 3，分发给蓝色的PX进程(第11~12行)。“HASH JOIN BUFFERED”这个操作使用了7GB的临时空间，写IO7GB，读IO 7GB，IO总量为 14GB。
5. 每个蓝色的PX进程作为消费者，接收了大约75M行记录。对于通过tablequeue3接收到的 数据，同时进行第5行的hash join，并且通过join操作的数据进行第4行的聚合操作。当tablequeue3上的数据分发结束，每个蓝色的PX进程完成hash join和聚合操作之后，再把各自的聚合结果，一行记录，通过tablequeue4，分发给QC(第3~5行)。QC完成最后的聚合，返回给客户端。
小结
因为使用星型模型测试，这个例子使用Broadcast分发或者replicate才是合理的。实际应用中，连续的hash分发并不一定会出现HASH JOIN BUFFERED这个阻塞点，如果查询涉及的表都较小，一般不会出现HASH JON BUFFERED。即使执行计划中出现BUFFER SORT，HASH JOIN BUFFERED等阻塞操作，也不意味着执行计划不是最优的。如果sql性能不理想，HASH JOIN BUFFERED操作消耗了大部分的CPU和大量临时空间，通过sql monitor报告，你可以判断这是否是合理的：
- 检查estimated rows和actual rows这两列，确定优化器对hash Join左右两边cardinality估算是否出现偏差，所以选择hash分发。
- 同样检查hash join操作的estimated rows和actual rows这两列，优化器对hash join结果集cardinality的估算是否合理。优化器会把hash join的两边视为独立事件，对join结果集cardinality的估算可能过于保守，estimate rows偏小。对于星型模型的一种典型情况：如果多个维度表参与连接，执行路径很长，一开始维度表的分发方式为broadcast，事实表不用分发，经过几次join之后，结果集cardinality下降很快，后续hash join两边的estimated rows接近，导致优化器选择hash分发。
- 通过检查每个join所过滤的数据比例，确定优化器是否把最有效过滤数据的join最先执行，保证在执行路径上流动的数据量最少。
Hash join和布隆过滤
布隆过滤在并行执行计划中的使用非常普遍，我将在本章节解释这一数据结构及其作用。 从11.2版本开始，串行执行的sql也可以使用布隆过滤。
关于布隆过滤
布隆过滤是一种内存数据结构，用于判断某个元素是否属于一个集合。布隆过滤的工作原理图2如下：
引用自维基百科：http：//en.wikipedia.org/wiki/Bloom_filter
如图，布隆过滤是一个简单的bit数组，需要定义两个变量：
1. m：数组的大小，这个例子中，m=18.
2. k：hash函数的个数，这个例子中，k=3，
一个空的布隆过滤所有bit都为0。增加一个元素时，该元素需要经过三个hash函数计算， 得到3个hash值，把数组中这三个位置都置为1。集合{x，y，z}的3个元素，分布通过三次hash计算，把数组9个位置设置为1。判断某个元素是否属于一个集合，比如图中的w， 只需对w进行三次hash计算产生三个值，右边的位置在数组中不命中，该位置为0，可以确定，w不在{x，y，z}这个集合。由于存在hash碰撞，布隆过滤的判断会过于乐观(false positive)，可能存在元素不属于{x，y，z}，但是通过hash计算之后三个位置都命中，被错误认定为属于{x，y，z}。根据集合元素的个数，合理的设置数组大小m，可以把错误判断的几率控制在很小的范围之内。
布隆过滤对hash join性能的改进
布隆过滤的优势在于使用的很少内存，就可以过滤大部分的数据。如果hash join的左边包含过滤条件，优化器可能选择对hash join左边的数据集生成布隆过滤，在扫描hash join右边时使用这个布隆布隆作为过滤条件，第一时间把绝大部分不满足join条件数据排除。减少数据分发和join操作所处理的数据量，提高性能。
使用布隆过滤时的性能
使用布隆过滤时的性能对customer使用c_nation=’CHINA’条件，只计算来自中国地区的客户订单的利润总和。我们观察使用布隆过滤和不使用布隆过滤时性能的差别。
SQL执行时间为1秒，dbtime为7.9 秒。优化器默认选择replicate的方式。执行计划中多了JOIN FILTER CREATE和JOIN FILTER USE这两个操作。SQL的执行顺序为每个PX进程重复扫描customer表(第7行)，对符合c_nation=’CHINA’数据集，60K(240K/4)行记录，在c_custkey列生成布隆过滤：BF0000(第6行JOIN FILTER CREATE)。在扫描lineorder时使用这个布隆过滤(第8行JOIN FILTER USE)。虽然lineorder总行数为300M，sql没有过滤条件，只使用布隆过滤，扫描之后只返回28M行记录，其他272M行记录被过滤掉了。每个PX进程在hash join操作时，只需处理60K行customer记录和7M(28M/4)行lineorder记录的连接，大大降低join操作的成本。对于Exadata，Smart Scan支持布隆过滤卸载到存储节点，存储节点扫描lineorder时，使用布隆过滤排除272M行记录，对于符合条件的数据，把不需要的列也去掉。Cell offload Efficiency=98%，意味着只有30GB的2%从存储节点返回给PX进程。如果不使用布隆过滤，Cell Offload Efficieny不会高达98%，我们将在下个例子看到。对于非Exadata平台，由于没有Smart Scan特性，数据的过滤操作需要由PX进程完成，布隆过滤的效果不会这么明显。12C的新特性Database In-‐memory，支持扫描列式存储的内存数据时，使用布隆过滤。
执行计划中出现第10行对LINEORDER的扫描时，使用了布隆过滤条件：SYS_OP_BLOOM_FILTER(：BF0000，"LO_CUSTKEY")
不使用布隆过滤时的性能
接着，我们通过hint NO_PX_JOIN_FILTER，禁用布隆过滤，观察此时的sql执行性能。
SQL执行时间为9秒，dbtime为33.7秒。比使用布隆过滤时，性能下降明显。优化器依然选择replicate的方式，执行计划中没有PX JOIN CREATE和PX JOIN USE操作。db time增加为原来4倍的原因：
- 当PX扫描lineorder时，返回300M行记录。没有布隆过滤作为条件，每个PX进程需要从存储节点接收75M行记录。
- 进行第5行的hash join操作时，每个PX进程需要连接60k行customer记录和75M行lineorder记录。Join操作的成本大幅增加。
由于没有布隆过滤，Cell Offload Efficiency下降为83%。
HASH分发时布隆过滤的生成，传输，合并与使用
我们通过hint强制使用hash分发，观察此时sql执行计划中布隆过滤的生成和使用。
此时sql执行时间为4秒，db time为19.4秒。执行计划第6行为JOIN FILTER CREATE; 第13行为JOIN FILTER USE。此例，PX 进程分布在多个RAC两个实例，Hash分发时涉及布隆过滤的生成，传输，合并和使用，较为复杂，具体过程如下：
- 布隆过滤的产生：4个蓝色的PX进程作为消费者，通过tablequeue0，接收红色的PX进程hash分发的customer数据，每个蓝色的PX进程接收15K行记录。接收customer记录的同时，实例1的两个蓝色PX进程在SGA共同生成一个布隆过滤，假设为B1; 实例2的两个蓝色PX进程在SGA共同生成一个布隆过滤，假设为B2。因为位于SGA中，布隆过滤B1对于实例1的 两个红色的PX进程是可见的，同样，B2对于实例2的两个红色PX进程也是可见的。
- 布隆过滤的传输：当红色的PX进程完成对hash join左边customer的扫描，就会触发布隆过滤B1/B2的传输。实例1的红色PX进程把B1发给实例2的蓝色PX进程; 实例2的红色PX进程把B2发给实例1的蓝色PX进程。
- 布隆过滤的合并：实例1的蓝色PX进程合并B1和接收到的B2; 实例2的蓝色PX进程合并B2和接收到的B1。合并之后，实例1和2产生相同布隆过滤。
- 布隆过滤的使用：实例1和2的4个红色的PX进程作为生产者，并行扫描lineorder时使用 合并之后的布隆过滤进行过滤。Lineorder过滤之后为49M行记录，此时的布隆过滤似乎没有replicate时的有效。Cell Offloadload Efficiency为97%。
如果并行执行只在一个实例，则红色的PX进程不需要对布隆过滤进行传输，蓝色的PX进程也无需对布隆过滤进行合并。
因为hash join的成本大大降低了，对于lineorder 49M行记录的hash分发，成为明显的平均，占53%的db time。
小结
本节阐述了布隆过滤的原理，以及在Oracle中的一个典型应用：对hash join性能的提升。布隆过滤的本质在于把hash join的连接操作提前了，对hash join右边扫描时，就第一时间把不符合join条件的大部分数据过滤掉。大大降低后续数据分发和hash join操作的成本。不同的分布方式，布隆过滤的生成和使用略有不同：
- 对于broadcast分发和replicate，每个PX进程持有hash join左边的完整数据，对连接键生成一个完整的布隆过滤，扫描hash join右边时使用。如果sql涉及多个维度表，维度表全部使用broadcast分发，优化器可能对不同的维度表数据生成多个的布隆过滤，在扫描事实表时同时使用。
- 对于hash分发，作为消费者的PX进程接收了hash join左边的数据之后，每个PX进程分别对各自的数据集生成布隆过滤，再广播给作为生产者的每个PX进程，在扫描hash join右边时使用。
真实世界中，优化器会根据统计信息和sql的过滤条件自动选择布隆过滤。通常使用布隆过滤使都会带来性能的提升。某些极端的情况，使用布隆过滤反而造成性能下降，两个场景：
- 当hash join左边的数据集过大，比如几百万行，而且连接键上的唯一值很多，优化器依然选择使用布隆过滤。生成的布隆过滤过大，无法在CPU cache中完整缓存。那么使用布隆过滤时，对于hash join右边的每一行记录，都需要到内存读取布隆过滤做判断，导致性能问题。
- 如果Join操作本身无法过滤数据，使用布隆过滤时hash join右边的数据都会命中。优化器可能无法意识到join操作无法过滤数据，依然选择使用布隆布隆。如果hash join右边数据集很大，布隆过滤可能会消耗明显的额外cpu。
|
