怎樣保持Oracle資料庫SQL效能的穩定性

victorymoshui發表於2010-08-30
Oracle資料庫SQL
有客戶遇到SQL效能不穩定，突然變差導致系統效能出現嚴重問題的情況。對於大型的系統來說，SQL效能不穩定，有時突然變差，這是常常遇到的問題。這也是一些DBA的挑戰。

　　對於使用Oracle資料庫的應用系統，有時會出現執行得好好的SQL，效能突然變差。特別是對於OLTP型別系統執行頻繁的核心SQL，如果出現效能問題，通常會影響整個資料庫的效能，進而影響整個系統的正常執行。對於個別的SQL，比如較少使用的查詢報表之類的SQL，如果出現問題，通常隻影響少部分功能模組，而不會影響整個系統。

　　那麼應該怎麼樣保持SQL效能的穩定性？

　　SQL的效能變差，通常是在SQL語句重新進行了解析，解析時使用了錯誤的執行計劃出現的。下列情況是SQL會重新解析的原因：

　　1. SQL語句沒有使用繫結變數，這樣SQL每次執行都要解析。
        2. SQL長時間沒有執行，被刷出SHARED POOL，再次執行時需要重新解析。
        3. 在SQL引用的物件（表、檢視等）上執行了DDL操作，甚至是結構發生了變化，比如建了一個索引。
        4. 對SQL引用的物件進行了許可權更改。
        5. 重新分析（收集統計資訊）了SQL引用的表和索引，或者表和索引統計資訊被刪除。
        6. 修改了與效能相關的部分引數。
        7. 重新整理了共享池。
        8. 當然重啟資料庫也會使所有SQL全部重新解析。

　　SQL重新解析後，跟以前相比，效能突然變差，通常是下列原因：

　　1. 表和索引的優化統計資訊被刪除，或者重新收集後統計資訊不準確。重新收集統計資訊通常是由於收集策略（方法）不正確引起。比如對分割槽表使用analyze命令而不是用dbms_stats包、收集統計資訊時取樣比例過小等等。Oracle優化器嚴重依賴於統計資訊，如果統計資訊有問題，則很容易導致SQL不能使用正確的執行計劃。

　　2. SQL繫結變數窺探(bind peeking)，同時繫結變數對應的列上有直方圖；或者繫結變數的值變化範圍過大、分割槽資料分佈極不均勻：

　　1） 繫結變數的列上有直方圖：

　　假如表orders儲存所有的訂單，state列有3種不同的值：0表示未處理，1表示處理成功完成，2表示處理失敗。State列上有一個索引，表中絕大部分資料的state列為1，0和2佔少數。有下面的SQL： 

#div_code img{border:0px;}1 select * from orders where state=:b1

 

　　這裡:b1是變數，在大多數情況下這個值為0，則應該使用索引，但是如果SQL被重新解析，而第一次執行時應用傳給變數b1值為1，則不會使用索引，採用全表掃描的方式來訪問表。對於繫結變數的SQL，只在第一次執行時才會進行繫結變數窺探，並以此確定執行計劃，該SQL後續執行時全部按這個執行計劃。這樣在後續執行時，b1變數傳入的值為0的時候，仍然是第一次執行時產生的執行計劃，即使用的是全表掃描，這樣會導致效能很差。

　　2） 繫結變數的值變化範圍過大：

　　同樣假如orders表有一列created_date表示一筆訂單的下單時間，orders表裡面儲存了最近1年的資料，有如下的SQL：

#div_code img{border:0px;}1 Select * from orders where created_date >=:b1;

 

　　假如大多數情況下，應用傳入的b1變數值為最近幾天內的日期值，那麼SQL使用的是created_date列上的索引，而如果b1變數值為5個月之前的一個值，那麼就會使用全表掃描。與上面描述的直方圖引起的問題一樣，如果SQL第1次執行時傳入的變數值引起的是全表掃描，那麼將該SQL後續執行時都使用了全表掃描，從而影響了效能。

