Plan hash value: 4064487821
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2718 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | P->S | Q
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
-automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
已选择66行。
已用时间: 00: 00: 00.40
如果不使用并行操作，进行如此规模的insert操作，会如何呢？
//使用noparallel的hint进行并行抑制；
SQL>insert /*+ noparallel */ into t select * from t;
10039808 rows inserted
Executed in 87.813 seconds
对应的执行计划如下：
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'insert /*+ noparallel */%';
SQL_TEXT SQL_ID VERSION_COUNT
-------------------------------------------------- ------------- -------------
insert /*+ noparallel */ into t select * from t 9u0xcrr3bcjs1 1
Executed in 0.234 seconds
SQL> select * from table(dbms_xplan.display_cursor('9u0xcrr3bcjs1',format => 'advanced',cursor_child_no => 0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9u0xcrr3bcjs1, child number 0
-------------------------------------
insert /*+ noparallel */ into t select * from t
Plan hash value: 2153619298
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 19601 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | TABLE ACCESS FULL | T | 5019K| 469M| 19601 (1)| 00:03:56 |
---------------------------------------------------------------------------------
4、结论
本篇对PDML进行了简单的介绍，包括使用方法和并行度设置。由于篇幅原因，只介绍了并行insert和并行统计量的收集。并行update和delete本质相同，就不加以累述了。
最后，并行操作是一种带有特殊性的操作，绝对不要将其轻易作为经常性无监管下的操作。
作者zhangpeng012310