時(shí)間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
有客戶遇到SQL性能不穩(wěn)定，突然變差導(dǎo)致系統(tǒng)性能出現(xiàn)嚴(yán)重問題的情況。對(duì)于大型的系統(tǒng)來說，SQL性能不穩(wěn)定，有時(shí)突然變差，這是常常遇到的問題。這也是一些DBA的挑戰(zhàn)。
對(duì)于使用Oracle數(shù)據(jù)庫的應(yīng)用系統(tǒng)，有時(shí)會(huì)出現(xiàn)運(yùn)行得好好的SQL，性能突然變差。特別是對(duì)于OLTP類型系統(tǒng)執(zhí)行頻繁的核心SQL，如果出現(xiàn)性能問題，通常會(huì)影響整個(gè)數(shù)據(jù)庫的性能，進(jìn)而影響整個(gè)系統(tǒng)的正常運(yùn)行。對(duì)于個(gè)別的SQL，比如較少使用的查詢報(bào)表之類的SQL，如果出現(xiàn)問題，通常只影響少部分功能模塊，而不會(huì)影響整個(gè)系統(tǒng)。
那么應(yīng)該怎么樣保持SQL性能的穩(wěn)定性？
SQL的性能變差，通常是在SQL語句重新進(jìn)行了解析，解析時(shí)使用了錯(cuò)誤的執(zhí)行計(jì)劃出現(xiàn)的。下列情況是SQL會(huì)重新解析的原因：
1. SQL語句沒有使用綁定變量，這樣SQL每次執(zhí)行都要解析。
2. SQL長時(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ù)庫也會(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：
1 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：
1 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：
1 select * from orders where area=:b1 and product_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àn)榻壎ㄗ兞亢徒y(tǒng)計(jì)信息的原因。注意這里只討論了突然變差的情況，而對(duì)于由于數(shù)據(jù)量和業(yè)務(wù)量的增加性能逐步變差的情況不討論。
為保持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ì)信息。腳本中類似如下：
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指定的執(zhí)行計(jì)劃進(jìn)行執(zhí)行。這需要修改應(yīng)用，同時(shí)需要逐條SQL語句進(jìn)行，加上測(cè)試和發(fā)布，時(shí)間較長，成本較高，風(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語句，可以使用OUTLINE來加固其執(zhí)行計(jì)劃。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根據(jù)已有的執(zhí)行正常的SQL游標(biāo)來創(chuàng)建OUTLINE。如果事先對(duì)所有頻繁執(zhí)行的核心SQL使用OUTLINE加固執(zhí)行計(jì)劃，將最大可能地避免SQL語句性能突然變差。
注：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ì)信息。
關(guān)鍵詞標(biāo)簽：Oracle,數(shù)據(jù)庫,SQL性能
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法