Oracle並行基礎（連載一）

作者：沃趣科技高階資料庫技術專家 魏興華

概述

Oracle企業版有一項非常厲害的技術：並行查詢，也就是說一個語句可以僱傭多個伺服器程式（parallel slaves也叫PX slaves）來完成這一個查詢所需要的結果。並行操作不僅僅能夠充分利用主機的CPU資源，也能夠充分利用系統的IO資源、記憶體資源，這看起來是一個優點，但是也需要看情況，如果資料庫系統沒有太多的空閒CPU、空閒IO或空閒記憶體資源，那麼並行技術是否要使用非常值得考慮，甚至即使系統有著很多的CPU空閒資源，但是IO資源已經遠遠不夠，那麼同樣需要考慮是否要使用並行（並行往往產生大量的IO）。鑑於並行操作的工作方式，不能讓它在系統中被濫用，否則可能導致系統的資源很快的被耗盡。並行操作本身也是複雜的，它有著很多序列執行所不具備的概念，例如table queue，資料分發方式等等，並且閱讀並行語句執行計劃的方式也與序列可能會有所不同。
並行操作的目的是為了提升語句執行的線性度，如果一個語句序列執行的時間為4分鐘，那麼透過指定4個並行來操作，可以加快查詢執行時間為1分鐘，當然這只是一種預期，現實的情況往往不能達到這種線性度。有一些消耗和事實需要了解：

• 僱傭並行程式本身需要一些時間，這些時間往往比較短，如果程式池中沒有可用的並行程式，那麼還需要作業系統去spawn出需要的並行程式，這時資料庫可能會遭遇os thread startup等待。如果語句執行時間只有數秒，你要考慮它是否適合使用並行。
• QC程式給PX slaves分配工作，這會消耗一些時間，這個時間一般也非常短。例如QC程式需要給每個PX slave程式分配掃描ROWID的範圍。
• 如果並行查詢要返回大量的資料給客戶端，那麼僅有的一個QC程式本身可能會成為瓶頸。
• 由於Oracle的並行執行採用的是生產者消費者模型，因此一般DOP為4的查詢，最終僱傭的PX slaves為8，再加上QC程式本身，一共會佔用9個系統程式，你要認識到付出的這些是否值得。
• 在Exadata下即使使用序列查詢，由於在IO層面預設就是並行，因此Exadata下的語句並行效果沒有非Exadata下好。

為了讓並行能夠非常好的發揮作用，有一些要求需要被滿足：

• 非常有效率的執行計劃，如果執行計劃本身非常糟糕，使用並行可能並不能多大程度上改善語句的執行效率。
• 資料庫系統有著充足的資源可用。這點已經在文章的開頭提到過。
• 工作量的分配沒有明顯的傾斜，大家都熟悉短板理論，如果某一個PX slave幹了絕大部分的活，那麼最終的響應時間最大的瓶頸就是它。

也許上面的很多概念和術語你還不清楚，沒關係，我們下面的內容都會介紹到。使用並行首先應該考慮的問題是如何分配工作量，在序列執行的情況，這個問題不用考慮，只有一個程式幹活，所有的工作都是由它來完成，但是如果使用了並行操作，那就意味著有多個程式在幹同樣一件事，工作的分配就顯得非常的重要。

單表的並行操作

對於單表的並行操作，工作量的切分是比較簡單的，Oracle也沒有設計任何複雜的演算法，它一般是按照
ROWID或者分割槽（假如它是分割槽表的話）來分配工作。例如下面的並行查詢：

上面的SQL及其執行計劃顯示，對錶test以並行度2進行了記錄數的統計，Id為5的行源Operation部分為：PX BLOCK ITERATOR，這是一個在並行操作中經常能看到的一個操作，代表了QC程式按照ROWID把表做了切分，每個PX slave掃描表的不同範圍，然後每個PX slave聚合出自己所掃描部分的記錄數（Id=4,SORT AGGREGATE ）,最後把結果傳送給QC，QC進一步聚合這些PX slaves的結果形成一個記錄返回給客戶端。
透過SQL MONITORING可以看到的更為直觀（下圖），絕大部分的工作都是透過藍色的PX slaves來完成的，然後這些PX slaves把各自做過預聚集的結果傳送給（行源ID為3）QC做最終的聚合。

