关于PDML，之前在 并行，想说爱你不容易中的第一节就介绍过，今天在客户现场协助测试时又遇到几个有关PDML的问题，都蛮典型的，记录一下：
问题1：某存储过程报错ORA-12839。
查看该错误号说明：
代码语言：javascript复制[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)
之前常规的方法是：
代码语言：javascript复制ALTER SESSION ENABLE PARALLEL DML;
<execute DML statement> 这种方式对会话启用PDML，不够灵活。12c之后可以使用 ENABLE_PARALLEL_DML：
代码语言：javascript复制/* 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.欢迎补充～
