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

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

陳煥生

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

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

上一篇請閱讀：深入並行：從生產者到消費者模型深度理解Oracle的並行

數據傾斜是指某一列上的大部分數據都是少數熱門的值(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.

Replicate 方式，不受數據傾斜的影響

測試 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進程消耗的dbtime是平均的,對於replicate方式, lineorder_skew的數據傾斜並 沒有造成 4 個 PX 進程的執行傾斜。

當優化器使用 replicate 方式時, 可以通過執行計劃中 outline 中的 hint PQ_REPLICATE 確認. 以下 部分 dbms_xplan.display_cursor 輸出沒有顯示, 隻顯示 outline 數據。

Hash 分發，數據傾斜造成執行傾斜

通過 hint 使用 hash 分發, 測試 sql 如下:

使用 hash 分發, SQL 執行時間為 58 秒, db time 2.1 分鍾. 對於 replicate 時 sql 執行時間 23 秒, db time 1.5 分鍾. 有趣的是, 整個 sql 消耗的 db time 隻增加了 37 秒, 而執行時間確增加了 35 秒, 意 味著所增加的 db time 並不是平均到每個 PX 進程的. 如果增加的 db time 平均到每個 PX 進程, 而 且並行執行沒有傾斜的話, 那麼 sql 執行時間應該增加 37/4, 約 9 秒, 而不是現在的 35 秒。

紅色的 PX 進程作為生產者, 分別對 customer_skew 和 lineorder_skew 完成並行掃描並通過 table queue0/1, hash 分發給藍色的 PX 進程. 對 lineorder_skew 的分發, 占了 45%的 db cpu.

實例2的藍色 PX 進程p001消耗了57.1秒的db time, sql執行時間58秒,這個PX進程在sql執 行過程中一直是活躍狀態. 可以預見, lineorder_skew 所有 lo_custkey=-1 的數據都分發到這個進 程處理. 而作為生產者的紅色 PX 進程, 負責掃描 lineorder_skew 並進行分發, 它們的工作量是平均的。

大部分時候 AAS=2, 隻有實例 2 的 p001 進程不斷的從 4 個生產者接收數據並進行 hash join.

從 V$PQ_TQSTAT 視圖我們可以確認, 對 hash join 右邊分發時, 通過 table queue 1, 作為消費者的 實例 2 的 P001, 接收了兩億七千多萬的數據. 這就是該 PX 進程在整個 sql 執行過程中一直保持活躍的原因。

12c 的 sql monitor 報告作了增強, 並行執行傾斜時, 包含了消耗最大的 PX 進程的采樣信息. 在plan statistics 頁麵, 下拉菜單選擇’Parallel Server 3(instance 2, p001)’, 從執行計劃的第 10 行, ‘PX RECEIVE’, 以及 Actual Rows 列的數據 278M, 也可以確認實例 2 的 p001 進程接收了兩億七千多萬 數據。

小結

對於實際的應用, 處理數據傾斜是一個複雜的主題. 比如在傾斜列上使用綁定變量進行過濾, 綁定變量窺視(bind peeking)可能造成執行計劃不穩定. 本節討論了數據傾斜對不同分發方式的帶來影響:

1. 通常, replicate 或者 broadcast 分發不受數據傾斜的影響.

2. 對於 hash 分發, hash join 兩邊連接鍵的最熱門數據, 會被分發到同一 PX 進程進行 join 操作, 容易造成明顯的並行執行傾斜.

3. 12c 引入 adaptive 分發, 可以解決 hash 分發時並行執行傾斜的問題. 我將在下一篇文章 “深入理解 Oracle 的並行執行傾斜(下)”演示 adaptive 分發這個新特性。

到目前為止, 所有的測試隻涉及兩個表的連接. 如果多於兩個表, 就需要至少兩次的 hash join, 數據分發次數變多, 生產者消費者的角色可能互換, 執行計劃將不可避免變得複雜. 執行路徑變長,為了保證並行執行的正常進行, 執行計劃可能會插入相應的阻塞點, 在 hash join 時 , 把符合 join條件的數據緩存到臨時表, 暫停數據繼續分發. 本節我使用一個三表連接的 sql 來說明連續 hash join 時, 不同分發方式的不同行為。

使用 Broadcast 分發，沒有阻塞點。

測試三個表連接的 sql 如下, 加入 part 表, 使用 hint 讓優化器兩次 hash join 都使用 broadcast 分發。Replicate SQL 查詢性能類似。

SQL 執行時間為 42 秒，db time 為 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 順序的原則, 閱讀這個執行計劃。

1. 紅色 PX 進程作為生產者並行掃描 part, 通過 table queue 0 廣播給每個藍色的消費者 PX 進程 (第 7~9 行). 每個藍色的 PX 進程接收 part 的完整數據(第 6 行), 1.2M 行記錄, 並準備好第 5 行 hash join 的 build table.

2. 紅色 PX 進程作為生產者並行掃描 customer, 通過 table queue 1 廣播 broadcast 給每個藍色的消費者 PX 進程(第 12~14 行). 每個藍色的 PX 進程接收 customer 的完整數據(第 11 行), 1.5M 行記錄, 並準備好第 10 行 hash join 的 build table.

3. 藍色的 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, 放在最接近事實表的位置執行。

連續 hash 分發, 執行計劃出現阻塞點

使用以下 hints, 強製 SQL 使用 hash 分發。

SQL 執行時間為 1.5 分鍾, db time 為 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, 通過 table queue 0, 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, 通過 table queue 1, hash 分發作為消費者的 紅色 PX 進程(第 18~20 行). 每個紅色 PX 進程接收了 1/4 的 lineorder 數據(第 17 行), 大約75M 行記錄. 每個紅色 PX 進程在接收通過 table queue 1 接收數據的同時, 進行第 12 行的hash join, 並把 join 的結果集在 PGA 中作緩存, 使數據暫時不要繼續往上流動. 如果結果集過 大的話, 需要把數據暫存到臨時空間, 比如我們這個例子, 用了 7GB 的臨時空間. 你可以理解 為把 join 的結果集暫存到一個臨時表. 那麼, 為什麼執行計劃需要在這裏插入一個阻塞點, 阻 止數據繼續往上流動呢?

   
   

   這裏涉及生產者消費者模型的核心: 同一棵 DFO 樹中, 最多隻能有兩組 PX 進程, 一個 數據分發要求兩組 PX 進程協同工作; 這意味著同一時刻, 兩組 PX 進程之間, 最多隻能 存在一個活躍的數據分發, 一組作為生產者發送數據, 一組作為消費者接收數據, 每個PX 進程隻能扮演其中一種角色, 不能同時扮演兩種角色。 當紅色的 PX 進程通過 table queue 1 向藍色的 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 並把結果完全暫存到臨時空間之後. Table queue 2 的數據分發就開 始了.

紅色的 PX 進程作為生產者, 並行掃描 part, 通過 table queue 2, 分發給作為消費者的藍色 PX進程(第 7~9 行). 每個藍色 PX 進程接收了 1/4 的 part 數據(第 6 行), 大概 300k 行記錄, 並準備 好第 5 行 hash join 的 build table.

紅色的 PX 進程作為生產者, 把在第 12 行”HASH JOIN BUFFERED”操作, 存在臨時空間的對於customer 和 lineorder 連接的結果集, 讀出來, 通過 table queue 3, 分發給藍色的 PX 進程(第11~12 行). “HASH JOIN BUFFERED”這個操作使用了 7GB 的臨時空間, 寫 IO 7GB, 讀 IO 7GB, IO總量為 14GB.

每個藍色的 PX 進程作為消費者, 接收了大約 75M 行記錄. 對於通過 table queue 3 接收到的 數據, 同時進行第 5 行的 hash join, 並且通過 join 操作的數據進行第 4 行的聚合操作. 當 table queue 3 上的數據分發結束, 每個藍色的 PX 進程完成 hash join 和聚合操作之後, 再把各自的 聚合結果, 一行記錄, 通過 table queue 4, 分發給 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 報告, 你可以判斷這是否是合理的:

1. 檢查 estimated rows 和 actual rows 這兩列, 確定優化器對 hash Join 左右兩邊 cardinality 估算 是否出現偏差, 所以選擇 hash 分發.

2. 同樣檢查 hash join 操作的 estimated rows 和 actual rows 這兩列, 優化器對 hash join 結果集cardinality 的估算是否合理. 優化器會把 hash join 的兩邊視為獨立事件, 對 join 結果集cardinality 的估算可能過於保守, estimate rows 偏小. 對於星型模型的一種典型情況: 如果多 個維度表參與連接, 執行路徑很長, 一開始維度表的分發方式為 broadcast, 事實表不用分發,經過幾次 join 之後, 結果集 cardinality 下降很快, 後續 hash join 兩邊的 estimated rows 接近,導致優化器選擇 hash 分發.

3. 通過檢查每個 join 所過濾的數據比例, 確定優化器是否把最有效過濾數據的 join 最先執行,保證在執行路徑上流動的數據量最少.

布隆過濾在並行執行計劃中的使用非常普遍, 我將在本章節解釋這一數據結構及其作用. 從 11.2版本開始, 串行執行的 sql 也可以使用布隆過濾。

關於布隆過濾

布隆過濾是一種內存數據結構, 用於判斷某個元素是否屬於一個集合. 布隆過濾的工作原理圖2如下:

（引用自維基百科: https://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’條件, 隻計算來自中國地區的客戶訂單的利潤總和. 我們觀察 使用布隆過濾和不使用布隆過濾時性能的差別.

SQL 執行時間為 1 秒, db time 為 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 執行性能.

SQL 執行時間為 9 秒, db time 為 33.7 秒. 比使用布隆過濾時, 性能下降明顯. 優化器依然選擇replicate 的方式, 執行計劃中沒有 PX JOIN CREATE 和 PX JOIN USE 操作. db time 增加為原來 4 倍的 原因:

1. 當 PX 掃描 lineorder 時, 返回 300M 行記錄. 沒有布隆過濾作為條件, 每個 PX 進程需要從 存儲節點接收 75M 行記錄。

2. 進行第 5 行的 hash join 操作時, 每個 PX 進程需要連接 60k 行 customer 記錄和 75M 行lineorder 記錄. Join 操作的成本大幅增加。

由於沒有布隆過濾, Cell Offload Efficiency 下降為 83%.

HASH 分發時布隆過濾的生成, 傳輸, 合並與使用

我們通過 hint 強製使用 hash 分發, 觀察此時 sql 執行計劃中布隆過濾的生成和使用.

此時 sql 執行時間為 4 秒, db time 為 19.4 秒. 執行計劃第 6 行為 JOIN FILTER CREATE; 第 13 行為JOIN FILTER USE. 此例, PX 進程分布在多個 RAC 兩個實例, Hash 分發時涉及布隆過濾的生成,傳輸,合並和使用, 較為複雜, 具體過程如下:

1. 布隆過濾的產生: 4 個藍色的 PX 進程作為消費者, 通過 table queue 0 , 接收紅色的 PX 進程hash 分發的 customer 數據, 每個藍色的 PX 進程接收 15K 行記錄. 接收 customer 記錄的同時,實例 1 的兩個藍色 PX 進程在 SGA 共同生成一個布隆過濾, 假設為 B1; 實例 2 的兩個藍色 PX進程在 SGA 共同生成一個布隆過濾, 假設為 B2. 因為位於 SGA 中, 布隆過濾 B1 對於實例 1 的 兩個紅色的 PX 進程是可見的, 同樣, B2 對於實例 2 的兩個紅色 PX 進程也是可見的.

2. 布隆過濾的傳輸: 當紅色的 PX 進程完成對 hash join 左邊 customer 的掃描, 就會觸發布隆過 濾B1/B2的傳輸.實例1的紅色PX進程把B1發給實例2的藍色PX進程;實例2的紅色PX進程把B2發給實例1的藍色PX進程.

3. 布隆過濾的合並: 實例 1 的藍色 PX 進程合並 B1 和接收到的 B2; 實例 2 的藍色 PX 進程合並B2 和接收到的 B1. 合並之後, 實例 1 和 2 產生相同布隆過濾.

4. 布隆過濾的使用: 實例 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.

現實世界中, 由於使用不當, 並行操作無法並行, 或者並行執行計劃效率低下, 沒有獲得期望的性 能提升. 本節舉幾個典型例子.

1. 在 sql 中使用 rownum, 導致出現 PX SEND 1 SLAVE 操作, 所有數據都需要分發到一個 PX 進程，以給每一行記錄賦值一個唯一的 rownum 值, 以及 BUFFER SORT 等阻塞操作.

2. 使用用戶自定義的 pl/sql 函數, 函數沒有聲明為 parallel_enable, 導致使用這個函數的 sql 無法並行.

3. 並行 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.

不使用 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進程, PXSEND1SLAVE和BUFFERSORT兩個操作在之前的測試沒有出現 過. 根據跟隨 table queue 順序的原則, 我們來閱讀這個執行計劃:

1. 藍色的 PX 進程並行掃描 lineorder, 通過 table queue 0 把所有數據分發給一個紅色的 PX 進程 (第 10~12 行). 因為 rownum 是一個偽列, 為了保證每一行記錄擁有一個唯一行號, 對所 有數據的 rownum 賦值這個操作隻能由一個進程完成, 為 rownum 列賦值成為整個並 行執行計劃的串行點. 這就是出現 PX SEND 1 SLAVE 操作, 性能急劇下降的原因. 這個例 子中, 唯一活躍的紅色 PX 進程為實例 1 p008 進程. Lineorder 的 300M 行記錄都需要發送到實 例 1 p008 進程進行 rownum 賦值操作, 再由這個進程分發給 16 個藍色的 PX 進程進行數據並行插入操作.

2. 實例 1 p008 進程接收了 16 個藍色 PX 進程分發的數據, 給 rownum 列賦值(第 8 行 count 操作)之後, 需要通過 table queue 1 把數據分發給藍色的 PX 進程. 但是因為通過 table queue 0 的數 據分發的還在進行, 所以執行計劃插入一個阻塞點 BUFFER SORT(第 7 行), 把 rownum 賦值之 後的數據緩存到臨時空間, 大小為 31GB.

3. Table queue 0 的數據分發結束之後, 實例 1 p008 把 31GB 數據從臨時空間讀出, 通過 table queue 1 分發給 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，不支持並行執行。

以下例子中在 where 語句中使用函數 pk_test.f, 如果在 select 列表中使用函數 pk_test.f, 也會導致執行計劃變成串行執行。

查詢執行時間為 54 秒, db time 也為 54 秒。雖然我們指定使用 Dop=4 並行執行, 執行計劃實際是串行的。

在函數的聲明時設置 parallel_enable, 表明函數支持並行執行, 再次執行 sql.

此時查詢的執行時間為 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 操作是可以並行的.

小結

我列舉了使用並行執行時, 常見的三種問題:

1. 使用 rownum.

2. 自定義 pl/sql 函數沒有聲明 parallel_enable.

3. 並行 DML 時沒有 enable parallel dml.

希望通過以上三個例子, 希望讀者對調試並行執行計劃有一個更直觀的感受. 處理並行執行的問 題, sql monitor 報告是最好的分析工具. 對於並行 DDL 和 DML, Oracle 本身有一些限製, 可以參見官方文檔, 比如:

1. 表上的觸發器或者外鍵約束可能導致 DML 無法並行.

2. 包含 LOB 字段的非分區表, 不支持並行 DML 和 DDL; 包含 LOB 字段的分區表, 隻支持分區間的並行 DML 和 DDL.

3. 遠程表(通過 db link) 不支持並行 DML; 臨時表不支持並行 update, merge, delete.

這篇長長的文章更像是我在 Real-World Performance Group 的工作總結. 在大量實際項目中, 我們 發現很多開發或者 DBA 並沒有很好理解並行執行的工作原理, 設計和使用並行執行時, 往往也沒 取得最佳的性能. 對於並行執行, 已經有很多的 Oracle 書籍和網上文章討論過, 在我看來, 這些內 容更偏重於並行執行原理的解釋, 缺乏實際的使用案例. 我希望在本文通過真實的例子和數據,以最簡單直接的方式, 向讀者闡述 Oracle 並行執行的核心內容, 以及在現實世界中, 如果規避最 常見的使用誤區.也希望本文所使用 sql monitor 報告分析性能問題的方法, 對讀者有所啟示! 如果 現在你對以下並行執行的關鍵點, 都胸有成竹的話, 我相信現實世界中 Oracle 的並行執行問題都 不能難倒你。

• Oracle 並行執行為什麼使用生產者-消費者模型.
• 如何閱讀並行執行計劃.
• 不同的數據分發方式分別適合什麼樣的場景.
• 使用 partition wise join 和並行執行的組合提高性能.

• 數據傾斜會對不同的分發方式帶來什麼影響.
• 由於生產者-消費者模型的限製, 執行計劃中可能出現阻塞點.

• 布隆過濾是如何提高並行執行性能的.
• 現實世界中, 使用並行執行時最常見的問題.

下一篇文章, 我將介紹 12c 的新特性, adaptive 分發.

本文目前的內容和質量, 源於對初稿的多次審校和迭代. 

本文的兩個難點, 1) 連續 hash 分發時出 現阻塞點; 2) hash 分發時使用布隆過濾的具體過程, 得到了我英國同事 Mike Hallas 的解答和確認.我的同事董誌平對初稿的做了詳細的審校, 指出多處紕漏. 本文的一些內容是在他的建議下增加 的, 比如 Partition Wise Join 時, DoP 大於分區數時 partition wise join 會失效, 比如 replicate 方式為 什麼不能完全替代 broadcast 分發. 我的同事徐江和李常勇, 我的朋友蔣健閱讀初稿之後, 也提供了諸多反饋, 在此一並感謝!

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

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

  上一篇：  理論實踐：循序漸進理解AWR細致入微分析性能報告

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