Oracle DML并行

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题: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;

此条目发表在 Oracle性能优化 分类目录。将固定链接加入收藏夹。

评论功能已关闭。