小知识：PDML的注意事项补充

关于PDML，之前在 并行，想说爱你不容易中的第一节就介绍过，今天在客户现场协助测试时又遇到几个有关PDML的问题，都蛮典型的，记录一下：

问题1：某存储过程报错ORA-12839。

查看该错误号说明：

[oracle@db193 ~]$ oerr ora 12839
12839, 00000, "cannot modify an object in parallel after modifying it"
// *Cause: Within the same transaction, an attempt was made to perform
// parallel modification operations on a table after it had been modified.
// This is not permitted.
// *Action: Rewrite the transaction or break it up into two transactions:
// one containing the parallel modification and the second containing the
// initial modification operation.

实际查看存储过程发现其中有并行DML的操作，而PDML需在一个事物中执行，即注意执行完成前后要结束/回滚事物。这里解决方法就是查找没有提交的部分，写上commit语句。

问题2：PDML语句无法使用设置的并行

之前文章就介绍过要设置session启用并行DML， 因为这里的客户环境是19.12，在12c之后还有一个新的方法：

• New 12c Hint: ENABLE_PARALLEL_DML Lets you Easily Enable Parallel DML (PDML) at the Statement Level (Doc ID 1991034.1)

之前常规的方法是：

ALTER SESSION ENABLE PARALLEL DML;
<execute DML statement> 

这种方式对会话启用PDML，不够灵活。12c之后可以使用 ENABLE_PARALLEL_DML：

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

eg：
explain plan for insert /*+ enable_parallel_dml parallel(8) */ into  t1 select * from t1;

--1.提示PDML在当前会话未启用
SQL> explain plan for insert /*+ parallel(8) */ into  t1 select /*+ parallel(8) */ * from t1;
select * from table(dbms_xplan.display);
Explained.

SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2494645258

------------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |	    | 72617 |  9360K|	 55   (0)| 00:00:01 |	     |	    |		 |
|   1 |  LOAD TABLE CONVENTIONAL | T1	    |	    |	    |		 |	    |	     |	    |		 |
|   2 |   PX COORDINATOR	 |	    |	    |	    |		 |	    |	     |	    |		 |
|   3 |    PX SEND QC (RANDOM)	 | :TQ10000 | 72617 |  9360K|	 55   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR	 |	    | 72617 |  9360K|	 55   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
|   5 |      TABLE ACCESS FULL	 | T1	    | 72617 |  9360K|	 55   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
------------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -	STATEMENT
	 U -  parallel(8) / duplicate hint

Note
-----
   - Degree of Parallelism is 8 because of hint
   - PDML is disabled in current session

24 rows selected.

--2.尝试使用enable_parallel_dml的hint
在insert和select部分都写上hint，会提示有重复的hint：
SQL> explain plan for insert /*+ enable_parallel_dml parallel(8) */ into  t1 select /*+ enable_parallel_dml parallel(8) */ * from t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 494765410

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT		   |	      | 72617 |  9360K|    55	(0)| 00:00:01 |        |      | 	   |
|   1 |  PX COORDINATOR 		   |	      |       |       | 	   |	      |        |      | 	   |
|   2 |   PX SEND QC (RANDOM)		   | :TQ10000 | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T1       |       |       | 	   |	      |  Q1,00 | PCWP | 	   |
|   4 |     OPTIMIZER STATISTICS GATHERING |	      | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWP | 	   |
|   5 |      PX BLOCK ITERATOR		   |	      | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWC | 	   |
|   6 |       TABLE ACCESS FULL 	   | T1       | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWP | 	   |
----------------------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -	STATEMENT
	 U -  parallel(8) / duplicate hint

Note
-----
   - Degree of Parallelism is 8 because of hint

24 rows selected.

--3.验证只写一个hint也在insert和select部分同时用到了并行：
上面说明写一个hint就OK，所以在insert后面写hint，看下效果OK：
SQL> explain plan for insert /*+ enable_parallel_dml parallel(8) */ into  t1 select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 494765410

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT		   |	      | 72617 |  9360K|    55	(0)| 00:00:01 |        |      | 	   |
|   1 |  PX COORDINATOR 		   |	      |       |       | 	   |	      |        |      | 	   |
|   2 |   PX SEND QC (RANDOM)		   | :TQ10000 | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T1       |       |       | 	   |	      |  Q1,00 | PCWP | 	   |
|   4 |     OPTIMIZER STATISTICS GATHERING |	      | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWP | 	   |
|   5 |      PX BLOCK ITERATOR		   |	      | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWC | 	   |
|   6 |       TABLE ACCESS FULL 	   | T1       | 72617 |  9360K|    55	(0)| 00:00:01 |  Q1,00 | PCWP | 	   |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint

17 rows selected.

欢迎补充～