深入並行：從並行加載到12c Adaptive特性深度理解Oracle並行

陳煥生

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

編輯手記：感謝陳煥生的精品文章，這篇文章首發在ACOUG，在此轉載分享給大家，Sidney撰寫這個係列的文章時間跨度也有兩年，下篇剛剛出爐。

上篇分為兩篇文章：

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

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

在深入理解Oracle的並行執行(上)中, 闡述了一個並行執行計劃包含的四個核心概念:

• DFO: Data FlowOperators, 可以並行執行的操作, 比如全表掃描, hash join.

• Table Queue: 生產者和消費者之間的數據分發. 常見的數據並行分發方式有broadcast, hash. 12c引入多種新的數據分發, 比如replicate, 更加智能的adaptive分發.

• Granule: 並行掃描數據時, 表的數據如何切分, 按照地址區間, 或者以分區為單位的

• QC: 對生產者和消費者PX進程進行管理和調度

 

本篇文章, 我將談談並行執行的其他兩個主題

1. 並行加載數據時四種數據分發方式, 分別為None/ Partition/Random/Randomlocal.

2. 12C的Adaptive 分發特性

測試環境和數據

 

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

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

 

表名	行數
lineorder	300005811
part	1200000
customer	1500000

請輸並行加載

對於 CTAS(CreateTable As Select) 或者 IAS(Insert asselect) 形式的並行插入語句, 作為生產者的 PX 進程執行查詢操作, 作為消費者的 PX 進程執行數據插入. 生產者和消費者之間數據存在以下四種分發方式.

1. None: 隻需一組PX進程, 把查詢, 數據加載操作合並在一起. 因為數據不需要通過進程間或者節點間通信進行分發, 加載大量數據時, 這種方式可以節省大量CPU, 通常是並行加載最快的方式.使用這種方式需要注意兩點:

   ① 數據傾斜: 如果查詢部分數據存在傾斜, 會導致後續插入操作的也存在執行傾斜

   ② 內存的消耗: 對於分區表, 因為每個PX進程都會插入數據到每一個分區, 每個分區的數據需要0.5M    內存的緩衝數據, 對於使用HCC(Hybrid ColumnarCompress: 混合列壓縮)壓縮的表, 緩衝區大小為1.5M, 以提高寫性能. 當分區數很多, 或者DoP很大時, 總的內存消耗接近DoP * 分區數 * 0.5M(對於HCC壓縮表是1.5M).

2. Partition: 根據目標表的分區屬性, 查詢PX進程把數據發送給相應加載PX進程. 當分區數大於DoP時, 每個加載 PX進程會平均加載多個分區的數據. 這種方式操作每個分區的PX進程最少, 消耗的內存也最少. 如果分區間的數據存在傾斜, 加載PX進程會出現執行傾斜.

3. Random: 查詢 PX 進程把數據按照round-robin的方式分送給每個加載PX進程.

4. Random local: 於 random 方式類似, 使用 slaves mapping 特性, 按 round-robin 的方式分發給本實例的加載 PX 進程, 避免節點間的數據傳輸. Random local 的數據分發成本比 Random 更低, 消耗的內存也更少. 如果使用 none 分發內存過大, 又不能使用 partition 分發因為分區數據存在傾斜, 那應該選擇 random local 的分發方式.

 

PQ_Distribute 這個 hint 除了可以控製 hash join 時數據的分發方式, 從11.2開始, 也可以控製並行加載時數據在查詢PX進程和加載PX進程之間的分發方式. 為了說明數據傾斜, 內存消耗對各種分發方式的影響, 我構造一個 range-hash 複合分區表, 一級分區為32 個 range 分區, 每個分區區間大小不同使數據存在傾斜, 二級分區為16個 hash 分區, 總共512個子分區. 並行度為64.

None 無數據分發

通過hint pq_distribute(t_nonenone)使數據不需要分發.

 

SQL執行時間為2.1分鍾, DB Time為18.5分鍾. 執行計劃隻需一組藍色的PX進程.

每個PX進程都加載512個分區的數據, 最大的PGA消耗為13.86GB, 接近預估的值:  DoP*[Number of Subpartitions]*0.5MB = 64*512*0.5MB = 16GB. IO Interconnect的流量=read bytes +2*(Write Bytes), 每秒接近6GB. CPU的AAS保持在60左右, 這是期望的情況, 不需要數據分發, 充分利用係統的 CPU資源和IO帶寬, 進行數據加載,

Partition 分發

通過 hint pq_distribute(tpartition) , 使用partition分發方式.

