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

Question

I have a PARALLEL(a,8) hint in a merge query. My server has 4 cpus with oracle 11.2.0.3.0 - 64bit

While executing merge query I disabled the parallel DDL and DML -in v$session 8 sessions were create.

While executing merge query I enabled the parallel DDL and DML -in v$session 16 sessions were created.

Why is this happening? Is there any explanation on this?

Additionally, I noticed that if the parallel DDL and DML are enabled

• for PARALLEL(a,2) : total 4 sessions were created
• for PARALLEL(a,4) : total 8 sessions were created

• for PARALLEL(a,8) : total 16 sessions were created

  ALTER SESSION DISABLE PARALLEL QUERY;
  ALTER SESSION DISABLE PARALLEL DML;
  ALTER SESSION DISABLE PARALLEL DDL;

  MERGE /*+ Parallel(a,8) */ BIGTABLE_1 a USING BIGTABLE_2 b ON (a.KEY = b.KEY) WHEN MATCHED THEN UPDATE SET a.Value1 = b.value1;

Additionally, on 10g documentation I read this

The default mode of a session is DISABLE PARALLEL DML. When parallel DML is disabled, no DML will be executed in parallel even if the PARALLEL hint is used.

https://docs.oracle.com/cd/B19306_01/server.102/b14223/usingpe.htm#CACCBEJC

Thanks in advance

Answer 1

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 |
------------------------------------------------