最近才发现并行提示增加了语句级并行的功能。
以前添加并行都是对指定的表添加，最近才发现，如果不加表名，是指定这个语句的并行度：
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语句中分别指定查询和插入表的并行度。如果存在多个表的连接，并行设置还会更麻烦。
而通过语句级的并行设置很好的解决了这个问题，通过在第一个命令后添加不带表名的并行提示，使得这个语句中所有的子句都会使用并行。