oracle parallelenable,Oracle Database 12C新hint—ENABLE_PARALLEL_DML

在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)

分享到：

更多