Categories
程式開發

由SELECT *引發的多個生產故障,問題藏太深了吧……


本文由dbaplus 社群授權轉載。

在眾多的SQL審核產品中,幾乎都會提到一個審核規則,即select *,規則描述幾乎一致:禁止使用select *,必须明确选择所需的列。而這個規則其實有著很多真實的生產故障案例,下面介紹幾個比較常見的案​​例:

案例 1

用戶反饋生產環境有兩條SQL語句,可以確認區別只有表名的不同(實際參數相同),但性能上卻有10倍以上的差距。

由SELECT *引發的多個生產故障,問題藏太深了吧…… 1

通過生成的監視報告可發現SQL1執行時間8s,IO 403MB,如下圖:

由SELECT *引發的多個生產故障,問題藏太深了吧…… 2

SQL2 執行時間2.1m,IO則有15GB,如下圖:

由SELECT *引發的多個生產故障,問題藏太深了吧…… 3

根據業務反饋,SQL1中表是影子表,數據量,表結構跟對應表幾乎相同,那麼為什麼執行時間差距這麼大呢?

DBA在此之前已經在準生產環境多次通過DBMS_SHARED_POOL.PURGE刪除對應的執行計劃,換參數多次重複解析,均沒獲得正確的執行計劃。

通過分析對比監視報告發現,SQL1中with語句正常物化,執行計劃中存在臨時表轉化操作,即TEMP TABLE TRANSFORMATION,而SQL2中由於沒做臨時表轉化操作,IM_HISMESSAGE表被訪問多次,效率低下。

由SELECT *引發的多個生產故障,問題藏太深了吧…… 4

通過再次觀察整個SQL運行期間的等待事件,我們可以快速發現,其實SQL慢了10倍的原因並非是執行計劃引起的(主要等待事件為直接路徑讀,讀寫臨時表比例很低,而且並沒有出現經典的大表作為NL被驅動表的情況),對比運行數據,可以發現IO的增量主要來源於對IM_HISMESSAGE表的掃描。

影子表的差異

通過逐行對比號返回列的詳細信息,我們終於發現了謎底:

原始表是有LOB字段的,影子表沒有LOB字段,IO量小了很多。同時由於存在LOB字段,with語句無法進行臨時表轉化。

而SQL文本中經典的select 則完美的掩蓋了這一差異,開發人員圖方便寫出來的 select * 查詢了根本不需要的LOB字段,導致了性能的急劇下降。

案例 2

客戶生產環境的AWR報告上有一條誇張的TOPSQL,佔全天DBTIME的84%

由SELECT *引發的多個生產故障,問題藏太深了吧…… 5

原始SQL不展示了,SQL本身其實比較簡單,模擬下來如下:

select * from test_a where object_id =11;

執行計劃也很簡單,所以很快也能發現問題,TABLE ACCESS BY INDEX ROWID的COST相對異常的高,排查下表的統計信息時,驚奇的發現,這是張寬表,有400+列,當寬表遇上select *時,性能就急劇下降了。

由SELECT *引發的多個生產故障,問題藏太深了吧…… 6

由SELECT *引發的多個生產故障,問題藏太深了吧…… 7

問題定位雖然很快,但處理起來卻並不方便,畢竟需要找到開發改SQL,這快不了。當然沒什麼疑問的是,系統的性能問題出在SQL代碼的質量。

案例 3

準備環境如下:從dba_objects中復制兩張表t1,t2作為測試環境表。

由SELECT *引發的多個生產故障,問題藏太深了吧…… 8

準備了兩個查詢,相同的條件,區別主要在於一個只查單列,另外一個查詢全列。

通過模擬,可以發現,use_merge這種表連接方式情況下,排序操作的內存消耗有較大的差距,這種差距會在有索引情況下,且指定查詢列也能命中索引走索引快速全掃描時被大幅放大。

查詢全列,SORT JOIN內存消耗1810K:

由SELECT *引發的多個生產故障,問題藏太深了吧…… 9

由SELECT *引發的多個生產故障,問題藏太深了吧…… 10

查詢id,SORT JOIN內存消耗424K:

由SELECT *引發的多個生產故障,問題藏太深了吧…… 11

由SELECT *引發的多個生產故障,問題藏太深了吧…… 12

如果是HASH JOIN的話,join操作影響相對較小,可以換hint再測試看看。

案例 4

有些場景,SQL查詢的表數據量較大,查詢字段也較多(無法全部走索引)的時候,這裡暫時不考慮*增加的不需要使用的列在數據庫返回數據到應用時網絡層的消耗。

如果你的機器剛好是EXADATA,那麼smart scan也會讓select *與指定列的查詢有明顯的性能差異。

這個限於篇幅推薦直接參考Oracle官方技術博客:

https://blogs.oracle.com/exadatacn/exadata-v5

總結

通過這些案例,select *這個規則,變得立體了許多。

select *寫法方便快捷,但帶來的問題卻藏得很深,這種問題在上線後,隨著系統的維護,都將變成修復成本極高的隱患。

作者介紹

蔣健,薄冰科技創始人,Oracle ACE,11g OCM,多年Oracle設計、管理及實施經驗,精通數據庫優化。

原文鏈接

SELECT *引發的多個生產故障,問題藏太深了吧……