SQL執行時間為39秒, db time為24.2分鍾. DB time比none分發時的兩1.5倍, 因為第5行PX SEND PARTITION(KEY)占23%的db time, 第4行的PX RECEIVE占8.41%的db time, 數據分發一共占 31.41%的db time. SQL執行時間幾乎為none分發時的兩倍, 是因為分區數據存在傾斜導致加載的PX進程之間存在執行傾斜.

每個藍色的PX加載進程隻需加載8個分區的數據, 最大的PGA消耗為1.44GB, 僅為none分發的十分之一, IO interconnect的流量接近2.2GB每秒. 於none分發的峰值6GB每秒的流量相差很多. CPU的AAS隻在40左右.

從視圖V$PQ_TQSTAT觀察並行傾斜, 每個紅色的查詢PX進程通過 table queue 0分發大約4.6M行記錄, 藍色的加載PX進程接收的數據接收的記錄數差別很大, 有的超過10M, 有的接近4百萬, 有的隻有2M.

Random local 分發

通過hint pq_distribute(trandom_local) 使用random local的分發方式.

insert  /*+ pq_distribute(t random_local) */ intolineorder_p512 t select * from lineorder;

 

SQL執行時間為32秒, db time為27.5分鍾. 相比partition分發, Random local分發的db time增加三分鍾, 但是sql執行時間縮短了, 因為消除了並行執行傾斜.

每個藍色的PX加載進程隻需加載256個分區的數據, 最大的PGA消耗為7.88GB, 大概為none分發時的一半.  IO Interconnect的流量每秒大概3GB.

Random 分發

通過hint pq_distribute(trandom) 使用random local的分發方式.

SQL執行時間為49秒, db time為29.1分鍾. Random分發相對於random local分發, 性能明顯下降, 實際中很少使用random分發.

每個藍色的PX加載進程加載512個分區的數據, 和none分發類似,  最大的PGA消耗為13.77GB.

IO interconnect最大的流量為2.5GB每秒.

 小結

為了充分發揮係統的CPU和IO能力, 在並行加載時, 應該盡可能的使用none分發的執行計劃. 除非查詢部分存在傾斜, 或者內存不夠限製無法使用none分發.  當目標表的分區數很多時, 優化器考慮到none分發消耗的內存過濾, 過於保守會選擇partition分發或者random local分發的方式, 比如上麵的例子中, 沒有使用pq_distribute的話, DoP=64時, 優化器選擇random_local的分發方式. 因此實際中, 往往需要使用pq_distribute指定合適的分發方式.

 

Adaptive 分發介紹

12c引入了adaptive分發特性, 執行計劃中的分發操作為PX  SEND HYBRID HASH. Adaptive分發在運行時, 根據hash join左邊的數據量, 決定何種分發方式. Adaptive分發也可以解決連接鍵存在數據傾斜, 導致hash分發並行執行傾斜的情況. Adaptive分發的工作機製如下:

1.   執行計劃中, 對hash join左邊分發之前, 會插入一個STATISTICS COLLECTOR操作, 用於運行時確定hash join左邊數據集的大小. 如果hash join左邊的數據量小於並行度的兩倍, 那麼對於hash join左邊的分發會切換為broadcast方式, 對hash join右邊的分發為round-robin. 如果hash join左邊的數據大於等於並行度的兩倍, 對於hash join兩邊的分發方式都為hash, 和傳統的hash分發一樣.

2.   如果存在柱狀圖信息, 表明hash join右邊連接鍵上存在數據傾斜, 大部分數據為少數熱門的值.  硬解析時, 會對hash join右邊的表進行動態采樣, 確認熱門的值. 通過如下分發方式消除數據傾斜的影響:

·      Hash join的左邊, 熱門的數據會被廣播到每個接收者, 不熱門的數據被hash分發.

·      Hash join的右邊, 熱門的數據通過round-robin的方式發送, 非熱門的數據被hash分發.

 

我將在本章演示 Adaptive分發的動態特性, 以及如果處理數據傾斜的.  本章的所有測試, optimizer_adaptive_features設置為true, 以使用adaptive分發特性.

 

Hash join左邊數據量小於DoP的兩倍時

測試SQL如下, DoP=4. 在customer上使用c_custkey<8的條件, 返回7行記錄, 剛好小於8(=2*DoP).

查詢時間為3秒, 觀察Timeline列時間軸信息, 執行計劃中的操作 first active的時間在3秒左右, 硬解析花了接近3秒的時間.  SQL執行過程如下:

