有客戶遇到SQL性能不穩定突然變差導致系統性能出現嚴重問題的情況對於大型的系統來說SQL性能不穩定有時突然變差這是常常遇到的問題這也是一些DBA的挑戰
對於使用Oracle數據庫的應用系統有時會出現運行得好好的SQL性能突然變差特別是對於OLTP類型系統執行頻繁的核心SQL如果出現性能問題通常會影響整個數據庫的性能進而影響整個系統的正常運行對於個別的SQL比如較少使用的查詢報表之類的SQL如果出現問題通常只影響少部分功能模塊而不會影響整個系統
那麼應該怎麼樣保持SQL性能的穩定性？
SQL的性能變差通常是在SQL語句重新進行了解析解析時使用了錯誤的執行計劃出現的下列情況是SQL會重新解析的原因
SQL語句沒有使用綁定變量這樣SQL每次執行都要解析
SQL長時間沒有執行被刷出SHARED POOL再次執行時需要重新解析
在SQL引用的對象（表視圖等）上執行了DDL操作甚至是結構發生了變化比如建了一個索引
對SQL引用的對象進行了權限更改
重新分析（收集統計信息）了SQL引用的表和索引或者表和索引統計信息被刪除
修改了與性能相關的部分參數
刷新了共享池
當然重啟數據庫也會使所有SQL全部重新解析
SQL重新解析後跟以前相比性能突然變差通常是下列原因
表和索引的優化統計信息被刪除或者重新收集後統計信息不准確重新收集統計信息通常是由於收集策略（方法）不正確引起比如對分區表使用analyze命令而不是用dbms_stats包收集統計信息時采樣比例過小等等Oracle優化器嚴重依賴於統計信息如果統計信息有問題則很容易導致SQL不能使用正確的執行計劃
SQL綁定變量窺探(bind peeking)同時綁定變量對應的列上有直方圖或者綁定變量的值變化范圍過大分區數據分布極不均勻
） 綁定變量的列上有直方圖
假如表orders存儲所有的訂單state列有種不同的值表示未處理表示處理成功完成表示處理失敗State列上有一個索引表中絕大部分數據的state列為和占少數有下面的SQL
select * from orders where state=:b
這裡:b是變量在大多數情況下這個值為則應該使用索引但是如果SQL被重新解析而第一次執行時應用傳給變量b值為則不會使用索引采用全表掃描的方式來訪問表對於綁定變量的SQL只在第一次執行時才會進行綁定變量窺探並以此確定執行計劃該SQL後續執行時全部按這個執行計劃這樣在後續執行時b變量傳入的值為的時候仍然是第一次執行時產生的執行計劃即使用的是全表掃描這樣會導致性能很差
） 綁定變量的值變化范圍過大
同樣假如orders表有一列created_date表示一筆訂單的下單時間orders表裡面存儲了最近年的數據有如下的SQL
Select * from orders where created_date >=:b;
假如大多數情況下應用傳入的b變量值為最近幾天內的日期值那麼SQL使用的是created_date列上的索引而如果b變量值為個月之前的一個值那麼就會使用全表掃描與上面描述的直方圖引起的問題一樣如果SQL第次執行時傳入的變量值引起的是全表掃描那麼將該SQL後續執行時都使用了全表掃描從而影響了性能
） 分區數據量不均勻
對於范圍和列表分區可能存在各個分區之間數據量極不均勻的情況下比如分區表orders按地區area進行了分區P分區只有幾千行而P分區有萬行數據同時假如有一列product_id其上有一個本地分區索引有如下的SQL
select * from orders where area=:b and product_id =:b
這條SQL由於有area條件因此會使用分區排除如果第 次執行時應用傳給b變量的值正好落在P分區上很可能導致SQL采用全表掃描訪問如前面所描述的導致SQL後續執行時全部使用了全表掃描
其他原因比如表做了類似於MOVE操作之後索引不可用對索引進行了更改當然這種情況是屬於維護不當引起的問題不在本文討論的范圍
綜上所述SQL語句性能突然變差主要是因為綁定變量和統計信息的原因注意這裡只討論了突然變差的情況而對於由於數據量和業務量的增加性能逐步變差的情況不討論
為保持SQL性能或者說是執行計劃的穩定性需要從以下幾個方面著手
規劃好優化統計信息的收集策略對於Oracle g來說默認的策略能夠滿足大部分需求但是默認的收集策略會過多地收集列上的直方圖由於綁定變量與直方圖固有的矛盾為保持性能穩定對使用綁定變量的列不收集列上的直方圖對的確需要收集直方圖的列在SQL中該列上的條件就不要用綁定變量統計信息收集策略可以考慮對大部分表使用系統默認的收集策略而對於有問題的可以用DBMS_STATSLOCK_STATS鎖定表的統計信息避免系統自動收集該表的統計信息然後編寫腳本來定制地收集表的統計信息腳本中類似如下
exec dbms_statsunlock_table_stats…
exec dbms_statsgather_table_stats…
exec dbms_statslock_table_stats…
修改SQL語句使用HINT使SQL語句按HINT指定的執行計劃進行執行這需要修改應用同時需要逐條SQL語句進行加上測試和發布時間較長成本較高風險也較大
修改隱含參數 _optim_peek_user_binds為FALSE修改這個參數可能會引起性能問題（這裡討論的是穩定性問題）
使用OUTLINE對於曾經出現過執行計劃突然變差的SQL語句可以使用OUTLINE來加固其執行計劃在g中DBMS_OUTLNCREATE_OUTLINE可以根據已有的執行正常的SQL游標來創建OUTLINE如果事先對所有頻繁執行的核心SQL使用OUTLINE加固執行計劃將最大可能地避免SQL語句性能突然變差
注DBMS_OUTLN可以通過$ORACLE_HOME/rdbms/admin/dbmsolsql腳本來安裝
使用SQL ProfileSQL Profile是Oracle g之後的新功能此處不再介紹請參考相應的文檔
除此之外可以調整一些參數避免潛在的問題比如將_btree_bitmap_plans參數設置為FALSE（這個參數請參考互聯網上的文章或Oracle文檔）
而在實際工作中通過使用定制的統計信息收集策略以及在部分系統上使用OUTLINE系統基本上不會出現已有的SQL性能突然變差的情況當然也有維護人員操作不當引起的SQL性能突然變差比如建了某個索引而沒有收集統計信息導致SQL使用了新建的索引而該索引並不適合於那條SQL維護人員意外刪除了表個索引的統計信息
From:http://tw.wingwit.com/Article/program/Oracle/201311/18054.html
