深入理解Oracle 的并行执行.docx

深入理解Oracle 的并行执行.docx

《深入理解Oracle 的并行执行.docx》由会员分享，可在线阅读，更多相关《深入理解Oracle 的并行执行.docx（79页珍藏版）》请在冰点文库上搜索。

深入理解Oracle的并行执行

深入理解Oracle的并行执行

摘要：

Oracle并行执行是一种分而治之的方法。

执行一个sql时，分配多个并行进程同时执行数据扫描，连接以及聚合等操作，使用更多的资源，得到更快的sql响应时间。

并行执行是充分利用硬件资源，处理大量数据时的核心技术。

Oracle并行执行是一种分而治之的方法。

执行一个sql时，分配多个并行进程同时执行数据扫描，连接以及聚合等操作，使用更多的资源，得到更快的sql响应时间。

并行执行是充分利用硬件资源，处理大量数据时的核心技术。

在本文中，在一个简单的星型模型上，我会使用大量例子和sqlmonitor报告，力求以最直观简单的方式，向读者阐述并行执行的核心内容：

•Oracle并行执行为什么使用生产者——消费者模型。

•如何阅读并行执行计划。

•不同的数据分发方式分别适合什么样的场景。

•使用partitionwisejoin和并行执行的组合提高性能。

•数据倾斜会对不同的分发方式带来什么影响。

•由于生产者-‐消费者模型的限制，执行计划中可能出现阻塞点。

•布隆过滤是如何提高并行执行性能的。

•现实世界中，使用并行执行时最常见的问题。

术语说明：

S：

时间单位秒。

K：

数量单位一千。

M：

 数量单位一百万， 或者时间单位分钟。

DoP：

DegreeofParallelism，并行执行的并行度。

QC：

并行查询的QueryCoordinator。

PX进程：

ParallelExecutionSlaves。

AAS：

Averageactivesession，并行执行时平均的活动会话数。

分发：

pqdistributionmethod，并行执行的分发方式，包括replicate，broadcast，hash和adaptive分发等4种方式，其中adaptive分发是12c引入的的新特性，我将在本篇文章中一一阐述。

Hashjoin的左边：

驱动表，thebuildsideofhashjoin，一般为小表。

Hashjoin的右边：

被驱动表，theprobesideofhashjoin，一般为大表。

布隆过滤：

bloomfilter，一种内存数据结构，用于判断一个元素是否属于一个集合。

测试环境和数据

Oracle版本为12.1.0.2.2，两个节点的RAC，硬件为ExadataX3-‐8。

这是一个典型的星型模型，事实表lineorder有3亿行记录，维度表part/customer分别包含1.2M

和1.5M行记录，3个表都没有进行分区，lineorder大小接近30GB。

[js]viewplaincopyprint?

select  

owner seg_owner，  

segment_name seg_segment_name， round（bytes/1048576，2） SEG_MB  

from  

dba_segments where  

owner = 'SID'  

and segment_name in （'LINEORDER'，'PART'，'CUSTOMER'）  

/  

OWNER   SEGMENT_NAME SEGMENT_TYPE   SEG_MB  

------ ------------ ------------ -------- SID   LINEORDER   TABLE   30407.75  

SID CUSTOMER    TABLE   168  

SID PART    TABLE   120  

select

ownerseg_owner，

segment_nameseg_segment_name，round（bytes/1048576，2）SEG_MB

from

dba_segmentswhere

owner='SID'

andsegment_namein（'LINEORDER'，'PART'，'CUSTOMER'）

/

OWNERSEGMENT_NAMESEGMENT_TYPESEG_MB

--------------------------------------SIDLINEORDERTABLE30407.75

SIDCUSTOMERTABLE168

SIDPARTTABLE120

本篇文章所有的测试，除非特别的说明，我关闭了12c的adaptiveplan特性，参数optimizer_adaptive_features被默认设置为false。

Adaptive相关的特性如cardinalityfeedback，adaptivedistributionmethod，adaptivejoin都不会启用。

如果检查执行计划的outline数据，你会发现7个优化器相关的隐含参数被设置为关闭状态。

事实上，12c优化器因为引入adaptiveplan特性，比以往版本复杂得多，剖析12c的优化器的各种新特性，我觉得非常具有挑战性，或许我会在另一篇文章里尝试一下。

[js]viewplaincopyprint?

select * from table（dbms_xplan.display_cursor（'77457qc9a324k',0,’outline’））;  

...  

Outline Data  

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