不過我們隨著後續的學習會發現，這裡的這個例子只僱傭了一組PX slaves程式，這在Oracle並行的世界中是一個特殊案例。按照Oracle的生產者、消費者模型，一般會僱傭兩組PX slaves，一組作為生產者掃描資料，另一組作為消費者把從生產者接收過來的資料做各種加工。（不過這個例子可以把QC作為消費者看待）。

本文大量使用了SQL MOMITORING工具，如果你對這個工具還不熟悉，請參閱我的另一篇文章：

我們對SQL進行簡單的改造，增加ORDER BY部分，看看結果會怎麼樣。

SQL>select /*+ parallel(a 2) */ * from hash_t1 a order by object_name;

同樣我們透過SQL MONITORING來進行視覺化解析，【操作】列出現了兩種不同顏色的PX slaves，紅色的PX slaves作為生產者正在掃描表HASH_T1，然後把掃描到的資料分發給藍色的PX slaves消費者，PX slaves消費者接收到這些資料後並做排序然後把結果集傳送給QC。
這個例子雖小，但是五臟俱全，在Oracle並行執行中，一個可以並行的操作單元（樹）稱為Data Flow Operator，一個QC代表了一個DFO單元，一個查詢可以有多個DFO單元（DFO tree），例如典型的像union all語句，就可以有多個DFO單元，不同的DFO單元之間也可以並行。
具備了Oracle並行執行生產者和消費者的概念，繼續看上圖中的【名稱】列，會發現有TQ10001，TQ10000的東西，這個是啥？

table queue

上面已經提到Oracle並行操作有生產者和消費者的概念，生產者和消費者分別代表著一組程式，他們之間需要傳遞訊息和資料，那麼他們是靠什麼來進行傳遞訊息和資料的呢？這就是table queue的作用。
繼續以上圖為例：

這裡一共包含了兩組PX slaves，一組為紅色的生產者，一組為藍色的消費者，生產者透過ID為6，7的行源掃描表HASH_T1，同時透過ID為5的行源把掃描結果寫入table queue TQ10000（PX SEND RANGE），消費者從table queue TQ10000讀取資料然後做排序（PX RECEIVE），消費者對於已經完成排序的結果透過table queue TQ10001傳送給QC程式，QC程式把接收到的結果聚合後傳送給客戶端。

如何切分多表

為什麼要引入資料分佈演算法

對於單表（無JOIN）的資料切分是非常簡單的，只需要按照ROWID做切分就可以保證結果的正確，因為多個並行slaves之間沒有資料的交叉，也就不會有資料的丟失，而且按照ROWID切分也非常容易保證每個PX slave的工作量均勻。但是如果是兩表的JOIN呢？你如何保證1/N的X表的記錄和相對應的1/N的Y表的記錄在一個並行操作內（也就是由一個並行程式處理）？兩個表都按照ROWID來切分是不能保證的。
為了讓例子足夠的簡單，可以透過如下例子來進行描述：
集合一：
【1，3，5，7，9，11】
集合二：
【1，9，3，6，7，8，5】

假如要求使用並行度2來判斷，【集合二】和【集合一】有多少資料有交集，該如何實現？
我們模擬透過ROWID來切分，把【集合一】按照順序切分為2部分：
set 1:1，3，5 =>程式1
set 2:7，9，11 =>程式2
我們再使用同樣的辦法，把【集合二】切分為2部分：
set 3:1，9，3 =>程式1
set 4:6，7，8，5 =>程式2

