SQL的效能變差，通常是在SQL語句重新進行了解析，解析時使用了錯誤的執行計劃出現的。

下列情況是SQL會重新解析的原因：

• SQL語句沒有使用繫結變數，這樣SQL每次執行都要解析。

• SQL長時間沒有執行，被刷出SHARED POOL，再次執行時需要重新解析。

• 在SQL引用的物件（表、檢視等）上執行了DDL操作，甚至是結構發生了變化，比如建了一個索引。

• 對SQL引用的物件進行了許可權更改。

• 重新分析（收集統計資訊）了SQL引用的表和索引，或者表和索引統計資訊被刪除。

• 修改了與效能相關的部分引數。

• 重新整理了共享池。

• 當然重啟資料庫也會使所有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：

select * from orders where state=:b1

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

2） 繫結變數的值變化範圍過大：
同樣假如orders表有一列created_date表示一筆訂單的下單時間，orders表裡面儲存了最近1年的資料，有如下的SQL：

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：

select * from orders where area=:b1 and produce_id=:b2;

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

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

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

2、如何保持SQL效能的穩定

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

1. 規劃好最佳化統計資訊的收集策略。對於Oracle 10g來說，預設的策略能夠滿足大部分需求，但是預設的收集策略會過多地收集列上的直方圖。由於繫結變數與直方圖固有的矛盾，為保持效能穩定，對使用繫結變數的列，不收集列上的直方圖；對的確需要收集直方圖的列，在SQL中該列上的條件就不要用繫結變數。

統計資訊收集策略，可以考慮對大部分表，使用系統預設的收集策略，而對於有問題的，可以用DBMS_STATS.LOCK_STATS鎖定表的統計資訊，避免系統自動收集該表的統計資訊，然後編寫指令碼來定製地收集表的統計資訊。指令碼中類似如下：

EXEC dbms_stats.unlock_table_stats...
EXEC dbms_stats.gather_table_stats...
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；維護人員意外刪除了表個索引的統計資訊。