Oracle并行执行特性应用初探

叁歲伎倆 2022-07-17 00:15 223阅读 0赞

## 出于对作者的尊重和感谢，原文地址为http://www.cnblogs.com/zyk/p/3719600.html ##

##  ##

1.  **1.      序**

在历史数据转出测试过程中，通过不断的优化，包括SQL调整和数据库调整，从AWR中看到，基本上难以进行更多的性能提升，于是准备试试并行执行的特性，从这个任务的特点来分析，也比较适合采用这项技术。本文介绍了使用并行执行过程中的常用SQL，以及遇到的一些问题，以及性能对比试验的结果，并且分享了一些问题的解决经验，分析了适合并行执行的场景。

1.  **2.      概念及常用语法**

并行执行通过充分利用硬件资源来实现特定任务的性能提升，将一个SQL语句同时分布到多个CPU上去执行，从而缩短总的耗时。

Oracle的并行执行包括：

1)        **并行查询**

2)        **并行DML**(insert,delete,update)

3)        **并行DDL**(表和索引的创建)。

为了方便参考使用，下面将收集的常用语句按并行执行的三个级别进行介绍：

l  **对象级**

设置表和索引的并行度，从而使用涉及这些对象的SQL操作按设定的并行度执行。

例如：alter table 门诊费用记录 **parallel** 8;

alter index 门诊费用记录\_IX\_登记时间 **parallel**;

如果不指定并行度的值，Oracle会根据参数和CPU数来估算一个缺省值。

禁用并行度(指定并行度为1或使用noparallel)：

alter table 门诊费用记录 **parallel** 1;

alter index 门诊费用记录\_IX\_登记时间 **noparallel**;

如果不改变对象的并行度属性，唯一可以禁用并行查询的方法是将初始化参数parallel\_max\_servers设置为0。

l  **会话级**

人工启用和禁用的语法如下：

ALTER Session ENABLE PARALLEL query\[|DML|DDL\];

ALTER Session DISABLE PARALLEL query\[|DML|DDL\];

强制按指定的并行度执行。

ALTER Session FORCE PARALLEL QUERY PARALLEL 8;

ALTER Session FORCE PARALLEL DML PARALLEL 8;

ALTER Session FORCE PARALLEL DDL PARALLEL 8;

强制并行对于递归SQL不起作用，但覆盖表或索引上定义的并行度。

查询当前会话是否启用了并行执行：

（Oracle 10.2.0.1上，缺省启用了并行QUERY和DDL，没有启用DML）

SQL> SELECT pq\_status ,pdml\_status, pddl\_status

FROM v$session WHERE sid=sys\_context('userenv','sid');

PQ\_STATUS PDML\_STATUS PDDL\_STATUS

\--------- ----------- -----------

ENABLED   DISABLED    ENABLED

l  **语句级**

通过在SQL中添加提示来指定并行执行及并行度。

优化器只是按指定的提示来考虑是否使用并行执行，并不会强制使用（它会选择成本最低的那一个执行计划）

例如：

Select /\*+ **parallel**(t1,8)\*/count(\*) from 门诊费用记录 t1;

Create table 医嘱执行时间 **parallel** 8 as

select /\*+ **parallel**(t1,8)\*/\* from医嘱执行时间 t1;

alter index 门诊费用记录\_IX\_登记时间 rebuild **parallel** 8;

**注意**：

并行DML需要先在会话级显示的启用，并且需要同时启用并行查询；

例：

SQL>ALTER Session ENABLE PARALLEL DML;

SQL> ALTER Session ENABLE PARALLEL QUERY;

SQL> Update /\*+ **parallel**(t1,8)\*/ 门诊费用记录 t1

Set 待转出 = 132

Where 结帐id In

(Select  /\*+ **parallel**(t2,8)\*/结帐id From 病人预交记录 t2 Where 待转出 = 132);

如果不提交事务，该会话的后续SQL无法访问被修改的表，查询未提交事务的表，将会返回错误：”ora-12838:无法读取、修改一个被并行修改过的表”

定义主键约束时，无法并行的自动创建主键索引，但可以采取以下变通方式：

CREATE UNIQUE INDEX 检验标本记录\_UQ\_标本序号

ON 检验标本记录(核收时间, 仪器ID, 标本序号, 标本类别) PARALLEL 8;

ALTER TABLE 检验标本记录 ADD CONSTRAINT

检验标本记录\_UQ\_标本序号 Unique (核收时间, 仪器ID, 标本序号, 标本类别);

这种方式创建的主键约束与自动创建的有一个差别，就是删除主键的时候，不会自动删除对应的索引，需要增加删除索引的语法，例：

ALTER TABLE 检验标本记录 drop

CONSTRAINT 检验标本记录\_UQ\_标本序号 cascade drop index;

**另外**，关于并行查询的参数配置，大部分情况下，无须调整，网上的资料比较多，这里不再一一列举。仅说明一个参数：

当执行并行重建索引时，可能会遇到ora-00600错误，通过修改参数parallel\_execution\_message\_size可解决这个问题，例：

SQL> alter system set parallel\_execution\_message\_size=8192 scope=spfile;

缺省值为2148，对于一般的并行任务，这个值太小。

修改后需重启数据库。

1.  **3.      使用效果**

l  **并行查询及并行DML**

由于所使用的历史数据转出，大部分查询均是索引范围扫描，没有全表扫描，所以，不适合并行执行。

但是，由于一次意外操作：索引压缩重建（并行DDL），执行后导致索引的属性自动加上了并行度，导致相关的SQL查询自动启用了并行查询，结果，执行计划采用了大表全表扫描，采用hash连接或嵌套连接，导致查询异常缓慢。

一些复杂的SQL执行超过了一个小时，甚至下面这种简单的SQL执行超过了**5个小时**仍然没有返回结果：

Update /\*+ rule\*/ 病人医嘱计价

Set 待转出 = n\_批次

Where 医嘱id In (Select ID From 病人医嘱记录 Where 待转出 = n\_批次);

查看执行计划，发现采用了全表扫描+嵌套连接索引的方式。

并且，提示字rule失效，优化器模式变成了CBO。最后，取消了索引的并行度，然后，重建索引，禁用了压缩特性，最终执行计划才恢复了正常。

因为普通的索引范围扫描并不能使用并行查询（除非是分区索引），所以，优化器会选择全表扫描方式，但是很多时候这并不是我们所期望的访问方式。

所以，并行查询和并行DML一定要慎重，否则，性能差别非常大。

l  **直接路径插入**

**      ** 为了快速的加载大量数据，采用直接路径插入方式可以大幅提升插入性能。

直接路径插入自动对insert 和Select操作采用了并行执行，并且目标表采用nologging最小日志模式的话，试验表明，最高可减少5倍的耗时。

例：

Insert Into /\*+ append\*/H住院费用记录(ID, 记录性质, NO, ……)

Select ID, 记录性质, NO, ……  From 住院费用记录  Where 待转出 = 132;

** **

l  **并行DDL**

历史数据转出过程中，需要重建转出表上的查询所用到的索引，以便及时回收空间，加快查询速度，但这个索引重建的过程非常耗时。测试环境的配置为：32G内存、32路CPU(4\*8)、Raid10的SCSI硬盘。

**测试结果表明**：

1.  并行DDL要**快50%**以上。

没有并行时，重建索引需要36分钟，并行执行只需要16分钟。

1.  并不是并行度越高就越快。

因为磁盘IO所限，该环境下并行度为8时最快

l  **并行收集统计信息**

收集对象统计信息的时候，有一个参数可以指定并行度，并行的效果非常明显。试验表明，收集ZLHIS的所有对象，并行执行可以将整个时间由1个半小时缩短到30分钟左右。

1.  **4.      小结**

并行执行属于Oracle的OLAP应用特性之一，如果你有一些耗时很长的任务，并且服务器有大量的闲置资源（CPU,内存,IO带宽），则比较适合采用并行执行技术，它可以给你带来成倍的性能提升效果，否则，并行执行可能会由于大量的消耗资源从而影响其他人的正常使用。所以，并行执行一般情况，较少的在OLTP场合中应用，因为通常我们并不希望某一个人占用过多的系统资源。

1.  **5.      参考资料**

1)      Oracle 性能诊断艺术.Christian Antognini.2009

2)      Oracle 性能优化求生指南.Guy Harrison .2012

1.  **6.      附：索引重建并行对比测试**

\--无并行

22:26:19 SQL> exec Zl1\_Datamove\_Reb(100, 1, 6);

PL/SQL procedure successfully completed

Executed in 2199.734 seconds

\--------------------------------------------------------------

SQL> ALTER session FORCE PARALLEL DDL PARALLEL 24;

SQL> exec Zl1\_Datamove\_Reb(100, 1, 6);

PL/SQL procedure successfully completed