八千字带你了解 Oracle 并行那些事(三)

原创 JiekeXu All China Database Union 2024-11-25

408

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

facebook_pro_light_1920 × 1080 副本.png

一）并行执行的工作原理	
	SQL 语句的并行执行过程	
	生产者/消费者模型	
	并行粒度	
	生产者和消费者之间的分配方式	
	并行执行服务器如何通信	
二）并行的相关概念	
	并行执行相关视图	
三）并行查询	
四）并行DML	
	默认并行度	
	LOB 列的并行
        并行 DML 的限制		
五）并行创建表	
六）并行创建索引	
七）并行收集统计信息	
八）Rman 备份的并行	
九）23ai 并行新特性	
十）参考链接
复制

废话不多说，干货来了，赶快收藏点赞转发起来吧，接上文，八千字带你了解 Oracle 并行那些事(一)

八千字带你了解 Oracle 并行那些事(二)

四）并行 DML

enable_parallel_dml

一般情况下，我们都只是在会话级别开启并行 DML。那么DML 有 INSERT、DELETE、UPDATE、MERGE 这四种，例如下面这样开启并行 DML：

alter session enable parallel dml;
INSERT /*+ parallel(16) */ into emp1 select /*+ parallel(16) */ from emp where empno=30;
复制

当然也有的直接是 alter session force parallel dml parallel 8; 这样就算后面的 SQL 语句没有加 HINT 也是使用并行 DML。

在 12c 以后呢，上面语句得到进一步简化，直接一条语句添加 HINT /*+ enable_parallel_dml parallel(8) */ 开启 PDML。

delete /*+ enable_parallel_dml parallel(8) */ from dws.mag_d_busi_detail where datadate < sysdate -1;
复制

SQL> explain plan for INSERT /*+ enable_parallel_dml parallel(16) */ into emp1 select /*+ parallel(8) */ EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     4 |   152 |     3   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL      | EMP  |     4 |   152 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."DEPTNO">=30)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  parallel(16) / hint conflicts with another in sibling query block
         U -  parallel(8) / hint conflicts with another in sibling query block

Note
-----
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel

27 rows selected.
复制

并行 DML
New 12c Hint: ENABLE_PARALLEL_DML Lets you Easily Enable Parallel DML (PDML) at the Statement Level (Doc ID 1991034.1)

SQL> explain plan for INSERT /*+ enable_parallel_dml parallel(16) */ into emp1 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4200853112

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     4 |   152 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     4 |   152 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| EMP1     |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     4 |   152 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     4 |   152 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL            | EMP      |     4 |   152 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("A"."DEPTNO">=30)

Note
-----
   - Degree of Parallelism is 16 because of hint

22 rows selected.
复制

图片.png

故如下三种 SQL 均等价，并行度为 16.

（1）12c 以上使用 Hint 开启并行

INSERT /*+ enable_parallel_dml parallel(16) */ into emp1 
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b 
where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
复制

（2）11g 以上会话级别开启并行 dml

alter session enable parallel dml;
INSERT /*+ parallel(16) */ into emp1 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO 
from emp a,dept b 
where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
复制

（3）10g 相关版本需要对每个表单独设置并行

alter session enable parallel dml;
INSERT /*+ parallel( emp1 16) */ into emp1 select  /*+ parallel(emp 16) parallel(dept 16) */  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b  
where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
复制

示例如下：

SQL> conn scott/scott
Connected.
set line 130 pages 234
alter session enable parallel dml;
explain plan for INSERT /*+ parallel( emp1 16) */ into emp1 select  /*+ parallel(emp 16) parallel(dept 16) */  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b  where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
select * from table(dbms_xplan.display);
复制

图片.png

注意，如果是 11g 及以上的数据库，只要在整个 SQL 的任何一个关键字（select、update、insert、delete、merge）后面出现一次parallel(n)，那么整个SQL相关的表，都会使用并行，如果是 10g 的库还需要分别指定每个要并行的表才行，/*+ parallel(emp 8) parallel(dept 8) */。如果写错并行 HINT，有可能会使用自动并行，这可能会导致系统资源 IO 标高，进而影响其他操作无法高效运行导致性能问题。

默认并行度

如果指定了并行，但未列出并行度(DOP),则对象将获取默认DOP。

ALTER TABLE sales PARALLEL;
复制

默认并行度使用公式根据系统配置确定 DOP，如下所示：

对于单个实例，DOP =PARALLEL_THREADS_PER_CPU x CPU_COUNT
对于 Oracle RAC 配置，DOP =PARALLEL_THREADS_PER_CPU x sum(CPU_COUNT)

默认情况下，sum(CPU_COUNT) 是集群中的 CPU 总数。但是，如果您使用 Oracle RAC 服务来限制可以执行并行操作的节点数，则 sum(CPU_COUNT) 是属于该服务的节点之间的 CPU 总数。例如，在 4 节点 Oracle RAC 集群上，每个节点有 8 个 CPU 核心且没有 Oracle RAC 服务，则默认 DOP 为 2 x （8+8+8+8） = 64。

