案例技術(shù)parallel cution zhs深入理解Oracle

1、深入理解 Oracle 的并行執(zhí)行(上)作者: 目錄:Oracle 的并行執(zhí)行2術(shù)語說明2測試環(huán)境和數(shù)據(jù)2并行初體驗3串行執(zhí)行3并行執(zhí)行4小結(jié)6生產(chǎn)者-­消 模型6Broadcast 分發(fā), 一次數(shù)據(jù)分發(fā)6生產(chǎn)者-­消 模型工作原理7小結(jié)9如何閱讀并行執(zhí)行計劃9HASH 分發(fā)方式, 兩次數(shù)據(jù)分發(fā)10小結(jié)12Replicate, Broadcast 和 Hash 的選擇12Hash 分發(fā), 有時是唯一合理的選擇12使用 broadcast 分發(fā), 糟糕的性能14小結(jié), Broadcast 和 Hash 分發(fā)的陷阱16Partition Wise Join, 消除分發(fā)的額外開銷

2、17Partition Wise Join, 不需要數(shù)據(jù)分發(fā)17當 DoP 大于分區(qū)數(shù)時, Partition Wise Join 發(fā)生19小結(jié)20數(shù)據(jù)傾斜對不同分發(fā)方式的影響20Replicate 方式, 不受數(shù)據(jù)傾斜的影響21Hash 分發(fā), 數(shù)據(jù)傾斜造成執(zhí)行傾斜22小節(jié)24HASH JOIN BUFFERED, 連續(xù) hash 分發(fā)時執(zhí)行計劃中的阻塞點24使用 Broadcast 分發(fā), 沒有阻塞點24連續(xù) hash 分發(fā), 執(zhí)行計劃出現(xiàn)阻塞點25小結(jié)27Hash join 和過濾27關(guān)于 過濾27過濾對 hash join 性能的改進28使用 過濾時的性能28不使用 過濾時的性能29H

3、ASH 分發(fā)時過濾的生成, 傳輸, 合并與使用30小結(jié)31并行執(zhí)行計劃中典型的串行點31Rownum, 導致并行執(zhí)行計劃效率低下32自定義 PL/SQL 函數(shù)沒有設(shè)置 parallel_enable, 導致無法并行34并行 DML, 沒有 enable parallel dml, 導致 DML 操作無法并行36小節(jié)37總結(jié)37致謝38作者簡介38Oracle 的并行執(zhí)行Oracle 的并行執(zhí)行是一種分而治之的. 執(zhí)行一個 sql 時, 分配多個并行進程同時執(zhí)行數(shù)據(jù)掃描, 連接以及聚合等操作, 使用的資源, 得到更快的 sql 響應時間. 并行執(zhí)行是充分利用硬件資源, 處理大量數(shù)據(jù)時的 技術(shù).在

4、本文中, 在一個簡單的星型模型上, 我會使用大量例子和 sql monitor 報告, 力求以最直觀簡單的方式, 向讀者闡述并行執(zhí)行的內(nèi)容:· Oracle 并行執(zhí)行為什么使用生產(chǎn)者-­消模型.· 如何閱讀并行執(zhí)行計劃.· 不同的數(shù)據(jù)分發(fā) 別適合什么樣的場景.· 使用 partition wise join 和并行執(zhí)行的組合提高性能.· 數(shù)據(jù)傾斜會對不同的分發(fā)方式帶來什么影響.· 由于生產(chǎn)者-­消 模型的限制, 執(zhí)行計劃中可能出現(xiàn)阻塞點.·過濾是如何提高并行執(zhí)行性能的.· 現(xiàn)實世界中, 使用并行

5、執(zhí)行時最常見的問題.術(shù)語說明:1. S: 時間秒.2. K: 數(shù)量一千.3. M: 數(shù)量一百萬, 或者時間分鐘.4. DoP: Degree of Parallelism, 并行執(zhí)行的并行度.5. QC: 并行的 Query Coordinator.6. PX 進程: Parallel Execution Slaves.7. AAS: Average active session, 并行執(zhí)行時平均的活動會話數(shù).8. 分發(fā): pq distribution method, 并行執(zhí)行的分發(fā)方式, replicate, broadcast, hash 和 adaptive分發(fā)等 4 種方式, 其中

