联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle DML并行
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1、UPDATE 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei'; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 121765358 ----------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | | | 1 | UPDATE | T1 | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | -------------------------------------------------------------------------- SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei'; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3308547044 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | INDEX MAINTENANCE | T1 | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 6 | UPDATE | T1 | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | --------------------------------------------------------------------------
通过执行计划可以看出，只有执行了ALTER SESSION ENABLE PARALLEL DML后，UPDATE操作才真正的实现了并行操作，如果不执行该语句，只是执行了并发查询，并没有实现并发更新操作
2、DELETE 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3718066193 ----------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------- | 0 | DELETE STATEMENT | | | | | | 1 | DELETE | T1 | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | ----------------------------------------------------------------------- SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2132458150 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | INDEX MAINTENANCE | T1 | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 6 | DELETE | T1 | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | --------------------------------------------------------------------------
试验证明，也需要执行ALTER SESSION ENABLE PARALLEL DML，才能够实现真正的删除并发操作
3、INSERT 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR INSERT /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2494645258 ---------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | ---------------------------------------------------------------------- SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL> EXPLAIN PLAN FOR INSERT /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 783041698 ------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T_1 | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND ROUND-ROBIN| :TQ10000 | Q1,00 | P->P | RND-ROBIN | | 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | -------------------------------------------------------------------------
通过这个执行计划可以发现，数据是被使用APPEND方式插入到表中，如果需要常规方式插入，需要加上noappend提示，同样设置了session 并行dml才能够实现真正意义上的插入并发操作
4、总结
通过上面的试验可以得出，如果要DML实现真正意义上的并发，在开始执行需要并发语句前，需要执行开启session并发
ALTER SESSION ENABLE PARALLEL DML;
在执行完语句后，需要执行关闭session并发
ALTER SESSION DISABLE PARALLEL DML;