　　3） 分割槽資料量不均勻：

　　對於範圍和列表分割槽，可能存在各個分割槽之間資料量極不均勻的情況下。比如分割槽表orders按地區area進行了分割槽，P1分割槽只有幾千行，而P2分割槽有200萬行資料。同時假如有一列product_id，其上有一個本地分割槽索引，有如下的SQL：

#div_code img{border:0px;}1 select * from orders where area=:b1 and product_id =:b2  

 

　　這條SQL由於有area條件，因此會使用分割槽排除。如果第1 次執行時應用傳給b1變數的值正好落在P1分割槽上，很可能導致SQL採用全表掃描訪問，如前面所描述的，導致SQL後續執行時全部使用了全表掃描。

　　3. 其他原因，比如表做了類似於MOVE操作之後，索引不可用，對索引進行了更改。當然這種情況是屬於維護不當引起的問題，不在本文討論的範圍。

　　綜上所述，SQL語句效能突然變差，主要是因為繫結變數和統計資訊的原因。注意這裡只討論了突然變差的情況，而對於由於資料量和業務量的增加效能逐步變差的情況不討論。

　　為保持SQL效能或者說是執行計劃的穩定性，需要從以下幾個方面著手：

　　1. 規劃好優化統計資訊的收集策略。對於Oracle 10g來說，預設的策略能夠滿足大部分需求，但是預設的收集策略會過多地收集列上的直方圖。由於繫結變數與直方圖固有的矛盾，為保持效能穩定，對使用繫結變數的列，不收集列上的直方圖；對的確需要收集直方圖的列，在SQL中該列上的條件就不要用繫結變數。統計資訊收集策略，可以考慮對大部分表，使用系統預設的收集策略，而對於有問題的，可以用DBMS_STATS.LOCK_STATS鎖定表的統計資訊，避免系統自動收集該表的統計資訊，然後編寫指令碼來定製地收集表的統計資訊。指令碼中類似如下： 

#div_code img{border:0px;}1 exec dbms_stats.unlock_table_stats…
2 exec dbms_stats.gather_table_stats…
3 exec dbms_stats.lock_table_stats…

 

　　2. 修改SQL語句，使用HINT，使SQL語句按HINT指定的執行計劃進行執行。這需要修改應用，同時需要逐條SQL語句進行，加上測試和釋出，時間較長，成本較高，風險也較大。

　　3. 修改隱含引數” _optim_peek_user_binds”為FALSE，修改這個引數可能會引起效能問題（這裡討論的是穩定性問題）。

　　4. 使用OUTLINE。對於曾經出現過執行計劃突然變差的SQL語句，可以使用OUTLINE來加固其執行計劃。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根據已有的執行正常的SQL遊標來建立OUTLINE。如果事先對所有頻繁執行的核心SQL使用OUTLINE加固執行計劃，將最大可能地避免SQL語句效能突然變差。

　　注：DBMS_OUTLN可以通過$ORACLE_HOME/rdbms/admin/dbmsol.sql指令碼來安裝。

　　5. 使用SQL Profile。SQL Profile是Oracle 10g之後的新功能，此處不再介紹，請參考相應的文件。

　　除此之外，可以調整一些引數避免潛在的問題，比如將”_btree_bitmap_plans”引數設定為FALSE（這個引數請參考網際網路上的文章或Oracle文件）。

　　而在實際工作中，通過使用定製的統計資訊收集策略，以及在部分系統上使用OUTLINE，系統基本上不會出現已有的SQL效能突然變差的情況。當然也有維護人員操作不當引起的SQL效能突然變差，比如建了某個索引而沒有收集統計資訊，導致SQL使用了新建的索引，而該索引並不適合於那條SQL；維護人員意外刪除了表個索引的統計資訊。
轉自：http://www.laoxiong.net/how-keeping-sql-plan-stability.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-672154/，如需轉載，請註明出處，否則將追究法律責任。

相關文章