代码改变世界

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

2021-10-28 22:38  AlfredZhao  阅读(658)  评论(0编辑  收藏  举报

关于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.

欢迎补充~