Misunderstanding of DML's use of parallelism and misunderstanding of DML's use of Parallelism
I always think that we can use parallelism for DML. We only need to add the hint of parallel. Actually, no. Using alter session force parallel dml is the real parallel operation. Here is an experiment:
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 -- Parallel update time is not used.
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 -- Parallel is used, but the effect is not obvious.
SQL> commit;
SQL>Alter session force parallel dml;
Or alter session enableParallel 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 -- very effective
SQL> commit;
Why? Open another session, observe their execution plan, and use alter session force parallel dml to scan the test table in the whole table in parallel, and then use it in the 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
Bytes ---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3695425075
Bytes ---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ---------------------------------------------------------------------------------------------------------------
| 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 |
Bytes ---------------------------------------------------------------------------------------------------------------
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
Bytes -------------------------------------------------------------------------------------------------------------------
Plan hash value: 2059761527
Bytes ---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ---------------------------------------------------------------------------------------------------------------
| 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 |
Bytes ---------------------------------------------------------------------------------------------------------------
12 rows selected.
