414      阿裏雲  技術社區[雲棲]

深入並行：從生產者到消費者模型深度理解Oracle的並行

陳煥生

Oracle Real-World Performance Group 成員，senior performance engineer，專注於 OLTP、OLAP 係統 在 Exadata 平台和 In-Memory 特性上的最佳實踐。個人博客 https://dbsid.com 。

編輯手記：感謝陳煥生授權我們發布他的精品文章，Sidney撰寫這個係列的文章時間跨度也有兩年，下篇剛剛出爐，我們先從他去年投稿的第一篇開始。

Oracle 的並行執行

Oracle 的並行執行是一種分而治之的方法. 執行一個 SQL 時, 分配多個並行進程同時執行數據掃描,連接以及聚合等操作, 使用更多的資源, 得到更快的 SQL 響應時間。並行執行是充分利用硬件資源,處理大量數據時的核心技術。

在本文中, 在一個簡單的星型模型上, 我會使用大量例子和 sql monitor 報告, 力求以最直觀簡單 的方式, 向讀者闡述並行執行的核心內容:

• Oracle 並行執行為什麼使用生產者-消費者模型.

• 如何閱讀並行執行計劃.

• 不同的數據分發方式分別適合什麼樣的場景.

• 使用 partition wise join 和並行執行的組合提高性能.

• 數據傾斜會對不同的分發方式帶來什麼影響.

• 由於生產者-消費者模型的限製, 執行計劃中可能出現阻塞點.

• 布隆過濾是如何提高並行執行性能的.

• 現實世界中, 使用並行執行時最常見的問題.

術語說明:

• 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, 一般為小表.

• Hashjoin的右邊:被驅動表,theprobesideofhashjoin,一般為大表.

• 布隆過濾: bloom filter, 一種內存數據結構, 用於判斷一個元素是否屬於一個集合.

測試環境和數據

Oracle 版本為 12.1.0.2.2，兩個節點的 RAC，硬件為 Exadata X3-8.

這是一個典型的星型模型，事實表 lineorder 有 3 億行記錄，維度表 part/customer 分別包含 1.2M 和 1.5M 行記錄，3 個表都沒有進行分區，lineorder 大小接近 30GB.

本篇文章所有的測試, 除非特別的說明, 我關閉了 12c 的 adaptive plan 特性, 參數optimizer_adaptive_features 被默認設置為 false. Adaptive 相關的特性如 cardinality feedback, adaptive distribution method, adaptive join 都不會啟用. 如果檢查執行計劃的 outline 數據, 你會發 現 7 個優化器相關的隱含參數被設置為關閉狀態. 事實上, 12c 優化器因為引入 adaptive plan 特 性, 比以往版本複雜得多, 剖析 12c 的優化器的各種新特性, 我覺得非常具有挑戰性, 或許我會在另一篇文章裏嚐試一下。

並行初體驗

串行執行

以下 sql 對 customers 和 lineorder 連接之後, 計算所有訂單的全部利潤. 串行執行時不使用parallel hint:

select /*+ monitor */ sum(lo_revenue) from lineorder, customer where lo_custkey = c_custkey;

串行執行時, sql 執行時間為 1.5 分鍾, db time 為 1.5 分鍾. 執行計劃有 5 行, 一個用戶進程工作完 成了對 customer, lineorder 兩個表的掃描, hash join, 聚合以及返回數據的所有操作. 此時AAS(average active sessions)為 1, sql 執行時間等於 db time. 幾乎所有的 db time 都為 db cpu, 72%的 cpu 花在了第二行的 hash join 操作. 因為測試機器為一台 Exadata X3-8, 30GB 的 IO 請求在一秒之內處理完成. Cell offload 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 行,從下往上分別多了’PX BLOCK ITERATOR’, ‘SORT AGGREGATE’, ‘PX SEND QC(RANDOM)’和’PX COORDINATOR’這四個操作.

其中 3 到 8 行的操作為並行處理, sql 的執行順序為: 每個 PX 進程掃描維度表 customer(第 6 行),以數據塊地址區間作為單位(第 7 行)掃描四分之一的事實表 lineorder(第 8 行), 接著進行 hash join(第 5 行), 然後對連接之後的數據做預先聚合(第 4 行), 最後把結果給 QC(第三行). QC 接收數 據(第 2 行)之後, 做進一步的匯總(第 1 行), 最後返回數據(第 0 行).