/*+ 

BEGIN_OUTLINE_DATA 

IGNORE_OPTIM_EMBEDDED_HINTS 

OPTIMIZER_FEATURES_ENABLE（'12.1.0.2'） 

DB_VERSION（'12.1.0.2'） 

OPT_PARAM（'_optimizer_use_feedback' 'false'） 

OPT_PARAM（'_px_adaptive_dist_method' 'off'） 

OPT_PARAM（'_optimizer_dsdir_usage_control' 0） 

OPT_PARAM（'_optimizer_adaptive_plans' 'false'） 

OPT_PARAM（'_optimizer_strans_adaptive_pruning' 'false'） 

OPT_PARAM（'_optimizer_gather_feedback' 'false'） 

OPT_PARAM（'_optimizer_nlj_hj_adaptive_join' 'false'） 

OPT_PARAM（'optimizer_dynamic_sampling' 11） 

ALL_ROWS 

…… 

END_OUTLINE_DATA 

*/  

select*fromtable（dbms_xplan.display_cursor（'77457qc9a324k',0,’outline’））;

...

OutlineData

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE（'12.1.0.2'）

DB_VERSION（'12.1.0.2'）

OPT_PARAM（'_optimizer_use_feedback''false'）

OPT_PARAM（'_px_adaptive_dist_method''off'）

OPT_PARAM（'_optimizer_dsdir_usage_control'0）

OPT_PARAM（'_optimizer_adaptive_plans''false'）

OPT_PARAM（'_optimizer_strans_adaptive_pruning''false'）

OPT_PARAM（'_optimizer_gather_feedback''false'）

OPT_PARAM（'_optimizer_nlj_hj_adaptive_join''false'）

OPT_PARAM（'optimizer_dynamic_sampling'11）

ALL_ROWS

……

END_OUTLINE_DATA

*/

并行初体验

串行执行

以下sql对customers和lineorder连接之后，计算所有订单的全部利润。

串行执行时不使用parallelhint：

[js]viewplaincopyprint?

select /*+ monitor */  

sum（lo_revenue）  

from  

lineorder, customer  

where  

lo_custkey = c_custkey;  

select/*+monitor*/

sum（lo_revenue）

from

lineorder,customer

where

lo_custkey=c_custkey;

串行执行时，sql执行时间为1.5分钟，dbtime为1.5分钟。

执行计划有5行，一个用户进程工作完成了对customer，lineorder两个表的扫描，hashjoin，聚合以及返回数据的所有操作。

此时AAS（averageactivesessions）为1，sql执行时间等于dbtime。

几乎所有的dbtime都为dbcpu，72%的cpu花在了第二行的hashjoin操作。

因为测试机器为一台ExadataX3——8，30GB的IO请求在一秒之内处理完成。

CelloffloadEfficiency等于87%意味着经过存储节点扫描，过滤不需要的列，最终返回计算节点的数据大小只有30GB的13%。

并行执行

使用hintparallel（4），指定DoP=4并行执行同样的sql：

[js]viewplaincopyprint?

select /*+ monitor parallel（4）*/  

sum（lo_revenue）  

from  

lineorder, customer  

where  

lo_custkey = c_custkey;  

select/*+monitorparallel（4）*/

sum（lo_revenue）

from

lineorder,customer

where

lo_custkey=c_custkey;

SQL执行时间为21s，dbtime为1.4分钟。

DoP=4，在两个实例上执行。

执行计划从5行增加为9行，从下往上分别多了’PXBLOCKITERATOR’，‘SORTAGGREGATE’，‘PXSENDQC（RANDOM）’和’PXCOORDINATOR’这四个操作。

其中3到8行的操作为并行处理，sql的执行顺序为：

每个PX进程扫描维度表customer（第6行），以数据块地址区间作为单位（第7行）扫描四分之一的事实表lineorder（第8行），接着进行hashjoin（第5行），然后对连接之后的数据做预先聚合（第4行），最后把结果给QC（第三行）。

QC接收数据（第2行）之后，做进一步的汇总（第1行），最后返回数据（第0行）。

SQL执行时间比原来快了4倍，因为最消耗时间的操作，比如对lineorder的全表扫描，hashjoin和聚合，我们使用4个进程并行处理，因此最终sql执行时间为串行执行的1/4。

另一方面，dbtime并没有明显下降，并行时1.4m，串行时为1.5m，从系统的角度看，两次执行消耗的系统资源是一样的。

