Database Reference
In-Depth Information
Except for INSERT statements, parallel queries must also be enabled to execute DML statements in parallel. In
fact, DML statements are basically composed of two operations: the first finds the rows to be modified, and the second
modifies them. The problem is that if the part that finds the rows isn't executed in parallel, the part that modifies the
rows can't be parallelized. To illustrate this behavior, let's look at several examples based on the px_dml.sql script.
When only parallel DML statements are enabled, no operation is parallelized:
SQL> ALTER SESSION DISABLE PARALLEL QUERY;
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER TABLE t PARALLEL 2;
SQL> UPDATE t SET id = id + 1;
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | T |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
When only parallel queries are enabled, the update part of the DML statement isn't executed
in parallel. In fact, only operations 3 to 5 are executed by slave processes. Therefore, the
update part (operation 1) is executed serially by the query coordinator:
SQL> ALTER SESSION ENABLE PARALLEL QUERY;
SQL> ALTER SESSION DISABLE PARALLEL DML;
SQL> ALTER TABLE t PARALLEL 2;
SQL> UPDATE t SET id = id + 1;
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | T | | | |
| 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| T | Q1,00 | PCWP | |
-----------------------------------------------------------------------
Search WWH ::




Custom Search