SQL 執行時間比原來快了 4 倍, 因為最消耗時間的操作, 比如對 lineorder 的全表掃描, hash join 和 聚合, 我們使用 4 個進程並行處理, 因此最終 sql 執行時間為串行執行的 1/4. 另一方麵, db time並沒有明顯下降, 並行時 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 進程。

小結

本節的例子中, 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如下:

Broadcast 分發，一次數據分發

為了舉例說明兩組 px 進程如何協作的, 設置_px_replication_enabled 為 false. QC 會分配兩組 PX進程, 一組為生產者, 一組為消費者。

見下圖，此時sql執行時間為23s，執行時間變慢了2s，dbtime 仍為1.5分鍾。

最大的變化來自執行計劃, 現在執行計劃有 12 行. 增加了對 customer 的並行掃描’PX BLOCK ITERATOR’(第 8 行), 分發’PX SEND BROADCAST’和接收’PX RECEIVE’. 執行計劃中出現了兩組 PX 進 程, 除了之前藍色的多人標誌, 現在出現了紅色的多人標誌. 此時, SQL 的執行順序為:

1. 4 個紅色的 PX 進程扮演生產者角色, 掃描維度表 customer, 把數據通過 broadcast 的方式分 發給每一個扮演消費者的藍色 PX 進程. 因為 DoP=4, 每一條被掃描出來的記錄被複製了 4 份,從 sql monitor 的第 9 行, customer 全表掃描返回 1.5m 行數據, 第 8 行的分發和第 7 行的接受 之時, 變成了 6m 行記錄, 每個作為消費者的藍色 px 進程都持有了一份完整包含所有custome 記錄的數據, 並準備好第 5 行 hash join 的 build table.

2. 4 個作為消費者的藍色 PX 進程, 以數據塊地址區間為單位掃描事實表 lineorder(第 10/11 行);同時和已經持有的 customer 表的數據進行 hash join(第 5 行), 然後對滿足 join 條件的數據做 預聚合(第 4 行), 因為我們查詢的目標是對所有 lo_revenue 求和, 聚合之後每個 PX 進程隻需 輸出一個總數。

3. 4 個藍色的 PX 進程反過來作為生產者, 把聚合的數據發給消費者 QC(第 3 行和第 2 行). 由 QC對接收到 4 行記錄做最後的聚合, 然後返回給用戶。

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. 實例 1、2 上的 p002/p003 進程作為生產者, 幾乎平均掃描 customer 的 1/4 記錄, 把每一條記錄廣播給 4 個消費者 PX 進程, 發送的記錄數之和為 6m 行. 通過 table queue 0(TQ_ID=0), 每個 作為消費者的 p000/p001 進程, 接收了完整的 1.5m 行 customer 記錄, 接收的記錄數之和為6m 行。

2. 實例 1、2 上的 p000/p0001 進程作為生產者, 通過 table queue 1(TQ_ID=1), 把聚合的一條結 果記錄發給作為消費者的 QC. QC 作為消費者, 接收了 4 行記錄。

那麼，以上的輸出中, DFO_NUMBER 和 TQ_ID 這兩列表示什麼意思呢?

1. DFO 代表 Data Flow Operator, 是執行計劃中可以並行執行的操作. 一個 QC 代表一棵 DFO 樹 (tree), 包含多個 DFO; 同一個 QC 中所有並行操作的 DFO_NUMBER 是相同的, 此例中, 所有DFO_NUMBER 為 1. 執行計劃包含多個 QC 的例子也不少見, 比如使用 union all 的語句, union all 每個分支都是獨立的 DFO 樹, 不同的 DFO 樹之間可以並行執行. 本篇文章僅討論執行計劃 隻有一個 QC 的情況.

   
   

