dml并行

Enabling Parallel DML
A DML statement can be parallelized only if you have explicitly enabled parallel DML in the session, as in the following statement:
关于dml并行必须在会话层面明确指定，不指定都是位并行

ALTER SESSION ENABLE PARALLEL DML;

下面查看例子

create table scott.t1 as select b.OBJECT_ID,b.OBJECT_NAME from dba_objects b ;
SQL> select count(1) from scott.t1;

COUNT(1)

608657

SQL> update /*+ parallel(a,2) */ scott.t1 a set OBJECT_NAME='hthorizon.com';

608657 rows updated.

Elapsed: 00:00:37.12

alter system flush buffer_cache;
alter system flush shared_pool;

SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.03
SQL> update /*+ parallel(a,2) */ scott.t1 a set OBJECT_NAME='hthorizon.com1';

608657 rows updated.

Elapsed: 00:00:13.56
真正的 parallel update更新比伪parallel块0.24秒，下面查看执行计划

SQL> explain plan for update /*+ parallel(a,2) */ scott.t1 a set OBJECT_NAME='hthorizon.com';

Explained.

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

PLAN_TABLE_OUTPUT

Plan hash value: 121765358

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

| 0 | UPDATE STATEMENT | | 1391K| 33M| 1011 (1)| 00:00:13 | | | |
| 1 | UPDATE | T1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWP | |

PLAN_TABLE_OUTPUT

上面并行更新时伪并行，只有在select时并行，真正的更新并行是发生在P->S（parallel_to_serial）之后;更新慢是慢在QC（query coordinator/查询协调)之上
SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for update /*+ parallel(a,2) */ scott.t1 a set OBJECT_NAME='hthorizon.com';

Explained.

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

PLAN_TABLE_OUTPUT

Plan hash value: 3991856572

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

| 0 | UPDATE STATEMENT | | 1391K| 33M| 1011 (1)| 00:00:13 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T1 | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWP | |

PLAN_TABLE_OUTPUT

从上述执行计划来看是真正的update并行
查看sql的实际执行计划（执行过的语句才能生成真实的执行计划）
SQL> select a.SQL_ID,a.EXECUTIONS,a.CHILD_number from v$sql a where a.SQL_TEXT like 'update /*+ parallel(a,2) */ scott.t1%';

SQL_ID EXECUTIONS CHILD_NUMBER

---

9fwr6kw5xwmq0 1 0

Elapsed: 00:00:00.00

SQL> select * from table(xplan.DISPLAY_cursor('9fwr6kw5xwmq0',0,'advanced'));

PLAN_TABLE_OUTPUT

SQL_ID 9fwr6kw5xwmq0, child number 0

update /*+ parallel(a,2) */ scott.t1 a set OBJECT_NAME='hthorizon.com1'

Plan hash value: 3991856572

---

| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

| 0 | 6 | UPDATE STATEMENT | | | | 1011 (100)| | | | |
| 1 | 5 | PX COORDINATOR | | | | | | | | |

PLAN_TABLE_OUTPUT

| 2 | 4 | PX SEND QC (RANDOM) | :TQ10000 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | P->S | QC (RAND) |
| 3 | 3 | UPDATE | T1 | | | | | Q1,00 | PCWP | |
| 4 | 2 | PX BLOCK ITERATOR | | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWC | |
|* 5 | 1 | TABLE ACCESS FULL| T1 | 1391K| 33M| 1011 (1)| 00:00:13 | Q1,00 | PCWP | |

Query Block Name / Object Alias (identified by operation id):

1 - UPD$1
5 - UPD$1 / A@UPD$1

PLAN_TABLE_OUTPUT

Outline Data

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")

PLAN_TABLE_OUTPUT

  FULL(@"UPD$1" "A"@"UPD$1")
  END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

5 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
2 - (#keys=0) SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
3 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
4 - (upd=2) "A".ROWID[ROWID,10], "OBJECT_NAME"[VARCHAR2,128]
5 - "A".ROWID[ROWID,10], "OBJECT_NAME"[VARCHAR2,128]

51 rows selected.