時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(0)
有客戶遇到SQL性能不穩(wěn)定，突然變差導致系統(tǒng)性能出現(xiàn)嚴重問題的情況。對于大型的系統(tǒng)來說，SQL性能不穩(wěn)定，有時突然變差，這是常常遇到的問題。這也是一些DBA的挑戰(zhàn)。
對于使用Oracle數(shù)據(jù)庫的應用系統(tǒng)，有時會出現(xiàn)運行得好好的SQL，性能突然變差。特別是對于OLTP類型系統(tǒng)執(zhí)行頻繁的核心SQL，如果出現(xiàn)性能問題，通常會影響整個數(shù)據(jù)庫的性能，進而影響整個系統(tǒng)的正常運行。對于個別的SQL，比如較少使用的查詢報表之類的SQL，如果出現(xiàn)問題，通常只影響少部分功能模塊，而不會影響整個系統(tǒng)。
那么應該怎么樣保持SQL性能的穩(wěn)定性？
SQL的性能變差，通常是在SQL語句重新進行了解析，解析時使用了錯誤的執(zhí)行計劃出現(xiàn)的。下列情況是SQL會重新解析的原因：
1. SQL語句沒有使用綁定變量，這樣SQL每次執(zhí)行都要解析。
2. SQL長時間沒有執(zhí)行，被刷出SHARED POOL，再次執(zhí)行時需要重新解析。
3. 在SQL引用的對象（表、視圖等）上執(zhí)行了DDL操作，甚至是結構發(fā)生了變化，比如建了一個索引。
4. 對SQL引用的對象進行了權限更改。
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：
1 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：
1 Select * from orders where created_date >=:b1;
假如大多數(shù)情況下，應用傳入的b1變量值為最近幾天內的日期值，那么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：
1 select * from orders where area=:b1 and product_id =:b2
這條SQL由于有area條件，因此會使用分區(qū)排除。如果第1 次執(zhí)行時應用傳給b1變量的值正好落在P1分區(qū)上，很可能導致SQL采用全表掃描訪問，如前面所描述的，導致SQL后續(xù)執(zhí)行時全部使用了全表掃描。
3. 其他原因，比如表做了類似于MOVE操作之后，索引不可用，對索引進行了更改。當然這種情況是屬于維護不當引起的問題，不在本文討論的范圍。
綜上所述，SQL語句性能突然變差，主要是因為綁定變量和統(tǒng)計信息的原因。注意這里只討論了突然變差的情況，而對于由于數(shù)據(jù)量和業(yè)務量的增加性能逐步變差的情況不討論。
為保持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)計信息。腳本中類似如下：
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í)行計劃進行執(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之后的新功能，此處不再介紹，請參考相應的文檔。
除此之外，可以調整一些參數(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)計信息。
關鍵詞標簽：Oracle,數(shù)據(jù)庫,SQL性能
相關閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務器設置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法