我一直认为对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;
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 | |
| 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.