DoP=4时，因为没有涉及数据的分发（distribution），QC只需分配一组PX进程，四个PX进程分别为实例1和2的p000/p0001。

我们可以从系统上查看这4个PX进程。

每个PX进程消耗大致一样的dbtime，CPU和IO资源。

AAS=4，这是最理想的情况，每个PX进程完成同样的工作量，一直保持活跃。

没有串行点，没有并行执行倾斜。

AAS=4，查看活动信息时，为了更好的展示活动信息，注意点掉”CPUCores”这个复选框。

在Linux系统上显示这四个PX进程。

[js]viewplaincopyprint?

[oracle@exa01db01 sidney]$ ps -ef | egrep "p00[01]_SSB"  

oracle 20888 1 4 2014 ?

 18:

50:

59 ora_p000_SSB1  

oracle 20892 1 4 2014 ?

 19:

01:

29 ora_p001_SSB1  

[oracle@exa01db01 sidney]$ ssh exa01db02 'ps -ef | egrep "p00[01]_SSB"'  

oracle 56910 1 4 2014 ?

 19:

01:

03 ora_p000_SSB2  

oracle 56912 1 4 2014 ?

 18:

53:

30 ora_p001_SSB2  

[oracle@exa01db01sidney]$ps-ef|egrep"p00[01]_SSB"

oracle20888142014?

18:

50:

59ora_p000_SSB1

oracle20892142014?

19:

01:

29ora_p001_SSB1

[oracle@exa01db01sidney]$sshexa01db02'ps-ef|egrep"p00[01]_SSB"'

oracle56910142014?

19:

01:

03ora_p000_SSB2

oracle56912142014?

18:

53:

30ora_p001_SSB2

小结

本节的例子中，DoP=4，并行执行时分配了4个PX进程，带来4倍的性能提升。

SQLmonitor报告包含了并行执行的总体信息和各种细节，比如QC，DoP，并行执行所在的实例，每个PX进程消耗的资源，以及执行SQL时AAS。

生产者-消费者模型

在上面并行执行的例子中，每个px进程都会扫描一遍维度表customer，然后扫描事实表lineorder进行hashjoin。

这时没有数据需要进行分发，只需要分配一组px进程。

这种replicate维度表的行为，是12c的新特性，由参数_px_replication_enabled控制。

更常见情况是并行执行时，QC需要分配两组PX进程，互为生产者和消费者协同工作，完成并行执行计划。

架构图1如下：

Broadcast分发，一次数据分发

为了举例说明两组px进程如何协作的，设置_px_replication_enabled为false。

QC会分配两组PX进程，一组为生产者，一组为消费者。

见下图，此时sql执行时间为23s，执行时间变慢了2s，dbtime仍为1.5分钟。

最大的变化来自执行计划，现在执行计划有12行。

增加了对customer的并行扫描PXBLOCKITERATOR（第8行），分发’PXSENDBROADCAST’和接收’PXRECEIVE’。

执行计划中出现了两组PX进程，除了之前蓝色的多人标志，现在出现了红色的多人标志。

此时，SQL的执行顺序为：

4个红色的PX进程扮演生产者角色，扫描维度表customer，把数据通过broadcast的方式分发给每一个扮演消费者的蓝色PX进程。

因为DoP=4，每一条被扫描出来的记录被复制了4份，从sqlmonitor的第9行，customer全表扫描返回1。

5m行数据，第8行的分发和第7行的接受之时，变成了6m行记录，每个作为消费者的蓝色px进程都持有了一份完整包含所有custome记录的数据，并准备好第5行hashjoin的buildtable。

4个作为消费者的蓝色PX进程，以数据块地址区间为单位扫描事实表lineorder（第10/11行）;同时和已经持有的customer表的数据进hashjoin（第5行），然后对满足join条件的数据做预聚合（第4行），因为我们查询的目标是对所有lo_revenue求和，聚合之后每个PX进程只需输出一个总数。

4个蓝色的PX进程反过来作为生产者，把聚合的数据发给消费者QC（第3行和第2行）。

由QC对接收到4行记录做最后的聚合，然后返回给用户。

使用broadcast的分发方式，只需要把customer的数据广播给每个消费者。

Lineorder的数不需要重新分发。

因为lineorder的数据量比customer大的多，应该避免对lineorder的数据进行分发，这种执行计划非常适合星型模型的数据。

观察sqlmonitor报告中Parallel标签下的信息，红色的PX进程为实例1、2上的p002/p003进程，蓝色的PX进程为p000/p001进程，因为蓝色的PX进程负责扫描事实表lineorder，hashjoin和聚合，所以消耗几乎所有的dbtime。

