大家好，我是 JiekeXu,江湖人称“强哥”，荣获 Oracle ACE Pro 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、KCP 等众多国产数据库认证证书,今天和大家一起来看看八千字带你了解 Oracle 并行那些事(一),欢迎点击最上方蓝字“JiekeXu DBA之路”关注我的微信公众号，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！

目  录 

一）并行执行的工作原理

    SQL 语句的并行执行过程

    生产者/消费者模型

    并行粒度

    生产者和消费者之间的分配方式

    并行执行服务器如何通信

二）并行的相关概念

    并行执行相关视图

    并行 DML 的限制

三）并行查询

四）并行DML

    默认并行度

    LOB 列的并行

五）并行创建表

六）并行创建索引

七）并行收集统计信息

八）rman 备份的并行

九）23ai 并行新特性

前  言

Oracle 并行是指在执行一个 SQL 语句时，通过分配多个进程同时执行数据的扫描、聚合等操作，在此过程中也会使用更多的 CPU、IO 等资源，使其 SQL 的响应时间更快。并行执行是充分利用硬件资源，以硬件资源换时间，处理大量数据时的核心技术。

在我们实际工作中，由于使用不当，并行操作无法并行，或者并行执行计划效率低下，没有获得期望的性能提升。了解 Oracle 的并行操作，有利于我们更好更快的完成工作。

一）并行执行的工作原理

并行执行将任务分解，这样一来，不是一个进程完成查询中的所有工作，而是多个进程同时完成部分工作。

SQL 语句的并行执行过程

每个 SQL 语句在解析时都会经历优化和并行化过程。

如果确定语句要并行执行，则执行计划中将发生以下步骤：

1.用户会话或影子进程充当协调器的角色，通常称为查询协调器 (QC) 或并行执行 (PX) 协调器。QC 是启动并行 SQL 语句的会话。

2.PX 协调器获取必要数量的进程，称为并行执行 (PX) 服务器。PX 服务器是代表启动会话并行执行工作的单个进程。

3.SQL 语句作为一系列操作执行，例如全表扫描或 ORDER BY 子句。如果可能，每个操作都并行执行。

4.当 PX 服务器完成语句执行后，PX 协调器将执行无法并行执行的任何工作部分。例如，带有 SUM() 运算的并行查询需要将每个 PX 服务器计算出的单独小计相加。

5.最后，PX 协调器将结果返回给用户。

生产者/消费者模型

并行执行使用生产者/消费者模型。

并行执行计划以一系列生产者/消费者操作的形式执行。为后续操作生成数据的并行执行 (PX) 服务器称为生产者，需要其他操作输出的 PX 服务器称为消费者。每个生产者或消费者并行操作由一组称为 PX 服务器集的 PX 服务器执行。PX 服务器集中的 PX 服务器数量称为并行度 (DOP)。PX 服务器集的基本工作单元称为数据流操作 (DFO)。

PX 协调器可以具有多级生产者/消费者操作（多个 DFO），但 PX 协调器的 PX 服务器集数量限制为两个。因此，在某个时间点，PX 协调器只能有两个 PX 服务器集处于活动状态。因此，DFO 中的操作和 DFO 之间的操作都存在并行性。单个 DFO 的并行性称为操作内并行性，DFO 之间的并行性称为操作间并行性。为了说明操作内和操作间并行性，请考虑以下语句：

SELECT * FROM employees ORDER BY last_name;

执行计划对 employees 雇员表进行全面扫描。此操作之后，根据 last_name 列的值对检索到的行进行排序。为了便于说明，假设 last_name 列未编入索引。还假设查询的 DOP 设置为 4，这意味着对于任何给定操作，可以有 4 个并行执行服务器处于活动状态。

如图所示，尽管 DOP 为 4，但实际上有 8 个 PX 服务器参与查询，这是因为生产者和消费者操作符可以同时执行（操作间并行性）。

此外，参与扫描操作的所有 PX 服务器都会将行发送到执行 SORT 操作的相应 PX 服务器。如果 PX 服务器扫描的行包含 A 和 G 之间的 last_name 列的值，则该行将发送到第一个 ORDER BY 并行执行服务器。扫描操作完成后，排序过程可以将排序结果返回给查询协调器，查询协调器再将完整的查询结果返回给用户。

并行粒度

并行中的基本工作单元称为颗粒。

Oracle 数据库将并行执行的操作（例如表扫描或索引创建）划分为多个颗粒。并行执行 (PX) 服务器每次执行一个颗粒的操作。颗粒的数量及其大小与并行度 (DOP) 相关。颗粒的数量还会影响 PX 服务器之间的工作平衡程度。

块范围粒度

