Oracle并行基础一

约定不等于承诺〃 2022-07-30 13:25 214阅读 0赞

# Oracle并行基础（连载一） #

作者：沃趣科技高级数据库技术专家 魏兴华

--------------------

## 概述 ##

![28218939_1453965603kWGK.jpg][]

Oracle企业版有一项非常厉害的技术：并行查询，也就是说一个语句可以雇佣多个服务器进程（parallel slaves也叫PX slaves）来完成这一个查询所需要的结果。并行操作不仅仅能够充分利用主机的CPU资源，也能够充分利用系统的IO资源、内存资源，这看起来是一个优点，但是也需要看情况，如果数据库系统没有太多的空闲CPU、空闲IO或空闲内存资源，那么并行技术是否要使用非常值得考虑，甚至即使系统有着很多的CPU空闲资源，但是IO资源已经远远不够，那么同样需要考虑是否要使用并行（并行往往产生大量的IO）。鉴于并行操作的工作方式，不能让它在系统中被滥用，否则可能导致系统的资源很快的被耗尽。并行操作本身也是复杂的，它有着很多串行执行所不具备的概念，例如table queue，数据分发方式等等，并且阅读并行语句执行计划的方式也与串行可能会有所不同。  
并行操作的目的是为了提升语句执行的线性度，如果一个语句串行执行的时间为4分钟，那么通过指定4个并行来操作，可以加快查询执行时间为1分钟，当然这只是一种预期，现实的情况往往不能达到这种线性度。有一些消耗和事实需要了解：

*  雇佣并行进程本身需要一些时间，这些时间往往比较短，如果进程池中没有可用的并行进程，那么还需要操作系统去spawn出需要的并行进程，这时数据库可能会遭遇os thread startup等待。如果语句执行时间只有数秒，你要考虑它是否适合使用并行。
 *  QC进程给PX slaves分配工作，这会消耗一些时间，这个时间一般也非常短。例如QC进程需要给每个PX slave进程分配扫描ROWID的范围。
 *  如果并行查询要返回大量的数据给客户端，那么仅有的一个QC进程本身可能会成为瓶颈。
 *  由于Oracle的并行执行采用的是生产者消费者模型，因此一般DOP为4的查询，最终雇佣的PX slaves为8，再加上QC进程本身，一共会占用9个系统进程，你要认识到付出的这些是否值得。
 *  在Exadata下即使使用串行查询，由于在IO层面默认就是并行，因此Exadata下的语句并行效果没有非Exadata下好。

为了让并行能够非常好的发挥作用，有一些要求需要被满足：

*  非常有效率的执行计划，如果执行计划本身非常糟糕，使用并行可能并不能多大程度上改善语句的执行效率。
 *  数据库系统有着充足的资源可用。这点已经在文章的开头提到过。
 *  工作量的分配没有明显的倾斜，大家都熟悉短板理论，如果某一个PX slave干了绝大部分的活，那么最终的响应时间最大的瓶颈就是它。

也许上面的很多概念和术语你还不清楚，没关系，我们下面的内容都会介绍到。使用并行首先应该考虑的问题是如何分配工作量，在串行执行的情况，这个问题不用考虑，只有一个进程干活，所有的工作都是由它来完成，但是如果使用了并行操作，那就意味着有多个进程在干同样一件事，工作的分配就显得非常的重要。

## 单表的并行操作 ##

对于单表的并行操作，工作量的切分是比较简单的，Oracle也没有设计任何复杂的算法，它一般是按照  
ROWID或者分区（假如它是分区表的话）来分配工作。例如下面的并行查询：  
![28218939_1455546349T91e.png][]

上面的SQL及其执行计划显示，对表test以并行度2进行了记录数的统计，Id为5的行源Operation部分为：PX BLOCK ITERATOR，这是一个在并行操作中经常能看到的一个操作，代表了QC进程按照ROWID把表做了切分，每个PX slave扫描表的不同范围，然后每个PX slave聚合出自己所扫描部分的记录数（Id=4,SORT AGGREGATE ）,最后把结果发送给QC，QC进一步聚合这些PX slaves的结果形成一个记录返回给客户端。  
通过SQL MONITORING可以看到的更为直观（下图），绝大部分的工作都是通过蓝色的PX slaves来完成的，然后这些PX slaves把各自做过预聚集的结果发送给（行源ID为3）QC做最终的聚合。  
![28218939_1453965698AvVS.jpg][]