生产者-消费者模型工作原理

并行查询之后，可以通过视图V$PQ_TQSTAT，验证以上描述的执行过程。

实例1、2上的p002/p003进程作为生产者，几乎平均扫描customer的1/4记录，把每一条记录广播给4个消费者PX进程，发送的记录数之和为6m行。

通过tablequeue0（TQ_ID=0），每个作为消费者的p000/p001进程，接收了完整的1。

5m行customer记录，接收的记录数之和为6m行。

实例1、2上的p000/p0001进程作为生产者，通过tablequeue1（TQ_ID=1），把聚合的一条结果记录发给作为消费者的QC。

QC作为消费者，接收了4行记录。

[js]viewplaincopyprint?

SELECT  

dfo_number, tq_id, server_type, instance, process, num_rows  

FROM  

V$PQ_TQSTAT  

ORDER BY  

dfo_number DESC, tq_id, server_type desc, instance, process;  

DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS  

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

1 0 Producer 1 P002 1461932  

1 0 Producer 1 P003 1501892  

1 0 Producer 2 P002 1575712  

1 0 Producer 2 P003 1460464  

1 0 Consumer 1 P000 1500000  

1 0 Consumer 1 P001 1500000  

1 0 Consumer 2 P000 1500000  

1 0 Consumer 2 P001 1500000  

1 1 Producer 1 P000 1  

1 1 Producer 1 P001 1  

1 1 Producer 2 P000 1  

1 1 Producer 2 P001 1  

1 1 Consumer 1 QC 4  

13 rows selected.  

SELECT

dfo_number,tq_id,server_type,instance,process,num_rows

FROM

V$PQ_TQSTAT

ORDERBY

dfo_numberDESC,tq_id,server_typedesc,instance,process;

DFO_NUMBERTQ_IDSERVER_TYPEINSTANCEPROCESSNUM_ROWS

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

10Producer1P0021461932

10Producer1P0031501892

10Producer2P0021575712

10Producer2P0031460464

10Consumer1P0001500000

10Consumer1P0011500000

10Consumer2P0001500000

10Consumer2P0011500000

11Producer1P0001

11Producer1P0011

11Producer2P0001

11Producer2P0011

11Consumer1QC4

13rowsselected.

那么，以上的输出中，DFO_NUMBER和TQ_ID这两列表示什么意思呢?

DFO代表DataFlowOperator，是执行计划中可以并行执行的操作。

一个QC代表一棵DFO树（tree），包含多个DFO;同一个QC中所有并行操作的DFO_NUMBER是相同的，此例中，所有DFO_NUMBER为1。

执行计划包含多个QC的例子也不少见，比如使用unionall的语句，unionall每个分支都是独立的DFO树，不同的DFO树之间可以并行执行。

本篇文章仅讨论执行计划只有一个QC的情况。

TQ代表tablequeue，用以PX进程之间或者和QC通信连接。

以上执行计划中，tablequeue0为PX进程之间的连接，tablequeue1为PX进程和QC之间的连接。

生产者通过tablequeue分发数据，消费者从tablequeue接收数据。

不同的tablequeue编号，代表了不同的数据分发。

通过tablequeue，我们可以理解Oracle并行执行使用生产者-‐消费者模型的本质：

同一棵DFO树中，最多只有两组PX进程。

每个生产者进程都存在一个和每个消费者进程的连接，每个PX进程和QC都存在一个连接。

假设DoP=n，连接总数为（n*n+2*n），随着n的增长，连接总数会爆炸型增长。

Oracle并行执行设计时，采用生产者和消费者模型，考虑到连接数的复杂度，每个DFO最多只分配两组PX进程。

假设DoP=100时，两组PX进程之间的连接总数为10000。

假设可以分配三组PX进程一起完成并行执行计划，那么三组PX之间连接总数会等于1百万，维护这么多连接，是一个不可能的任务。

同一棵DFO树中，两组PX进程之间，同一时间只存在一个活跃的数据分发。

如果执行路径很长，数据需要多次分发，两组PX进程会变换生产者消费者角色，相互协作，完成所有并行操作。

每次数据分发，对应的tablequeue的编号不同。

一个活跃的数据分发过程，需要两组PX进程都参与，一组为生产者发送数据，一组为消费者接收数据。

因为一个DFO里最多只有两组PX进程，意味着，PX进程之间，同一时间只能有一