6、adaptive 分發(fā)是 12c 引入的的新特性, 我將在本篇文章闡述.9. Hash join 的左邊: 驅(qū)動表, the build side of hash join, 為 .10. Hash join 的右邊: 被驅(qū)動表, the probe side of hash join, 為大表.11. 過濾: bloom filter, 一種內(nèi)存數(shù)據(jù)結(jié)構(gòu), 用于一個元素是否屬于一個集合.測試環(huán)境和數(shù)據(jù)Oracle 版本為 12.1.0.2.2, 兩個節(jié)點的 RAC, 硬件為 Exadata X3-­8.這是一個典型的星型模型, 事實表 lineorder 有 3 億行, 維度表 p

7、art/customer 分別包含 1.2M和 1.5M 行, 3 個表都沒有進行分區(qū), lineorder 大小接近 30GB.表名行數(shù)lineorder300005811part1200000customer1500000selectowner seg_owner,segment_name seg_segment_name, round(bytes/1048576,2) SEG_MBfrom38dba_segments whereowner = 'SID'and segment_name in ('LINEORDER','PART','

8、;CUSTOMER')/OWNER SEGMENT_NAME SEGMENT_TYPESEG_MBSIDLINEORDERTABLE30407.75SIDCUSTOMERTABLE168SIDPARTTABLE120本篇文章所有的測試, 除非特別的說明, 我關(guān)閉了 12c 的 adaptive plan 特性, 參數(shù)optimizer_adaptive_features 被默認設(shè)置為 false. Adaptive 相關(guān)的特性如 cardinality feedback, adaptive distribution method, adaptive join 都啟用. 如果檢查執(zhí)行計劃

9、的 outline 數(shù)據(jù), 你會發(fā)現(xiàn) 7 個優(yōu)化器相關(guān)的隱含參數(shù)被設(shè)置為關(guān)閉狀態(tài). 事實上, 12c 優(yōu)化器因為引入 adaptive plan 特 性, 比以往版本復雜得多, 剖析 12c 的優(yōu)化器的各種新特性, 我覺得非常具有性, 或許我會在另一篇文章里嘗試一下JJselect * from table(dbms_xplan.display_cursor('77457qc9a324k',0,outline);.Outline Data/*+*/BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_

10、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_P

11、ARAM('_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并行初體驗串行執(zhí)行以下 sql 對 custome

12、rs 和 lineorder 連接之后, 計算所有訂單的全部利潤. 串行執(zhí)行時不使用parallel hint:select /*+ monitor */ sum(lo_revenue)fromlineorder, customer wherelo_custkey = c_custkey;串行執(zhí)行時, sql 執(zhí)行時間為 1.5 分鐘, db time 為 1.5 分鐘. 執(zhí)行計劃有 5 行, 一個用戶進程工作完成了對 customer, lineorder 兩個表的掃描, hash join, 聚合以及返回數(shù)據(jù)的所有操作. 此時AAS(average active sessions)為 1,

13、 sql 執(zhí)行時間等于 db time. 幾乎所有的 db time 都為 db cpu, 72% 的 cpu 花在了第二行的 hash join 操作. 因為測試機器為一臺 Exadata X3-­8, 30GB 的 IO 請求在一秒之內(nèi)處理完成. Cell offload Efficiency 等于 87%意味著經(jīng)過節(jié)點掃描, 過濾不需要的列, 最終返回計算節(jié)點的數(shù)據(jù)大小只有 30GB 的 13%.并行執(zhí)行使用 hint parallel(4), 指定 DoP=4 并行執(zhí)行同樣的 sql:select /*+ monitor parallel(4)*/ sum(lo_revenu

14、e)fromlineorder, customer wherelo_custkey = c_custkey;SQL 執(zhí)行時間為 21s, db time 為 1.4 分鐘. DoP=4, 在兩個實例上執(zhí)行. 執(zhí)行計劃從 5 行增加為 9 行, 從下往上分別多了PX BLOCK ITERATOR, SORT AGGREGATE, PX SEND QC(RANDOM)和PX COORDINATOR這四個操作.其中 3 到 8 行的操作為并行處理, sql 的序為: 每個 PX 進程掃描維度表 customer(第 6 行), 以數(shù)據(jù)塊地址區(qū)間作為 (第 7 行)掃描四分之一的事實表 lineord

15、er(第 8 行), 接著進行 hash join(第 5 行), 然后對連接之后的數(shù)據(jù)做預先聚合(第 4 行), 最后把結(jié)果給 QC(第三行). QC 接收數(shù)據(jù)(第 2 行)之后, 做進一步的匯總(第 1 行), 最后返回數(shù)據(jù)(第 0 行).SQL 執(zhí)行時間比原來快了 4 倍, 因為最消耗時間的操作, 比如對 lineorder 的掃描, hash join 和聚合, 我們使用 4 個進程并行處理, 因此最終 sql 執(zhí)行時間為串行執(zhí)行的 1/4. 另一方面, db time 并沒有明顯下降, 并行時 1.4m, 串行時為 1.5m, 從系統(tǒng)的角度看, 兩次執(zhí)行消耗的系統(tǒng)資源是一 樣的.Do

16、P=4 時, 因為沒有涉及數(shù)據(jù)的分發(fā)(distribution), QC 只需分配一組 PX 進程, 四個 PX 進程分別為實例 1 和 2 的 p000/p0001. 我們可以從系統(tǒng)上查看這 4 個 PX 進程. 每個 PX 進程消耗大致一樣的db time, CPU 和 IO 資源. AAS=4, 這是最理想的情況, 每個 PX 進程完成同樣的工作量, 一直保持活躍. 沒有串行點, 沒有并行執(zhí)行傾斜.AAS=4, 查看活動時, 為了更展示活動, 注意點掉”CPU Cores”這個復選框.在 Linux 系統(tǒng)上顯示這四個 PX 進程.oracleexa01db01 sidney$ ps -e

17、f | egrep "p0001_SSB"oracle208881 4 2014 ?18:50:59 ora_p000_SSB1oracle208921 4 2014 ?19:01:29 ora_p001_SSB1 oracleexa01db01 sidney$ ssh exa01db02 'ps -ef | egrep "p0001_SSB"' oracle569101 4 2014 ?19:01:03 ora_p000_SSB2oracle569121 4 2014 ?18:53:30 ora_p001_SSB2小結(jié)本節(jié)的例子中, D

18、oP=4, 并行執(zhí)行時分配了 4 個 PX 進程, 帶來 4 倍 的性能提升. SQL monitor 報告包含了并行執(zhí)行的總體 和各種細節(jié), 比如 QC, DoP, 并行執(zhí)行所在的實例, 每個 PX 進程消耗的資源, 以及執(zhí)行 SQL 時 AAS. , 深入討論并行執(zhí)行的生產(chǎn)者-­消 模型.生產(chǎn)者-消 模型在上面并行執(zhí)行的例子中, 每個 px 進程都會掃描一遍維度表 customer, 然后掃描事實表 lineorder 進行 hash join. 這時沒有數(shù)據(jù)需要進行分發(fā), 只需要分配一組 px 進程. 這種 replicate 維度表的行為, 是 12c 的新特性, 由參數(shù)_p

19、x_replication_enabled .更常見情況是并行執(zhí)行時, QC 需要分配兩組 PX 進程, 互為生產(chǎn)者和消, 協(xié)同工作, 完成并行執(zhí)行計劃. 架構(gòu)圖1如下:Broadcast 分發(fā), 一次數(shù)據(jù)分發(fā)為了舉例說明兩組 px 進程如何協(xié)作的, 設(shè)置_px_replication_enabled 為 false. QC 會分配兩組 PX進程, 一組為生產(chǎn)者, 一組為消.見下圖, 此時 sql 執(zhí)行時間為 23s, 執(zhí)行時間變慢了 2s, db time 仍為 1.5 分鐘.最大的變化來自執(zhí)行計劃, 現(xiàn)在執(zhí)行計劃有 12 行. 增加了對 customer 的并行掃描PX BLOCK ITE

20、RATOR(第 8 行), 分發(fā)PX SEND BROADCAST和接收PX RECEIVE. 執(zhí)行計劃中出現(xiàn)了兩組 PX 進程, 除了之前藍色的多人標志, 現(xiàn)在出現(xiàn)了紅色的多人標志. 此時, SQL 的序為:1 自白皮書 Parallel Execution with Oracle Database 12c Fundamentals: 130766.pdf1. 4 個紅色的 PX 進程扮演生產(chǎn)者角色, 掃描維度表 customer, 把數(shù)據(jù)通過 broadcast 的 發(fā)給每一個扮演消 的藍色 PX 進程. 因為 DoP=4, 每一條被掃描出來的被 了 4 份, 從 sql monitor

21、的第 9 行, customer 掃描返回 1.5m 行數(shù)據(jù), 第 8 行的分發(fā)和第 7 行的接受之時, 變成了 6m 行, 每個作為消的藍色 px 進程都持有了一份完整包含所有custome的數(shù)據(jù), 并準備好第 5 行 hash join 的 build table.2. 4 個作為消的藍色 PX 進程, 以數(shù)據(jù)塊地址區(qū)間為掃描事實表 lineorder(第 10/11 行); 同時和已經(jīng)持有的 customer 表的數(shù)據(jù)進行 hash join(第 5 行), 然后對滿足 join 條件的數(shù)據(jù)做預聚合(第 4 行), 因為我們的目標是對所有 lo_revenue 求和, 聚合之后每個 PX

22、 進程只需輸出一個總數(shù).3. 4 個藍色的 PX 進程反過來作為生產(chǎn)者, 把聚合的數(shù)據(jù)發(fā)給消 QC(第 3 行和第 2 行). 由 QC對接收到 4 行做最后的聚合, 然后返回給用戶.4. 使用 broadcast 的分發(fā)方式, 只需要把 customer 的數(shù)據(jù)廣播給每個消. Lineorder 的數(shù)不需要重新分發(fā). 因為 lineorder 的數(shù)據(jù)量比 customer 大的多, 應該避免對 lineorder 的數(shù)據(jù)進行分發(fā), 這種執(zhí)行計劃非常適合星型模型的數(shù)據(jù).觀察 sql monitor 報告中 Parallel 下的 , 紅色的 PX 進程為實例 1、2 上的 p002/p003

23、進程, 藍色的 PX 進程為 p000/p001 進程, 因為藍色的 PX 進程負責掃描事實表 lineorder, hash join 和聚合, 所以消耗幾乎所有的 db time.生產(chǎn)者-消 模型工作原理并行 之后, 可以通過視圖 V$PQ_TQSTAT, 驗證以上描述的執(zhí)行過程.1. 實例 1、2 上的 p002/p003 進程作為生產(chǎn)者, 幾乎平均掃描 customer 的 1/4 , 把每一條記錄廣播給 4 個消 PX 進程, 發(fā)送的數(shù)之和為 6m 行. 通過 table queue 0(TQ_ID=0), 每個作為消 的 p000/p001 進程, 接收了完整的 1.5m 行 cu

24、stomer , 接收的數(shù)之和為6m 行.2. 實例 1、2 上的 p000/p0001 進程作為生產(chǎn)者, 通過 table queue 1(TQ_ID=1), 把聚合的一條結(jié)果 發(fā)給作為消 的 QC. QC 作為消, 接收了 4 行.SELECTdfo_number, tq_id, server_type, instance, process, num_rowsFROMV$PQ_TQSTAT ORDER BYdfo_number DESC, tq_id, server_type desc, instance, process;DFO_NUMBERTQ_IDSERVER_TYPEINSTANC

25、EPROCESSNUM_ROWS10Producer1P002146193210Producer1P003150189210Producer2P002157571210Producer2P003146046410Consumer1P000150000010Consumer1P001150000010Consumer2P000150000010Consumer2P001150000011Producer1P000111Producer1P001111Producer2P000111Producer2P001111Consumer1QC413 rows selected.那么, 以上的輸出中, D

26、FO_NUMBER 和 TQ_ID 這兩列表示什么意思呢?1. DFO 代表 Data Flow Operator, 是執(zhí)行計劃中可以并行執(zhí)行的操作. 一個 QC 代表一棵 DFO 樹(tree), 包含多個 DFO; 同一個 QC 中所有并行操作的 DFO_NUMBER 是相同的, 此例中, 所有DFO_NUMBER 為 1. 執(zhí)行計劃包含多個 QC 的例子也不少見, 比如使用 union all 的語句, union all 每個分支都是的 DFO 樹, 不同的 DFO 樹之間可以并行執(zhí)行. 本篇文章僅討論執(zhí)行計劃只有一個 QC 的情況.2. TQ 代表 table queue, 用以 P

27、X 進程之間或者和 QC 通信連接. 以上執(zhí)行計劃中, table queue 0 為 PX 進程之間的連接, table queue 1 為 PX 進程和 QC 之間的連接. 生產(chǎn)者通過 table queue 分發(fā)數(shù)據(jù), 消從 table queue 接收數(shù)據(jù). 不同的 table queue 編號, 代表了不同的數(shù)據(jù)分發(fā). 通過 table queue, 我們可以理解 Oracle 并行執(zhí)行使用生產(chǎn)者-­消模型的本質(zhì):· 同一棵 DFO 樹中, 最多只有兩組 PX 進程. 每個生產(chǎn)者進程都一個和每個消進程的連接, 每個 PX 進程和 QC 都接. 假設(shè) DoP=n,

28、連接總數(shù)為(n*n + 2*n), 隨著 n 的增長, 連接總數(shù)會爆炸型增長. Oracle 并行執(zhí)行設(shè)計時, 采用生產(chǎn)者和消模型, 考慮到連接數(shù)的復雜度, 每個 DFO 最多只分配兩組 PX 進程. 假設(shè) DoP=100 時, 兩組 PX 進程之間的連接總數(shù)為 10000. 假設(shè)可以分配三組 PX 進程一起完成并行執(zhí)行計劃, 那么三 組 PX 之間連接總數(shù)會等于 1 百萬, 維護這么多連接, 是一個不可能的任務(wù).· 同一棵 DFO 樹中, 兩組 PX 進程之間, 同一時間只一個活躍的數(shù)據(jù)分發(fā). 如果執(zhí)行路徑很長, 數(shù)據(jù)需要多次分發(fā), 兩組 PX 進程會變換生產(chǎn)者消角色, 相互協(xié)作,

29、 完成所有并行操作. 每次數(shù)據(jù)分發(fā), 對應的 table queue 的編號不同. 一個活躍的數(shù)據(jù)分發(fā)過程, 需要兩組 PX 進程都參與, 一組為生產(chǎn)者發(fā)送數(shù)據(jù), 一組為消接收數(shù)據(jù). 因為一個DFO 里最多只有兩組 PX 進程, 意味著, PX 進程之間, 同一時間只能有一個活躍的數(shù)據(jù)分發(fā). 如果 PX 進程在執(zhí)行計劃中需要多次分發(fā)數(shù)據(jù), 可能需要在執(zhí)行計劃一些阻塞點, 比如 BUFFER SORT 和 HASH JOIN BUFFERED 這兩個操作, 保證上一次的數(shù)據(jù)分發(fā)完成之后, 才開始下一次分發(fā). 在后面的章節(jié), 我將會說明這些阻塞點帶來什么影響. 這個例子中, table queue

30、 0 和 1 可以同時工作是因為: table queue 0 是兩組 PX 進程之間的鏈接, table queue 1 為 PX 進程和 QC 之間的連接, table queue 0 與 table queue 1 是相互的, 因此可以同時進行.· PX 進程之間或者與 QC 的連接至少一個(單節(jié)點下至多三個, RAC 環(huán)境下至多四個)消息緩沖區(qū)用于進程間數(shù)據(jù)交互, 該消息緩沖區(qū)默認在 Large pool 中分配(如果沒有配置Large pool 則在 Shared pool 中分配). 多個緩沖區(qū)是為了實現(xiàn)異步通信, 提高性能.· 每個消息緩沖區(qū)的大小由參數(shù) pa

31、rallel_execution_message_size , 默認為 16k。· 當兩個進程都在同一個節(jié)點的時候, 通過在 Large pool(如果沒有配置 Large pool 則Shared pool)中傳遞和接收消息緩沖進行數(shù)據(jù)交互. 當兩個進程位于不同節(jié)點時. 通過RAC 心跳網(wǎng)絡(luò)進行數(shù)據(jù)交互, 其中一方接收的數(shù)據(jù)需要緩本地 Large pool(如果沒有配置 Large pool 則 Shared pool)里面.小結(jié)為了說明并行執(zhí)行的生產(chǎn)者-­消模型是如何工作的, 我使用了 broadcast 分發(fā), QC 分配兩組 PX 進程, 一組為生產(chǎn)者, 一組為消.

32、 QC 和 PX 進程之間, 兩組 PX 進程之間通過 table queue 進行 數(shù)據(jù)分發(fā), 協(xié)同完成整個并行執(zhí)行計劃. 視圖 V$PQ_TQSTAT 了并行執(zhí)行過程中, 數(shù)據(jù)是如何分發(fā)的. 通過對 DFO, table queue 的描述, 我闡述生產(chǎn)者-­消模型的工作原理和通信過程, 或許有些描述對你來說過于突然, 不用擔心, 后面的章節(jié)我會通過的例子來輔助理解.如何閱讀并行執(zhí)行計劃Table queue 的編號代表了并行執(zhí)行計劃中, 數(shù)據(jù)分發(fā)的順序. 理解執(zhí)行計劃中的并行操作是如何被執(zhí)行的, 原則很簡單: 跟隨 Table queue 的順序.通過 sql monitor

33、報告 sql 的序, 需要結(jié)合 name 列的 table queue 名字比如:TQ10000(代表 DFO=1, table queue 0),:TQ10001(代表 DFO=1, table queue 1), 還有 PX 進程的顏色,進行確定.下面的例子為 dbms_xplan.display_cursor 的輸出. 對于并行執(zhí)行計劃, 會多出來三列:1. TQ 列: 為 Q1:00 或者 Q1:01, 其中 Q1 代表第一個 DFO, 00 或者 01 代表 table queue 的編號.a. ID 79 的操作的 TQ 列為 Q1,00, 該組 PX 進程, 作為生產(chǎn)者首先執(zhí)行,

34、 然后通過broadcast 的分發(fā)方式, 把數(shù)據(jù)發(fā)給消.b. ID 1011, 36 的操作的 TQ 列為 Q1,01, 該組 PX 進程作為消接受 customer 的數(shù)據(jù)之后, 掃描 lineorder, hash join, 聚合之后, 又作為生產(chǎn)者通過 table queue 2 把數(shù)據(jù)發(fā)給 QC.2. In-­out 列: 表明數(shù)據(jù)的和分發(fā).· PCWC: parallel combine with child.· PCWP: parallel combine with parent.· P-­>P: parallel to

35、parallel.· P-­>S: parallel to Serial.3. PQ Distribute 列: 數(shù)據(jù)的分發(fā)方式. 此執(zhí)行計劃中, 我們使用了 broadcast 的方式, 下面的章節(jié)我會講述其他的分發(fā)方式.HASH 分發(fā)方式, 兩次數(shù)據(jù)分發(fā)除了 broadcast 分發(fā)方式, 另一種常見的并行分發(fā) hash. 為了觀察使用 hash 分發(fā)時 sql 的執(zhí)行情況, 我對 sql 使用 pq_distribute hint.select /*+ monitor parallel(4) leading(customer lineorder) use_has

36、h(lineorder) pq_distribute(lineorder hash hash) */sum(lo_revenue)fromlineorder, customer wherelo_custkey = c_custkey;使用 hash 分發(fā), sql 的執(zhí)行時間為 29s, db time 為 2.6m. 相對于 broadcast 方式, sql 的執(zhí)行時間和 db time 都增加了大約 40%.執(zhí)行計劃如下, 執(zhí)行計劃為 14 行, 增加了對 lineorder 的 hash 分發(fā), 第 11 行的PX SEND HASH對 3 億行數(shù)據(jù)通過 hash 函數(shù)分發(fā), 第 10

37、 行的PX RECEIVE 通過 table queue 1 接收 3 億行數(shù)據(jù), 這兩個操作消耗了 38%的 db cpu. 這就是為什么 SQL 執(zhí)行時間和 db time 變長的. 此時, SQL 的執(zhí)行順序為:1. 紅色的 PX 進程作為生產(chǎn)者, 并行掃描 customer(第 89 行), 對于連接鍵 c_custkey 運用 hash 函數(shù), 根據(jù)每行的 hash 值, 通過 table queue 0, 發(fā)給 4 個藍色消的其中一個(第 7 行). Hash 分發(fā)方式并數(shù)據(jù), sql monitor 報告的第 69 行, actual rows 列都為 1.5m.2. 紅色的 P

38、X 進程作為生產(chǎn)者, 并行掃描 lineorder(第 1213 行), 對于連接鍵 lo_custkey 運用同樣的 hash 函數(shù), 通過 table queue 1, 發(fā)給 4 個藍色消的其中一個(第 11 行). 同樣的 hash 函數(shù)保證了 customer 和 lineorder 相同的連接鍵會發(fā)給同一個消, 保證 hash join 結(jié)果的正確. 因為 3 億行數(shù)據(jù)都需要經(jīng)過 hash 函數(shù)計算, 然后分發(fā)(這是進程間的通信, 或者需要通過 RAC 心跳網(wǎng)絡(luò)通信), 這些巨大的額外開銷, 就是增加 38% cpu 的.3. 4 個藍色的 PX 進程作為消接收了 customer

39、的 1.5M 行 (第 6 行), 和 lineorder 的 3 億行 (第 10 行), 進行 hash join(第 5 行), 預聚合(第 4 行).4.4 個藍色的 PX 進程反過來作為生產(chǎn)者,通過 table queue 2, 把聚合的數(shù)據(jù)發(fā)給消QC(第 3行和第 2 行). 由 QC 對接收到 4 行做最后的聚合, 然后返回給用戶(第 1 和 0 行).觀察 sql monitor 報告中 Parallel 下的 , 紅色的 px 進程為實例 1、2 上的 p002/p003 進程, 藍色的 PX 進程為 p000/p001 進程. 作為生產(chǎn)者的紅色 PX 進程負責掃描事實表 l

40、ineorder, 對 3 億行數(shù)據(jù)進行 hash 分發(fā), 占了超過 1/3 的 db time.因為涉及 3 億行數(shù)據(jù)的分發(fā)和接收, 作為生產(chǎn)者的紅色 PX 進程和作為消的藍色 PX 進程需要同時活躍, SQL monitor 報告中的 activity 顯示大部分時間, AAS 超過并行度 4, 意味這兩組 PX 進程同時工作. 不像 replicate 或者 broadcast 分發(fā)時, AAS 為 4, 只有一組 PX 進程保持活躍.并行之后, 通過視圖 V$PQ_TQSTAT, 進一步驗證以上描述的執(zhí)行過程. 并行執(zhí)行過程涉及 3個 table queue 0/1/2, V$PQ_T

41、QSTAT 包含 21 行.1. 實例 1、2 上的 p002/p003 進程作為生產(chǎn)者, 平均掃描 customer 的 1/4 , 然后通過 table queue 0(TQ_ID=0), 發(fā)給作為消的 p000/p001 進程. 發(fā)送和接收的 customer 之和都為1.5m.· 發(fā)送的 數(shù): 1500000 = 365658 + 364899 + 375679 + 393764· 接收的 數(shù): 1500000 = 374690 + 374924 + 375709 + 3746772. 實例 1、2 上的 p002/p0003 進程作為生產(chǎn)者, 平均掃描 lineo

42、rder 的 1/4 , 通過 table queue 1(TQ_ID=1), 發(fā)給作為消的 p000/p001 進程. 發(fā)送和接收的 lineorder 之和都為300005811.· 發(fā)送的 數(shù): 300005811 = 74987629 + 75053393 + 74979748 + 74985041· 接收的 數(shù): 300005811 = 74873553 + 74968719 + 75102151 + 750613883. 實例 1、2 上的 p000/p0001 進程作為生產(chǎn)者, 通過 table queue 2(TQ_ID=2), 把聚合的一條結(jié)果 發(fā)給作為消

43、 的 QC. QC 作為消, 接收了 4 行.SELECTdfo_number, tq_id, server_type, instance, process, num_rowsFROMV$PQ_TQSTAT ORDER BYdfo_number DESC, tq_id, server_type desc, instance, process;DFO_NUMBERTQ_ID SERVER_TYPEINSTANCE PROCESSNUM_ROWS10 Producer1 P00236565810Producer1P00336489910Producer2P00237567910Producer2P

44、00339376410Consumer1P00037469010Consumer1P00137492410Consumer2P00037570910Consumer2P00137467711Producer1P0027498762911Producer1P0037505339311Producer2P0027497974811Producer2P0037498504111Consumer1P00074873553Consumer1P00Consumer2P00Consumer2P00Producer1P00Producer1P001112Producer2P000112Producer2P00

45、1112Consumer1QC421 rows selected.小結(jié)我們觀察 hash 分發(fā)時 sql 的并行執(zhí)行過程. Hash 分發(fā)與 broadcast 最大的區(qū)分在于對 hash join 的兩 進行分發(fā). 這個例子中, 對 lineorder 的 hash 分發(fā)會增加明顯的 db cpu. , 我將使用另一個例子, 說明 hash 分發(fā)適用的場景.Replicate, Broadcast 和 Hash 的選擇我們已經(jīng)測試過 replicate, broadcast, 和 hash 這三種分發(fā)方式.1. Replicate: 每個 PX 進程重復掃描 hash join 的左邊, b

46、uffer cache 被用來緩存 hash join 左邊的 , 減少重復掃描所需的物理讀. 相對于 broadcast 分發(fā), replicate 方式只需一組 PX 進程. 但是 replicate 不能替換 broadcast 分發(fā). 因為 replicate 僅限于 hash join 左邊是表的情況, 如果hash join 的左邊的結(jié)果集來自其他操作, 比如 join 或者視圖, 那么此時無法使用 replicate.2. Broadcast 分發(fā): 作為生產(chǎn)者的 PX 進程通過廣播的方式, 把 hash join 左邊的結(jié)果集分發(fā)給每個作為消 的 PX 進程. 適用于 hash

47、 join 左邊結(jié)果集比右邊小得多的場景, 比如星型模型.3. Hash 分發(fā)的本質(zhì): 把 hash join 的左邊和右邊(兩個數(shù)據(jù)源), 通過同樣 hash 函數(shù)重新分發(fā), 切分為 N 個工作單元(假設(shè) DoP=N), 再進行 join, 目的是減少 PX 進程進行 join 操作時, 需要連接的數(shù)據(jù)量. Hash 分發(fā)的代價需要對 hash join 的兩進行分發(fā). 對于 customer 連接lineorder 的例子, 因為維度表 customer 的數(shù)據(jù)量比事實表 lineorder 小得多, 對 customer 進行 replicate 或者 broadcast 分發(fā)顯然是更選

48、擇, 因為這兩種方式不用對 lineorder 進行重新分發(fā). 如果是兩個大表 join 的話, join 操作會是整個執(zhí)行計劃的瓶頸所在, hash 分發(fā)是唯一合適的方式. 為了減低 join 的代價, 對 hash join 左邊和右進行 hash 分發(fā)的代價是可以接受的.Hash 分發(fā), 有時是唯一合理的選擇我們使用 lineorder 上的自連接來演示, 為什么有時 hash 分發(fā)是唯一合理的選擇. 測試的 SQL 如下:select /*+ monitor parallel(4)*/ sum(lo1.lo_revenue)fromlineorder lo1, lineorder lo

49、2 wherelo1.lo_orderkey = lo2.lo_orderkey;SQL 執(zhí)行時間為 2.4 分鐘, db time 為 10.5 分鐘.優(yōu)化器默認選擇 hash 分發(fā)方式, 執(zhí)行計劃為 14 行, 結(jié)構(gòu)與之前的 Hash 分發(fā)的例子是一致的. 不同的是, 第 5 行的 hash join 消耗了 73%的 db time, 使用了 9GB 的臨時表空間, 表空間的 IO 占 12% 的 db time. 大約 15%的 db time 用于 Lineorder 的兩次 hash 分發(fā)和接收, 相對上一個例子的占38% 比例, 這兩次 HASH 分發(fā)的整體影響降低了一倍多.紅色

50、的 PX 進程為實例 1、2 上的 p002/p003 進程, 藍色的 PX 進程為 p000/p001 進程.作為生產(chǎn)者的紅色 PX 進程占總 db time 的 15%左右.SQL 執(zhí)行開始, 對 lineorder 兩次 hash 分發(fā)時, AAS 大于 4, 分發(fā)完成之后, 只有藍色的 PX 進程進行hash join 操作, AAS=4.從 V$PQ_TQSTAT 視圖可以確認, 對于 lineorder 的兩次分發(fā), 通過 table queue 0 和 1, 作為消 的 4 個 PX 進程接收到的兩次數(shù)據(jù)是一樣的, 保證重新分發(fā)影響 join 結(jié)果的正確性. 每個藍色 PX 進程

51、需要 hash join 的左邊和右邊均為 3 億行數(shù)據(jù)的 1/4, 通過 hash 分發(fā), 3 億行連接3 億行的工作平均的分配四個 PX 進程各自處理, 每個 PX 進程處理 75M 行連接75M 行.SELECTdfo_number, tq_id, server_type, instance, process, num_rowsFROMV$PQ_TQSTAT ORDER BYdfo_number DESC, tq_id, server_type desc, instance, process;DFO_NUMBERTQ_IDSERVER_TYPEINSTANCEPROCESSNUM_ROW

52、S10Producer1P0027505572510Producer1P0037497745910Producer2P0027499527610Producer2P0037497735110 Consumer1P0074998419Consumer1P00Consumer2P0074976974Consumer2P00Producer1P007498679811Producer1P0037498526811Producer2P0027498488311Producer2P0037504886211 Consumer1P007499841911 Consumer1P00Consumer2P007

53、4976974Consumer2P00Producer1P00112Producer1P001112Producer2P000112Producer2P001112Consumer1QC421 rows selected.使用 broadcast 分發(fā), 糟糕的性能對于 lineorder, lineorder 的自連接, 如果我們使用 broadcast 分發(fā), 會出現(xiàn)什么情況呢? 我們測試一下:select /*+ monitor parallel(4) leading(lo1 lo2) use_hash(lo2)pq_distribute(lo2 broadcast none) */su

54、m(lo1.lo_revenue) fromlineorder lo1, lineorder lo2 wherelo1.lo_orderkey = lo2.lo_orderkey;使用 broadcase 分發(fā), SQL 的執(zhí)行時間為 5.9 分鐘, db time 為 23.8 分鐘. 相比 hash 分發(fā), 執(zhí)行時間和 db time 都增加了接近 1.5 倍.紅色的 PX 進程作為生產(chǎn)者, 對 lineorder 進行并行掃描之后, 3 億行通過 table queue 0 廣播給4 個作為消的藍色 PX 進程(第 69 行), 相當于了 4 份, 每個藍色的 PX 進程都接收了 3 億行 . 這次 broadcast 分發(fā)消耗了 11%的 db time, 因為需要每行傳輸給每個藍色 PX 進程, 消耗的 db cpu 比使用 hash 分發(fā)時兩次 hash 分發(fā)所消耗的還多.每個藍色 PX 進程進行 hash join 的數(shù)據(jù)變大了, hash join 的左邊為 3 億行數(shù)據(jù), has