parallel PDML with disabled FK

jnrjian

已于 2024-03-11 15:52:53 修改

阅读量839

点赞数 12

文章标签： oracle dba

于 2024-03-11 11:29:47 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/136618498

版权

---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.

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