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

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

SQL性能變差原因分析

SQL的性能變差,通常是在SQL語句重新進行了解析,解析時使用了錯誤的執(zhí)行計劃出現(xiàn)的.

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

1. SQL語句沒有使用綁定變量,這樣SQL每次執(zhí)行都要解析.
2. SQL長時間沒有執(zhí)行,被刷出SHARED POOL,再次執(zhí)行時需要重新解析.
3. 在SQL引用的對象(表、視圖等)上執(zhí)行了DDL操作,甚至是結(jié)構(gòu)發(fā)生了變化,比如建了一個索引.
4. 對SQL引用的對象進行了權(quán)限更改.
5. 重新分析(收集統(tǒng)計信息)了SQL引用的表和索引,或者表和索引統(tǒng)計信息被刪除.
6. 修改了與性能相關的部分參數(shù).
7. 刷新了共享池.
8. 當然重啟數(shù)據(jù)庫也會使所有SQL全部重新解析.

SQL重新解析后,跟以前相比,性能突然變差,通常是下列原因：

1. 表和索引的優(yōu)化統(tǒng)計信息被刪除,或者重新收集后統(tǒng)計信息不準確.重新收集統(tǒng)計信息通常是由于收集策略(方法)不正確引起.比如對分區(qū)表使用analyze命令而不是用dbms_stats包、收集統(tǒng)計信息時采樣比例過小等等.Oracle優(yōu)化器嚴重依賴于統(tǒng)計信息,如果統(tǒng)計信息有問題,則很容易導致SQL不能使用正確的執(zhí)行計劃.

2. SQL綁定變量窺探(bind peeking),同時綁定變量對應的列上有直方圖；或者綁定變量的值變化范圍過大、分區(qū)數(shù)據(jù)分布極不均勻：

1) 綁定變量的列上有直方圖：

假如表orders存儲所有的訂單,state列有3種不同的值：0表示未處理,1表示處理成功完成,2表示處理失敗.State列上有一個索引,表中絕大部分數(shù)據(jù)的state列為1,0和2占少數(shù).有下面的SQL：

select * from orders where state=:b1

這里:b1是變量,在大多數(shù)情況下這個值為0,則應該使用索引,但是如果SQL被重新解析,而第一次執(zhí)行時應用傳給變量b1值為1,則不會使用索引,采用全表掃描的方式來訪問表.對于綁定變量的SQL,只在第一次執(zhí)行時才會進行綁定變量窺探,并以此確定執(zhí)行計劃,該SQL后續(xù)執(zhí)行時全部按這個執(zhí)行計劃.這樣在后續(xù)執(zhí)行時,b1變量傳入的值為0的時候,仍然是第一次執(zhí)行時產(chǎn)生的執(zhí)行計劃,即使用的是全表掃描,這樣會導致性能很差.

2) 綁定變量的值變化范圍過大：
同樣假如orders表有一列created_date表示一筆訂單的下單時間,orders表里面存儲了最近1年的數(shù)據(jù),有如下的SQL：

select * from orders where created_date >=:b1;

假如大多數(shù)情況下,應用傳入的b1變量值為最近幾天內(nèi)的日期值,那么SQL使用的是created_date列上的索引,而如果b1變量值為5個月之前的一個值,那么就會使用全表掃描.與上面描述的直方圖引起的問題一樣,如果SQL第1次執(zhí)行時傳入的變量值引起的是全表掃描,那么將該SQL后續(xù)執(zhí)行時都使用了全表掃描,從而影響了性能.

3) 分區(qū)數(shù)據(jù)量不均勻：
對于范圍和列表分區(qū),可能存在各個分區(qū)之間數(shù)據(jù)量極不均勻的情況下.比如分區(qū)表orders按地區(qū)area進行了分區(qū),P1分區(qū)只有幾千行,而P2分區(qū)有200萬行數(shù)據(jù).同時假如有一列product_id,其上有一個本地分區(qū)索引,有如下的SQL：

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

這條SQL由于有area條件,因此會使用分區(qū)排除.如果第1 次執(zhí)行時應用傳給b1變量的值正好落在P1分區(qū)上,很可能導致SQL采用全表掃描訪問,如前面所描述的,導致SQL后續(xù)執(zhí)行時全部使用了全表掃描.

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

綜上所述,SQL語句性能突然變差,主要是因為綁定變量和統(tǒng)計信息的原因.注意這里只討論了突然變差的情況,而對于由于數(shù)據(jù)量和業(yè)務量的增加性能逐步變差的情況不討論.

如何保持SQL性能的穩(wěn)定

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

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

統(tǒng)計信息收集策略,可以考慮對大部分表,使用系統(tǒng)默認的收集策略,而對于有問題的,可以用DBMS_STATS.LOCK_STATS鎖定表的統(tǒng)計信息,避免系統(tǒng)自動收集該表的統(tǒng)計信息,然后編寫腳本來定制地收集表的統(tǒng)計信息.腳本中類似如下：

2. 修改SQL語句,使用HINT,使SQL語句按HINT指定的執(zhí)行計劃進行執(zhí)行.這需要修改應用,同時需要逐條SQL語句進行,加上測試和發(fā)布,時間較長,成本較高,風險也較大.

3. 修改隱含參數(shù)” _optim_peek_user_binds”為FALSE,修改這個參數(shù)可能會引起性能問題(這里討論的是穩(wěn)定性問題).

4. 使用OUTLINE.對于曾經(jīng)出現(xiàn)過執(zhí)行計劃突然變差的SQL語句,可以使用OUTLINE來加固其執(zhí)行計劃.在10g中DBMS_OUTLN.CREATE_OUTLINE可以根據(jù)已有的執(zhí)行正常的SQL游標來創(chuàng)建OUTLINE.如果事先對所有頻繁執(zhí)行的核心SQL使用OUTLINE加固執(zhí)行計劃,將最大可能地避免SQL語句性能突然變差.

注：DBMS_OUTLN可以通過$ORACLE_HOME/rdbms/admin/dbmsol.sql腳本來安裝.

5. 使用SQL Profile.SQL Profile是Oracle 10g之后的新功能,此處不再介紹,請參考相應的文檔.

除此之外,可以調(diào)整一些參數(shù)避免潛在的問題,比如將”_btree_bitmap_plans”參數(shù)設置為FALSE(這個參數(shù)請參考互聯(lián)網(wǎng)上的文章或Oracle文檔).

而在實際工作中,通過使用定制的統(tǒng)計信息收集策略,以及在部分系統(tǒng)上使用OUTLINE,系統(tǒng)基本上不會出現(xiàn)已有的SQL性能突然變差的情況.當然也有維護人員操作不當引起的SQL性能突然變差,比如建了某個索引而沒有收集統(tǒng)計信息,導致SQL使用了新建的索引,而該索引并不適合于那條SQL；維護人員意外刪除了表個索引的統(tǒng)計信息.

作者：熊軍

文章來自微信公眾號：數(shù)據(jù)和云

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/4249.html