1.  作為生產者的紅色PX進程對customer的掃描過濾結果為7行記錄, 在進行adaptive分發之前, 執行計劃插入STATISTICS COLLECTOR操作, 判斷hash join左邊結果集的大小於兩倍DoP的關係. 因為結果集隻有7行記錄, 小於8(兩倍DoP), 所以第8行的PX SEND HYBRID HASH操作實際為broadcast分發. 通過table queue 0, 每個藍色的PX進程接收了7行customer記錄, 總共28行, 並創建布隆過濾:BF0000, 發送給紅色的PX進程, 並準備好第5行hash join的build table.

2.  作為生產者的紅色PX進程並行掃描lineorder時使用布隆過濾:BF0000, lineorder過濾完隻有1507行, 第5行的hash join結果集也為1507, 說明這是一個完美的布隆過濾. 因為布隆過濾卸載到存儲節點之後, 返回數據量占總體300M行記錄的比例太小, Cell Offload Efficiency為100%. 紅色的PX進程, 把lineorder的1507行記錄, 通過table queue 1, 以round-robin的方式, 分發給藍色的PX進程.

3.  每個作為消費者的藍色PX進程, 接收lineorder大約377行記錄之後, 和hash join左邊的7行記錄進行連接. 連接結果集為1507行記錄, 進行聚合之後為4行記錄, 通過table queue 2發給QC.

4.  QC做最終的聚合, 返回數據.

 

Customer符合條件的7行記錄, 都由實例2 P003進程掃描得到, 通過table queue 0, 廣播給每個消費者PX進程. 對於lineorder的分發, 雖然每個生產者發送的數據量存在差異, 分別為311/552/313/331, 發送記錄數之和為1507. 因為分發方式為round-robin, 每個消費者接收的數據量接近平均, 分別為377/375/378/377.

Hash join左邊數據量大於等於DoP兩倍時

把條件改為c_custkey<=8,customer有 8行記錄符合條件, 等於兩倍DoP.

查詢執行時間為2秒. 此時執行計劃和c_custkey<8時相同, Plan Hash Value都為1139249071 , table queue, 布隆過濾的位置和編號是相同的, 並行執行的順序完全一樣.

因為hash join左邊的數據集有8行記錄, 等於兩倍DoP, PX SEND HYBRID HASH操作對customer數據的分發為hash分發. 紅色的PX進程通過table queue 0, hash分發8行記錄. 4個藍色的PX進程一共接收8行customer記錄, 然後分別對c_custkey列生成布隆過濾:BF000, 發送給4個紅色的PX進程.

作為生產者的4個紅色PX並行掃描lineorder時, 使用布隆過濾:BF0000, 過濾完為1620行記錄, 第5行的hash join結果集為1620行, 說明布隆過濾:BF0000是完美的. 1620行記錄table queue 1, hash分發給4個藍色的PX進程. 藍色的PX進程接收lineorder數據之後, 進行hash join和聚合, 再把結果集發給QC.

Customer符合c_custkey<=8的8行數據, 都由實例2 P002進程掃描獲得, 然後hash分發給4個消費者, 分別接收2/2/3/1行. 對lineorder過濾之後的1620行記錄, 通過hash分發之後不像round-robin那樣均勻, 4個消費者分別接收252/197/628/543行.

小節

本節介紹 adaptive分發如何根據hash join左邊數據集的大小, 和兩倍DoP的關係, 決定對hash join左右兩邊的分發方式. 兩倍DoP這個閥值由隱含參數_px_adaptive_dist_method_threshold控製, 默認值為0, 代表兩倍DoP.

 

請Adaptive 分發如何處理數據傾斜

為了演示adaptive分發如何處理數據傾斜, 新建兩個表, customer_skew包含一條c_custkey=-1的記錄, lineorder_skew 90%的記錄, 兩億七千萬行記錄 lo_custkey=-1.

Adaptive分發, 意想不到的硬解析問題

為了使用adaptive分發特性, 解決lineorder_skew.lo_custkey數據傾斜的問題, 我們需要:

1.  設置optimizer_adaptive_features 為 true.

2.  收集lineorder_skew在lo_custkey列上的柱狀圖信息.

 

推薦使用 DBMS_STATS.SEED_COL_USAGE, 監控sql是使用了哪些列, 以及如何使用. 通過explain plan for命令解析sql, 給優化器提供關鍵的信息, 比如那些列是連接鍵. 使用默認值選項收集統計信息時, 係統根據已有的信息, 自動收集所需的統計信息, 包括傾斜列上的柱狀圖信息. 下麵的過程演示了DBMS_STATS.SEED_COL_USAGE簡單使用方法.

 

過程如下, 一開始, LO_CUSTKEY 列上沒有柱狀圖信息(HISTOGRAM=NONE).

 

