文档课题：alter session enable parallel dml语句解析—dml与select同时使用并行功能.

1、相关知识

若要select和dml语句都使用并行，那须先运行以下命令.否则只有查询语句使用到并行，dml语句使用不到.

alter session enable parallel dml;

2、验证过程

HR@orcl150> explain plan for insert /*+APPEND PARALLEL*/ into t_hr_20230117 (id,time) select /*+PARALLEL(t_hr_20230117,4)*/ * from t_hr_20230117;

 

Explained.

 

HR@orcl150> col PLAN_TABLE_OUTPUT for a135

HR@orcl150> select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 282814601

 

------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT      |               | 86396 |  1096K|    10   (0)| 00:00:01 |       |       |        |      |            |

|   1 |  LOAD AS SELECT       | T_HR_20230117 |       |       |            |          |       |       |        |      |            |

|   2 |   PX COORDINATOR      |               |       |       |            |          |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)| :TQ10000      | 86396 |  1096K|    10   (0)| 00:00:01 |      |      |  Q1,00 | P->S | QC (RAND)  |

|   4 |     PX BLOCK ITERATOR |               | 86396 |  1096K|    10   (0)| 00:00:01 |     1 |     3 |  Q1,00 | PCWC |            |

|   5 |      TABLE ACCESS FULL| T_HR_20230117 | 86396 |  1096K|    10   (0)| 00:00:01 |     1 |     3 |  Q1,00 | PCWP |            |

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

 

Note

-----

   - automatic DOP: skipped because of IO calibrate statistics are missing

 

16 rows selected.

小结：如上所示，未执行alter session enable parallel dml语句时只有insert语句使用到并行.

HR@orcl150> commit;

 

Commit complete.

 

HR@orcl150> alter session enable parallel dml;

 

Session altered.

 

HR@orcl150> explain plan for insert /*+APPEND PARALLEL*/ into t_hr_20230117 (id,time) select /*+PARALLEL(t_hr_20230117,4)*/ * from t_hr_20230117;

 

Explained.

 

HR@orcl150> select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2617619301

 

------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT      |               | 86396 |  1096K|    10   (0)| 00:00:01 |       |       |        |      |            |

|   1 |  PX COORDINATOR       |               |       |       |            |          |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM) | :TQ10000      | 86396 |  1096K|    10   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    LOAD AS SELECT     | T_HR_20230117 |       |       |            |          |       |       |  Q1,00 | PCWP |            |

|   4 |     PX BLOCK ITERATOR |               | 86396 |  1096K|    10   (0)| 00:00:01 |     1 |     3 |  Q1,00 | PCWC |            |

|   5 |      TABLE ACCESS FULL| T_HR_20230117 | 86396 |  1096K|    10   (0)| 00:00:01 |     1 |     3 |  Q1,00 | PCWP |            |

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

 

Note

-----

   - automatic DOP: skipped because of IO calibrate statistics are missing

 

16 rows selected.

 

说明：执行过alter session enable parallel dml后insert和select语句均使用到并行.