另外，还有自动并行度（Auto DOP）使 Oracle 数据库能够自动决定语句是否应并行执行以及应使用什么 DOP。如下图所示，因没有写具体并行度，则是自动并行 Auto DOP，该自动并行由 PARALLEL_THREADS_PER_CPU * sum(CPU_COUNT) 决定。

图片.png

不过，优化器会限制实际最大 DOP，以确保并行执行服务器不会压垮系统。该限制由参数 PARALLEL_DEGREE_LIMIT 设置。该参数的默认值为 CPU，这意味着 DOP 受系统 CPU 数量（PARALLEL_THREADS_PER_CPU * sum(CPU_COUNT)）的限制，也称为默认 DOP。默认 DOP 可确保单个用户操作不会压垮系统。通过调整该参数设置，可以控制优化程序为 SQL 语句选择的最大 DOP。如果 Oracle 数据库资源管理器用于限制 DOP，优化器还可以进一步限制可选择的最大 DOP。

如果启用了并行 DML，并且在数据字典中为表设置了 PARALLEL 提示或 PARALLEL 属性，那么插入操作就是并行和附加的，除非有限制。如果缺少 PARALLEL 提示或 PARALLEL 属性，插入操作将以串行方式执行。自动 DOP 仅在并行 DML 启用或强制的情况下并行 SQL 语句的 DML 部分。如果启用了并行 DML，则可以使用 NOAPPEND 提示来执行并行常规插入操作。例如，可以在 SQL INSERT 语句中使用 /*+ noappend parallel */ 来执行并行常规插入操作。

SQL> INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist SELECT * FROM sales;
复制

并行常规插入操作的优点是可以执行在线操作，不受直接路径 INSERT 的限制。并行常规插入操作的缺点是，这个过程可能比直接路径 INSERT 慢。

LOB 列的并行

对带有 SecureFiles LOB 列的普通表可以进行并行查询，不能进行并行 DML。

create table t (a number,b varchar2(200),c clob) lob(c) store as securefile;
explain plan for insert /*+ parallel enable_parallel_dml */ into t select /*+ parallel */ * from t;
select * from table(dbms_xplan.display(format=>'basic +note'));

explain plan for delete /*+ parallel enable_parallel_dml */  from t;
select * from table(dbms_xplan.display(format=>'basic +note'));
复制

图片.png

对带有 SecureFiles LOB 列的分区表可以进行并行 DML 操作，但有效的并行仅是分区的数量，因为在这种情况下，将分区用作 PX 粒度。我们可以通过视图 V$PQ_SESSTAT 查看。

create table t_par (a number,b varchar2(200),c clob) lob(c) store as securefile partition by hash(a) partitions 4;
explain plan for delete /*+ parallel(8) enable_parallel_dml */  from t_par;
select * from table(dbms_xplan.display(format=>'basic +note'));
select * from V$PQ_SESSTAT;
复制

图片.png

并行 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 操作。
违反这些限制会导致语句串行执行，而不会发出警告或错误消息（但对在事务中访问同一表的语句的限制除外，该限制会导致错误消息）。

五）并行创建表

如下创建表，使用并行后，需要及时取消并行。

create table scott.test151 parallel 16 as select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from scott.emp a,scott.dept b  where a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
alter table scott.test151 noparallel;
复制

ALTER TABLE sales PARALLEL 8; --手动设置并行


Set autot on
select * from scott.test151;
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     5 |   435 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     5 |   435 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     5 |   435 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| TEST151  |     5 |   435 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 16 because of table property
复制

如下 CTAS 使用并行，然后继续使用并行 DML。

create table scott.test151 parallel(degree 16) as select  /*+ parallel(16) */ EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from scott.emp a,scott.dept b  where a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
INSERT /*+ parallel(16) */ into emp1 select  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,a.DEPTNO from emp a,dept b 
where  a.DEPTNO=b.DEPTNO and  a.DEPTNO>=30;
alter table scott.test151 noparallel;  --及时取消并行
复制

这种情况需要特别注意，创建的表默认并行度就是16，需要手工修改回1，这一步非常重要，一定要做。

六）并行创建索引

CREATE INDEX、ALTER INDEX REBUILD 均可使用并行创建/重建索引。并行 DDL 不能在有对象列的表上执行。并行 DDL 不能发生在具有 LOB 列的非分区表上。

create index idx_emp1_deptno on emp1(deptno) parallel 4 online;
alter index idx_emp1_deptno noparallel;
复制

由于文章过长，今天也就先到这里了，还剩下最后一节我们明天继续。

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

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————
facebook_pro_light_1920 × 1080 副本.png