深入理解Oracle的并行执行

深入理解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?selectownerseg_owner，segment_nameseg_segment_name，round(bytes/1048576，2)SEG_MBfromdba_segmentswhereowner='SID'andsegment_namein('LINEORDER'，'PART'，'CUSTOMER')/OWNERSEGMENT_NAMESEGMENT_TYPESEG_MB--------------------------------------SIDLINEORDERTABLE30407.75SIDCUSTOMERTABLE168SIDPARTTABLE120selectownerseg_owner，segment_nameseg_segment_name，round(bytes/1048576，2)SEG_MBfromdba_segmentswhereowner='SID'andsegment_namein('LINEORDER'，'PART'，'CUSTOMER')/OWNERSEGMENT_NAMESEGMENT_TYPESEG_MB--------------------------------------SIDLINEORDERTABLE30407.75SIDCUSTOMERTABLE168SIDPARTTABLE120本篇文章所有的测试，除非特别的说明，我关闭了12c的adaptiveplan特性，参数optimizer_adaptive_features被默认设置为false。Adaptive相关的特性如cardinalityfeedback，adaptivedistributionmethod，adaptivejoin都不会启用。如果检查执行计划的outline数据，你会发现7个优化器相关的隐含参数被设置为关闭状态。事实上，12c优化器因为引入adaptiveplan特性，比以往版本复杂得多，剖析12c的优化器的各种新特性，我觉得非常具有挑战性，或许我会在另一篇文章里尝试一下。[js]viewplaincopyprint?select*fromtable(dbms_xplan.display_cursor('77457qc9a324k',0,’outline’));...OutlineData-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_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_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_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)fromlineorder,customerwherelo_custkey=c_custkey;select/*+monitor*/sum(lo_revenue)fromlineorder,customerwherelo_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/*+monitorparallel(4)*/sum(lo_revenue)fromlineorder,customerwherelo_custkey=c_custkey;select/*+monitorparallel(4)*/sum(lo_revenue)fromlineorder,customerwherelo_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@exa01db01sidney]$ps-ef|egrepp00[01]_SSBoracle20888142014?18:50:59ora_p000_SSB1oracle20892142014?19:01:29ora_p001_SSB1[oracle@exa01db01sidney]$sshexa01db02'ps-ef|egrepp00[01]_SSB'oracle56910142014?19:01:03ora_p000_SSB2oracle56912142014?18:53:30ora_p001_SSB2[oracle@exa01db01sidney]$ps-ef|egrep