Oracle Database 12C新hint—ENABLE_PARALLEL_DML

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址: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_ID	8cbs8m8qrjy5b, 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_ID	bb53bfzfr7z1n, 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_ID	cd4s55gj7172y, 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)

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *