现在记录一下这个过程，并回顾那些年我们用并行遇到的坑：

环境：Oracle RAC 11.2.0.4(3 nodes)

• 4.所有节点并行测试

1.并行insert无效果

测试用例：

create table Z_OBJ tablespace TBS_1 as select * from dba_objects ; insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1; commit; --多次执行并查询大小 select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name='Z_OBJ';

根据测试用例执行，发现实际并没有合理使用到并行度，效率很差（监控到I/O写入每秒只有百兆级别，正常应该是每秒千兆级别）。
查看执行计划：

SQL> explain plan for insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1;  Explained.  SQL> set lines 1000 pages 200 SQL> select * from table(dbms_xplan.display());    PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1886916412  --------------------------------------------------------------------------------------------------------------- | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- |   0 | INSERT STATEMENT      |          |    91M|    17G| 23842   (1)| 00:00:01 |        |      |            | |   1 |  LOAD AS SELECT       | Z_OBJ    |       |       |            |          |        |      |            | |   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            | |   3 |    PX SEND QC (RANDOM)| :TQ10000 |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  | |   4 |     PX BLOCK ITERATOR |          |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | PCWC |            | |   5 |      TABLE ACCESS FULL| Z_OBJ    |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | PCWP |            | ---------------------------------------------------------------------------------------------------------------  Note -----    - dynamic sampling used for this statement (level=2)  16 rows selected.

可以看到，只有查询部分用到了并行，insert部分并没有使用到并行，尽管我们指定了并行度的hint。
知识点1：不仅仅是insert操作，其他DML操作的并行，都需要显示启用DML的并行才可以：

alter session enable parallel dml;

再次查看执行计划，发现insert部分已经可以使用到并行：

SQL> explain plan for insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1;  Explained.  SQL> select * from table(dbms_xplan.display());  PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2135351304  --------------------------------------------------------------------------------------------------------------- | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- |   0 | INSERT STATEMENT      |          |    91M|    17G| 23842   (1)| 00:00:01 |        |      |            | |   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            | |   2 |   PX SEND QC (RANDOM) | :TQ10000 |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  | |   3 |    LOAD AS SELECT     | Z_OBJ    |       |       |            |          |  Q1,00 | PCWP |            | |   4 |     PX BLOCK ITERATOR |          |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | PCWC |            | |   5 |      TABLE ACCESS FULL| Z_OBJ    |    91M|    17G| 23842   (1)| 00:00:01 |  Q1,00 | PCWP |            | ---------------------------------------------------------------------------------------------------------------  Note -----    - dynamic sampling used 

                    

                        
关键字：