接獲同事報案,某段 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,導致可怕的龜速… (我的老天鵝)

Comments

Be the first to post a comment

Post a comment