我们都知道，Oracle有项非常厉害的技术---并行技术，也就是说一个语句可以雇佣多个服务器进程（parallel slaves也叫PX slaves）来完成这一个查询所需要的结果.其核心思想是分而治之.大家都知道并行会加快 SQL的速度。但是如何使用并行和并行度设置的把握，以及并行的真正原理还需要了解一下。希望本文可以给你一些启发。
理想是这样的：假如一个sql 查询需要4分钟，如果我开4个并行的话，应该仅仅需要1分钟。
现实是这样的：
雇佣并行进程本身需要一些时间，这些时间往往比较短，如果进程池中没有可用的并行进程，那么还需要操作系统去spawn出需要的并行进程，这时数据库可能会遭遇os thread startup等待。如果语句执行时间只有数秒，你要考虑它是否适合使用并行。
QC（并行查询的Query Coordinator）进程给PX slaves（Parallel Execution Slaves）分配工作，这会消耗一些时间，这个时间一般也非常短。例如QC进程需要给每个PX slave进程分配扫描ROWID的范围。
如果并行查询要返回大量的数据给客户端，那么仅有的一个QC进程本身可能会成为瓶颈。由于Oracle的并行执行采用的是生产者消费者模型，因此一般DOP为4的查询，最终雇佣的PX slaves为8，再加上QC进程本身，一共会占用9个系统进程，你要认识到付出的这些是否值得。
因为是并行操作，所以每个进程干活的分配就尤为重要。所以说，理想和现实总是会有差距的。
1.单表的并行操作
对于单表的并行操作，Oracle一般是按照ROWID或者分区（假如它是分区表的话）来分配工作。
SQL Text
------------------------------
select /*+ parallel(2) xx */count(1) from T_BAL_PERSON a;
上面的SQL及其执行计划显示，对表T_BAL_PERSON以并行度2进行了记录数的统计，Id为5的行源Operation部分为：PX BLOCK ITERATOR，这是一个在并行操作中经常能看到的一个操作，代表了QC进程按照ROWID把表做了切分，每个PX slave扫描表的不同范围，然后每个PX slave聚合出自己所扫描部分的记录数（Id=4,SORT AGGREGATE ）,最后把结果发送给QC，QC进一步聚合这些PX slaves的结果形成一个记录返回给客户端。
通过SQL MONITORING带à 标记的可以直观的看到，(如果启动OEM ,可以到SQL Monitoring明显的看到消费者和生产者用不同颜色区分)绝大部分的工作都是通过3，4，5，6 PX slaves来完成的，然后这些PX slaves把各自做过预聚集的结果发送给（行源ID为3）QC做最终的聚合。
Oracle并行是生产者、消费者模型，一般会雇佣两组PX slaves，一组作为生产者扫描数据，另一组作为消费者把从生产者接收过来的数据做各种加工。看下面小例子：
SQL Text
------------------------------
select /*+ parallel(2) xxx */ * from T_BAL_PERSON a order by a.id
PX slaves作为生产者正在扫描表T_BAL_PERSON，然后把扫描到的数据分发给PX slaves消费者，PX slaves消费者接收到这些数据后并做排序然后把结果集发送给QC。
在Oracle并行执行中，一个可以并行的操作单元（树）称为Data Flow Operator，一个QC代表了一个DFO单元，一个查询可以有多个DFO单元（DFO tree）。生产者和消费者分别代表着一组进程，他们之间需要传递消息和数据，那么他们是靠什么来进行传递消息和数据的呢？这就是table queue的作用。
这里一共包含了两组PX slaves，生产者通过ID为6，7的行源扫描表T_BAL_PERSON，同时通过ID为5的行源把扫描结果写入 table queue TQ10000（PX SEND RANGE），消费者从table queue TQ10000读取数据然后做排序（PX RECEIVE）（id=4），消费者对于已经完成排序的结果通过table queue (TQ10001)发送给QC进程，QC进程把接收到的结果聚合后发送给客户端。
2.如何切分多表
单表可以的拆分可以简单的理解为oracle 按照 rowid来均匀的分布数据，但是多表的切分就不能按照rowid 来切分。因为里面涉及到各个进程的平衡工作和不能漏算数据的问题。
来看一个具体的例子：
SQL> select count(*) from t_big;
COUNT(*)
----------
4000000
SQL> select count(*) from t_small;
COUNT(*)
----------
20000
SQL> select /*+parallel(2) pq_distribute(t_small none broadcast) full(t_small) full(t_big) ttt */
2 count(*)
3 from t_small, t_big
4 where t_small.id = t_big.id;
COUNT(*)
----------
20000
我们通过添加hint pq_distribute( t_small none broadcast)强制让hash join左边的表进行了广播分发，根据SQL MONITORING的输出，我们做如下分析：
1.ID 9，8，7是生产者进程（【操作】列）按照ROWID做切分扫描表 t_small，然后把扫描的结果写入table queue，以广播方式做分发，ID为7的行源PX SEND BROADCAST操作代表了广播的分发方式。
2.ID 6，5是消费进程（【操作】列）接收到生产者进程 PX slave广播的数据，然后构建HASH TABLE。每一个消费PX slave都接收到了全量的 t_small表的数据。
3. ID 11，10是消费进程按照ROWID切割扫描t_big表并与前面构建的HASH TABLE做JOIN。
可以看到这里并没有对t_big进行任何的分发，消费进程只需要按照ROWID范围扫描t_small即可，因为t_small表的数据在每个消费者的PX slave都保持着全量，也就是说t_small实际扫描行数是20000×2 行。
小结：
对于broadcast分发方式来说：
HASH JOIN右边的表不用分发。并且没有结果不对的风险，因为消费者的每个PX slave持有了全部的HASH JOIN左边表的数据，每个消费者进程都持有一个完整的HASH TABLE。
HASH JOIN左边表如果小表的话，分发代价不大。但是随着并行度DOP的提高或者左边表数据量的增大，分发的代价会越来越大。
所以说左边是小表的话，BROADCAST的执行计划具有非常好的扩展性。
第一组PX进程扫描HASH JOIN左边表广播给第二组PX slave如下图：
3.Hash分发
上面说的broadcast/replicate分发方式有一个问题是，因为消费者的每一个PX slaves要持有完整左边表的记录， 因此适合左边表比较小的情况。如果对于两个大表的HASH 连接，Oracle一般使用HASH的分发方式。比如例子：
【集合一】：
1，3，5，7，9，11
【集合二】：
1，9，3，6，7，8，5
【集合一】和【集合2】按照同样的HASH 函数分发后，总能保证有关联的数据对在一起，这样就能保证结果集的正确性。但是这样的方式，多出了一个代价，那就是对于【集合二】也需要做HASH分发， 会多出一些CPU资源的消耗，相对于广播的分发方式，只有【集合一】需要做分发。
例如：
SQL Text
------------------------------
select /*+ parallel(2) pq_distribute(b hash hash) sss*/ count(*) from t_big a, t_big b where a.id = b.id
首先：生产者PX slaves按照ROWID切分并行扫描表T_BIG，然后依据HASH算法把记录通过table queue TQ10000分发给特定的消费者PX slave。
消费者从table queue TQ10000接收到数据后构建HASH TABLE。
上面2步操作完成后,生产者PX slaves继续按照ROWID切分并行扫描表T_BIG_1，然后按照HASH算法把记录通过table queue TQ10001分发给特定的消费者PX slave， 消费者PX slave从table queue TQ10001接收数据并与前面构建的HASH TABLE做JOIN。最后消费者PX slave把自己聚合的结果通过table queue TQ10002发送给QC。
可以看到：【实际行数】列，记录按照HASH分发后并没有增加。
对T_BIG_1 扫描过程，由于数据需要分发，因此会有同时2组PX slaves同时活跃。
HASH分发有着很好的扩展性，每个进程有部分的HASH 表，而不是完整的HASH表，每一行只会分发给一个特定的PX SLAVE。而不是像broadcast分发把每一行广播给每一个SLAVE。但是对HASH JOIN左边表分发完毕后，同样对于HASH JOIN右边的表也需要进行分发，多了一次分发的代价，增加了一些CPU和内存的成本。
4. 如何阅读并行执行计划
技巧2.Table queue 的编号代表了并行执行计划中,数据分发的顺序.理解执行计划中的并行操作是如何被执行的,原则很简单:跟随Table queue 的顺序.
例如：TQ10000(代表DFO=1,table queue 0),:TQ10001(代表DFO=1, table queue 1),
5.v$pq_tqstat
它的内容只记录在QC进程的私有PGA中，而且只在 并行查询结束后内容才会被填充，因此如果并行执行过程中，你取消了查询，那么查询这个视图依然不会有任何结果，因为它只存在进程的PGA中，因此你不能通 过另一个会话去查询它。
可以通过它了解并行执行过程中数据是如何通过table queue分发的。
2 个生产者把各自扫描到的记录做了汇聚各自产生一个记录并把它写入table queue，QC通过table queue接收了这2个记录。NUM_ROWS代表的是PX slaves通过table queue写入、读取的数据量，可以通过NUM_ROWS的值非常容易看出并行进程的工作量是否均匀，是否有并行倾斜存在。 DFO 代表Data Flow Operator, 是执行计划中可以并行执行的操作.一个QC 代表一棵DFO树,包含多个DFO;同一个QC 中所有并行操作的DFO_NUMBER 是相同的。
并行的三种分发方式：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 分发的代价需要对hash join 的两边都进行分发.对于t_small 连接 t_big 的例子, 因为 t_small的数据量比 t_big小得多,对t_small进行replicate 或者broadcast 分发显然是更好的选择,因为这两种方式不用对t_big 进行重新分发.如果是两个大表join 的话, join 操作会是整个执行计划的瓶颈所在,但是hash 分发是唯一合适的方式.为了减低join 的代价,对hash join 左边和右边都进行hash 分发的代价是可以接受的.
关于并行使用的一些总结：
1.非常有效率的执行计划，如果执行计划本身非常糟糕，使用并行可能并不能多大程度上改善语句的执行效率。
2.数据库系统有着充足的资源可用。
3.工作量的分配没有明显的倾斜，大家都知道短板理论，如果某一个PX slave干了绝大部分的活，那么最终的响应时间最大的瓶颈就是它。
最基本的先聊到这里，后面还有消费者生产者模型，布隆过滤，12c并行新特征等有关并行的技术等着大家去学习挖掘。
--lishou
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-2014274/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-2014274/
1205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