透過上面一系列的操作我們把2個集合都切分為了2份，然後我們透過程式1對set 1與set 3做join，程式2對set 2與set 4做join，OK？
顯然是不行的，因為最終的結果集是不對的。
兩個集合做JOIN正確的結果是：3，5，7，9
但是按照上面的演算法，set 1和set 3的結果集為3，set 2和set 4的結果集為7,最終的結果集為3，7，丟失了5，9兩個結果。
因此不能為了加快查詢的速度而不保證結果正確性對對資料進行隨意切割。那麼Oracle是如何做的？如何保證程式讀取了X表的1/N的資料與Y表相對應的1/N資料？
從這裡看出了引入了資料分佈演算法的重要性，也解釋了為什麼執行並行度N需要2N個並行slave來完成工作，一組程式用來掃描表X，然後把資料按照分佈演算法把資料分發給另一組程式Y，這樣表X的資料分佈完成後，Y的表記錄要根據X表的分佈演算法來決定自己的分佈方式。你看到這裡可能有些地方可能還看不明白，沒關係，後續有足夠的內容讓你明白這些操作。

BroadCast

繼續前面的例子
【集合一】：
1，3，5，7，9，11
【集合二】：
1，9，3，6，7，8，5

broadcast的分發方式為（這裡假設並行度為2）：
Oracle首先需要產生2組PX slaves，一組為生產者包含2個PX slave程式，一組為消費者，同樣包含2個PX slave程式，（注意生產者和消費者角色是可能互換的）。
每個生產者PX slave按照ROWID切分，掃描1/2的【集合一】，然後廣播給每一個消費者的PX slave，最終每一個消費者的PX slave都有一份全量的【集合一】。
然後每個消費者的PX slave程式按照ROWID切分，掃描【集合二】，然後與【集合一】做關聯判斷，最終得出結果集。
這裡的關鍵是，每個消費者的PX slave都持有了全量的【集合一】，因此不需要再對集合二有任何的分發需要，只需要按照ROWID掃描然後再進行JOIN操作就能夠保證結果的正確性。

集合一：
【1，3，5，7，9，11】
分發後為：
set 1: 1，3，5，7，9，11 =>程式1
set 2:1，3，5，7，9，11 =>程式2

集合二：
【1，9，3，6，7，8，5】
分發後為：
set 3: 1，9，3，6 =>程式1
set 4: 7，8，5 =>程式2
set 1，set 3的結果集為1，3，9，set 2和set 4的結果集為5,最終的結果集為1，3，5，9，這樣不但把工作量做了比較均勻的切分，而且保證了結果的正確性。
這裡我們透過一個具體的查詢例子再來看一下整個過程：

表T1的資料量為70，表T4的資料量為343000。

SQL>select /*+parallel(2) pq_distribute(t1 none broadcast) full(t1) full(t4) monitor*/ count(*) from t1,t4 where t1.id = t4.id1;

我們透過新增hint pq_distribute(t1 none broadcast)強制讓hash join左邊的表進行了廣播分發，根據SQL MONITORING的輸出，我們做如下分析：

• （行ID 9，8，7），生產者紅色程式（【操作】列）按照ROWID做切分掃描表T1，然後把掃描的結果寫入table queue，以廣播方式做分發，ID為7的行源PX SEND BROADCAST操作代表了廣播的分發方式。
• 行ID6，5，藍色的消費程式（【操作】列）接收到紅色PX slave廣播的資料，然後構建HASH TABLE。每一個藍色的消費PX slave都接收到了全量的T1表 的資料，根據【實際行數】列可以顯示這一點，表T1總共70行的資料經過廣播分發後，實際產生了70*2（並行度）=140行的記錄。
• 行ID 11，10，藍色的消費程式按照ROWID切割掃描T4表並與前面構建的HASH TABLE做JOIN。

這裡並沒有對T4進行任何的分發，認識到這一點很重要，藍色的消費程式只需要按照ROWID範圍掃描即可，因為T1表的資料在每個消費者的PX slave都保持著全量。
這裡我們做一個階段性的總結：
對於broadcast分發方式來說：