不过我们随着后续的学习会发现，这里的这个例子只雇佣了一组PX slaves进程，这在Oracle并行的世界中是一个特殊案例。按照Oracle的生产者、消费者模型，一般会雇佣两组PX slaves，一组作为生产者扫描数据，另一组作为消费者把从生产者接收过来的数据做各种加工。（不过这个例子可以把QC作为消费者看待）。

> 本文大量使用了SQL MOMITORING工具，如果你对这个工具还不熟悉，请参阅我的另一篇文章：  
> [http://www.jianshu.com/p/ce85dd0c05ab][http_www.jianshu.com_p_ce85dd0c05ab]

我们对SQL进行简单的改造，增加ORDER BY部分，看看结果会怎么样。

SQL>select /*+ parallel(a 2) */ * from hash_t1 a order by object_name;

![28218939_14539657982DuB.jpg][]

同样我们通过SQL MONITORING来进行可视化解析，【操作】列出现了两种不同颜色的PX slaves，红色的PX slaves作为生产者正在扫描表HASH\_T1，然后把扫描到的数据分发给蓝色的PX slaves消费者，PX slaves消费者接收到这些数据后并做排序然后把结果集发送给QC。  
这个例子虽小，但是五脏俱全，在Oracle并行执行中，一个可以并行的操作单元（树）称为Data Flow Operator，一个QC代表了一个DFO单元，一个查询可以有多个DFO单元（DFO tree），例如典型的像union all语句，就可以有多个DFO单元，不同的DFO单元之间也可以并行。  
具备了Oracle并行执行生产者和消费者的概念，继续看上图中的【名称】列，会发现有TQ10001，TQ10000的东西，这个是啥？

## table queue ##

上面已经提到Oracle并行操作有生产者和消费者的概念，生产者和消费者分别代表着一组进程，他们之间需要传递消息和数据，那么他们是靠什么来进行传递消息和数据的呢？这就是table queue的作用。  
继续以上图为例：

![28218939_14539658404G4g.jpg][]

这里一共包含了两组PX slaves，一组为红色的生产者，一组为蓝色的消费者，生产者通过ID为6，7的行源扫描表HASH\_T1，同时通过ID为5的行源把扫描结果写入table queue TQ10000（PX SEND RANGE），消费者从table queue TQ10000读取数据然后做排序（PX RECEIVE），消费者对于已经完成排序的结果通过table queue TQ10001发送给QC进程，QC进程把接收到的结果聚合后发送给客户端。

## 如何切分多表 ##

### 为什么要引入数据分布算法 ###

对于单表（无JOIN）的数据切分是非常简单的，只需要按照ROWID做切分就可以保证结果的正确，因为多个并行slaves之间没有数据的交叉，也就不会有数据的丢失，而且按照ROWID切分也非常容易保证每个PX slave的工作量均匀。但是如果是两表的JOIN呢？你如何保证1/N的X表的记录和相对应的1/N的Y表的记录在一个并行操作内（也就是由一个并行进程处理）？两个表都按照ROWID来切分是不能保证的。  
为了让例子足够的简单，可以通过如下例子来进行描述：  
集合一：  
【1，3，5，7，9，11】  
集合二：  
【1，9，3，6，7，8，5】

假如要求使用并行度2来判断，【集合二】和【集合一】有多少数据有交集，该如何实现？  
我们模拟通过ROWID来切分，把【集合一】按照顺序切分为2部分：  
set 1:1，3，5 =>进程1  
set 2:7，9，11 =>进程2  
我们再使用同样的办法，把【集合二】切分为2部分：  
set 3:1，9，3 =>进程1  
set 4:6，7，8，5 =>进程2

通过上面一系列的操作我们把2个集合都切分为了2份，然后我们通过进程1对set 1与set 3做join，进程2对set 2与set 4做join，OK？  
显然是不行的，因为最终的结果集是不对的。  
两个集合做JOIN正确的结果是：3，5，7，9  
但是按照上面的算法，set 1和set 3的结果集为3，set 2和set 4的结果集为7,最终的结果集为3，7，丢失了5，9两个结果。  
因此不能为了加快查询的速度而不保证结果正确性对对数据进行随意切割。那么Oracle是如何做的？如何保证进程读取了X表的1/N的数据与Y表相对应的1/N数据？  
从这里看出了引入了数据分布算法的重要性，也解释了为什么运行并行度N需要2N个并行slave来完成工作，一组进程用来扫描表X，然后把数据按照分布算法把数据分发给另一组进程Y，这样表X的数据分布完成后，Y的表记录要根据X表的分布算法来决定自己的分布方式。你看到这里可能有些地方可能还看不明白，没关系，后续有足够的内容让你明白这些操作。

## BroadCast ##

继续前面的例子  
【集合一】：  
1，3，5，7，9，11  
【集合二】：  
1，9，3，6，7，8，5

broadcast的分发方式为（这里假设并行度为2）：  
Oracle首先需要产生2组PX slaves，一组为生产者包含2个PX slave进程，一组为消费者，同样包含2个PX slave进程，（注意生产者和消费者角色是可能互换的）。  
每个生产者PX slave按照ROWID切分，扫描1/2的【集合一】，然后广播给每一个消费者的PX slave，最终每一个消费者的PX slave都有一份全量的【集合一】。  
然后每个消费者的PX slave进程按照ROWID切分，扫描【集合二】，然后与【集合一】做关联判断，最终得出结果集。  
这里的关键是，每个消费者的PX slave都持有了全量的【集合一】，因此不需要再对集合二有任何的分发需要，只需要按照ROWID扫描然后再进行JOIN操作就能够保证结果的正确性。

集合一：  
【1，3，5，7，9，11】  
分发后为：  
set 1: 1，3，5，7，9，11 =>进程1  
set 2:1，3，5，7，9，11 =>进程2

集合二：  
【1，9，3，6，7，8，5】  
分发后为：  
set 3: 1，9，3，6 =>进程1  
set 4: 7，8，5 =>进程2  
set 1，set 3的结果集为1，3，9，set 2和set 4的结果集为5,最终的结果集为1，3，5，9，这样不但把工作量做了比较均匀的切分，而且保证了结果的正确性。  
这里我们通过一个具体的查询例子再来看一下整个过程：  
![28218939_1455546452FSwn.png][]  
表T1的数据量为70，表T4的数据量为343000。

SQL>select /*+parallel(2) pq_distribute(t1 none broadcast) full(t1) full(t4) monitor*/ count(*) from t1,t4 where t1.id = t4.id1;

![28218939_1453966188COAj.jpg][]

我们通过添加hint pq\_distribute(t1 none broadcast)强制让hash join左边的表进行了广播分发，根据SQL MONITORING的输出，我们做如下分析：

*  （行ID 9，8，7），生产者红色进程（【操作】列）按照ROWID做切分扫描表T1，然后把扫描的结果写入table queue，以广播方式做分发，ID为7的行源PX SEND BROADCAST操作代表了广播的分发方式。
 *  行ID6，5，蓝色的消费进程（【操作】列）接收到红色PX slave广播的数据，然后构建HASH TABLE。每一个蓝色的消费PX slave都接收到了全量的T1表 的数据，根据【实际行数】列可以显示这一点，表T1总共70行的数据经过广播分发后，实际产生了70\*2（并行度）=140行的记录。
 *  行ID 11，10，蓝色的消费进程按照ROWID切割扫描T4表并与前面构建的HASH TABLE做JOIN。

这里并没有对T4进行任何的分发，认识到这一点很重要，蓝色的消费进程只需要按照ROWID范围扫描即可，因为T1表的数据在每个消费者的PX slave都保持着全量。  
这里我们做一个阶段性的总结：  
对于broadcast分发方式来说：

*  HASH JOIN右边的表不用分发。
 *  BROADCAST方式， 没有结果不对的风险，因为消费者的每个PX slave持有了全部的HASH JOIN左边表的数据，每个消费者进程都持有一个完整的HASH TABLE。
 *  HASH JOIN左边表 如果小的话，分发代价不大。但是随着并行度DOP的提高或者左边表数据量的增大，分发的代价会越来越大。
 *  如果左边表小的话，BROADCAST的执行计划具有非常好的扩展性。
 *  第一组PX进程扫描HASH JOIN左边表广播给第二组PX slave,CPU，内存，竞争都会有消耗，竞争的消耗来自于第一个组的每一个进程扫描的数据都要广播给第二组的每一个进程，如下图：

![28218939_1453966219Wg7d.jpg][]

## Replicate ##

replicate代表每个并行进程都全量扫描hash join左边的表，不按照rowid做却分，由于数据是被每一个进程全量扫描的，因此不需要再对数据做分发，也就只需要一组PX slaves。

select /*+ parallel(2) */ count(*) from hash_t1 a,reptest b where a.id=b.id;

![28218939_1453966296Qk2q.jpg][]

观察操作列只有一组蓝色的PX slaves进程，这里没有涉及到数据的分发：

*  2个进程全量扫描reptest表，然后构建hash table（全量的hash table）
 *  扫描完成后，2个进程按照ROWID范围扫描hash\_t1表，由于2个进程持有了全量的reptest表的hash table，因此对于hash\_t1表不需要分发。扫描hash\_t1表过程中探测hash table。

## 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资源的消耗，相对于广播的分发方式，只有【集合一】需要做分发。  
我们来看一个具体的例子：

select /*+ parallel(3) pq_distribute(b hash hash)*/ count(*) from hash_t1 a ,hash_t2 b where a.id=b.id;

![28218939_1453966379m8zy.jpg][]

*  首先红色的生产者PX slaves按照ROWID切分并行扫描表HASH\_T1，然后依据HASH算法把记录通过table queue TQ10000分发给特定的蓝色的消费者PX slave。
 *  蓝色的消费者从table queue TQ10000接收到数据后构建HASH TABLE。
 *  上面2步操作完成后，红色的生产者PX slaves继续按照ROWID切分并行扫描表HASH\_T2，然后按照HASH算法把记录通过table queue TQ10001分发给特定的蓝色消费者PX slave，蓝色消费者PX slave从table queue TQ10001接收数据并与前面构建的HASH TABLE做JOIN。最后每个蓝色的消费者PX slave把自己聚合的结果通过table queue TQ10002发送给QC。

注意

*  【实际行数】列，记录按照HASH分发后并没有增加。
 *  对hash\_t2扫描过程，由于数据需要分发，因此会有同时2组PX slaves同时活跃。

HASH分发有着很好的扩展性，每个进程有部分的HASH 表，而不是完整的HASH表，每一行只会分发给一个特定的PX SLAVE。而不是像broadcast分发把每一行广播给每一个SLAVE。  
但是就像上面已经指出过，待对HASH JOIN左边表分发完毕后，同样对于HASH JOIN右边的表也需要进行分发，多了一次分发的代价，增加了一些CPU和内存的成本。

## HYBRID-HASH ##

错误的分发方式可能会对并行执行带来非常大的性能问题，Oracle 12C介绍自适应的并行分发方法，hybrid hash，在真正执行过程中，再决定该使用何种分发方式，Oracle 优化器要做到这一点，使用了statistics collector，它在语句运行过中统计语句的一些运行时信息，例如返回记录的数量等等。需要注意使用了HYBRID-HASH后，每次语句执行，都要通过statistics collector来动态决定使用的并行分发方式。  
![28218939_1455546575Fgal.png][]  
例如上面的执行计划，观察行源ID 7，并行执行过程中会统计结果集的返回值数量，如果返回的结果集数量小于并行度\*2，那么会使用广播方式来进行数据分发，反之则使用HASH的数据分发方式，作为回应，在行源ID 为6的分发方式确定后，行源ID 11再决定使用round-robin还是hash分发。

## 并行执行计划的阅读顺序  ##

我刚做DBA那会，一些老DBA告诉我如何看并行执行计划，那就是把PX相关的操作都统统抹去，然后再看，例如：  
![28218939_1455546692B4FQ.png][]  
真的等价吗？我们把相关的PX等操作都全部去掉，最终和串行执行的如下文本是"等价"的：  
![28218939_1455546749dKtX.png][]

在串行执行过程中，对于上面执行计划的执行顺序是这样的：  
扫描T3表，构建hash table，扫描T2表，构建hash table，扫描T1表构建hash table，最后扫描T4表，扫描到的每一个记录都要探测前面所产生的3个hash table。  
但是并行执行的执行顺序并不一定是按照上面描述的顺序，对于并行执行计划的阅读要跟随table queue的创建顺序，它代表着并行执行中数据分发的顺序。因此就上面的并行执行，执行顺序为：

*  扫描table T1，构建hash table，之所以首先扫描T1，是因为table queue的编号TQ10000 是最小的。
 *  根据table queue TQ10001的位置知道，然后扫描table T4并与上面的hash table做hash join。
 *  根据table queue TQ10002的位置知道，接着扫描table T2，构建hash table，然后，上面两步产生的结果集与这个hash table做hash join。
 *  根据table queue TQ10002的位置知道，最后扫描table T3,构建hash table，然后上面三步产生的结果集与这个hash table做hash join。

## v$pq\_tqstat ##

v$pq\_tqstat视图是非常特别的，它的内容只记录在QC进程的私有PGA中，而且只在 并行查询结束后内容才会被填充，因此如果并行执行过程中，你取消了查询，那么查询这个视图依然不会有任何结果，因为它只存在进程的PGA中，因此你不能通过另一个会话去查询它。  
可以通过它了解并行执行过程中数据是如何通过table queue分发的。举两个例子：  
![28218939_145554690411qr.png][]

例如我们对hash\_t1 以并行度4进行记录统计，执行完成后，查看v$pq\_tqstat视图：  
![28218939_145554696311Wf.png][]

4个生产者把各自扫描到的记录做了汇聚各自产生一个记录并把它写入table queue，QC通过table queue接收了这四个记录。注意NUM\_ROWS代表的是PX slaves通过table queue写入、读取的数据量，可以通过NUM\_ROWS的值非常容易看出并行进程的工作量是否均匀，是否有并行倾斜存在。

再看一个复杂点的例子：  
![28218939_14555470327h5Z.png][]

观察SQL的hint部分，强制让hash join的左边表使用了broadcast的分发方式，结合上面的输出和下面的图一起看，每个红色的生产者先按照ROWID范围扫描HASH\_T2，然后把数据通过table queue TQ10000广播给蓝色的消费者，由于采用了并行度4，因此其实每个生产者真正写入table queue中的数据量是扫描数据量的4倍（9999999 是真实的记录数，经过广播分发产生了9999999 \*4 的记录数），消费者从table queueTQ10000中接收数据后构建hash table，每个消费者PX slave都构建了表hash\_t2完整的的hash table，然后蓝色消费者开始扫描hash\_t1，并与之前构建的hash table做join，最后每个蓝色消费者把各自最终形成的预聚合结果发送给QC（这里其实已经转化了角色变为了生产者）,QC接收到4条记录。

![28218939_14539678469Gh9.jpg][]

[28218939_1453965603kWGK.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453965603kWGK.jpg
[28218939_1455546349T91e.png]: http://blog.itpub.net/attachment/201602/15/28218939_1455546349T91e.png
[28218939_1453965698AvVS.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453965698AvVS.jpg
[http_www.jianshu.com_p_ce85dd0c05ab]: http://www.jianshu.com/p/ce85dd0c05ab
[28218939_14539657982DuB.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_14539657982DuB.jpg
[28218939_14539658404G4g.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_14539658404G4g.jpg
[28218939_1455546452FSwn.png]: http://blog.itpub.net/attachment/201602/15/28218939_1455546452FSwn.png
[28218939_1453966188COAj.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453966188COAj.jpg
[28218939_1453966219Wg7d.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453966219Wg7d.jpg
[28218939_1453966296Qk2q.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453966296Qk2q.jpg
[28218939_1453966379m8zy.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_1453966379m8zy.jpg
[28218939_1455546575Fgal.png]: http://blog.itpub.net/attachment/201602/15/28218939_1455546575Fgal.png
[28218939_1455546692B4FQ.png]: http://blog.itpub.net/attachment/201602/15/28218939_1455546692B4FQ.png
[28218939_1455546749dKtX.png]: http://blog.itpub.net/attachment/201602/15/28218939_1455546749dKtX.png
[28218939_145554690411qr.png]: http://blog.itpub.net/attachment/201602/15/28218939_145554690411qr.png
[28218939_145554696311Wf.png]: http://blog.itpub.net/attachment/201602/15/28218939_145554696311Wf.png
[28218939_14555470327h5Z.png]: http://blog.itpub.net/attachment/201602/15/28218939_14555470327h5Z.png
[28218939_14539678469Gh9.jpg]: http://blog.itpub.net/attachment/201601/28/28218939_14539678469Gh9.jpg