oracle like执行效率低用什么方法_[O记]Oracle并行执行面面观

68 阅读 0 评论 45 点赞

我是靠谱客的博主标致唇膏，最近开发中收集的这篇文章主要介绍oracle like执行效率低用什么方法_[O记]Oracle并行执行面面观，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

并行的概念

Oracle并行执行的本质是以额外的硬件资源消耗来换取更短的执行时间，一种以空间换取时间的做法。所谓的并行有上述两种模式，各有优缺点，但是Oracle的并行虽然采用的是Shared Everything架构，其实上可以达到其他Shared Nothing的效率,又可以避免Shared Nothing架构所带来的固有的预先分区，并行度固定的问题。

Oracle并行的分类

并行查询

并行DML

并行数据加载

并行备份恢复

并行DDL

并行收集统计信息

Oracle并行执行的相关组件

Query Coordinator——QC是整个并行执行协调者的角色，负责对接用户进程，解析用户的SQL，并将最终结果反馈给用户进程；
Query Slave——QS是由QC启动的各个子进程，负责并行执行子任务，将处理结果返回给QC，我们通常说的并行度是指一组QS Set里并行子进程QS的数量
Query Slave Set——QS会进行分组，每一组里各个QS都做相同的工作，这样一组QS成为QS Set，Oracle一个并行SQL最多会启动两个QS Set
TQ——Oracle用TQ来实现QS set之间以及QS Set和QC之间数据的传递和重新分布

并行执行数据传递方法

可以从执行计划的PQ Distrib列看出组件之间是以何种方式进行数据传递

哈希(HASH)——适合大表之间的连接
广播(Broadcast)——广播时，Oracle会根据QS Set的子进程个数来决定需要把生产者的表以复制的方式传多少份，如果一个大表被广播了，那将是灾难，会导致临时表空间暴涨，如果是表很小，以广播的方式，效率会比较高
分区(Partition ,包含Partition-Wise Join 和 Partial Partition-Wise Join)——PWJ主要发生在连接的两个表都必须是分区表，而且分区个数必须相等，而对于PPWJ，参与连接的两个表只要有一个是分区表就可以，对应的PQ Distrib为“PARTITION (KEY)”
排序(Order)——各个子进程并发扫描数据，以RANGE方式并发传递给下一个QS Set，然后再自己的范围内局部并发排序，再将结果以有序的方式并发传递给QC
随机(Random)——随机传递时Oracle并行体系中最简单的数据传递方式，一般出现在并行执行的最后阶段，即当QS Set的并行子进程将他们读取的/处理完的结果并发传给QC的时候
循环(Round Robin)——RR是以除QC以外的单个服务器端进程循环的向一组QS Set的并发子进程传递数据

数据传递方向(IN-OUT)

P->P: Parallel_To_Parallel,表示数据通过TQ从一组QS Set传送到另外一组QS Set
P->S: Parallel_To_Serial,表示数据通过TQ从一组QS Set传送到串行执行进程(一般是QC)
PCWP: Parallel_Combined_With_Parent,表示PCWP所在的执行步骤的输出会流转到下一个执行步骤，且PCWP所在执行步骤和下一个执行步骤处于同一个QS Set中，不存在QS Set间的数据交互
PCWC: Parallel_Combined_With_Child,表示PCWC所在的执行步骤的输入来源于上一个执行步骤，且PCWC所在的执行步骤和上一个执行步骤处于同一个QS Set中，不存在QS Set间的数据交互
S->P: Serial_To_Parallel,表示除QC外的单个服务器进程以Round Robin方式向一组QS Set的并发子进程传送数据(eg: insert /*+ parallel(t2) */) into t2 select * from t1)

什么叫granules

粒度 granule(单个并行子进程单次执行所需要执行的工作量)

单个分区整个划分为一个granule，执行计划的Operation列会显示为 PX BLOCK ITERATOR
目标对象的数据块范围划分granule，执行计划的Operation列会显示为 PX PARTITION LIST ALL/PX PARTITION HASH ALL

并行执行适用的场景

并行体系中，QS Set里的并行子进程并发扫描目标对象的数据块时，采用的是绕开Buffer Cache，从数据文件中直接读取(Direct Reads)数据块的方式，适用于全表扫描和全索引扫描的执行计划。

如果一个表的数据块的总数小于_SMALL_TABLE_THRESHOLD(单位是数据块数)，则不会直接读取。

如何开启并行

1. 指定表上SQL默认开启并行

alter table table_name parallel;

alter table table_name parallel n;

2. 使用hint指定SQL并行

/*+ parallel(table_name) */

/*+ no_parallel(table_name) */

/*+ parallel_index(table_name[,index_name[,degree]] */

/*+ no_parallel_index(table_name[,index_name] */

/*+ pq_distribute(table_name,out,in) */

3. 设置session并行执行

alter session force parallel query;

alter session enable parallel query;

alter session force parallel dml;

Oracle并行度的控制

DOP是数据库里面的并行度,它是指执行时一组QS Set中并行子进程的数量，因为Oracle最多会有两组QS Set,所以实际并行执行时并行子进程的总数可能会是并行度的两倍。

默认并行度=parallel_threads_per_cpu * cpu_count‍

parallel_degree_policy参数控制了自动并行的行为，默认为MANUAL，表示没有开启，LIMITED表示有限开启，AUTO表示完全开启；当自动并行开启后，如果预估SQL执行耗时小于parallel_min_time_threshold(默认为AUTO，值为10s)时，不会启动并行，如果预估SQL执行耗时大于parallel_min_time_threshold的值，则会启动自动并行，并行度=MIN(理想并行度，parallel_degree_limit)。