• HASH JOIN右邊的表不用分發。
• BROADCAST方式， 沒有結果不對的風險，因為消費者的每個PX slave持有了全部的HASH JOIN左邊表的資料，每個消費者程式都持有一個完整的HASH TABLE。
• HASH JOIN左邊表 如果小的話，分發代價不大。但是隨著並行度DOP的提高或者左邊表資料量的增大，分發的代價會越來越大。
• 如果左邊表小的話，BROADCAST的執行計劃具有非常好的擴充套件性。
• 第一組PX程式掃描HASH JOIN左邊表廣播給第二組PX slave,CPU，記憶體，競爭都會有消耗，競爭的消耗來自於第一個組的每一個程式掃描的資料都要廣播給第二組的每一個程式，如下圖：

Replicate

replicate代表每個並行程式都全量掃描hash join左邊的表，不按照rowid做卻分，由於資料是被每一個程式全量掃描的，因此不需要再對資料做分發，也就只需要一組PX slaves。

select /*+ parallel(2) */ count(*) from hash_t1 a,reptest b where a.id=b.id;

觀察操作列只有一組藍色的PX slaves程式，這裡沒有涉及到資料的分發：

• 2個程式全量掃描reptest表，然後構建hash table（全量的hash table）
• 掃描完成後，2個程式按照ROWID範圍掃描hash_t1表，由於2個程式持有了全量的reptest表的hash table，因此對於hash_t1表不需要分發。掃描hash_t1表過程中探測hash table。

Hash分發

就像上面提到的，broadcast/replicate分發方式有一個問題是，因為消費者的每一個PX slaves要持有完整左邊表的記錄， 因此適合左邊表比較小的情況。如果對於兩個大表的HASH 連線，Oracle一般使用HASH的分發方式。例如還是上面的例子：
【集合一】：
1，3，5，7，9，11
【集合二】：
1，9，3，6，7，8，5
【集合一】和【集合2】按照同樣的HASH 函式分發後，總能保證有關聯的資料對在一起，這樣就能保證結果集的正確性。但是這樣的方式，多出了一個代價，那就是對於【集合二】也需要做HASH分發，會多出一些CPU資源的消耗，相對於廣播的分發方式，只有【集合一】需要做分發。
我們來看一個具體的例子：

select /*+ parallel(3) pq_distribute(b hash hash)*/ count(*) from hash_t1 a ,hash_t2 b where a.id=b.id;

• 首先紅色的生產者PX slaves按照ROWID切分並行掃描表HASH_T1，然後依據HASH演算法把記錄透過table queue TQ10000分發給特定的藍色的消費者PX slave。
• 藍色的消費者從table queue TQ10000接收到資料後構建HASH TABLE。
• 上面2步操作完成後，紅色的生產者PX slaves繼續按照ROWID切分並行掃描表HASH_T2，然後按照HASH演算法把記錄透過table queue TQ10001分發給特定的藍色消費者PX slave，藍色消費者PX slave從table queue TQ10001接收資料並與前面構建的HASH TABLE做JOIN。最後每個藍色的消費者PX slave把自己聚合的結果透過table queue TQ10002傳送給QC。

注意

• 【實際行數】列，記錄按照HASH分發後並沒有增加。
• 對hash_t2掃描過程，由於資料需要分發，因此會有同時2組PX slaves同時活躍。

HASH分發有著很好的擴充套件性，每個程式有部分的HASH 表，而不是完整的HASH表，每一行只會分發給一個特定的PX SLAVE。而不是像broadcast分發把每一行廣播給每一個SLAVE。
但是就像上面已經指出過，待對HASH JOIN左邊表分發完畢後，同樣對於HASH JOIN右邊的表也需要進行分發，多了一次分發的代價，增加了一些CPU和記憶體的成本。

HYBRID-HASH