2. TQ 代表 table queue, 用以 PX 進程之間或者和 QC 通信連接. 以上執行計劃中, table queue 0為 PX 進程之間的連接, table queue 1 為 PX 進程和 QC 之間的連接. 生產者通過 table queue分發數據, 消費者從 table queue 接收數據. 不同的 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 進程會變換生產者消費者角色, 相互協作, 完成 所有並行操作. 每次數據分發, 對應的 table queue 的編號不同. 一個活躍的數據分發過程,需要兩組 PX 進程都參與, 一組為生產者發送數據, 一組為消費者接收數據. 因為一個 DFO 裏最多隻有兩組 PX 進程, 意味著, PX 進程之間, 同一時間隻能有一個活躍的數據分發. 如果 PX 進程在執行計劃中需要多次分發數據, 可能需要在執行計劃插入一些阻塞點，比如 BUFFER SORT 和 HASH JOIN BUFFERED 這兩個操作, 保證上一次的數據分發完成之後,才開始下一次分發. 在後麵的章節, 我將會說明這些阻塞點帶來什麼影響.。這個例子中, table queue 0 和 1 可以同時工作是因為: table queue 0 是兩組 PX 進程之間的鏈接, table queue1為PX進程和 QC之間的連接, tablequeue0與tablequeue1是相互獨立的，因此可以同時進行.

  
  

• PX 進程之間或者與 QC 的連接至少存在一個(單節點下至多三個, RAC 環境下至多四個)消 息緩衝區用於進程間數據交互, 該消息緩衝區默認在 Large pool 中分配(如果沒有配置Large pool 則在 Shared pool 中分配). 多個緩衝區是為了實現異步通信, 提高性能.

• 每個消息緩衝區的大小由參數 parallel_execution_message_size 控製, 默認為 16k。

• 當兩個進程都在同一個節點的時候，通過在 Large pool (如果沒有配置 Large pool 則 Shared pool) 中傳遞和接收消息緩衝進行數據交互。當兩個進程位於不同節點時。通過 RAC 心跳網絡進行數據交互，其中一方接收的數據需要緩存在本地 Large pool (如果沒有 配置 Large pool 則 Shared pool) 裏麵。

小結

為了說明並行執行的生產者-消費者模型是如何工作的, 我使用了 broadcast 分發, QC 分配兩組 PX進程, 一組為生產者, 一組為消費者. QC 和 PX 進程之間, 兩組 PX 進程之間通過 table queue 進行 數據分發, 協同完成整個並行執行計劃. 視圖 V$PQ_TQSTAT 記錄了並行執行過程中, 數據是如何 分發的. 通過對 DFO, table queue 的描述, 我闡述生產者-消費者模型的工作原理和通信過程, 或許 有些描述對你來說過於突然, 不用擔心, 後麵的章節我會通過更多的例子來輔助理解.

如何閱讀並行執行計劃

Table queue 的編號代表了並行執行計劃中, 數據分發的順序. 理解執行計劃中的並行操作是如何 被執行的, 原則很簡單：跟隨 Table queue 的順序。

通過 sql monitor 報告判斷 sql 的執行順序, 需要結合 name 列的 table queue 名字比 如:TQ10000(代表 DFO=1, table queue 0),:TQ10001(代表 DFO=1, table queue 1), 還有 PX 進程的顏色,進行確定.

下麵的例子為 dbms_xplan.display_cursor 的輸出. 對於並行執行計劃, 會多出來三列:

1. TQ 列: 為 Q1:00 或者 Q1:01, 其中 Q1 代表第一個 DFO, 00 或者 01 代表 table queue 的編號。

   1. ID 7~9 的操作的 TQ 列為 Q1,00, 該組 PX 進程, 作為生產者首先執行, 然後通過broadcast 的分發方式, 把數據發給消費者。

   2. ID 10~11, 3~6 的操作的 TQ 列為 Q1,01, 該組 PX 進程作為消費者接受 customer 的數 據之後, 掃描 lineorder, hash join, 聚合之後, 又作為生產者通過 table queue 2 把數據 發給 QC.
      

      
      

2. In-out 列: 表明數據的流動和分發。

• PCWC: parallel combine with child.

• PCWP: parallel combine with parent.

• P->P: parallel to parallel.

• P->S: parallel to Serial.

  
  

