小知识: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.

欢迎补充~

This entry was posted in Oracle性能优化 and tagged , , , . Bookmark the permalink.