最近才发现并行提示增加了语句级并行的功能。

以前添加并行都是对指定的表添加，最近才发现，如果不加表名，是指定这个语句的并行度：

SQL> create table t_p_i as
  2  select *
  3  from dba_objects
  4  where 1 = 2;

Table created.

SQL> create table t_p_s as
  2  select *
  3  from dba_objects;

Table created.

SQL> set autot on exp 
SQL> insert into t_p_i
  2  select *
  3  from t_p_s;

13593 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 3463104165

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       | 14768 |  2985K|    53   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T_P_I |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T_P_S | 14768 |  2985K|    53   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> insert into t_p_i
  2  select /*+ parallel(t_p_s 4) */ *
  3  from t_p_s;

13593 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 455351089

----------------------------------------------------------------------------------------
|Id|Operation               |Name    |Rows |Bytes |Cost (%CPU)|   TQ |IN-OUT|PQ Distrib|
----------------------------------------------------------------------------------------
| 0|INSERT STATEMENT        |        |14768| 2985K|   15   (0)|      |      |          |
| 1| LOAD TABLE CONVENTIONAL|T_P_I   |     |      |           |      |      |          |
| 2|  PX COORDINATOR        |        |     |      |           |      |      |          |
| 3|   PX SEND QC (RANDOM)  |:TQ10000|14768| 2985K|   15   (0)| Q1,00| P->S |QC (RAND) |
| 4|    PX BLOCK ITERATOR   |        |14768| 2985K|   15   (0)| Q1,00| PCWC |          |
| 5|     TABLE ACCESS FULL  |T_P_S   |14768| 2985K|   15   (0)| Q1,00| PCWP |          |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> insert /*+ parallel(t_p_s 4) */ into t_p_i
  2  select *
  3  from t_p_s;

13593 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 3463104165

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       | 14768 |  2985K|    53   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T_P_I |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T_P_S | 14768 |  2985K|    53   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> commit;

Commit complete.

SQL> alter session enable parallel dml;

Session altered.

SQL> set autot off
SQL> explain plan for
  2  insert /*+ parallel(t_p_i 4) */ into t_p_i
  3  select *
  4  from t_p_s;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2807692233
---------------------------------------------------------------------------------------
|Id|Operation               |Name    |Rows |Bytes |Cost (%CPU)|  TQ |IN-OUT|PQ Distrib|
--------------------------------------------------------------------------------------
| 0|INSERT STATEMENT        |        |14768| 2985K|   53   (0)|     |      |          |
| 1| PX COORDINATOR         |        |     |      |           |     |      |          |
| 2|  PX SEND QC (RANDOM)   |:TQ10001|14768| 2985K|   53   (0)|Q1,01| P->S |QC (RAND) |
| 3|   LOAD AS SELECT       |T_P_I   |     |      |           |Q1,01| PCWP |          |
| 4|    PX RECEIVE          |        |14768| 2985K|   53   (0)|Q1,01| PCWP |          |
| 5|     PX SEND ROUND-ROBIN|:TQ10000|14768| 2985K|   53   (0)|     | S->P |RND-ROBIN |
| 6|      TABLE ACCESS FULL |T_P_S   |14768| 2985K|   53   (0)|     |      |          |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL> explain plan for
  2  insert /*+ parallel(t_p_i 4) */ into t_p_i
  3  select /*+ parallel(t_p_s 4) */ *
  4  from t_p_s;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2808998595
-------------------------------------------------------------------------------------
|Id|Operation             |Name    |Rows |Bytes |Cost (%CPU)|  TQ |IN-OUT|PQ Distrib|
------------------------------------------------------------------------------------
| 0|INSERT STATEMENT      |        |14768| 2985K|   15   (0)|     |      |          |
| 1| PX COORDINATOR       |        |     |      |           |     |      |          |
| 2|  PX SEND QC (RANDOM) |:TQ10000|14768| 2985K|   15   (0)|Q1,00| P->S |QC (RAND) |
| 3|   LOAD AS SELECT     |T_P_I   |     |      |           |Q1,00| PCWP |          |
| 4|    PX BLOCK ITERATOR |        |14768| 2985K|   15   (0)|Q1,00| PCWC |          |
| 5|     TABLE ACCESS FULL|T_P_S   |14768| 2985K|   15   (0)|Q1,00| PCWP |          |
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

16 rows selected.

SQL> explain plan for
  2  insert /*+ parallel(4) */ into t_p_i
  3  select *
  4  from t_p_s;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------
Plan hash value: 2808998595
-------------------------------------------------------------------------------------
|Id|Operation             |Name    |Rows |Bytes |Cost (%CPU)|  TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------
| 0|INSERT STATEMENT      |        |14768| 2985K|   15   (0)|     |      |          |
| 1| PX COORDINATOR       |        |     |      |           |     |      |          |
| 2|  PX SEND QC (RANDOM) |:TQ10000|14768| 2985K|   15   (0)|Q1,00| P->S |QC (RAND) |
| 3|   LOAD AS SELECT     |T_P_I   |     |      |           |Q1,00| PCWP |          |
| 4|    PX BLOCK ITERATOR |        |14768| 2985K|   15   (0)|Q1,00| PCWC |          |
| 5|     TABLE ACCESS FULL|T_P_S   |14768| 2985K|   15   (0)|Q1,00| PCWP |          |
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 4 because of hint

17 rows selected.

从这个小例子可以看到，如果指定表级的并行，那么必须在访问表的语句中，比如上面的例子中，如果对查询的表指定并行，将并行的HINT放到INSERT语句中是没有效果的。

而如果想要INSERT和SELECT同时并行执行，那么必须在INSERT和SELECT语句中分别指定查询和插入表的并行度。如果存在多个表的连接，并行设置还会更麻烦。

而通过语句级的并行设置很好的解决了这个问题，通过在第一个命令后添加不带表名的并行提示，使得这个语句中所有的子句都会使用并行。