應用程序執行周期性時快時慢問題解決一例

       做Oracle DBA，經常會遇到一些性能問題，有些性能問題是一開始就很慢的，有些性能問題是逐漸變慢的，有些性能問題是突然變慢的，而有性能問題時快時慢的，不知道其他同行覺得哪種性能問題比較好處理，今天我在這里分享一個性能問題周期性問題時快時慢的案例，用于總結反思，如有錯誤請不吝指正。

       最近一位應用運維同事發郵件請求協助，反映有一套應用系統跑批期間周期性時快時慢，而且很規律。周一到周五晚上批量的計算耗時很長，要6-8小時；而周六和周日晚上批量計算耗時很快，只需要15分鐘左右。看到郵件中描述的這一現象，有經驗的老司機可能一下子就想到了問題可能出現在哪里。但對于經驗不足我來說，解決這種問題還是比較吃力的。

        于是拉了一個微信群，在群里詳細詢問了，跑批快和慢的具體時間。這里多提一句，在我接手這個case之前有一個同事也查了這個問題，他根據awr從dbtime到redo log生成量再到事務都做了一些分析，還做成了折線圖，在群里發了一堆，但就是沒有做一些詳細的詢問和分析。最終也沒有一個結論。

        言規正傳，從開發和應用運維那里得到一些詳細信息之后，首先從awr下手。分別取到快的時間和慢的時間區間的awr報告。以我的能力，從各項指標沒有看出數據庫整體負載有什么問題。但在top SQL那部分發現了兩個時間段的TOP SQL確實是不同的。在慢的時間段有一條update語句73320次，耗時9869.7s，而在快的時間段的TOP SQL中卻沒有顯示這條update語句。難道這就是問題所在嗎？

        于是詢問開發同事，update語句是否為批量期間執行的語句。得到肯定的答復后，開始懷疑是不是工作日和周末跑批的邏輯不同的，但awr中信息否定了我的猜測，原來這條update語句也在周末也跑了，只不是跑的很快，跑了75331次，耗時24.07s。從目前 的線索來看，之條update語句執行效率無疑是導致程序批量時快時慢的原因。但是為什么會出現這種現象現在還不得而知。

        問題sql定位了，剩下的問題就簡單了，把sql優化掉，每次批量都在24s完成，那問題自然就解決了。現在開始尋找sql執行時快時慢的原因。這里介紹一個很強大的工具awrsqrpt，這個工具可以獲取awr中記錄的sql語句的歷史執行計劃。使用awrsqrpt取到兩個時間段sql的執行計劃，分別如下兩個圖所示：

        從執行計劃中可以看出，都走了表的主鍵，選擇了不同的方式，其中INDEX RANGE SCAN平均單次執行時間為384.3ms，而INDEX SKIP SCAN的平均單次時間是0.3ms，差了3000多倍，難怪批量執行時間差異如此之大。看到這個，腦子中反映出來的第一個解決方法是固定執行計劃，強制走INDEX SKIP SCAN。但這種方法治標不治本。只能用在實在找不到問題原因的情況下，或緊急的情況下。

        繼續與開發溝通，得知，update語句中的表，每天批量后都會被truncate掉，這是一個很重要的信息。難道就是由于這一個truncate操作導致sql語句執行效率差異如此之大嗎？我們繼續往下分析。

        我們都知道，Oracle的執行計劃都是通過CBO，根據表上的統計信息，而估算出來Oracle認為最做優的執行計劃。也就是不論INDEX RANGE SCAN 還是SKIP SCAN，Oracle都認為是最優的，難道是Oracle優化器出現了問題了嗎？應該不是的，如果優化器這么容易出問題，那Oracle在商用數據庫也不會稱霸這么久了。于是想到了表上的統計信息，通過查詢視圖sys.wri$_optstat_tab_history得到表上的歷史統計信息如下圖：

        從上面的圖上可以看到，表上的統計信息時而為0，時而為很大，看來就是統計信息導致CBO在選擇執行計劃時，沒有選擇它應該選的最優執行計劃。

        從上面的分析來看，應該是找到了問題的根本原因，批量表每次批量完成后都會做truncate操作，數據庫默認每天都會自動收集表的統計信息，周一到周五22：00開始收集，周末6：00開始收集。從而導致數據庫在不同時間點收集到表的統計信息是不同的，進而導致了優化器基于統計信息來選擇了慢的執行計劃。

        原因找到了，就開始討論解決方法，這里列出來幾種方法應該都可以解決這個問題：

        1、在批量導入數據后，對批量表做一次統計信息收集

        2、鎖定批量表在有數據時的統計信息

        3、truncate操作改為批前執行(開發提出)

        4、固定問題sql的執行計劃(可能解決不了問題)

        反思：

        1、我們在解決問題時，不能只從數據庫整體層面來分析，有時可能是一條本身性能不是很差的sql導致出的性能問題

        2、多溝通、細溝通，把盡可能掌握多的信息點，有助于問題的解決

        3、從性能慢的sql中應該也可以猜想到問題原因，Oracle評估的cost 為0

        以上為整個case的一個解決過程，歡迎指正。