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

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

SQL性能變差原因分析

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

下列情況是SQL會(huì)重新解析的原因：

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

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

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

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

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

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

select * from orders where state=:b1

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

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

select * from orders where created_date >=:b1;

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

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

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

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

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

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

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

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

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

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

2. 修改SQL語(yǔ)句,使用HINT,使SQL語(yǔ)句按HINT指定的執(zhí)行計(jì)劃進(jìn)行執(zhí)行.這需要修改應(yīng)用,同時(shí)需要逐條SQL語(yǔ)句進(jìn)行,加上測(cè)試和發(fā)布,時(shí)間較長(zhǎng),成本較高,風(fēng)險(xiǎn)也較大.

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

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

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

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

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

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

作者：熊軍

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

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