對DML使用並行的誤解，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 | |
| 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.
