在执行合并查询时,我禁用了并行DDL和DML
-in v $session创建了8个会话.
在执行合并查询时,我启用了并行DDL和DML
-in v $session创建了16个会话.
为什么会这样？对此有什么解释吗？
另外,我注意到如果启用了并行DDL和DML
>对于PARALLEL(a,2)：创建了总共4个会话
>对于PARALLEL(a,4)：创建了总共8个会话
>对于PARALLEL(a,8)：共创建了16个会话
ALTER SESSION DISABLE ALLALL QUERY;
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DDL;
MERGE / * Parallel(a,8)* / BIGTABLE_1 a
使用BIGTABLE_2 b
ON(a.KEY = b.KEY)
当匹配更新时
SET a.Value1 = b.value1;
另外,在10g文档中我读到了这个
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
提前致谢
alter session disable parallel dml;仅禁用语句的WRITE部分的并行性. READ部分可能仍然并行运行.由于这是MERGE操作,因此并行提示请求读写并行写入.此外,并行提示覆盖alter session disable parallel query;,即使它不覆盖alter session disable parallel dml;.
并行服务器的数量将是所请求的并行度的两倍,以支持producer and consumer operations,以便充分利用互操作并行性.对结果进行分组或排序的查询将使用两倍的线程.在某些情况下,即使没有显式的GROUP BY或ORDER BY,也可能会发生这种情况,因为某些操作可能隐式需要排序.
样本表
create table bigtable_1(key number,value1 number); create table bigtable_2(key number,value1 number);
并行读写
注意操作#1的PX COORDINATOR.当该步骤高于MERGE时,意味着写入是并行完成的.
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 | ------------------------------------------------------
串行写入,并行读取
现在,MERGE操作首先是PX …操作.写操作是串行完成的,但读操作仍然是并行完成的.
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 | ------------------------------------------------
