ORACLE SQL調優之'PLAN_TABLE' is old version

  在為國投做SQL調優時，他們開發說不要動現在的SQL，調整一下執行計劃即可，即查詢某個表時執行特定的執行計劃。乍一聽，我是嚇了一跳！
 由於他們開發不讓動SQL結構（該SQL經過PLSQL最佳化後有500多行，其是2層巢狀遞迴查詢，外邊一個SQL如圖1-2，外層SQL的每一個列是一個子查詢如下圖1-1，遞迴子查詢有32個），所以只能從SQL涉及的表、索引下手，查詢問題的具體原因及解決辦法。我的做法是，先檢視了SQL涉及的表的統計資訊，問題SQL涉及了8張表（最大的表有300M左右，小表只有幾M大小），表的統計資訊距離現在有3個月。透過與他們維護人員溝通，對問題SQL涉及的8張表進行了統計資訊更新；然後在他們的測試環境中測試問題SQL，發現SQL執行的速度比之前的20多分鐘縮減到15分鐘，速度有提升但是不明顯。然後，在不改變SQL結構的前提下，我做了如下調整（由於SQL涉及薪水查詢，比較敏感，這裡只能大概給出處理涉及的部分）：
 
  圖 1-1 遞迴查詢部分中新增的hint
 
 圖 1-2 外層查詢中新增的hint
 我的處理依據是：執行該sql生成的執行計劃中有比較多的全表掃描（全表掃描的表是同一個表，大小有12M，全表掃的次數有18次之多），根據提示的全表掃描，又查詢了謂詞涉及的列中是否有索引，查詢到謂詞涉及的列中有索引但是執行計劃沒有走而是全表掃描。先不計較新增hint是否有利於執行速度的提高，我就試了一下。新增hint後，SQL的執行速度果然有提高，在他們的測試庫中執行時間從15分鐘減少到3分多鐘，有明顯的提高了。但是，在他們的生產庫中進行最佳化實施後，發現SQL的執行速度不但沒有改善反而嚴重下降了，從原來的20多分鐘到3個多小時，簡直讓人發瘋。
 此時，我意識到，他們執行SQL是透過ORACLE DISCOVER執行的，執行後生成報表，就是薪資表，但是他們開發堅持該SQL執行客戶端沒問題。我只能在他們的生產庫中生成執行計劃查詢原因，經過與測試環境中調整後的SQL執行計劃對比發現個很嚴重的問題，如下圖所示：

 執行計劃提示：當前SQL執行使用的執行計劃是老版本，也就是說我們做的統計資訊更新及hint的設定根本沒任何作用，這就是導致SQL問題慢的具體原因了（也是他們開發說的讓調整SQL執行計劃，但是又不能改變SQL的結構）。
 接下來，我做了如下步驟的操作：
1、業務使用者登入資料庫伺服器
2、SQL>set autot on
   SQL>set timing on
3、執行速度慢的SQL
4、觀察執行計劃尾部是否有如下提示：
   Note -- plan table is old version
5、在4有提示的情況下（建議在伺服器端sqlplus中執行）
  SQL>drop table plan_table;  --業務使用者執行
  SQL>@?/rdbms/admin/utlxplan; --具有DBA許可權的使用者（SYS）執行
6、再次重複執行速度慢的SQL3-5次，觀察SQL速度是否有所改善
 經過調整後，再次執行SQL，發現新增hint的SQL執行時間是3:15s，不新增hint的SQL執行時間是6::35s；終於，生產庫的執行時間也能從20多分鐘減少到3分多鐘，達到了他們開發的基本要求了。總結：問題SQL執行慢的原因有3個，一是表的統計資訊沒有及時更新，二是表的索引沒有被用上，三是plan_table快取了執行計劃。