進行一次硬解析

查詢對於 lineorder_skew 的監控結果, 在 lo_custkey 上使用的相等連接的操作, 為統計信息的收集提供了關鍵信息.

使用默認 auto 選項收集統計信息

重新收集統計信息之後, lo_custkey列上有了HYBRID類型的統計信息.

設置optimizer_adaptive_features為true, 使用與hash分發時相同的sql重新執行:

出乎意料的是, sql 的執行時間為65秒, 比使用 hash 分發時58秒還慢了7秒. 

執行計劃的結構以及執行順序和 hash 分發時類似, 但是分發的操作不一樣. 從第8行 STATISTICS COLLECTOR, 第9行PX SEND HYBRID HASH 這兩個操作可以確認,  並行執行使用了adaptive分發.  因為 lineorder_skew.lo_custkey 數據存在傾斜, 對於 lineorder_skew 的分發操作為 PX SEND HYBRID HASH(SKEW).

奇怪的是從 Timeline 列時間軸信息可以看到, 所有並行操作的 first active 時間為36秒, 這意味著所有 PX 進程在36秒時才開始執行 sql, sql 的實際執行時間隻需30秒.  在執行查詢時, SQL 一般需要經過解析-執行-返回數據等三個階段, 因為我們使用 adaptive 分發, 並且 lineorder_skew.lo_custkey 列上的柱狀圖信息暗示了數據傾斜, 在硬解析階段需要對 lineorder_skew 進行采樣, 確認熱門值.

在 sql 的活動信息中, 一半時間處於硬解析階段, 出現 cursor: pin S, cursor: pinS wait on X等 shared pool 相關的等待, 同時還有多塊讀的 IO 和 GC 等待, 這是采樣 lineorder_skew 引起的. 在硬解析之後 sql 執行時, AAS 大於4, 至少4個 PX 進程同時活躍, 這是 adaptive 分發消除並行執行傾斜之後, 期望的效果. 這個例子中, Adaptive 分發帶來的性能改進被長時間硬解析問題掩蓋了.下一節, 我們將通過10046事件, 分析硬解析為什麼需要30秒左右的時間.

DS_SKEW 采樣語句造成長時間的硬解析

下麵的測試, 我們可以確定硬解析過長的問題隻有使用 adaptive 分發時出現(新特性引入新問題, 你應該不會驚訝吧J). 默認replicate 方式, 硬解析時間為0.3秒, adaptive 分發, 硬解析時間為24.89秒.

我使用orasrp這個工具分析adaptive分發硬解析時的10046跟蹤文件. 分析遞歸調用樹(Session Call Graph)部分, 幾乎所有的硬解析時間都來自 sql hash value=1157599518這個遞歸sql.

 

SQL hashvalue=1157599518如下, DS_SKEW的注釋表明處理數據傾斜時, 這條sql用以確定最熱門的值.

 

DS_SKEW采樣語句需要24.8秒的原因在於:

1.  使用行采樣, 而不是塊采樣. 采樣語句使用SAMPLE, 而不是SAMPLE BLOCK, 導致了661678次物理讀.

2.  沒有一個中斷機製, 當采樣sql執行時間過長時取消進行中斷.

DS_SKEW采樣語句執行過長是一個已知的問題, 通過bug 21384810在12.2版本修複, 12.1.0.2已有補丁發布.

 

Bug 21384810 -GCW 12C: PARSE TIME REGRESSION DUE TO JOIN SKEW HANDLING (DS_SKEW QUERY)

 

因為DS_SKEW采樣語句與adaptive分發特性是綁定在一起的, 使用adaptive分發無法繞過DS_SKEW采樣語句. 如果你發現硬解析的成本高於adaptive分發帶來的性能提升, 你可以通過以下兩種方法關閉adaptive分發特性.

1.  optimizer_adaptive_features=false.

2.  _px_join_skew_handling=false,此時你可以保留optimizer_adaptive_features=true,使用其他adaptive特性.

 

Adaptive 分發的實際執行效果

第二次執行以下SQL, 觀察不需要硬解析時, adaptive分發的實際效果.

查詢執行時間為28秒, 接近replicate時23秒.

對於 lineorder_skew300M 行記錄的adaptive分發, 和hash join操作, 消耗了大部分的db cpu.

使用 adaptive 分發時, 藍色PX進程不再出現執行傾斜的情況.

 

從V$PQ_TQSTAT視圖可以確認, 對lineorder_skew通過table queue 1的adaptive分發, 每個消費者接收了75M行記錄左右, adaptive分發解決了數據傾斜的影響.