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

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

i have parallel(a,8) hint in merge query. server has 4 cpus oracle 11.2.0.3.0 - 64bit

while executing merge query disabled parallel ddl , dml -in v$session 8 sessions create.

while executing merge query enabled parallel ddl , dml -in v$session 16 sessions created.

why happening? there explanation on this?

additionally, noticed if parallel ddl , dml enabled

for parallel(a,2) : total 4 sessions created for parallel(a,4) : total 8 sessions created

for parallel(a,8) : total 16 sessions created

alter session disable parallel query; alter session disable parallel dml; alter session disable parallel ddl;

merge /*+ parallel(a,8) */ bigtable_1 using bigtable_2 b on (a.key = b.key) when matched update set a.value1 = b.value1;

additionally, on 10g documentation read this

the default mode of session disable parallel dml. when parallel dml disabled, no dml executed in parallel if parallel hint used.

https://docs.oracle.com/cd/b19306_01/server.102/b14223/usingpe.htm#caccbejc

thanks in advance

read , write parallelism not tied together.

alter session disable parallel dml; disables parallelism write part of statement. read part may still run in parallel. since merge operation, parallel hint requests both read , write parallelism. also, parallel hint overrides alter session disable parallel query;, though not override alter session disable parallel dml;.

the number of parallel servers twice requested grade of parallelism back upwards producer , consumer operations, in order utilize inter-operation parallelism. queries grouping or order results utilize twice many threads. in cases may happen if there no explicit group by or order by because operations may implicitly require sort.

sample tables

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

parallel read , write

note px coordinator operation #1. when step above merge means writing done in parallel.

rollback; alter session enable parallel dml; alter session enable parallel query; explain plan merge /*+ parallel(a,8) */ bigtable_1 using bigtable_2 b on (a.key = b.key) when matched update set a.value1 = b.value1; select * 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 bring together buffered | | | 8 | buffer sort | | | 9 | px receive | | | 10 | px send hash | :tq10000 | | 11 | table access total | bigtable_2 | | 12 | px receive | | | 13 | px send hash | :tq10001 | | 14 | px block iterator | | | 15 | table access total | bigtable_1 | ------------------------------------------------------

serial write, parallel read

now merge operation above px ... operations. write done serially, read still done in parallel.

rollback; alter session disable parallel dml; alter session disable parallel query; explain plan merge /*+ parallel(a,8) */ bigtable_1 using bigtable_2 b on (a.key = b.key) when matched update set a.value1 = b.value1; select * 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 bring together 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 | ------------------------------------------------

oracle oracle11g query-optimization