錯誤的分發方式可能會對並行執行帶來非常大的效能問題，Oracle 12C介紹自適應的並行分發方法，hybrid hash，在真正執行過程中，再決定該使用何種分發方式，Oracle 最佳化器要做到這一點，使用了statistics collector，它在語句執行過中統計語句的一些執行時資訊，例如返回記錄的數量等等。需要注意使用了HYBRID-HASH後，每次語句執行，都要透過statistics collector來動態決定使用的並行分發方式。

例如上面的執行計劃，觀察行源ID 7，並行執行過程中會統計結果集的返回值數量，如果返回的結果集數量小於並行度*2，那麼會使用廣播方式來進行資料分發，反之則使用HASH的資料分發方式，作為回應，在行源ID 為6的分發方式確定後，行源ID 11再決定使用round-robin還是hash分發。

並行執行計劃的閱讀順序 

我剛做DBA那會，一些老DBA告訴我如何看並行執行計劃，那就是把PX相關的操作都統統抹去，然後再看，例如：

真的等價嗎？我們把相關的PX等操作都全部去掉，最終和序列執行的如下文字是"等價"的：

在序列執行過程中，對於上面執行計劃的執行順序是這樣的：
掃描T3表，構建hash table，掃描T2表，構建hash table，掃描T1表構建hash table，最後掃描T4表，掃描到的每一個記錄都要探測前面所產生的3個hash table。
但是並行執行的執行順序並不一定是按照上面描述的順序，對於並行執行計劃的閱讀要跟隨table queue的建立順序，它代表著並行執行中資料分發的順序。因此就上面的並行執行，執行順序為：

• 掃描table T1，構建hash table，之所以首先掃描T1，是因為table queue的編號TQ10000 是最小的。
• 根據table queue TQ10001的位置知道，然後掃描table T4並與上面的hash table做hash join。
• 根據table queue TQ10002的位置知道，接著掃描table T2，構建hash table，然後，上面兩步產生的結果集與這個hash table做hash join。
• 根據table queue TQ10002的位置知道，最後掃描table T3,構建hash table，然後上面三步產生的結果集與這個hash table做hash join。

v$pq_tqstat

v$pq_tqstat檢視是非常特別的，它的內容只記錄在QC程式的私有PGA中，而且只在 並行查詢結束後內容才會被填充，因此如果並行執行過程中，你取消了查詢，那麼查詢這個檢視依然不會有任何結果，因為它只存在程式的PGA中，因此你不能透過另一個會話去查詢它。
可以透過它瞭解並行執行過程中資料是如何透過table queue分發的。舉兩個例子：

例如我們對hash_t1 以並行度4進行記錄統計，執行完成後，檢視v$pq_tqstat檢視：

4個生產者把各自掃描到的記錄做了匯聚各自產生一個記錄並把它寫入table queue，QC透過table queue接收了這四個記錄。注意NUM_ROWS代表的是PX slaves透過table queue寫入、讀取的資料量，可以透過NUM_ROWS的值非常容易看出並行程式的工作量是否均勻，是否有並行傾斜存在。

再看一個複雜點的例子：

觀察SQL的hint部分，強制讓hash join的左邊表使用了broadcast的分發方式，結合上面的輸出和下面的圖一起看，每個紅色的生產者先按照ROWID範圍掃描HASH_T2，然後把資料透過table queue TQ10000廣播給藍色的消費者，由於採用了並行度4，因此其實每個生產者真正寫入table queue中的資料量是掃描資料量的4倍（9999999 是真實的記錄數，經過廣播分發產生了9999999 *4 的記錄數），消費者從table queueTQ10000中接收資料後構建hash table，每個消費者PX slave都構建了表hash_t2完整的的hash table，然後藍色消費者開始掃描hash_t1，並與之前構建的hash table做join，最後每個藍色消費者把各自最終形成的預聚合結果傳送給QC（這裡其實已經轉化了角色變為了生產者）,QC接收到4條記錄。