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.