块范围粒度是大多数并行操作的基本单位，即使在分区表上也是如此。从 Oracle 数据库的角度来看，并行度与分区数无关。

块范围粒度是表中物理块的范围。Oracle 数据库在运行时计算粒度的数量和大小，以优化和平衡所有受影响的并行执行 (PX) 服务器的工作分配。粒度的数量和大小取决于对象的大小和 DOP。块范围粒度不依赖于表或索引的静态预分配。在计算粒度期间，Oracle 数据库会考虑 DOP，并尝试将来自不同数据文件的粒度分配给每个 PX 服务器，以尽可能避免争用。此外，Oracle 数据库还会考虑大规模并行处理 (MPP) 系统上粒度的磁盘亲和性，以利用 PX 服务器和磁盘之间的物理接近性。

分区颗粒

当使用分区粒度时，并行执行 (PX) 服务器会对表或索引的整个分区或子分区进行操作。

由于分区粒度是在创建表或索引时由表或索引的结构静态确定的，因此分区粒度无法为您提供块粒度所能提供的并行执行操作的灵活性。允许的最大并行度 (DOP) 是分区数。这可能会限制系统的利用率和 PX 服务器之间的负载平衡。

当使用分区粒度并行访问表或索引时，应使用相对较大数量的分区，理想情况下是 DOP 的三倍，以便 Oracle 数据库可以有效地在 PX 服务器之间平衡工作。

分区颗粒是并行索引范围扫描、查询优化器选择使用分区连接的两个均等分区表之间的连接以及修改分区对象的多个分区的并行操作的基本单位。这些操作包括并行创建分区索引和并行创建分区表。

通过查看语句的执行计划，您可以判断使用了哪些类型的粒度。表或索引访问上方的 PX BLOCK ITERATOR 行表示已使用块范围粒度。在下面的示例中，您可以在 SALES 表的 TABLE FULL ACCESS 上方的解释计划输出的第 6 行上看到这一点。

当使用分区粒度时，您会在解释计划输出中的表或索引访问上方看到行 PX PARTITION RANGE。在下面的示例的第 6 行中，该计划具有 PX PARTITION RANGE ALL，因为此语句访问了表中的所有 16 个分区。如果没有访问所有分区，则仅显示 PX PARTITION RANGE。

生产者和消费者之间的分配方式

分发方法是将数据从一个并行执行 (PX) 服务器集发送（或重新分发）到另一个服务器集的方法。

混合哈希是用于连接操作的一种自适应分布方法。实际分布方法由优化器在运行时根据连接左侧的结果集大小决定。计算从左侧返回的行数并将其与阈值进行核对。当行数小于或等于阈值时，将对连接的左侧使用广播分布，而不对右侧进行分布，因为分配给连接操作的相同消费者 PX 服务器将扫描右侧并执行连接。当从左侧返回的行数高于阈值时，将对连接的两侧都使用哈希分布。

为了确定分布方法，并行执行 (PX) 协调器会检查 SQL 语句执行计划中的每个操作，然后确定必须在 PX 服务器之间重新分布操作所操作的行的方式。

查询计划显示 PX 协调器选择了自适应分布方法。假设优化器在运行时选择哈希分布，则执行过程如下：为查询分配两组 PX 服务器 SS1 和 SS2，由于 PARALLEL 提示指定了语句的 DOP，因此每组服务器都有四个 PX 服务器。

PX 集 SS1 首先扫描表 customers 并将行发送到 SS2，SS2 在这些行上构建哈希表。换句话说，SS2 中的消费者和 SS1 中的生产者同时工作：一个并行扫描客户，另一个并行消费行并构建哈希表以启用哈希连接。这是操作间并行性的一个例子。

SS1 中的 PX 服务器进程扫描客户表中的一行后，应该将其发送给 SS2 中的哪个 PX 服务器进程？在这种情况下，从执行客户并行扫描的 SS1 流向执行并行哈希连接的 SS2 的行的重新分配是通过对连接列进行哈希分配来完成的。也就是说，扫描客户的 PX 服务器进程对列 customers.cust_id 的值计算哈希函数，以决定将其发送给 SS2 中的哪个 PX 服务器进程。使用的重新分配方法在查询的 EXPLAIN PLAN 中的 Distrib 列中明确显示。在图 8-2 中，可以在 EXPLAIN PLAN 的第 5、9 和 14 行中看到这一点。

SS1 扫描完整个客户表后，将并行扫描销售表。它将行发送到 SS2 中的 PX 服务器，然后后者执行探测以并行完成哈希连接。这些 PX 服务器还会在连接后执行 GROUP BY 操作。SS1 并行扫描销售表并将行发送到 SS2 后，将切换到并行执行最后的分组操作。此时，SS2 中的 PX 服务器使用哈希分布将其行发送到 SS1 上的 PX 服务器以执行分组操作。这就是两个服务器集如何并发运行以实现查询树中各个运算符之间的操作间并行性。

