READ和WRITE并行性并不總是捆綁在一起.

alter session disable parallel dml;僅禁用語(yǔ)句的WRITE部分的并行性. READ部分可能仍然并行運(yùn)行.由于這是MERGE操作,因此并行提示請(qǐng)求讀寫(xiě)并行寫(xiě)入.此外,并行提示覆蓋alter session disable parallel query;,即使它不覆蓋alter session disable parallel dml;.

并行服務(wù)器的數(shù)量將是所請(qǐng)求的并行度的兩倍,以支持producer and consumer operations,以便充分利用互操作并行性.對(duì)結(jié)果進(jìn)行分組或排序的查詢將使用兩倍的線程.在某些情況下,即使沒(méi)有顯式的GROUP BY或ORDER BY,也可能會(huì)發(fā)生這種情況,因?yàn)槟承┎僮骺赡茈[式需要排序.

樣本表

create table bigtable_1(key number,value1 number);

create table bigtable_2(key number,value1 number);

并行讀寫(xiě)

注意操作#1的PX COORDINATOR.當(dāng)該步驟高于MERGE時(shí),意味著寫(xiě)入是并行完成的.

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

| 2 | PX SEND QC (RANDOM) | :TQ10003 |

| 3 | MERGE | BIGTABLE_1 |

| 4 | PX RECEIVE | |

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

------------------------------------------------------

串行寫(xiě)入,并行讀取

現(xiàn)在,MERGE操作首先是PX …操作.寫(xiě)操作是串行完成的,但讀操作仍然是并行完成的.

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 |

| 2 | PX COORDINATOR | |

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

------------------------------------------------

總結(jié)

以上是生活随笔為你收集整理的oracle 并行执行 杀掉会话,oracle – 为什么即使我禁用并行DML和并行DDL也会创建并行会话...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。