在12c之前的版本中,通过alter session启用了并行DML。12c引入了新的hint,可以过SQL中加/ * + ENABLE_PARALLEL_DML * / 启用并行DML 。这就有一个好处,之前我们在session级别更改的,一旦更改了会话级,则所有的其他语句也将会并行执行DML。这显然不科学。现在通过新的hint,我们可以只对我们感兴趣的语句使用。当然我在测试中发现,我们使用了ENABLE_PARALLEL_DML这个hint开启并行DML,我们的语句并没有并行执行,仍然是串行的,这是因为我们仍然需要在里面写入并行的表。还有一种情况就是我们写了hint,但是违反了并行操作的限制也不会开启。那么我们来简单演示一下这个hint。
首先我使用普通的并行操作的hint,可以看到自动开启了并行操作,但是它显示“PDML is disabled in current session”.
SQL> update /*+parallel */test set OWNER='Buddy';
72392 rows updated.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID8cbs8m8qrjy5b, child number 1
-------------------------------------
update /*+parallel */test set OWNER='Buddy'
Plan hash value: 3695425075
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 212 (100)| | | | |
| 1 | UPDATE | TEST | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| TEST | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- PDML is disabled in current session
接下来,我使用ENABLE_PARALLEL_DML的hint,但是结果确显示“PDML disabled because object is not decorated with parallel clause”,这是因为我们虽然开启了ENABLE_PARALLEL_DML,但是没开启parallel,所以受到限制PDML也是disable的。
SQL> update /*+ ENABLE_PARALLEL_DML */ test set OWNER='Buddy';
72392 rows updated.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_IDbb53bfzfr7z1n, child number 0
-------------------------------------
update /*+ ENABLE_PARALLEL_DML */ test set OWNER='Buddy'
Plan hash value: 839355234
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 383 (100)| |
| 1 | UPDATE | TEST | | | | |
| 2 | TABLE ACCESS FULL| TEST | 72392 | 353K| 383 (1)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- PDML disabled because object is not decorated with parallel clause
这次我们同时打开了parallel和ENABLE_PARALLEL_DML。现在的执行计划和我们单独开parallel的执行计划并不一样。如果只开parallel是仅仅查询的部分开启了并行,DML部分没有使用并行。两个提示都打开,则查询部分和DML操作都会使用并行。
SQL> update /*+ ENABLE_PARALLEL_DML parallel */ test set OWNER='Buddy';
72392 rows updated.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_IDcd4s55gj7172y, child number 2
-------------------------------------
update /*+ ENABLE_PARALLEL_DML parallel */ test set OWNER='Buddy'
Plan hash value: 4152913824
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | |212 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 72392 |353K|212 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | TEST | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 72392 |353K|212 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 72392 |353K|212 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | UPDATE | TEST | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 72392 |353K|212 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| TEST | 72392 |353K|212 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
还有一种特殊的情况,假设一大堆语句,你都想用PDML,但是有那么一条你不想使用,这个时候你可以在session级别设置了DML parallel,然后在语句上设置disable_parallel_dml就可以了。
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
update /*+ disable_parallel_dml parallel */ test set OWNER='Buddy'
Plan hash value: 3695425075
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 212 (100)| | | | |
| 1 | UPDATE | TEST | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| TEST | 72392 | 353K| 212 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- PDML is disabled in current session
27 rows selected.
参考文档:New 12c Hint: ENABLE_PARALLEL_DML Lets you Easily Enable Parallel DML (PDML) at the Statement Level (Doc ID 1991034.1)
分享到:
更多