【茶包射手日記】ORACLE JOIN GROUP BY 子查詢爆慢疑案
0 |
接獲同事報案,某段 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