Why parallel sessions are created even when i disable parallel DML and parallel DDL

READ and WRITE parallelism are not always tied together.

alter session disable parallel dml; only disables parallelism for the WRITE part of the statement. The READ part may still run in parallel. Since this is a MERGE operation, the parallel hint requests both read and write parallelism. Also, a parallel hint overrides alter session disable parallel query;, even though it does not override alter session disable parallel dml;.

The number of parallel servers will be twice the requested Degree of Parallelism to support producer and consumer operations, in order to fully utilize inter-operation parallelism. Queries that group or order the results will use twice as many threads. In some cases this may happen even if there is no explicit GROUP BY or ORDER BY because some operations may implicitly require a sort.

Sample tables

create table bigtable_1(key number, value1 number);create table bigtable_2(key number, value1 number);

Parallel read and write

Note the PX COORDINATOR for operation #1. When that step is above the MERGE it means the writing is done in parallel.

rollback;alter session enable parallel dml;alter session enable parallel query;explain plan for merge /*+ parallel(a,8) */ into bigtable_1 a using bigtable_2 b  on (a.key = b.key) when matched then update set a.value1 = b.value1;select * from table(dbms_xplan.display(format => 'basic'));Plan hash value: 827272579------------------------------------------------------| Id  | Operation                       | Name       |------------------------------------------------------|   0 | MERGE STATEMENT                 |            ||   1 |  PX COORDINATOR                 |            |  <-- PARALLEL WRITE|   2 |   PX SEND QC (RANDOM)           | :TQ10003   ||   3 |    MERGE                        | BIGTABLE_1 ||   4 |     PX RECEIVE                  |            |  <-- PARALLEL READ|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10002   ||   6 |       VIEW                      |            ||   7 |        HASH JOIN BUFFERED       |            ||   8 |         BUFFER SORT             |            ||   9 |          PX RECEIVE             |            ||  10 |           PX SEND HASH          | :TQ10000   ||  11 |            TABLE ACCESS FULL    | BIGTABLE_2 ||  12 |         PX RECEIVE              |            ||  13 |          PX SEND HASH           | :TQ10001   ||  14 |           PX BLOCK ITERATOR     |            ||  15 |            TABLE ACCESS FULL    | BIGTABLE_1 |------------------------------------------------------

Serial write, parallel read

Now the MERGE operation is above all PX ... operations. The write is done serially, but the read is still done in parallel.

rollback;alter session disable parallel dml;alter session disable parallel query;explain plan for merge /*+ parallel(a,8) */ into bigtable_1 a using bigtable_2 b  on (a.key = b.key) when matched then update set a.value1 = b.value1;select * from table(dbms_xplan.display(format => 'basic'));Plan hash value: 1648019208------------------------------------------------| Id  | Operation                 | Name       |------------------------------------------------|   0 | MERGE STATEMENT           |            ||   1 |  MERGE                    | BIGTABLE_1 |  <-- SERIAL WRITE|   2 |   PX COORDINATOR          |            |  <-- PARALLEL READ|   3 |    PX SEND QC (RANDOM)    | :TQ10002   ||   4 |     VIEW                  |            ||   5 |      HASH JOIN BUFFERED   |            ||   6 |       BUFFER SORT         |            ||   7 |        PX RECEIVE         |            ||   8 |         PX SEND HASH      | :TQ10000   ||   9 |          TABLE ACCESS FULL| BIGTABLE_2 ||  10 |       PX RECEIVE          |            ||  11 |        PX SEND HASH       | :TQ10001   ||  12 |         PX BLOCK ITERATOR |            ||  13 |          TABLE ACCESS FULL| BIGTABLE_1 |------------------------------------------------