对DML使用并行的误解 - 数据库编程

我一直认为对DML使用并行，只需要加上parallel的hint就完事了。其实不是，要使用alter session force parallel dml才是真正的并行操作。下面来做个试验：

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;
SQL> insert into test select * from test;
SQL> insert into test select * from test;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(user,'test');

SQL> set timing on
SQL> update test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:05.06
SQL> commit;

SQL> update test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:03.68 --不使用并行,update的时间
SQL> commit;

SQL> update /*+parallel(t,4)*/ test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:03.81
SQL> commit;

SQL> update /*+parallel(t,4)*/ test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:03.31 --使用了并行，但效果不明显
SQL> commit;

SQL> alter session force parallel dml;

或者 alter session enable parallel dml;

SQL> update /*+parallel(t,4)*/ test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:00.51
SQL> commit;

SQL> update /*+parallel(t,4)*/ test t set object_name='ggg';
318988 rows updated.
Elapsed: 00:00:00.38 --效果非常明显
SQL> commit;

为什么会这样呢？再开一个session，观察下它们的执行计划，使用alter session force parallel dml之前并行使用在全表扫描test表上，使用之后，并行才使用在update上：

SQL> EXPLAIN PLAN FOR update /*+parallel(t,4)*/ test t set object_name='ggg';
Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3695425075
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 318K| 7787K| 245 (1)| 00:00:04 | | | |
| 1 | UPDATE | TEST | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 318K| 7787K| 245 (1)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 318K| 7787K| 245 (1)| 00:00:04 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TEST | 318K| 7787K| 245 (1)| 00:00:04 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
12 rows selected.
SQL> alter session force parallel dml;

SQL> EXPLAIN PLAN FOR update /*+parallel(t,4)*/ test t set object_name='ggg';
Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 2059761527
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 318K| 7787K| 245 (1)| 00:00:04 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 318K| 7787K| 245 (1)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | TEST | | | | | Q1,00 | PCWP | |

对DML使用并行的误解(一)