并行执行服务器如何通信

为了并行执行查询，Oracle 数据库通常会创建一组生产者并行执行服务器和一组消费者并行执行服务器。

生产者服务器从表中检索行，消费者服务器对这些行执行连接、排序、DML 和 DDL 等操作。生产者集中的每个服务器都与消费者集中的每个服务器建立连接。并行执行服务器之间的虚拟连接数随着并行度的平方而增加。

每个通信通道至少有一个，有时最多有四个内存缓冲区，这些缓冲区是从共享池中分配的。多个内存缓冲区有助于并行执行服务器之间的异步通信。

单实例环境每个通信通道最多使用三个缓冲区。Oracle Real Application Clusters 环境每个通道最多使用四个缓冲区。下图说明了消息缓冲区以及生产者并行执行服务器如何连接到消费者并行执行服务器。

当连接位于同一实例上的两个进程之间时，服务器通过在内存（共享池中）中来回传递缓冲区进行通信。当连接位于不同实例中的进程之间时，消息通过互连使用外部高速网络协议发送。在上图中，DOP 等于并行执行服务器的数量，在本例中为 n。上图未显示并行执行协调器。每个并行执行服务器实际上都有一个与并行执行协调器的额外连接。使用并行执行时，适当调整共享池的大小非常重要。如果共享池中没有足够的可用空间来为并行服务器分配必要的内存缓冲区，则并行服务器将无法启动。

二）并行的相关概念

并行执行可将多个 CPU 和 I/O 资源用于执行单条 SQL 语句。

并行执行有时也称为并行性。所谓并行，就是将任务分解，让多个进程同时进行部分工作，而不是由一个进程完成查询中的所有工作。举例来说，当四个进程共同计算一年的总销售额时，每个进程处理一年中的一个季度，而不是由一个进程单独处理所有四个季度。这对绩效的提高是非常显著的。

首先我们来了解两个基本概念:

并行度(Degree of parallelism):单个操作所关联的并行执行服务器个数。

并行执行服务器(Parallel Execution Servers)：当数据库实例启动后,数据库创建一个并行执行服务器池以供并行操作. 一个叫 parallel execution coordinator 的进程负责分配Parallel Execution Servers并协调把并行执行的结果返回给用户，默认情况下Parallel Execution Servers处于开启状态。

SQL> show parameter parallel_max_serversNAME                 TYPE        VALUE------------------------------------ ----------- ------------------------------parallel_max_servers integer     16  <-----非零值

Oracle根据下面的规则来确定并行度，如果指定或请求并行执行，但没有指定并行度，默认的并行度被设置为系统CPU核数的两倍，对RAC系统，并行度设置为整个集群的cpu核数的两倍，因此 parallel_max_servers 最大并行参数值为cpu*2。

SQL> show parameter cpu_countNAME      TYPE        VALUE------------------------------------ ----------- ------------------------------cpu_count integer        8

默认情况下，并行操作会分发到RAC的各个节点，而在很多生产环境下，我们并不希望并行跨节点执行，此时就需要设置该参数为 TRUE：

SQL> alter system set parallel_force_local=true sid=’*’;SQL> show parameter parallel_forceNAME TYPE        VALUE------------------------------------ ----------- ------------------------------parallel_force_local boolean     TRUE

除了上面说的参数外，这里还需要注意一下 SHARED_POOL_SIZE 大小，除了串行 SQL 执行所需的内存资源外，并行执行还需要内存资源。额外的内存用于在查询服务器进程和查询协调器之间通信和传递数据。Oracle 从共享池中为查询服务器进程分配内存。如果没有足够的可用内存，则会出现错误消息 12853（PX 缓冲区内存不足：当前字符串K，最大所需字符串K）。这是由于可用于 PX 缓冲区的 SGA 内存不足造成的。您必须重新配置 SGA 以至少具有 （MAX- CURRENT） 字节的额外内存。

并行执行相关视图

V$PQ_TQSTAT视图：

即使有explan_PLAN和sql_traces输出，还是很难准确地解释并行查询是如何执行的，例如，实际的并行度是什么，每个并行服务进程都做了哪些工作？v$pq_tqstat视图包含了有关并行查询服务器的每个集合间传输的信息，包括发送和接收的行数。不过这个视图仅对发送并行查询的会话和最近执行过的查询是可见的。这些限制了它在产品中的使用，但在调优并行查询事，它是有作用的。

SELECT /*+ parallel */ DEPTNO,SUM(SAL) FROM emp GROUP BY DEPTNO ORDER BY 2 DESC;select * from v$pq_tqstat;

