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

• 并行的概念    

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

• Oracle并行的分类

    并行查询

    并行DML

    并行数据加载

    并行备份恢复

    并行DDL

    并行收集统计信息

• Oracle并行执行的相关组件

1. Query Coordinator——QC是整个并行执行协调者的角色，负责对接用户进程，解析用户的SQL，并将最终结果反馈给用户进程；

2. Query Slave——QS是由QC启动的各个子进程，负责并行执行子任务，将处理结果返回给QC，我们通常说的并行度是指一组QS Set里并行子进程QS的数量

3. Query Slave Set——QS会进行分组，每一组里各个QS都做相同的工作，这样一组QS成为QS Set，Oracle一个并行SQL最多会启动两个QS Set

4. TQ——Oracle用TQ来实现QS set之间以及QS Set和QC之间数据的传递和重新分布

• 并行执行数据传递方法

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

1. 哈希(HASH)——适合大表之间的连接

2. 广播(Broadcast)——广播时，Oracle会根据QS Set的子进程个数来决定需要把生产者的表以复制的方式传多少份，如果一个大表被广播了，那将是灾难，会导致临时表空间暴涨，如果是表很小，以广播的方式，效率会比较高

3. 分区(Partition ,包含Partition-Wise Join 和 Partial Partition-Wise Join)——PWJ主要发生在连接的两个表都必须是分区表，而且分区个数必须相等，而对于PPWJ，参与连接的两个表只要有一个是分区表就可以，对应的PQ Distrib为“PARTITION (KEY)”

4. 排序(Order)——各个子进程并发扫描数据，以RANGE方式并发传递给下一个QS Set，然后再自己的范围内局部并发排序，再将结果以有序的方式并发传递给QC

   

5. 随机(Random)——随机传递时Oracle并行体系中最简单的数据传递方式，一般出现在并行执行的最后阶段，即当QS Set的并行子进程将他们读取的/处理完的结果并发传给QC的时候

6. 循环(Round Robin)——RR是以除QC以外的单个服务器端进程循环的向一组QS Set的并发子进程传递数据

• 数据传递方向(IN-OUT)

1. P->P: Parallel_To_Parallel,表示数据通过TQ从一组QS Set传送到另外一组QS Set

2. P->S: Parallel_To_Serial,表示数据通过TQ从一组QS Set传送到串行执行进程(一般是QC)

3. PCWP: Parallel_Combined_With_Parent,表示PCWP所在的执行步骤的输出会流转到下一个执行步骤，且PCWP所在执行步骤和下一个执行步骤处于同一个QS Set中，不存在QS Set间的数据交互

4. PCWC: Parallel_Combined_With_Child,表示PCWC所在的执行步骤的输入来源于上一个执行步骤，且PCWC所在的执行步骤和上一个执行步骤处于同一个QS Set中，不存在QS Set间的数据交互

5. S->P: Serial_To_Parallel,表示除QC外的单个服务器进程以Round Robin方式向一组QS Set的并发子进程传送数据(eg: insert /*+ parallel(t2) */) into t2 select * from t1)

• 什么叫granules

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

1. 单个分区整个划分为一个granule，执行计划的Operation列会显示为 PX BLOCK ITERATOR

2. 目标对象的数据块范围划分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)。