PQ Distribute 列: 數據的分發方式. 此執行計劃中, 我們使用了 broadcast 的方式, 下麵的章節 我會講述其他的分發方式.

HASH 分發方式, 兩次數據分發

除了 broadcast 分發方式, 另一種常見的並行分發方式為 hash. 為了觀察使用 hash 分發時 sql 的 執行情況, 我對 sql 使用 pq_distribute hint.

使用 hash 分發方式時, sql 的執行時間為 29s, db time 為 2.6m. 相對於 broadcast 方式, sql 的執行 時間和 db time 都增加了大約 40%.

執行計劃如下, 執行計劃為 14 行, 增加了對 lineorder 的 hash 分發, 第 11 行的’PX SEND HASH’對 3億行數據通過 hash 函數分發, 第 10 行的’PX RECEIVE’ 通過 table queue 1 接收 3 億行數據, 這兩個 操作消耗了 38%的 db cpu. 這就是為什麼 SQL 執行時間和 db time 變長的原因. 此時, SQL 的執行 順序為:

1. 紅色的 PX 進程作為生產者, 並行掃描 customer(第 8~9 行), 對於連接鍵 c_custkey 運用 hash函數, 根據每行記錄的 hash 值, 通過 table queue 0, 發給 4 個藍色消費者的其中一個(第 7 行). Hash 分發方式並不會複製數據, sql monitor 報告的第 6~9 行, actual rows 列都為 1.5m.

2. 紅色的 PX 進程作為生產者, 並行掃描 lineorder(第 12~13 行), 對於連接鍵 lo_custkey 運用同 樣的 hash 函數, 通過 table queue 1, 發給 4 個藍色消費者的其中一個(第 11 行). 同樣的 hash函數保證了 customer 和 lineorder 相同的連接鍵會發給同一個消費者, 保證 hash join 結果的 正確. 因為 3 億行數據都需要經過 hash 函數計算, 然後分發(這是進程間的通信, 或者需要通 過 RAC 心跳網絡通信), 這些巨大的額外開銷, 就是增加 38% cpu 的原因.

3. 4 個藍色的 PX 進程作為消費者接收了 customer 的 1.5M 行記錄 (第 6 行), 和 lineorder 的 3 億 行記錄(第 10 行), 進行 hash join(第 5 行), 預聚合(第 4 行).

4. 4 個藍色的 PX 進程反過來作為生產者,通過 table queue 2, 把聚合的數據發給消費者 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 進程保持活躍.

並行查詢之後, 通過視圖 V$PQ_TQSTAT, 進一步驗證以上描述的執行過程. 並行執行過程涉及 3個 table queue 0/1/2, V$PQ_TQSTAT 包含 21 行記錄。

