《如何保持Oracle數據庫SQL性能的穩定性》要點：
本文介紹了如何保持Oracle數據庫SQL性能的穩定性，希望對您有用。如果有疑問，可以聯系我們。

使用Oracle數據庫的應用系統,有時出現SQL性能突然變差,特別是對于OLTP類型系統執行頻繁的核心SQL,如果出現性能問題,通常會影響整個數據庫的性能,進而影響整個系統的正常運行.這是常常遇到的問題,也是一些DBA的挑戰.

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：

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語句性能突然變差,主要是因為綁定變量和統計信息的原因.注意這里只討論了突然變差的情況,而對于由于數據量和業務量的增加性能逐步變差的情況不討論.

如何保持SQL性能的穩定

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

1. 規劃好優化統計信息的收集策略.對于Oracle 10g來說,默認的策略能夠滿足大部分需求,但是默認的收集策略會過多地收集列上的直方圖.由于綁定變量與直方圖固有的矛盾,為保持性能穩定,對使用綁定變量的列,不收集列上的直方圖；對的確需要收集直方圖的列,在SQL中該列上的條件就不要用綁定變量.

統計信息收集策略,可以考慮對大部分表,使用系統默認的收集策略,而對于有問題的,可以用DBMS_STATS.LOCK_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.fzlkiss.com/jiaocheng/4249.html