溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“怎么理解Oracle的并行執行”，在日常操作中，相信很多人在怎么理解Oracle的并行執行問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么理解Oracle的并行執行”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
S： 時間單位秒。
K： 數量單位一千。
M： 數量單位一百萬， 或者時間單位分鐘。
DoP： Degree of Parallelism， 并行執行的并行度。
QC： 并行查詢的 Query Coordinator。
PX 進程： Parallel Execution Slaves。
AAS： Average active session， 并行執行時平均的活動會話數。
分發： pq distribution method， 并行執行的分發方式， 包括 replicate， broadcast， hash 和 adaptive分發等 4 種方式， 其中 adaptive 分發是 12c 引入的的新特性， 我將在本篇文章中一一闡述。
Hash join 的左邊： 驅動表， the build side of hash join， 一般為小表。
Hash join 的右邊： 被驅動表， the probe side of hash join， 一般為大表。
布隆過濾： bloom filter， 一種內存數據結構， 用于判斷一個元素是否屬于一個集合。
Oracle版本為12.1.0.2.2，兩個節點的RAC，硬件為ExadataX3--‐8。
這是一個典型的星型模型，事實表lineorder有3億行記錄，維度表part/customer分別包含1.2M
和1.5M行記錄，3個表都沒有進行分區，lineorder大小接近30GB。
select owner seg_owner， segment_name seg_segment_name， round(bytes/1048576，2) SEG_MB from dba_segments where owner = 'SID' and segment_name in ('LINEORDER'，'PART'，'CUSTOMER') / OWNER SEGMENT_NAME SEGMENT_TYPE SEG_MB ------ ------------ ------------ -------- SID LINEORDER TABLE 30407.75 SID CUSTOMER TABLE 168 SID PART TABLE 120
本篇文章所有的測試，除非特別的說明，我關閉了12c的adaptive plan特性，參數optimizer_adaptive_features被默認設置為false。Adaptive相關的特性如cardinality feedback，adaptive distribution method，adaptive join都不會啟用。如果檢查執行計劃的outline數據，你會發現7個優化器相關的隱含參數被設置為關閉狀態。事實上，12c優化器因為引入adaptive plan特性，比以往版本復雜得多，剖析12c的優化器的各種新特性，我覺得非常具有挑戰性，或許我會在另一篇文章里嘗試一下。
select * from table(dbms_xplan.display_cursor('77457qc9a324k',0,’outline’)); ... Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_dsdir_usage_control' 0) OPT_PARAM('_optimizer_adaptive_plans' 'false') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') OPT_PARAM('optimizer_dynamic_sampling' 11) ALL_ROWS …… END_OUTLINE_DATA */
以下sql對customers和lineorder連接之后，計算所有訂單的全部利潤。 串行執行時不使用parallel hint：
select /*+ monitor */ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;
串行執行時，sql執行時間為1.5分鐘，dbtime為1.5分鐘。執行計劃有5行，一個用戶進程工作完成了對customer，lineorder兩個表的掃描，hashjoin，聚合以及返回數據的所有操作。此時AAS(average active sessions)為1，sql執行時間等于db time。幾乎所有的dbtime都為db cpu，72%的cpu花在了第二行的hash join操作。因為測試機器為一臺Exadata X3——8，30GB的IO請求在一秒之內處理完成。Celloffload Efficiency等于87%意味著經過存儲節點掃描，過濾不需要的列，最終返回計算節點的數據大小只有30GB的13%。
使用hint parallel(4)，指定DoP=4并行執行同樣的sql：
select /*+ monitor parallel(4)*/ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;
SQL執行時間為21s，db time為1.4分鐘。DoP=4，在兩個實例上執行。執行計劃從5行增加為9行，從下往上分別多了’PXBLOCKITERATOR’， ‘SORTAGGREGATE’， ‘PXSENDQC(RANDOM)’ 和 ’PXCOORDINATOR’ 這四個操作。
其中3到8行的操作為并行處理，sql的執行順序為：每個PX進程掃描維度表customer(第6行)，以數據塊地址區間作為單位(第7行)掃描四分之一的事實表lineorder(第8行)，接著進行hash join(第5行)，然后對連接之后的數據做預先聚合(第4行)，最后把結果給QC(第三行)。QC接收數據(第2行)之后，做進一步的匯總(第1行)，最后返回數據(第0行)。
SQL執行時間比原來快了4倍，因為最消耗時間的操作，比如對lineorder的全表掃描，hashjoin和聚合，我們使用4個進程并行處理，因此最終sql執行時間為串行執行的1/4。另一方面，dbtime并沒有明顯下降，并行時1.4m，串行時為1.5m，從系統的角度看，兩次執行消耗的系統資源是一樣的。
DoP=4時，因為沒有涉及數據的分發(distribution)，QC只需分配一組PX進程，四個PX進程分別為實例1和2的p000/p0001。我們可以從系統上查看這4個PX進程。每個PX進程消耗大致一樣的db time，CPU和IO資源。AAS=4，這是最理想的情況，每個PX進程完成同樣的工作量，一直保持活躍。沒有串行點，沒有并行執行傾斜。
AAS=4，查看活動信息時，為了更好的展示活動信息，注意點掉”CPU Cores”這個復選框。
在Linux系統上顯示這四個PX進程。
[oracle@exa01db01 sidney]$ ps -ef | egrep "p00[01]_SSB" oracle 20888 1 4 2014 ? 18:50:59 ora_p000_SSB1 oracle 20892 1 4 2014 ? 19:01:29 ora_p001_SSB1 [oracle@exa01db01 sidney]$ ssh exa01db02 'ps -ef | egrep "p00[01]_SSB"' oracle 56910 1 4 2014 ? 19:01:03 ora_p000_SSB2 oracle 56912 1 4 2014 ? 18:53:30 ora_p001_SSB2
本節的例子中，DoP=4，并行執行時分配了4個PX進程，帶來4倍的性能提升。SQL monitor報告包含了并行執行的總體信息和各種細節，比如QC，DoP，并行執行所在的實例，每個PX進程消耗的資源，以及執行SQL時AAS。
在上面并行執行的例子中，每個px進程都會掃描一遍維度表customer，然后掃描事實表lineorder進行hash join。這時沒有數據需要進行分發，只需要分配一組px進程。這種replicate維度表的行為，是12c的新特性，由參數_px_replication_enabled控制。
更常見情況是并行執行時，QC需要分配兩組PX進程，互為生產者和消費者協同工作，完成并行執行計劃。架構圖1如下：
為了舉例說明兩組px進程如何協作的，設置_px_replication_enabled為false。QC會分配兩組PX進程，一組為生產者，一組為消費者。
見下圖，此時sql執行時間為23s，執行時間變慢了2s，dbtime仍為1.5分鐘。
最大的變化來自執行計劃，現在執行計劃有12行。增加了對customer的并行掃描 PXBLOCKITERATOR (第8行)，分發’PXSENDBROADCAST’和接收’PXRECEIVE’。執行計劃中出現了兩組PX進程，除了之前藍色的多人標志，現在出現了紅色的多人標志。此時，SQL的執行順序為：
4個紅色的PX進程扮演生產者角色，掃描維度表customer，把數據通過broadcast的方式分發給每一個扮演消費者的藍色PX進程。因為DoP=4，每一條被掃描出來的記錄被復制了4份，從sqlmonitor的第9行，customer全表掃描返回1。5m行數據，第8行的分發和第7行的接受之時，變成了6m行記錄，每個作為消費者的藍色px進程都持有了一份完整包含所有custome記錄的數據，并準備好第5行hashjoin的buildtable。
4個作為消費者的藍色PX進程，以數據塊地址區間為單位掃描事實表lineorder(第10/11行);同時和已經持有的customer表的數據進hashjoin(第5行)，然后對滿足join條件的數據做預聚合(第4行)，因為我們查詢的目標是對所有lo_revenue求和，聚合之后每個PX進程只需輸出一個總數。
4個藍色的PX進程反過來作為生產者，把聚合的數據發給消費者QC(第3行和第2行)。由QC對接收到4行記錄做最后的聚合，然后返回給用戶。
使用broadcast的分發方式，只需要把customer的數據廣播給每個消費者。Lineorder的數不需要重新分發。因為lineorder的數據量比customer大的多，應該避免對lineorder的數據進行分發，這種執行計劃非常適合星型模型的數據。
觀察sql monitor報告中Parallel標簽下的信息，紅色的PX進程為實例1、2上的p002/p003進程，藍色的PX進程為p000/p001進程，因為藍色的PX進程負責掃描事實表lineorder，hash join和聚合，所以消耗幾乎所有的db time。
并行查詢之后，可以通過視圖V$PQ_TQSTAT，驗證以上描述的執行過程。
實例1、2上的p002/p003進程作為生產者，幾乎平均掃描customer的1/4記錄，把每一條記錄廣播給4個消費者PX進程，發送的記錄數之和為6m行。通過table queue0(TQ_ID=0)，每個作為消費者的p000/p001進程，接收了完整的1。5m行customer記錄，接收的記錄數之和為6m行。
實例1、2上的p000/p0001進程作為生產者，通過table queue1(TQ_ID=1)，把聚合的一條結果記錄發給作為消費者的QC。QC作為消費者，接收了4行記錄。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- -------------- ---------- --------- ---------- 1 0 Producer 1 P002 1461932 1 0 Producer 1 P003 1501892 1 0 Producer 2 P002 1575712 1 0 Producer 2 P003 1460464 1 0 Consumer 1 P000 1500000 1 0 Consumer 1 P001 1500000 1 0 Consumer 2 P000 1500000 1 0 Consumer 2 P001 1500000 1 1 Producer 1 P000 1 1 1 Producer 1 P001 1 1 1 Producer 2 P000 1 1 1 Producer 2 P001 1 1 1 Consumer 1 QC 4 13 rows selected.
那么，以上的輸出中，DFO_NUMBER和TQ_ID這兩列表示什么意思呢?
DFO代表Data Flow Operator，是執行計劃中可以并行執行的操作。一個QC代表一棵DFO樹(tree)，包含多個DFO;同一個QC中所有并行操作的DFO_NUMBER是相同的，此例中，所有DFO_NUMBER為1。執行計劃包含多個QC的例子也不少見，比如使用unionall的語句，unionall每個分支都是獨立的DFO樹，不同的DFO樹之間可以并行執行。本篇文章僅討論執行計劃只有一個QC的情況。
TQ代表table queue，用以PX進程之間或者和QC通信連接。以上執行計劃中，table queue0為PX進程之間的連接，table queue1為PX進程和QC之間的連接。生產者通過table queue分發數據，消費者從tablequeue接收數據。不同的table queue編號，代表了不同的數據分發。通過table queue，我們可以理解Oracle并行執行使用生產者--‐消費者模型的本質：
同一棵DFO樹中，最多只有兩組PX進程。每個生產者進程都存在一個和每個消費者進程的連接，每個PX進程和QC都存在一個連接。假設DoP=n，連接總數為(n*n+2*n)，隨著n的增長，連接總數會爆炸型增長。Oracle并行執行設計時，采用生產者和消費者模型，考慮到連接數的復雜度，每個DFO最多只分配兩組PX進程。假設DoP=100時，兩組PX進程之間的連接總數為10000。假設可以分配三組PX進程一起完成并行執行計劃，那么三組PX之間連接總數會等于1百萬，維護這么多連接，是一個不可能的任務。
同一棵DFO樹中，兩組PX進程之間，同一時間只存在一個活躍的數據分發。如果執行路徑很長，數據需要多次分發，兩組PX進程會變換生產者消費者角色，相互協作，完成所有并行操作。每次數據分發，對應的tablequeue的編號不同。一個活躍的數據分發過程，需要兩組PX進程都參與，一組為生產者發送數據，一組為消費者接收數據。因為一個DFO里最多只有兩組PX進程，意味著，PX進程之間，同一時間只能有一個活躍的數據分發。如果PX進程在執行計劃中需要多次分發數據，可能需要在執行計劃插入一些阻塞點，比如BUFFERSORT和HASHJOINBUFFERED這兩個操作，保證上一次的數據分發完成之后，才開始下一次分發。在后面的章節，我將會說明這些阻塞點帶來什么影響。這個例子中，tablequeue0和1可以同時工作是因為：tablequeue0是兩組PX進程之間的鏈接，tablequeue1為PX進程和QC之間的連接，tablequeue0與tablequeue1是相互獨立的，因此可以同時進行。
PX進程之間或者與QC的連接至少存在一個(單節點下至多三個，RAC環境下至多四個)消息緩沖區用于進程間數據交互，該消息緩沖區默認在Largepool中分配(如果沒有配置Largepool則在Sharedpool中分配)。多個緩沖區是為了實現異步通信，提高性能。
每個消息緩沖區的大小由參數parallel_execution_message_size控制，默認為16k。
當兩個進程都在同一個節點的時候，通過在Largepool(如果沒有配置Largepool則Sharedpool)中傳遞和接收消息緩沖進行數據交互。當兩個進程位于不同節點時。通過RAC心跳網絡進行數據交互，其中一方接收的數據需要緩存在本地Largepool(如果沒有配置Largepool則Sharedpool)里面。
為了說明并行執行的生產者--消費者模型是如何工作的，我使用了broad cast分發，QC分配兩組PX進程，一組為生產者，一組為消費者。QC和PX進程之間，兩組PX進程之間通過table queue進行數據分發，協同完成整個并行執行計劃。視圖V$PQ_TQSTAT記錄了并行執行過程中，數據是如何分發的。通過對DFO，table queue的描述，我闡述生產者--‐消費者模型的工作原理和通信過程，或許有些描述對你來說過于突然，不用擔心，后面的章節我會通過更多的例子來輔助理解。
Table queue 的編號代表了并行執行計劃中，數據分發的順序。理解執行計劃中的并行操作是如何被執行的，原則很簡單：跟隨Tablequeue的順序。
通過sqlmonitor報告判斷sql的執行順序，需要結合name列的tablequeue名字比如：TQ10000(代表DFO=1，tablequeue0)，：TQ10001(代表DFO=1，tablequeue1)，還有PX進程的顏色，進行確定。
下面的例子為dbms_xplan。display_cursor 的輸出。對于并行執行計劃，會多出來三列：
1. TQ列：為Q1：00或者Q1：01，其中Q1代表第一個DFO，00或者01代表tablequeue的編號。
a. ID7~9的操作的TQ列為Q1，00，該組PX進程，作為生產者首先執行，然后通過broadcast 的分發方式，把數據發給消費者。
b. ID10~11，3~6的操作的TQ列為Q1，01，該組PX進程作為消費者接受customer的數據之后，掃描lineorder，hashjoin，聚合之后，又作為生產者通過tablequeue2把數據發給QC。
2. In--‐out 列：表明數據的流動和分發。
? PCWC：parallelcombinewithchild。
? PCWP：parallelcombinewithparent。
? P--‐>P： paralleltoparallel。
? P--‐>S： paralleltoSerial。
3. PQDistribute 列：數據的分發方式。此執行計劃中，我們使用了broadcast 的方式，下面的章節
我會講述其他的分發方式。
除了broadcast分發方式，另一種常見的并行分發方式為hash。為了觀察使用hash分發時sql的 執行情況，我對sql使用pq_distributehint。
select /*+ monitor parallel(4) leading(customer lineorder) use_hash(lineorder) pq_distribute(lineorder hash hash) */ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;
使用hash分發方式時，sql的執行時間為29s，dbtime為2.6m。相對于broadcast方式，sql的執行時間和dbtime都增加了大約40%。
執行計劃如下，執行計劃為14行，增加了對lineorder的hash分發，第11行的’PXSENDHASH’對3億行數據通過hash函數分發，第10行的’PXRECEIVE’通過tablequeue1接收3億行數據，這兩個操作消耗了38%的dbcpu。這就是為什么SQL執行時間和dbtime變長的原因。此時，SQL的執行順序為：
紅色的PX進程作為生產者，并行掃描customer(第8~9行)，對于連接鍵c_custkey運用函數，根據每行記錄的hash值，通過tablequeue0，發給4個藍色消費者的其中一個(第7行)。Hash分發方式并不會復制數據，sqlmonitor報告的第6~9行，actualrows列都為1.5m。
紅色的PX進程作為生產者，并行掃描li neorder(第12~13行)，對于連接鍵lo_custkey運用同樣的dhash函數，通過tablequeue1，發給4個藍色消費者的其中一個(第11行)。同樣的hash函數保證了customer和lineorder相同的連接鍵會發給同一個消費者，保證hashjoin結果的正確。因為3億行數據都需要經過hash函數計算，然后分發(這是進程間的通信，或者需要通過RAC心跳網絡通信)，這些巨大的額外開銷，就是增加38%cpu的原因。
4個藍色的PX進程作為消費者接收了customer的1.5M行記錄(第 6 行)，和lineorder的3億行記錄(第10行)，進行hash join(第5行)，預聚合(第4行)。
4個藍色的PX進程反過來作為生產者，通過table queue2，把聚合的數據發給消費者QC(第3 行和第2行)。由QC對接收到4行記錄做最后的聚合， 然后返回給用戶(第1和0行)。
觀察sql monitor報告中Parallel標簽下的信息，紅色的px進程為實例1、2上的p002/p003進程，藍色的PX進程為p000/p001進程。作為生產者的紅色PX進程負責掃描事實表lineorder，對3億行數據進行hash分發，占了超過1/3的db time。
因為涉及3億行數據的分發和接收，作為生產者的紅色PX進程和作為消費者的藍色PX進程需要同時活躍，SQL monitor報告中的activity信息顯示大部分時間，AAS超過并行度4，意味這兩組PX進程同時工作。不像replicate或者broadcast分發時，AAS為4，只有一組PX進程保持活躍。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- -------------- ---------- --------- ---------- 1 0 Producer 1 P002 299928364 1 0 Producer 1 P003 299954384 1 0 Producer 2 P002 300188788 1 0 Producer 2 P003 299951708 1 0 Consumer 1 P000 300005811 1 0 Consumer 1 P001 300005811 1 0 Consumer 2 P000 300005811 1 0 Consumer 2 P001 300005811 1 1 Producer 1 P000 1 1 1 Producer 1 P001 1 1 1 Producer 2 P000 1 1 1 Producer 2 P001 1 1 1 Consumer 1 QC 4 13 rows selected.
select /*+ monitor parallel(4)*/ sum(lo1.lo_revenue) from lineorder_hash42 lo1, lineorder_hash42 lo2 where lo1.lo_orderkey = lo2.lo_orderkey;
并行查詢之后，通過視圖V$PQ_TQSTAT，進一步驗證以上描述的執行過程。并行執行過程涉及3
個tablequeue0/1/2，V$PQ_TQSTAT包含21行記錄。
1. 實例1、2上的p002/p003進程作為生產者，平均掃描customer的1/4記錄，然后通過tablequeue0(TQ_ID=0)，發給作為消費者的p000/p001進程。發送和接收的customer記錄之和都為 1.5m。
? 發送的記錄數：1500000= 365658+364899+375679+393764
? 接收的記錄數：1500000= 374690+374924+375709+374677
2. 實例1、2上的p002/p0003進程作為生產者，平均掃描lineorder的1/4記錄，通過table queue1(TQ_ID=1) ，發給作為消費者的p000/p001進程。發送和接收的lineorder 記錄之和都為300005811。
? 發送的記錄數：300005811= 74987629+75053393+74979748+74985041
? 接收的記錄數：300005811= 74873553+74968719+75102151+75061388
3. 實例1、2上的p000/p0001進程作為生產者，通過tablequeue2(TQ_ID=2)，把聚合的一條結果記 錄發給作為消費者的QC。QC作為消費者，接收了4行記錄。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- ---------------- ---------- --------- ---------- 1 0 Producer 1 P002 365658 1 0 Producer 1 P003 364899 1 0 Producer 2 P002 375679 1 0 Producer 2 P003 393764 1 0 Consumer 1 P000 374690 1 0 Consumer 1 P001 374924 1 0 Consumer 2 P000 375709 1 0 Consumer 2 P001 374677 1 1 Producer 1 P002 74987629 1 1 Producer 1 P003 75053393 1 1 Producer 2 P002 74979748 1 1 Producer 2 P003 74985041 1 1 Consumer 1 P000 74873553 1 1 Consumer 1 P001 74968719 1 1 Consumer 2 P000 75102151 1 1 Consumer 2 P001 75061388 1 2 Producer 1 P000 1 1 2 Producer 1 P001 1 1 2 Producer 2 P000 1 1 2 Producer 2 P001 1 1 2 Consumer 1 QC 4 21 rows selected.
數組大小m，可以把錯誤判斷的幾率控制在很小的范圍之內。
我們觀察hash分發時sql的并行執行過程。Hash分發與broadcast最大的區分在于對hashjoin的兩邊都進行分發。這個例子中，對lineorder的hash分發會增加明顯的dbcpu。下一節，我將使用另一個例子，說明hash分發適用的場景。
我們已經測試過replicate，broadcast，和hash這三種分發方式。
Replicate ：每個PX進程重復掃描hashjoin的左邊，buffercache被用來緩存hashjoin左邊的小表，減少重復掃描所需的物理讀。相對于broadcast分發，replicate方式只需一組PX進程。但是replicate不能替換broadcast分發。因為repli cate僅限于hashjoin左邊是表的情況，如果 hashjoin的左邊的結果集來自其他操作，比如join或者視圖，那么此時無法使用replicate。
Broadcast分發：作為生產者的PX進程通過廣播的方式，把hashjoin左邊的結果集分發給每 個作為消費者的PX進程。一般適用于hashjoin左邊結果集比右邊小得多的場景，比如星型模型。
Hash分發的本質：把hashjoin的左邊和右邊(兩個數據源)，通過同樣hash函數重新分發，切 分為N個工作單元(假設DoP=N)，再進行join ，目的是減少PX進程進行join操作時，需要連接的數據量。Hash分發的代價需要對hashjoin的兩邊都進行分發。對于customer連接lineorder的例子，因為維度表customer的數據量比事實表lineorder小得多，對customer進行replicate或者broadcast分發顯然是更好的選擇，因為這兩種方式不用對lineorder進行重新分發。如果是兩個大表join的話，join操作會是整個執行計劃的瓶頸所在，hash分發是唯一合適的方式。為了減低join的代價，對hashjoin左邊和右邊都進行hash分發的代價是可以接受的。
我們使用lineorder上的自連接來演示，為什么有時hash分發是唯一合理的選擇。測試的SQL如 下：
select /*+ monitor parallel(4)*/ sum(lo1.lo_revenue) from lineorder lo1, lineorder lo2 where lo1.lo_orderkey = lo2.lo_orderkey;
SQL執行時間為2.4分鐘，dbtime為10.5分鐘。
優化器默認選擇hash分發方式，執行計劃為14行，結構與之前的Hash分發的例子是一致的。不 同的是，第5行的hash join消耗了73%的db time，使用了9GB的臨時表空間，表空間的IO占12%的db time。大約15%的db time用于Lineorder的兩次hash分發和接收，相對上一個例子的占38%比例，這兩次HASH分發的整體影響降低了一倍多。
紅色的PX進程為實例1、2上的p002/p003進程，藍色的PX進程為p000/p001進程。作為生產者的紅色PX進程占總db time的15%左右。
SQL執行開始，對lineorder兩次hash分發時，AAS大于4，分發完成之后，只有藍色的PX進程進行 hash join操作，AAS=4。
從V$PQ_TQSTAT視圖可以確認，對于lineorder的存在兩次分發，通過table queue0和1，作為消費者的4個PX進程接收到的兩次數據是一樣的，保證重新分發不會影響join結果的正確性。每個藍色PX 進程需要hash join的左邊和右邊均為3億行數據的1/4，通過hash分發，3億行記錄連接3億行記錄的工作平均的分配四個獨立PX進程各自處理，每個PX進程處理75M行記錄連接75M行記錄。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- -------------- ---------- --------- ---------- 1 0 Producer 1 P002 75055725 1 0 Producer 1 P003 74977459 1 0 Producer 2 P002 74995276 1 0 Producer 2 P003 74977351 1 0 Consumer 1 P000 74998419 1 0 Consumer 1 P001 74995836 1 0 Consumer 2 P000 74976974 1 0 Consumer 2 P001 75034582 1 1 Producer 1 P002 74986798 1 1 Producer 1 P003 74985268 1 1 Producer 2 P002 74984883 1 1 Producer 2 P003 75048862 1 1 Consumer 1 P000 74998419 1 1 Consumer 1 P001 74995836 1 1 Consumer 2 P000 74976974 1 1 Consumer 2 P001 75034582 1 2 Producer 1 P000 1 1 2 Producer 1 P001 1 1 2 Producer 2 P000 1 1 2 Producer 2 P001 1 1 2 Consumer 1 QC 4 21 rows selected.
對于lineorder,lineorder的自連接， 如果我們使用broadcast分發，會出現什么情況呢?我們測試一下：
select /*+ monitor parallel(4) leading(lo1 lo2) use_hash(lo2) pq_distribute(lo2 broadcast none) */ 15 sum(lo1.lo_revenue) from lineorder lo1, lineorder lo2 where lo1.lo_orderkey = lo2.lo_orderkey;
使用broadcase分發，SQL的執行時間為5.9分鐘，db time為23.8分鐘。相比hash分發，執行時間和 db time都增加了接近1.5倍。
紅色的PX進程作為生產者，對lineorder進行并行掃描之后，3億行記錄通過tablequeue0廣播給4個作為消費者的藍色PX進程(第6~9行)，相當于復制了4份，每個藍色的PX進程都接收了3億行記錄.這次broadcast分發消耗了11%的db time，因為需要每行記錄傳輸給每個藍色PX進程，消耗的db cpu比使用hash分發時兩次hash分發所消耗的還多。
第5行的hash join的所消耗的臨時表空間上升到27GB，臨時表空間IO占的db time的38%。因為每個藍色PX進程進行hash join的數據變大了，hash join的左邊為3億行數據，hash join的右邊為3億行記錄的1/4.
藍色PX進程為消費者負責hash join，所消耗的db time都大幅增加了。
hash join時，臨時表空間讀等待事件’direct path read temp’明顯增加了。
V$PQ_TQSTAT的輸出中，實例1、2上的p000/p001進程作為消費者，都接收了3億行數據，造成后續hash join的急劇變慢。Broadcast分發對hash join左邊進行廣播的機制，決定了它不適合hash join兩邊都為大表的情況。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- -------------- ---------- --------- ---------- 1 0 Producer 1 P002 299928364 1 0 Producer 1 P003 299954384 1 0 Producer 2 P002 300188788 1 0 Producer 2 P003 299951708 1 0 Consumer 1 P000 300005811 1 0 Consumer 1 P001 300005811 1 0 Consumer 2 P000 300005811 1 0 Consumer 2 P001 300005811 1 1 Producer 1 P000 1 1 1 Producer 1 P001 1 1 1 Producer 2 P000 1 1 1 Producer 2 P001 1 1 1 Consumer 1 QC 4 13 rows selected.
通過前一節和本節的例子，我們知道，如果選擇了不合理的分發方式，SQL執行時性能會明顯下降
對于broadcast分發：只對hash join的左邊進行分發，但是采用廣播分發，hash join時左邊的數據量并沒有減少，如果hash join左邊的包含大量數據，并行對hash join性能改善有限。對大量數據的broadcast分發也會消耗額外的db cpu，比如本節中lineorder自連接的例子。Replicate 同理。
對于hash分發：對hash join的兩邊都進行分發，使每個PX進程進行hash join時，左邊和右邊的數據量都為原始的1/N，N為并行度。Hash分發的潛在陷阱在于：
?兩次分發，尤其對大表的分發，可能帶來明顯的額外開銷，比如前一節customer連接lineorder 的例子。使用Partition wise join可以消除分發的需要，后面會舉例說明。
?如果數據存在傾斜，連接鍵上的少數值占了大部分的數據，通過hash分發，同一個鍵值的記錄會分發給同一個PX進程，某一個PX進程會處理大部分數據的hash join，引起并行執行傾斜。我會在后面的章節說明這種情況和解決方法。
SQL解析時，優化器會根據hash join左邊和右邊估算的cardinality，并行度等信息，選擇具體何種分發方式。維護正確的統計信息，對于優化器產生合理的并行執行計劃是至關重要的。
無論對于broadcast或者hash分發，數據需要通過進程或者節點之間通信的完成傳輸，分發的數據越多，消耗的db cpu越多。并行執行時，數據需要分發，本質上是因為Oracle采用share---everything的集中存儲架構，任何數據對每個實例的PX進程都是共享的。為了對hash join操作分而治之，切分為N個獨立的工作單元(假設 DoP=N)，必須提前對數據重新分發，數據的分發操作就是并行帶來的額外開銷。
使用full或者partial partition wise join技術，可以完全消除分發的額外開銷，或者把這種開銷降到最低。如果hash join有一邊在連接鍵上做hash分區，那么優化器可以選擇對分區表不分發，因為hash分區已經對數據完成切分，這只需要hash分發hash join的其中一邊，這是partial partition wise join。如果hash join的兩邊都在連接鍵上做了hash join分區，那么每個PX進程可以獨立的處理對等的hash分區， 沒有數據需要分發，這是full partition wise join。hash分區時，hash join的工作單元就是對等hash分區包含的數據量，應該控制每個分區的大小，hash join時就可能消除臨時表空間的使用，大幅減少所需的PGA。
如果在lineorder的列lo_orderkey上做hash分區，分區數為32個。每個分區的大小接近1G。
SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_MB ------------------ --------------- -------------------- ---------- LINEORDER_HASH32 SYS_P3345 TABLE PARTITION 960 LINEORDER_HASH32 SYS_P3344 TABLE PARTITION 960 ... LINEORDER_HASH32 SYS_P3315 TABLE PARTITION 960 LINEORDER_HASH32 SYS_P3314 TABLE PARTITION 960 ---------- 30720 32 rows selected.
使用lo_orderkey 連接時，lineorder不需要再分發。我們繼續使用自連接的sql，演示full partition wise join。
select /*+ monitor parallel(4)*/ sum(lo1.lo_revenue) from lineorder_hash42 lo1, lineorder_hash42 lo2 where lo1.lo_orderkey = lo2.lo_orderkey;
此時sql執行時間為1.6分鐘，dbtime 6分鐘;不分區使用hash分發時，執行時間為2.4分鐘，db time 10.5 分鐘。使用Partition Wise join快了三分之一。執行計劃中只有一組藍色的PX進程，不需要對數據進行分發。因為lineorder_hash42的3億行數據被切分為32個分區。雖然并行度為4，每個PX進程hash join時，工作單元為一對匹配的hash分區，兩邊的數據量都為3億的1/32。更小的工作單元，使整個hash join消耗的臨時表空間下降為 448MB。每個PX進程消耗8對hash分區，可以預見，當我們把并行度提高到8/16/32，每個PX進程處理的hash分區對數，應該分別為4/2/1，sql執行時間會線性的下降。
藍色的PX進程為、的p000/p001進程。每個PX進程消耗的db time是平均的，每個PX進程均處理了8對分區的掃描和hash join。
AAS絕大部分時間都為4。
唯一的數據連接為tablequeue0，每個PX進程向QC發送一行記錄。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- ------------- ---------- ---------- ---------- 1 0 Producer 1 P000 1 1 0 Producer 1 P001 1 1 0 Producer 2 P000 1 1 0 Producer 2 P001 1 1 0 Consumer 1 QC 4 5 rows selected
當并行執行的DoP大于hash分區數時，partition wise join不會發生，這時優化器會使用 broadcast local的分發。使用DoP=64執行同樣的sql：
select /*+ monitor parallel(64)*/ sum(lo1。lo_revenue) from lineorder_hash42 lo1， lineorder_hash42 lo2 where lo1。lo_orderkey = lo2。lo_orderkey
DoP=64，查詢執行時間為15秒，db time為11.3分鐘。
執行計劃中出現了兩組PX進程。優化器選擇對hash join的右邊進行broadcast local分發。如果hash join的左邊比較小的話，broadcast local會發生在hash join的左邊。因為DoP是分區數的兩倍，hash join兩邊的lineorder_hash74的每個分區，由2個PX進程共同處理。處理一對匹配分區的兩個藍色的PX進程和兩個紅色的PX進程，會處在同一個實例上。數據只會在同一個實例的PX進程之間，不會跨實例傳輸，降低數據分發成本，這是broadcast local的含義。SQL的執行順序如下：
以數據庫地址區間為單位，藍色的PX進程并行掃描hash join左邊的lineorder_hash42(第7行)，因為DoP是分區數的兩倍，每個分區由兩個藍色PX進程共同掃描，這兩個PX進程在同一個實例上。每個藍色的PX進程大約掃描每個分區一半的數據，大約4.7M行記錄，并準備好第5行hash join的build table。
紅色的PX進程并行掃描hash join右邊的lineorder_hash42，每個紅色的PX進程大概掃描4.7M行記錄，然后tablequeue0，以broadcast local的方式，分發給本實例兩個紅色的PX進程(數據分發時，映射到本實例某些PX進程，避免跨節點傳輸的特性，稱為slaves mapping，除了broadcast local，還有hash local，random local等分發方式)。通過broadcast local分發，數據量從300M行變成600M行。
每個藍色的PX進程通過tablequeue0接收了大概9.4M行數據，這是整個匹配分區的數據量。然后進行hash join，以及之后的聚合操作。每個藍色的PX進程hash join操作時，左邊的數據量為lineorder_hash42的1/64(=1/DoP)，右邊的數據為lineorder_hash42的1/32(=1/分區數)。如果繼續提高DoP，只有hash join左邊的數據量減少，右邊的數據量并不會減少; 同時，更多的PX進程處理同一個分區，會提高broadcast分發成本。所以當DoP大于分區數時，并行執行的隨著DoP的提高，擴展性并不好。
查看一個藍色的PX進程，實例1p005進程的執行信息，可以確認hash join的左邊為lineorder_hash42的1/64，hash join的右邊為lineorder_hash42的1/32。
數據倉庫設計時，為了取得最佳的性能，應該使用partition wise join和并行執行的組合。在大表最常用的連接鍵上，進行hash分區，hash join時使優化器有機會選擇partition wise join。Range-hash或者list-hash是常見的分區組合策略，一級分區根據業務特點，利用時間范圍或者列表對數據做初步的切分，二級分區使用hash分區。查詢時，對一級分區裁剪之后，優化器可以選擇partition wise join。
設計partition wise join時，應該盡可能提高hash分區數，控制每個分區的大小。Partition wise join時，每對匹配的分區由一個PX進程處理，如果分區數據太多，可能導致join操作時使用臨時空間，影響性能。另一方面，如果分區數太少，當DoP大于分區數時，partition wise join會失效，使用更大的DoP對性能改善非常有限。
數據傾斜是指某一列上的大部分數據都是少數熱門的值(Popular Value)。Hash join時，如果hash join的右邊連接鍵上的數據是傾斜的，數據分發導致某個PX進程需要處理所有熱門的數據，拖長sql執行時間，這種情況稱為并行執行傾斜。如果優化器選擇了hash分發，此時join兩邊的數據都進行hash分發，數據傾斜會導致執行傾斜。同值記錄的hash值也是一樣的，會被分發到同一PX進程進行hash join。工作分配不均勻，某個不幸的PX進程需要完成大部分的工作，消耗的db time會比其他PX進程多，SQL執行時間會因此被明顯延長。對于replicate或者broadcast分發，則不存在這種執行傾斜的風險，因為hash join右邊(一般為大表)的數據不用進行分發，PX進程使用基于數據塊地址區間或者基于分區的granule，平均掃描hash join右邊的數據，再進行join操作。
為了演示數據傾斜和不同分發的關系，新建兩個表，customer_skew包含一條c_custkey=-1 的記錄，lineorder_skew 90%的記錄，兩億七千萬行記錄lo_custkey=-1。
sid@SSB> select count(*) from customer_skew where c_custkey = -1; COUNT(*) ---------- 1 sid@SSB> select count(*) from customer_skew; COUNT(*) ---------- 1500000 sid@SSB> select count(*) from lineorder_skew where lo_custkey = -1; COUNT(*) ---------- 270007612 sid@SSB> select count(*) from lineorder_skew; COUNT(*) ---------- 21 300005811
測試sql如下：
select /*+ monitor parallel(4) */ sum(lo_revenue) from lineorder_skew, customer_skew where lo_custkey = c_custkey;
SQL執行時間為23秒，db time為1.5m。優化器默認的執行計劃選擇replicate的方式，只需分配一組PX進程，與broadcast分發的方式類似。每個藍色的PX進程重復掃描customer，并行掃描lineorder_skew時，是采用基于地址區間的granule為掃描單位，見第7行的’PX BLOCK ITERATOR’。
4個藍色的PX進程消耗的db time是平均的，對于replicate方式，lineorder_skew的數據傾斜并沒有造成4個PX進程的執行傾斜。
當優化器使用replicate方式時，可以通過執行計劃中outline中的hint PQ_REPLICATE確認。以下部分dbms_xplan。display_cursor輸出沒有顯示，只顯示outline數據。
select * from table(dbms_xplan.display_cursor('77457qc9a324k',0,’outline’)); Plan hash value: 4050055921 ... Outline Data ------------- /*+ BEGIN_OUTLINE_DATA 22 IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') …… ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "CUSTOMER_SKEW"@"SEL$1") FULL(@"SEL$1" "LINEORDER_SKEW"@"SEL$1") LEADING(@"SEL$1" "CUSTOMER_SKEW"@"SEL$1" "LINEORDER_SKEW"@"SEL$1") USE_HASH(@"SEL$1" "LINEORDER_SKEW"@"SEL$1") PQ_DISTRIBUTE(@"SEL$1" "LINEORDER_SKEW"@"SEL$1" BROADCAST NONE) PQ_REPLICATE(@"SEL$1" "LINEORDER_SKEW"@"SEL$1") END_OUTLINE_DATA */
通過hint使用hash分發，測試sql如下：
select /*+ monitor parallel(4) leading(customer_skew lineorder_skew) use_hash(lineorder_skew) pq_distribute(lineorder_skew hash hash) */ sum(lo_revenue) from lineorder_skew, customer_skew where lo_custkey = c_custkey;
使用hash分發，SQL執行時間為58秒，dbtime 2.1分鐘。對于replicate時sql執行時間23秒，dbtime 1.5分鐘。有趣的是，整個sql消耗的db time只增加了37秒，而執行時間確增加了35秒，意味著所增加的dbtime并不是平均到每個PX進程的。如果增加的dbtime平均到每個PX進程，而且并行執行沒有傾斜的話，那么sql執行時間應該增加37/4，約9秒，而不是現在的35秒。紅色的PX 進程作為生產者，分別對customer_skew和lineorder_skew 完成并行掃描并通過tablequeue0/1，hash分發給藍色的PX進程。對lineorder_skew的分發，占了45%的db cpu。
實例2的藍色PX進程p001消耗了57.1秒的dbtime，sql執行時間58秒，這個PX進程在sql執 行過程中一直是活躍狀態。可以預見，lineorder_skew所有lo_custkey=-1的數據都分發到這個進程處理。而作為生產者的紅色PX進程，負責掃描lineorder_skew并進行分發，它們的工作量是平均的。
大部分時候AAS=2，只有實例2的p001進程不斷的從4個生產者接收數據并進行hash join。
從V$PQ_TQSTAT視圖我們可以確認，對hash join右邊分發時，通過tablequeue1，作為消費者的實例2的P001，接收了兩億七千多萬的數據。這就是該PX進程在整個sql執行過程中一直保持活躍的原因。
SELECT dfo_number, tq_id, server_type, instance, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type desc, instance, process; DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS ---------- ---------- ------------- ---------- ---------- ---------- 1 0 Producer 1 P004 375754 1 0 Producer 1 P005 365410 1 0 Producer 2 P003 393069 1 0 Producer 2 P004 365767 1 0 Consumer 1 P002 375709 1 0 Consumer 1 P003 374677 1 0 Consumer 2 P001 374690 1 0 Consumer 2 P002 374924 1 1 Producer 1 P004 75234478 1 1 Producer 1 P005 74926098 1 1 Producer 2 P003 74923913 1 1 Producer 2 P004 74921322 1 1 Consumer 1 P002 7497409 1 1 Consumer 1 P003 7467378 1 1 Consumer 2 P001 277538575 1 1 Consumer 2 P002 7502449 24 1 2 Producer 1 P002 1 1 2 Producer 1 P003 1 1 2 Producer 2 P001 1 1 2 Producer 2 P002 1 1 2 Consumer 1 QC 4 21 rows selected.
12c的sqlmonitor報告作了增強，并行執行傾斜時，包含了消耗最大的PX進程的采樣信息。在plan statistics頁面，下拉菜單選擇’Parallel Server 3(instance 2，p001)’， 從執行計劃的第10行，‘PX RECEIVE’，以及Actual Rows列的數據278M，也可以確認實例2的p001進程接收了兩億七千多萬數據。
對于實際的應用，處理數據傾斜是一個復雜的主題。比如在傾斜列上使用綁定變量進行過濾，綁定變量窺視(bind peeking)可能造成執行計劃不穩定。本節討論了數據傾斜對不同分發方式的帶來影響：
通常，replicate或者broadcast分發不受數據傾斜的影響。
對于hash分發，hash join兩邊連接鍵的最熱門數據，會被分發到同一PX進程進行join操作，容易造成明顯的并行執行傾斜。
12c引入adaptive分發，可以解決hash分發時并行執行傾斜的問題。我將在下一篇文章” 深入理解Oracle的并行執行傾斜(下)”演示adaptive分發這個新特性。
到目前為止，所有的測試只涉及兩個表的連接。如果多于兩個表，就需要至少兩次的hash join，數據分發次數變多，生產者消費者的角色可能互換，執行計劃將不可避免變得復雜。執行路徑變長，為了保證并行執行的正常進行，執行計劃可能會插入相應的阻塞點，在hash join時，把符合join條件的數據緩存到臨時表，暫停數據繼續分發。本節我使用一個三表連接的sql來說明連續hash join時，不同分發方式的不同行為。
測試三個表連接的sql如下，加入part表，使用hint讓優化器兩次hash join都使用broadcast分發。Replicate SQL查詢性能類似。
select /*+ monitor parallel(4) LEADING(CUSTOMER LINEORDER PART) USE_HASH(LINEORDER) USE_HASH(PART) SWAP_JOIN_INPUTS(PART) PQ_DISTRIBUTE(PART NONE BROADCAST) NO_PQ_REPLICATE(PART) PQ_DISTRIBUTE(LINEORDER BROADCAST NONE) NO_PQ_REPLICATE(LINEORDER) 25 */ sum(lo_revenue) from lineorder, customer, part where lo_custkey = c_custkey and lo_partkey = p_partkey;
SQL執行時間為42秒，dbtime為2.6分鐘。
AAS=(sql db time)/(sql 執行時間)=(2.6*60)/42=3.7，接近4，說明4個PX進程基本一直保持活躍。
執行計劃是一顆完美的右深樹，這是星型模型查詢時執行計劃的典型形式。生產者對兩個維度進行broadcast分發，消費者接受數據之后準備好兩次hash join的build table， 最后掃描事實表，并進行hash join。我們通過跟隨table queue順序的原則，閱讀這個執行計劃。
紅色PX進程作為生產者并行掃描part，通過tablequeue0廣播給每個藍色的消費者PX進程 (第7~9行)。每個藍色的PX進程接收part的完整數據(第6行)，1.2M行記錄，并準備好第5行hash join的build table。
紅色PX進程作為生產者并行掃描customer，通過tablequeue1廣播broadcast給每個藍色的 消費者PX進程(第12~14行)。每個藍色的PX進程接收customer的完整數據(第11行)，1.5M行記錄，并準備好第10行hash join的build table。
藍色的PX進程并行掃描事實表lineorder，對每條符合掃描條件(如果sql語句包含對lineorder的過濾條件)的3億行記錄，進行第10行的hash join，對于每一條通過第10行的 hash join的記錄，馬上進行第5行的hash join，接著再進行聚合。從sql monitor報告的 Timeline列信息，對lineorder的掃描和兩個hash join操作是同時進行的。執行計劃中沒有阻塞點，數據在執行路徑上的流動不需要停下來等待。大部分的db cpu消耗在兩次hash join操作。最優化的執行計劃，意味著經過每個hash join的數據越少越好。對于這類執行計劃，你需要確保優化器把最能過濾數據的join，放在最接近事實表的位置執行。
使用以下hints，強制SQL使用hash分發。
select /*+ monitor parallel(4) LEADING(CUSTOMER LINEORDER PART) USE_HASH(LINEORDER) USE_HASH(PART) SWAP_JOIN_INPUTS(PART) PQ_DISTRIBUTE(PART HASH HASH) 26 PQ_DISTRIBUTE(LINEORDER HASH HASH) */ sum(lo_revenue) from lineorder, customer, part where lo_custkey = c_custkey and lo_partkey = p_partkey;
SQL執行時間為1.5分鐘，dbtime為8.1分鐘。相對于增加了14GB的IO操作。
連續兩次hash join都使用HASH分發，每次hash join左右兩邊都需要分發，PX進程之間發生4次數據分發。執行計劃中最顯著的地方來自第12行的HASH JOIN BUFFERED，這是一個阻塞性的操作。下面，我們依然通過跟隨table queue順序的原則，閱讀執行計劃，并解析為什么出現HASH JOIN BUFFERED這個阻塞操作，而不是一般的HASH JOIN。
1. 藍色的PX進程作為生產者，并行掃描customer，通過tablequeue0，hash分發給作為消費者的紅色PX進程(第14~16行)。每個紅色的PX進程接收了1/4的customer的數據(第13行)， 大約為370k行記錄，并準備好第12行‘HASH JOIN BUFFERED’的build table。與broadcast分發區別的是，此時執行計劃是從第16行，掃描靠近lineorder的customer開始的，而不是從第一個沒有’孩子’的操作(第9行掃描part)開始的。這是hash分發和串行執行計劃以及broadcast分發不同的地方。
2. 藍色的PX進程作為生產者，并行掃描lineorder，通過tablequeue1，hash分發作為消費者的紅色PX進程(第18~20行)。每個紅色PX進程接收了1/4的lineorder數據(第17行)，大約75M行記錄。每個紅色PX進程在接收通過tablequeue1接收數據的同時，進行第12行的hash join，并把join的結果集在PGA中作緩存，使數據暫時不要繼續往上流動。如果結果集過 大的話，需要把數據暫存到臨時空間，比如我們這個例子，用了7GB的臨時空間。你可以理解為把join的結果集暫存到一個臨時表。那么，為什么執行計劃需要在這里插入一個阻塞點，阻止數據繼續往上流動呢?
這里涉及生產者消費者模型的核心：同一棵DFO樹中，最多只能有兩組PX進程，一個數據分發要求兩組PX進程協同工作; 這意味著同一時刻，兩組PX進程之間，最多只能存在一個活躍的數據分發，一組作為生產者發送數據，一組作為消費者接收數據，每個PX進程只能扮演其中一種角色，不能同時扮演兩種角色。當紅色的PX進程通過tablequeue1向藍色的PX進程分發lineorder數據，同時，藍色的PX進程正在接收lineorder數據，并進行hash join。觀察timeline列的時間軸信息，第12，17~20行是同時進行的。 但是此時紅色的PX進程不能反過來作為生產者，把hash join的結果分發給藍色進程，因為此時有兩個限制：
? 藍色的PX進程作為生產者，正忙著掃描lineorder;此時，無法反過來作為消費者，接收來自紅色PX進程的數據。
? 第5行hash jon操作的build table還沒準備好，這時表part甚至還沒被掃描。
所以Oracle需要在第12行hash join這個位置插入一個阻塞點，變成HASH JOIN BUFFER操作，把join的結果集緩存起來。當藍色的PX進程完成對lineorder的掃描和分發，紅色的PX進程完成第12行的hash join并把結果完全暫存到臨時空間之后。Tablequeue2的數據分發就開始了。
3. 紅色的PX進程作為生產者，并行掃描part，通過tablequeue2，分發給作為消費者的藍色PX進程(第7~9行)。每個藍色PX進程接收了1/4的part數據(第6行)，大概300k行記錄，并準備好第5行hash join的build table。
4. 紅色的PX進程作為生產者，把在第12行”HASH JOIN BUFFERED”操作，存在臨時空間的對于customer和lineorder連接的結果集，讀出來，通過table queue 3，分發給藍色的PX進程(第11~12行)。“HASH JOIN BUFFERED”這個操作使用了7GB的臨時空間，寫IO7GB，讀IO 7GB，IO總量為 14GB。
5. 每個藍色的PX進程作為消費者，接收了大約75M行記錄。對于通過tablequeue3接收到的 數據，同時進行第5行的hash join，并且通過join操作的數據進行第4行的聚合操作。當tablequeue3上的數據分發結束，每個藍色的PX進程完成hash join和聚合操作之后，再把各自的聚合結果，一行記錄，通過tablequeue4，分發給QC(第3~5行)。QC完成最后的聚合，返回給客戶端。
因為使用星型模型測試，這個例子使用Broadcast分發或者replicate才是合理的。實際應用中，連續的hash分發并不一定會出現HASH JOIN BUFFERED這個阻塞點，如果查詢涉及的表都較小，一般不會出現HASH JON BUFFERED。即使執行計劃中出現BUFFER SORT，HASH JOIN BUFFERED等阻塞操作，也不意味著執行計劃不是最優的。如果sql性能不理想，HASH JOIN BUFFERED操作消耗了大部分的CPU和大量臨時空間，通過sql monitor報告，你可以判斷這是否是合理的：
檢查estimated rows和actual rows這兩列，確定優化器對hash Join左右兩邊cardinality估算是否出現偏差，所以選擇hash分發。
同樣檢查hash join操作的estimated rows和actual rows這兩列，優化器對hash join結果集cardinality的估算是否合理。優化器會把hash join的兩邊視為獨立事件，對join結果集cardinality的估算可能過于保守，estimate rows偏小。對于星型模型的一種典型情況：如果多個維度表參與連接，執行路徑很長，一開始維度表的分發方式為broadcast，事實表不用分發，經過幾次join之后，結果集cardinality下降很快，后續hash join兩邊的estimated rows接近，導致優化器選擇hash分發。
通過檢查每個join所過濾的數據比例，確定優化器是否把最有效過濾數據的join最先執行，保證在執行路徑上流動的數據量最少。
布隆過濾在并行執行計劃中的使用非常普遍，我將在本章節解釋這一數據結構及其作用。 從11.2版本開始，串行執行的sql也可以使用布隆過濾。
布隆過濾是一種內存數據結構，用于判斷某個元素是否屬于一個集合。布隆過濾的工作原理圖2如下：
引用自維基百科：http：//en.wikipedia.org/wiki/Bloom_filter
如圖，布隆過濾是一個簡單的bit數組，需要定義兩個變量：
1. m：數組的大小，這個例子中，m=18.
2. k：hash函數的個數，這個例子中，k=3，
一個空的布隆過濾所有bit都為0。增加一個元素時，該元素需要經過三個hash函數計算， 得到3個hash值，把數組中這三個位置都置為1。集合{x，y，z}的3個元素，分布通過三次hash計算，把數組9個位置設置為1。判斷某個元素是否屬于一個集合，比如圖中的w， 只需對w進行三次hash計算產生三個值，右邊的位置在數組中不命中，該位置為0，可以確定，w不在{x，y，z}這個集合。由于存在hash碰撞，布隆過濾的判斷會過于樂觀(false positive)，可能存在元素不屬于{x，y，z}，但是通過hash計算之后三個位置都命中，被錯誤認定為屬于{x，y，z}。根據集合元素的個數，合理的設置數組大小m，可以把錯誤判斷的幾率控制在很小的范圍之內。
布隆過濾對hash join性能的改進
布隆過濾的優勢在于使用的很少內存，就可以過濾大部分的數據。如果hash join的左邊包含過濾條件，優化器可能選擇對hash join左邊的數據集生成布隆過濾，在掃描hash join右邊時使用這個布隆布隆作為過濾條件，第一時間把絕大部分不滿足join條件數據排除。減少數據分發和join操作所處理的數據量，提高性能。
使用布隆過濾時的性能對customer使用c_nation=’CHINA’條件，只計算來自中國地區的客戶訂單的利潤總和。我們觀察使用布隆過濾和不使用布隆過濾時性能的差別。
select /*+ monitor parallel(4)*/ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey and c_nation = 'CHINA';
SQL執行時間為1秒，dbtime為7.9 秒。優化器默認選擇replicate的方式。執行計劃中多了JOIN FILTER CREATE和JOIN FILTER USE這兩個操作。SQL的執行順序為每個PX進程重復掃描customer表(第7行)，對符合c_nation=’CHINA’數據集，60K(240K/4)行記錄，在c_custkey列生成布隆過濾：BF0000(第6行JOIN FILTER CREATE)。在掃描lineorder時使用這個布隆過濾(第8行JOIN FILTER USE)。雖然lineorder總行數為300M，sql沒有過濾條件，只使用布隆過濾，掃描之后只返回28M行記錄，其他272M行記錄被過濾掉了。每個PX進程在hash join操作時，只需處理60K行customer記錄和7M(28M/4)行lineorder記錄的連接，大大降低join操作的成本。對于Exadata，Smart Scan支持布隆過濾卸載到存儲節點，存儲節點掃描lineorder時，使用布隆過濾排除272M行記錄，對于符合條件的數據，把不需要的列也去掉。Cell offload Efficiency=98%，意味著只有30GB的2%從存儲節點返回給PX進程。如果不使用布隆過濾，Cell Offload Efficieny不會高達98%，我們將在下個例子看到。對于非Exadata平臺，由于沒有Smart Scan特性，數據的過濾操作需要由PX進程完成，布隆過濾的效果不會這么明顯。12C的新特性Database In--‐memory，支持掃描列式存儲的內存數據時，使用布隆過濾。
執行計劃中出現第10行對LINEORDER的掃描時，使用了布隆過濾條件：SYS_OP_BLOOM_FILTER(：BF0000，"LO_CUSTKEY")
不使用布隆過濾時的性能
接著，我們通過hint NO_PX_JOIN_FILTER，禁用布隆過濾，觀察此時的sql執行性能。
select /*+ monitor parallel(4) NO_PX_JOIN_FILTER(LINEORDER)*/ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey and c_nation = 'CHINA';
SQL執行時間為9秒，dbtime為33.7秒。比使用布隆過濾時，性能下降明顯。優化器依然選擇replicate的方式，執行計劃中沒有PX JOIN CREATE和PX JOIN USE操作。db time增加為原來4倍的原因：
當PX掃描lineorder時，返回300M行記錄。沒有布隆過濾作為條件，每個PX進程需要從存儲節點接收75M行記錄。
進行第5行的hash join操作時，每個PX進程需要連接60k行customer記錄和75M行lineorder記錄。Join操作的成本大幅增加。
由于沒有布隆過濾，Cell Offload Efficiency下降為83%。
我們通過hint強制使用hash分發，觀察此時sql執行計劃中布隆過濾的生成和使用。
select /*+ monitor parallel(4) leading(customer lineorder) use_hash(lineorder) pq_distribute(lineorder hash hash) */ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey and c_nation = 'CHINA';
此時sql執行時間為4秒，db time為19.4秒。執行計劃第6行為JOIN FILTER CREATE; 第13行為JOIN FILTER USE。此例，PX 進程分布在多個RAC兩個實例，Hash分發時涉及布隆過濾的生成，傳輸，合并和使用，較為復雜，具體過程如下：
布隆過濾的產生：4個藍色的PX進程作為消費者，通過tablequeue0，接收紅色的PX進程hash分發的customer數據，每個藍色的PX進程接收15K行記錄。接收customer記錄的同時，實例1的兩個藍色PX進程在SGA共同生成一個布隆過濾，假設為B1; 實例2的兩個藍色PX進程在SGA共同生成一個布隆過濾，假設為B2。因為位于SGA中，布隆過濾B1對于實例1的 兩個紅色的PX進程是可見的，同樣，B2對于實例2的兩個紅色PX進程也是可見的。
布隆過濾的傳輸：當紅色的PX進程完成對hash join左邊customer的掃描，就會觸發布隆過濾B1/B2的傳輸。實例1的紅色PX進程把B1發給實例2的藍色PX進程; 實例2的紅色PX進程把B2發給實例1的藍色PX進程。
布隆過濾的合并：實例1的藍色PX進程合并B1和接收到的B2; 實例2的藍色PX進程合并B2和接收到的B1。合并之后，實例1和2產生相同布隆過濾。
布隆過濾的使用：實例1和2的4個紅色的PX進程作為生產者，并行掃描lineorder時使用 合并之后的布隆過濾進行過濾。Lineorder過濾之后為49M行記錄，此時的布隆過濾似乎沒有replicate時的有效。Cell Offloadload Efficiency為97%。
如果并行執行只在一個實例，則紅色的PX進程不需要對布隆過濾進行傳輸，藍色的PX進程也無需對布隆過濾進行合并。
因為hash join的成本大大降低了，對于lineorder 49M行記錄的hash分發，成為明顯的平均，占53%的db time。
小結
本節闡述了布隆過濾的原理，以及在Oracle中的一個典型應用：對hash join性能的提升。布隆過濾的本質在于把hash join的連接操作提前了，對hash join右邊掃描時，就第一時間把不符合join條件的大部分數據過濾掉。大大降低后續數據分發和hash join操作的成本。不同的分布方式，布隆過濾的生成和使用略有不同：
對于broadcast分發和replicate，每個PX進程持有hash join左邊的完整數據，對連接鍵生成一個完整的布隆過濾，掃描hash join右邊時使用。如果sql涉及多個維度表，維度表全部使用broadcast分發，優化器可能對不同的維度表數據生成多個的布隆過濾，在掃描事實表時同時使用。
對于hash分發，作為消費者的PX進程接收了hash join左邊的數據之后，每個PX進程分別對各自的數據集生成布隆過濾，再廣播給作為生產者的每個PX進程，在掃描hash join右邊時使用。
真實世界中，優化器會根據統計信息和sql的過濾條件自動選擇布隆過濾。通常使用布隆過濾使都會帶來性能的提升。某些極端的情況，使用布隆過濾反而造成性能下降，兩個場景：
當hash join左邊的數據集過大，比如幾百萬行，而且連接鍵上的唯一值很多，優化器依然選擇使用布隆過濾。生成的布隆過濾過大，無法在CPU cache中完整緩存。那么使用布隆過濾時，對于hash join右邊的每一行記錄，都需要到內存讀取布隆過濾做判斷，導致性能問題。
如果Join操作本身無法過濾數據，使用布隆過濾時hash join右邊的數據都會命中。優化器可能無法意識到join操作無法過濾數據，依然選擇使用布隆布隆。如果hash join右邊數據集很大，布隆過濾可能會消耗明顯的額外cpu。
現實世界中，由于使用不當，并行操作無法并行，或者并行執行計劃效率低下，沒有獲得期望的性能提升。本節舉幾個典型例子。
在sql中使用rownum，導致出現PX SEND 1 SLAVE操作，所有數據都需要分發到一個PX進，以給每一行記錄賦值一個唯一的rownum值，以及BUFFER SORT等阻塞操作。
使用用戶自定義的pl/sql函數，函數沒有聲明為parallel_enable，導致使用這個函數的sql無法并行。
并行DML時，沒有enable parallel dml，導致DML操作無法并行。
Rownum，導致并行執行計劃效率低下
在’數據傾斜對不同分發方式的影響’小節中，我們新建一個表lineorder_skew把lineorder的lo_custkey列90%的值修改為-1。因為lo_custkey是均勻分布的，我們可以通過對lo_custkey列求模，也可以通過對rownum求模，把90%的數據修改為-1。使用如下的case when語句：
1. case when mod(lo_orderkey, 10) > 0 then -1 else lo_orderkey end lo_orderkey
2. case when mod(rownum, 10) > 0 then -1 else lo_orderkey end lo_orderkey
通過以下的建表sql來測試兩種用法時的sql執行性能，并行度為16。
create table lineorder_skew1 parallel 16 as select case when mod(lo_orderkey, 10) > 0 then -1 else lo_orderkey end lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_tax, lo_commitdate, lo_shipmode, lo_status from lineorder;
不使用rownum時，create table執行時間為1分鐘，db time為15.1分鐘。QC只分配了一組 PX進程，每個藍色的PX進程以基于數據塊地址區間為單位，并行掃描lineorder表，收集統計信息，并加載到lineorder_skew1表。沒有數據需要分發，每個PX進程一直保持活躍，這是最有效率的執行路徑。
大部分時間，AAS=16。
使用rownum時，create table執行時間為22.3分鐘，db time為38.4分鐘。SQL的執行時間為使用lo_orderkey時的22倍。
執行計劃中出現兩組PX進程，PX SEND 1 SLAVE和BUFFER SORT兩個操作在之前的測試沒有出現過。根據跟隨table queue順序的原則，我們來閱讀這個執行計劃：
藍色的PX進程并行掃描lineorder，通過tablequeue0把所有數據分發給一個紅色的PX進程 (第10~12行)。因為rownum是一個偽列，為了保證每一行記錄擁有一個唯一行號，對所有數據的rownum賦值這個操作只能由一個進程完成，為rownum列賦值成為整個并行執行計劃的串行點。這就是出現PX SEND 1 SLAVE操作，性能急劇下降的原因。這個例子中，唯一活躍的紅色PX進程為實例1 p008進程。Lineorder的300M行記錄都需要發送到實例1 p008進程進行rownum賦值操作，再由這個進程分發給16個藍色的PX進程進行數據并行插入操作。
實例1 p008進程接收了16個藍色PX進程分發的數據，給rownum列賦值(第8行count操作)之后，需要通過tablequeue1把數據分發給藍色的PX進程。但是因為通過tablequeue0的數據分發的還在進行，所以執行計劃插入一個阻塞點BUFFER SORT(第7行)，把rownum賦值之后的數據緩存到臨時空間，大小為31GB。
Tablequeue0的數據分發結束之后，實例1 p008把31GB數據從臨時空間讀出，通過tablequeue1分發給16個藍色的PX進程進行統計信息收集和插入操作
紅色的PX進程只有實例1 p008是活躍的。消耗了16.7分鐘的db time。對于整個執行計劃而言，兩次數據分發也消耗了大量的db cpu。通過Table queue 0把300M行記錄從16個藍色的PX進程分發給1個紅色的 PX 進程。通過Table queue 1把300M行記錄從1個紅色的PX進程分發給16個藍色的PX進程。
雖然DoP=16，實際AAS=1.5，意味著執行計劃效率低下。
現實世界中，在應用中應該避免使用rownum。Rownum的生成操作會執行計劃的串行點，增加無謂的數據分發。對于使用rownum的sql，提升并行度往往不會改善性能，除了修改sql代碼，沒有其他方法。
自定義PL/SQL函數沒有設置parallel_enable，導致無法并行
Rownum會導致并行執行計劃出現串行點，而用戶自定義的pl/sql函數，如果沒有聲明為parallel_enable，會導致sql只能串行執行，即使用hint parallel指定sql并行執行。我們來測試一下，創建package pk_test，包含函數f，返回和輸入參數一樣的值。函數的聲明中沒有parallel_enable，不支持并行執行。
create or replace package pk_test authid current_user as function f(p_n number) return number; end; / create or replace package body pk_test as function f(p_n number) return number as l_n1 number; begin select 0 into l_n1 from dual; return p_n - l_n1; end; end; /
以下例子中在where語句中使用函數pk_test.f，如果在select列表中使用函數pk_test.f，也會導致執行計劃變成串行執行。
select /*+ monitor parallel(4) */ count(*) from customer where c_custkey = pk_test.f(c_custkey);
查詢執行時間為54秒，db time也為54秒。雖然我們指定使用Dop=4并行執行，執行計劃實際是串行的。
在函數的聲明時設置parallel_enable，表明函數支持并行執行，再次執行sql。
create or replace package pk_test authid current_user as function f(p_n number) return number parallel_enable; end; / create or replace package body pk_test as function f(p_n number) return number parallel_enable as l_n1 number; begin select 0 into l_n1 from dual; return p_n - l_n1; end; end; /
此時查詢的執行時間為12秒，db time為46.4秒。并行執行如期發生，并行度為4。
除非有特殊的約束，創建自定義pl/sql函數時，都應該聲明為parallel_enable。pl/sql函數聲明時沒有設置parallel_enable導致無法并行是一個常見的問題，我曾在多個客戶的系統中遇到。在11g中，這種情況發生時，執行計劃中可能會出現PX COORDINATOR FORCED SERIAL操作，這是一個明顯的提示; 或者你需要通過sql monitor報告定位這種問題。僅僅通過dbms_xplan。display_cursor檢查執行計劃是不夠的，這種情況執行計劃的note部分，還是會顯示DoP=4。
并行DML，沒有enable parallel dml，導致DML操作無法并行。
這是ETL應用中常見的問題，沒有在session級別enable或者force parallel dml，導致dml操作無法并行。使用customer的1.5M行數據演示一下。
建一個空表customer_test：
create table customer_test as select * from customer where 1=0;
我們使用并行直接路徑插入的語句作為例子。分別執行兩次insert，第一次沒有enable parallel dml，insert語句如下：
insert /*+ append parallel(4) */ into customer_test select * from customer;
Insert語句執行時間9秒。雖然整個語句的并行度為4，但是執行計劃中，第2行直接路徑插入操作LOAD AS SELECT是串行執行的。
此時執行計劃的Note部分會顯示PDML沒有啟用：
Note ----- - PDML is disabled in current session
啟用parallel dml之后，重新執行insert語句。
alter session enable parallel dml;
此時insert語句執行時間為3秒，執行計劃中第三行，LOAD AS SELECT操作是可以并行的。
到此，關于“怎么理解Oracle的并行執行”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