1. 實例 1、2 上的 p002/p003 進程作為生產者, 平均掃描 customer 的 1/4 記錄, 然後通過 table queue 0(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 queue 1(TQ_ID=1)，發給作為消費者的 p000/p001 進程。發送和接收的 lineorder 記錄之和都為300005811.

• 發送的記錄數: 300005811 = 74987629 + 75053393 + 74979748 + 74985041

• 接收的記錄數: 300005811 = 74873553 + 74968719 + 75102151 + 75061388

3. 實例 1、2 上的 p000/p0001 進程作為生產者, 通過 table queue 2(TQ_ID=2), 把聚合的一條結果記錄發給作為消費者的 QC. QC 作為消費者, 接收了 4 行記錄。

小結

我們觀察 hash 分發時 sql 的並行執行過程. Hash 分發與 broadcast 最大的區分在於對 hash join 的 兩邊都進行分發. 這個例子中, 對 lineorder 的 hash 分發會增加明顯的 db cpu. 下一節, 我將使用另 一個例子, 說明 hash 分發適用的場景。

Replicate, Broadcast 和 Hash 的選擇

我們已經測試過 replicate, broadcast, 和 hash 這三種分發方式.

1. Replicate: 每個 PX 進程重複掃描 hash join 的左邊, buffer cache 被用來緩存 hash join 左邊的小表, 減少重複掃描所需的物理讀. 相對於 broadcast 分發, replicate 方式隻需一組 PX 進程. 但 是 replicate 不能替換 broadcast 分發. 因為 replicate 僅限於 hash join 左邊是表的情況, 如果hash join 的左邊的結果集來自其他操作, 比如 join 或者視圖, 那麼此時無法使用 replicate.

2. Broadcast 分發: 作為生產者的 PX 進程通過廣播的方式, 把 hash join 左邊的結果集分發給每 個作為消費者的 PX 進程. 一般適用於 hash join 左邊結果集比右邊小得多的場景, 比如星型模型。

3. Hash 分發的本質: 把 hash join 的左邊和右邊(兩個數據源), 通過同樣 hash 函數重新分發, 切 分為 N 個工作單元(假設 DoP=N), 再進行 join, 目的是減少 PX 進程進行 join 操作時, 需要連接 的數據量. Hash分發的代價需要對hashjoin的兩邊都進行分發.對於customer連接lineorder 的例子, 因為維度表 customer 的數據量比事實表 lineorder 小得多, 對 customer 進 行 replicate 或者 broadcast 分發顯然是更好的選擇, 因為這兩種方式不用對 lineorder 進行重 新分發. 如果是兩個大表 join 的話, join 操作會是整個執行計劃的瓶頸所在, hash 分發是唯一 合適的方式. 為了減低 join 的代價, 對 hash join 左邊和右邊都進行 hash 分發的代價是可以接受的。

Hash 分發, 有時是唯一合理的選擇

我們使用 lineorder 上的自連接來演示, 為什麼有時 hash 分發是唯一合理的選擇. 測試的 SQL 如下:

SQL 執行時間為 2.4 分鍾, db time 為 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 queue 0 和 1, 作為消 費者的 4 個 PX 進程接收到的兩次數據是一樣的, 保證重新分發不會影響 join 結果的正確性. 每個 藍色 PX 進程需要 hash join 的左邊和右邊均為 3 億行數據的 1/4, 通過 hash 分發, 3 億行記錄連接3 億行記錄的工作平均的分配四個獨立 PX 進程各自處理, 每個 PX 進程處理 75M 行記錄連接75M 行記錄.

使用 broadcast 分發，糟糕的性能

對於 lineorder, lineorder 的自連接, 如果我們使用 broadcast 分發, 會出現什麼情況呢? 我們測試一下:

使用 broadcase 分發, SQL 的執行時間為 5.9 分鍾, db time 為 23.8 分鍾. 相比 hash 分發, 執行時間 和 db time 都增加了接近 1.5 倍。

紅色的 PX 進程作為生產者, 對 lineorder 進行並行掃描之後, 3 億行記錄通過 table queue 0 廣播給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 兩邊都為大表的情況。

小結，Broadcast 和 Hash 分發的陷阱

通過前一節和本節的例子, 我們知道, 如果選擇了不合理的分發方式, SQL 執行時性能會明顯下降。

1. 對於 broadcast 分發: 隻對 hash join 的左邊進行分發, 但是采用廣播分發, hash join 時左邊的數據量並沒有減少, 如果 hash join 左邊的包含大量數據, 並行對 hash join 性能改善有限. 對大 量數據的 broadcast 分發也會消耗額外的 db cpu, 比如本節中 lineorder 自連接的例子. Replicate 同理。

2. 對於 hash 分發: 對 hash join 的兩邊都進行分發, 使每個 PX 進程進行 hash join 時, 左邊和右邊 的數據量都為原始的 1/N, N 為並行度. Hash 分發的潛在陷阱在於:

• 兩次分發, 尤其對大表的分發, 可能帶來明顯的額外開銷, 比如前一節 customer 連接 lineorder 的例子. 使用 Partition wise join 可以消除分發的需要, 後麵會舉例說明。

• 如果數據存在傾斜, 連接鍵上的少數值占了大部分的數據, 通過 hash 分發, 同一個鍵值的 記錄會分發給同一個 PX 進程, 某一個 PX 進程會處理大部分數據的 hash join, 引起並行執行傾斜. 我會在後麵的章節說明這種情況和解決方法。

SQL 解析時，優化器會根據 hash join 左邊和右邊估算的 cardinality，並行度等信息，選擇具體何種分發方式。維護正確的統計信息，對於優化器產生合理的並行執行計劃是至關重要的。

Partition Wise Join, 消除分發的額外開銷

無論對於 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.

Partition Wise Join, 不需要數據分發。

如果在 lineorder 的列 lo_orderkey 上做 hash 分區，分區數為 32 個。每個分區的大小接近 1G.

使用 lo_orderkey 連接時, lineorder 不需要再分發. 我們繼續使用自連接的 sql, 演示 full partition wise join.

此時 sql 執行時間為 1.6 分鍾, db time 6 分鍾; 不分區使用 hash 分發時, 執行時間為 2.4 分鍾, db time 10.5 分鍾. 使用 Partition Wise join 快了三分之一. 執行計劃中隻有一組藍色的 PX 進程, 不需 要對數據進行分發. 因為 lineorder_hash32 的 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.

唯一的數據連接為 table queue 0, 每個 PX 進程向 QC 發送一行記錄.

當 DoP 大於分區數時, Partition Wise Join 不會發生

當並行執行的 DoP 大於 hash 分區數時, partition wise join 不會發生, 這時優化器會使用 broadcast local 的分發。使用 DoP=64 執行同樣的 sql:

DoP=64, 查詢執行時間為 15 秒, db time 為 11.3 分鍾。

執行計劃中出現了兩組 PX 進程. 優化器選擇對 hash join 的右邊進行 broadcast local 分發. 如果hash join 的左邊比較小的話, broadcast local 會發生在 hash join 的左邊. 因為 DoP 是分區數的兩倍, hash join 兩邊的 lineorder_hash64 的每個分區, 由 2 個 PX 進程共同處理。處理一對匹配分區的兩 個藍色的 PX 進程和兩個紅色的 PX 進程, 會處在同一個實例上. 數據隻會在同一個實例的 PX 進程之間, 不會跨實例傳輸, 降低數據分發成本, 這是 broadcast local 的含義。SQL 的執行順序如下:

1. 以數據庫地址區間為單位, 藍色的 PX 進程並行掃描 hash join 左邊的 lineorder_hash32(第 7 行), 因為 DoP 是分區數的兩倍, 每個分區由兩個藍色 PX 進程共同掃描, 這兩個 PX 進程在同 一個實例上. 每個藍色的 PX 進程大約掃描每個分區一半的數據, 大約 4.7M 行記錄, 並準備好 第 5 行 hash join 的 build table.

2. 紅色的 PX 進程並行掃描 hash join 右邊的 lineorder_hash32, 每個紅色的 PX 進程大概掃描4.7M 行記錄, 然後 table queue 0, 以 broadcast local 的方式, 分發給本實例兩個紅色的 PX 進 程(數據分發時, 映射到本實例某些 PX 進程, 避免跨節點傳輸的特性, 稱為 slaves mapping, 除 了 broadcast local, 還有 hash local, random local 等分發方式). 通過 broadcast local 分發, 數據 量從 300M 行變成 600M 行。

3. 每個藍色的 PX 進程通過 table queue 0 接收了大概 9.4M 行數據, 這是整個匹配分區的數據量.然後進行 hash join, 以及之後的聚合操作. 每個藍色的 PX 進程 hash join 操作時, 左邊的數據 量為 lineorder_hash32 的 1/64(=1/DoP), 右邊的數據為 lineorder_hash32 的 1/32(=1/分區數).如果繼續提高 DoP, 隻有 hash join 左邊的數據量減少, 右邊的數據量並不會減少; 同時, 更多 的 PX 進程處理同一個分區, 會提高 broadcast 分發成本. 所以當 DoP 大於分區數時, 並行執行 的隨著 DoP 的提高, 擴展性並不好。

   
   

   
   

查看一個藍色的 PX 進程, 實例 1 p005 進程的執行信息, 可以確認 hash join 的左邊為lineorder_hash32 的 1/64, hash join 的右邊為 lineorder_hash32 的 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 對性能改善非常有限.

本文出自數據和雲公眾號，原文鏈接

最後更新：2017-07-17 18:03:13

  上一篇：  深入並行：從數據傾斜到布隆過濾深度理解Oracle的並行

  下一篇：  故障分析：一則library cache lock問題處理