v$px_session 视图：

通过检查 v$px_session 视图，我们可以实时得到系统上正在发生的并执行的视图，这个视图显示当前哪些并行子进程正在执行SQL，将 v$px_session，v$session 和 v$sql 3 个视图关联，使我们能识别出当前正在使用并行处理的会话和 SQL，以及看到想要的和真实的并行度。

WITH px_session AS(SELECT qcsid,qcserial#,MAX(degree) degree,MAX(req_degree) req_degree,COUNT(*) no_of_processesFROM v$px_session p GROUP BY qcsid, qcserial#)SELECT s.sid, s.username, degree, req_degree, no_of_processes, sql_text  FROM v$session s  JOIN px_session p  ON (s.sid = p.qcsid AND s.serial# = p.qcserial#)  JOIN v$sql SQL  ON (sql.sql_id = s.sql_id);

V$PX_BUFFER_ADVICE 视图：

该视图提供有关所有并行查询的历史和预计最大缓冲区使用情况的统计信息。您可以查阅此视图以重新配置 SGA 大小，以响应并行查询的内存不足问题。

V$PX_PROCESS 视图：

使用 V$PX_PROCESS 动态性能视图监控并行执行性能。V$PX_PROCESS 视图包含有关并行进程的信息，包括状态、会话 ID、进程 ID 和其他信息。

V$PQ_SESSTAT 视图：

该视图显示系统中所有当前服务器组的状态，例如有关查询如何分配进程以及多用户和负载平衡算法如何影响默认值和提示值的数据。

SELECT NAME, VALUE FROM GV$SYSSTAT  WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'  OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

以下查询显示每个工作程序（子进程）的当前等待状态，并且查询协调器进程：

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",       px.SERVER_GROUP "Group", px.SERVER_SET "Set",       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT wWHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID, DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

并行 DML 的限制

并行 DML 有如下几个限制：

• 以下限制适用于并行 DML（包括直接路径 INSERT）：

• UPDATE、MERGE 和 DELETE 操作的分区内并行要求 COMPATIBLE 初始化参数设置为 9.2 或更高。

• INSERT VALUES 语句永远不会并行执行。

• 一个事务可以包含多条修改不同表的并行 DML 语句，但在并行 DML 语句修改表后，该事务中的后续串行或并行语句（DML 或查询）都不能再访问同一表。

• 在串行直接路径 INSERT 语句之后也存在这种限制：在该事务中，任何后续 SQL 语句（DML 或查询）都不能访问修改过的表。

• 在并行 DML 或直接路径 INSERT 语句之前，允许访问同一表的查询，但在并行 DML 或直接路径 INSERT 语句之后则不允许。

• 任何串行或并行语句如果试图访问在同一事务中已被并行 UPDATE、DELETE 或 MERGE 或直接路径 INSERT 修改过的表，都会被错误消息拒绝。

• 并行 DML 操作不能在带触发器的表上执行。

• 并行 DML 不支持复制功能。

• 并行 DML 不能在存在某些约束的情况下进行：自参照完整性、删除级联和延迟完整性。此外，对于直接路径 INSERT，不支持任何参照完整性。

• 只要不访问对象列，就可以在有对象列的表上执行并行 DML。

• 只要对表进行了分区，就可以在带有 LOB 列的表上执行并行 DML。但不支持分区内并行。

• 对于带有 LOB 列的非分区表，如果 LOB 列声明为 SecureFiles LOB，则支持并行 INSERT 操作。不支持对此类表进行并行更新、删除和合并操作。

• 如果 DML 操作是在分布式事务中，或者 DML 或查询操作是在远程对象上，则不能并行执行。

• 不支持集群表。

• 临时表不支持并行 UPDATE、DELETE 和 MERGE 操作。

• 如果表未分区，则不支持对带有位图索引的表进行并行 DML 操作。

• 违反这些限制会导致语句串行执行，而不会发出警告或错误消息（但对在事务中访问同一表的语句的限制除外，该限制会导致错误消息）。

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-6626C70C-876C-47A4-8C01-9B66574062D8

由于本节内容比较多，剩下的部分后面我们在接着看，今天就到这里吧。本文前面全是枯燥的文字，估计大家也不想看，没关系，后面的内容全是实用干货了，敬请期待，想看的点zan 转fa 走起来~~~这样我就有动力更新了，灰常感谢~~~

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程
MOP 系列|MOP 三种主流数据库索引简介
Oracle 主流版本不同架构下的静默安装指南
关机重启导致 ASM 磁盘丢失数据库无法启动
Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
Oracle ACE 视角下的国产数据库现状与选型及应对策略
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？