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

    欢迎补充~

    AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」
  • 相关阅读:
    VestaCP中国用户遭到大量DDOS攻击
    ZooKeeper设置ACL权限控制
    linux rsync 指定用户名和密码的方式同步
    关于Apache HTTPD 2.2.15的部分漏洞修复建议
    AutoMapper官方文档(二)【升级指南】
    openssl升级
    MongoDB如何无缝版本升级
    mysql在线升级更新步骤
    手动升级kubernetes集群
    更新Docker容器
  • 原文地址:https://www.cnblogs.com/jyzhao/p/15478078.html
Copyright © 2020-2023  润新知