READ and WRITE parallelism are not always related to each other.
alter session disable parallel dml; disables parallelism for the WRITE part of the instruction. The READ part can work in parallel. Since this is a MERGE operation, a parallel prompt requests both read and write parallelism. In addition, the parallel prompt overrides alter session disable parallel query; although it does not cancel alter session disable parallel dml; .
The number of parallel servers will be twice the requested degree of parallelism to support production and consumer operations , in order to make full use of the inter-operation parallelism. Queries that group or order results will use twice as many threads. In some cases, this can happen even if there is no explicit GROUP BY or ORDER BY , because some operations may implicitly require sorting.
Sample Tables
create table bigtable_1(key number, value1 number); create table bigtable_2(key number, value1 number);
Concurrent read and write
Pay attention to PX COORDINATOR for operation No. 1. When this step is above MERGE , it means that recording is performed in parallel.
rollback; alter session enable parallel dml; alter session enable parallel query; explain plan for merge 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
Sequential write, parallel read
Now the MERGE operation is primarily the PX ... operation. Writing is done in batches, but reading is still done in parallel.
rollback; alter session disable parallel dml; alter session disable parallel query; explain plan for merge 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