alter session enable parallel dml语句解析—dml与select同时使用并行功能

文档课题：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语句均使用到并行.