【茶包射手日記】ORACLE JOIN GROUP BY 子查詢爆慢疑案

接獲同事報案,某段 Oracle 查詢偶發嚴重效能問題。查詢時資料表經由 JOIN 自身的 GROUP BY 子查詢挑出某日期前客戶最新的一筆資料:

SELECT 
    ccb.Key1,
    ccb.Key2,
    ccb.Key3,
    ccb.Key4,
    ccb.Key5,
    ccb.Col1,
    ccb.Col2,
    ccb.Col3,
    ccb.Col4,
    --...略...
    ccb.Col20
FROM MyTable ccb
INNER JOIN ( 
    SELECT Key0,Key1,Key2, Key3, MAX(Key4) as Key4,Key5
    FROM MyTable
    WHERE Key4 <= :TDay
    GROUP BY Key0,Key2,Key1, Key3, Key5
)  maxb 
ON   ccb.Key0 = maxb.Key0
AND  ccb.Key1 = maxb.Key1
AND  ccb.Key2 = maxb.Key2
AND  ccb.Key3 = maxb.Key3
AND  ccb.Key4 = maxb.Key4
AND  ccb.Key5 = maxb.Key5

MyTable 約 90 萬筆,GROUP BY 子查詢結果約 8000 筆,JOIN 後筆數與子查詢筆數相同。

Key0 到 Key5 有設 Index,先查出 8000 筆,再透過 Index 從 90 萬筆中找出 8000 筆感覺效能不致於離譜,測試正常的執行時間不超過 12 秒,但卻不時會發生數分鐘跑不完導致程式逾時的狀況。

一早接獲報案,優先想到的偵辦方向是檢視執行計劃試著找出瓶頸,立即實測,此時執行時間約十來秒正常,而執行計劃如下:

我對 Oracle 執行計劃沒啥研究,但先 GROUP BY 再 JOIN 的順序很符合我對「JOIN 一個 GROUP BY 子查詢」的理解。

不到一多小時後再測一次,問題出現了!執行時間超過數分鐘,檢視執行計劃,Oracle 給了一個匪夷所思的結果:

先 JOIN 再 GROUP BY 再 GROUP BY 是什麼鬼啦?且執行成本爆增三倍,應是查詢爆慢的原因。

進一步調查,這一小時間資料有些變化,但筆數差異不大,應不致產生巨大差異。但同事提到一點,在兩次測試間曾執行過 Analyze Estimate Statistics 動作試著更新統計改善 Index 效能。在模擬環境測試,竟意外重現 Analyze 之後執行計劃崩壞查詢爆慢的現象,真是出乎意料的劇情發展!

官方文件則提到:Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. 意思是 Analyze 是舊指令,不宜再用,而且它所蒐集資料無助於 Cost-Based Optimizer (只對 VALIDATE or LIST CHAINED ROWS clauses 或 Freelist Blocks 資訊蒐集有效),要更新統計資料改善查詢效能,應一律改用 DBMS_STATS。不過,無效是一回事,我沒有找到任何執行 Analyze 會傷害效能的說法可以佐證,Oracle 為什麼會出現難以理解的執行計劃及執行速度,對我來說是謎。

無法解釋執行計劃崩壞的原因,但我的想法是只要阻止 Oracle 將 JOIN 拉進子查詢瞎攪和,我就不會踩到雷。用 Temp Table 是一招,而我懷念起 SQL 的 CTE (Common Table Expression),爬文後有意外發現,原來 Oracle  9i 起就已支援 WITH … AS 這種寫法(學名叫 Subquery Factoring),早期沒有遞迴功能,但到 11g 時已加上向 SQL 看齊了。

試著將查詢修改如下:

WITH maxb AS (
    SELECT Key0,Key1,Key2, Key3, MAX(Key4) as Key4,Key5
    FROM MyTable
    WHERE Key4 <= :TDay
    GROUP BY Key0,Key1,Key2,Key3,Key5
)
SELECT 
    ccb.Key1,
    ccb.Key2,
    ccb.Key3,
    ccb.Key4,
    ccb.Key5,
    ccb.Col1,
    ccb.Col2,
    ccb.Col3,
    ccb.Col4,
    --...略...
    ccb.Col20
FROM MyTable ccb INNER JOIN maxb 
ON   ccb.Key0 = maxb.Key0
AND  ccb.Key1 = maxb.Key1
AND  ccb.Key2 = maxb.Key2
AND  ccb.Key3 = maxb.Key3
AND  ccb.Key4 = maxb.Key4
AND  ccb.Key5 = maxb.Key5

經實測,改用這種 CTE 寫法,就能避免 Oracle 惡搞執行計劃~

本次辦案心得:

  • Oracle 從 9i 起就可以寫 CTE(11g 起支援遞迴)是一大發現,有不少複雜查詢可以因此簡化
  • Analyze 指令已是歷史,勿再使用 ,請改用 DBMS_STATS
  • 在某些情境下,Oracle 可能讓 JOIN ( GROUP BY 子查詢)先 JOIN 再 GROUP BY,導致可怕的龜速… (我的老天鵝)
歡迎推文分享:
Published 20 July 2017 09:49 PM 由 Jeffrey
Filed under: ,
Views: 3,104



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<July 2017>
SunMonTueWedThuFriSat
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication