parallel PDML with disabled FK_- pdml disabled because object is not decorated wi-优快云博客

本文链接：https://blog.youkuaiyun.com/jnrjian/article/details/136618498

本文介绍了在Oracle数据库中启用并行DML(PDML)的新功能，包括ALTERSESSION命令和12c中引入的ENABLE_PARALLEL_DMLhint。同时提到从19.16.0.0.220719版本开始，由于ReferentialIntegrity的限制，PDML在修改外键列时被禁用，需手动解决。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---PDML--------

GOAL

Parallel DML (PDML) must be explicitly enabled in order for DML to be considered for parallel execution. In RDBMS versions lower than 12c, you could accomplish this only at the session level by using an ALTER SESSION statement. Assuming restrictions on parallel DML do not apply to your situation, once the session is altered, all further statements in the session will be candidates for execution of DML in parallel.

ALTER SESSION ENABLE PARALLEL DML;
<execute DML statement>

SOLUTION

NOTE: In the images and/or the document content below, the user information and data used represents fictitious
data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

New Hint in 12c: ENABLE_PARALLEL_DML

Starting in 12c, there is a new hint that will let you enable PDML at the statement level: ENABLE_PARALLEL_DML

/*+ enable_parallel_dml parallel(x) */ -- (x) is optional, where x is the requested degree of parallelism

Example 1: PDML is disabled

In this example, the select will run in parallel, but the load will be done serially because PDML is not explicitly enabled. In 12c, the explain plan will include the reason that PDML was not considered.

insert /*+ parallel */ into t1 select * from t1_staging;

Execution Plan

----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | T1 | <---- LOAD is not under a PX COORDINATOR, so PDML is disabled
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 |
| 4 | PX BLOCK ITERATOR | |
| 5 | TABLE ACCESS FULL | <TABLE1> |
----------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 2
- PDML is disabled in current session <------

Example 2: PDML is enabled

In this example, we see both the select and load are done in parallel.

insert /*+ parallel(8) enable_parallel_dml */ into t1 select * from t1_staging;

----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| T1 | <---- LOAD is under a PX COORDINATOR, so PDML is enabled
| 4 | OPTIMIZER STATISTICS GATHERING | |
| 5 | PX BLOCK ITERATOR | |
| 6 | TABLE ACCESS FULL | <TABLE1> |
----------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- - Degree of Parallelism is 8 because of hint

Notes:
1. The "enable_parallel_dml" hint may occur in any order within the /*+ */ delimiters.
2. The hint "disable_parallel_dml" is also new in 12c and disables PDML at the statement level.
3. This hint is not yet included in the 12.1 documentation.

--------------------------------------------