SQL語句執(zhí)行計劃變化的原因分析與應(yīng)對策略-For Oracle 10g課件

1、SQL語句執(zhí)行計劃變化的原因分析與應(yīng)對策略 For ORACLE 10g 2012.01一、研究目的目前，ORACLE 10g以其高效的數(shù)據(jù)處理能力仍然是國內(nèi)外業(yè)務(wù)系統(tǒng)后臺數(shù)據(jù)庫的首選之一。因為對諸如金融行業(yè)等對實時聯(lián)機業(yè)務(wù)的處理能力非常重視的業(yè)務(wù)系統(tǒng)來說，數(shù)據(jù)庫的數(shù)據(jù)處理能力是最關(guān)鍵的因素之一。以銀行業(yè)為例，當今國內(nèi)銀行核心系統(tǒng)在高峰時期的交易量約達到每分鐘3000筆左右，而在數(shù)據(jù)庫中每筆交易的平均響應(yīng)時間則需控制在0.2秒以內(nèi)，從這些數(shù)字可以看出銀行核心系統(tǒng)對數(shù)據(jù)處理能力的要求之高。因此，使用ORACLE 10g作為后臺數(shù)據(jù)庫的業(yè)務(wù)系統(tǒng)往往會對SQL的執(zhí)行效率相當關(guān)注，從而另SQL語句的效

2、率優(yōu)化成為了每個數(shù)據(jù)庫系統(tǒng)管理人員的重要任務(wù)之一。從而，通過對SQL執(zhí)行效率的分析，掌握SQL執(zhí)行計劃改變的根本原因并對此采取對策是數(shù)據(jù)庫管理員維護好數(shù)據(jù)庫，保證業(yè)務(wù)系統(tǒng)穩(wěn)定運行的關(guān)鍵之一。二、數(shù)據(jù)庫應(yīng)用的SQL效率分析 對于數(shù)據(jù)庫應(yīng)用而言，一個SQL語句傳送到數(shù)據(jù)庫引擎，需要經(jīng)過幾個主要的處理才能最終完成，它是解析，執(zhí)行和獲取。獲取操作會由于數(shù)據(jù)邏輯的不同而不同，也沒有特別多的優(yōu)化方法，但對于前兩個處理而言，是否可以高效的解析，解析之后是否能得到快速執(zhí)行就決定了應(yīng)用程序是否可以被高效處理 。二、數(shù)據(jù)庫應(yīng)用的SQL效率分析續(xù)以下是兩種通常的優(yōu)化處理：解析的優(yōu)化使用嵌入變量減少硬解析硬解析在SQ

3、L語句第一次被數(shù)據(jù)庫解析時發(fā)生，它包含語法，權(quán)限，對象屬性等進行檢查，然后生成最快的執(zhí)行計劃。通常硬解析是大量消耗CPU的操作，應(yīng)當盡可能的避免。嵌入變量的使用將不同的常量寫入嵌入變量緩沖中，在SQL語句的多次執(zhí)行中保持哈希值不變，避免了重新的解析。執(zhí)行的優(yōu)化基于成本的CBO經(jīng)過精確計算得到最低成本的執(zhí)行計劃對于優(yōu)化器而言，它在拿到一個SQL的哈希值并結(jié)合訪問變量，通過對對象屬性，索引特性，分區(qū)等因素（統(tǒng)計信息）的分析確定各種可能的訪問路徑，排序方式或關(guān)聯(lián)方式等，并最終形成一個最小執(zhí)行成本的執(zhí)行計劃。 二、數(shù)據(jù)庫應(yīng)用的SQL效率分析續(xù)通常而言，只需按照上文中的兩個方面來做，應(yīng)用的效果會非常的好

4、，然而在實際進行數(shù)據(jù)庫SQL優(yōu)化的過程中，當這兩個方面的優(yōu)化措施相互作用時，卻可能產(chǎn)生一些意想不到的情景產(chǎn)生多版本的執(zhí)行計劃，在一些高訪問負荷，數(shù)據(jù)量巨大且分布復(fù)雜的業(yè)務(wù)系統(tǒng)中甚至可能引發(fā)突然性的性能下降。 因此，為使業(yè)務(wù)系統(tǒng)長期穩(wěn)定運行，以上問題是必須避免的。換言之，找到多版本執(zhí)行計劃的產(chǎn)生原因并避免由于嵌入變量的處理而導(dǎo)致的執(zhí)行計劃非正常改變將成為維護好業(yè)務(wù)系統(tǒng)的關(guān)鍵因素。三、多版本執(zhí)行計劃的產(chǎn)生原因續(xù)多版本執(zhí)行計劃產(chǎn)生的主要原因 由此可見，由于統(tǒng)計信息與優(yōu)化器設(shè)置是執(zhí)行計劃的決定性因素，故造成多版本執(zhí)行計劃產(chǎn)生的根本原因是由于統(tǒng)計信息或優(yōu)化器參數(shù)設(shè)置的改變，導(dǎo)致在下次觸發(fā)硬解析時生成了與

5、之前相比不同的執(zhí)行計劃。四、嵌入變量處理對執(zhí)行計劃的影響顯然，正常情況下執(zhí)行計劃的改變對業(yè)務(wù)系統(tǒng)的運行是有利無害的，比如由于業(yè)務(wù)量的不斷增長導(dǎo)致后臺數(shù)據(jù)庫記錄數(shù)的增多，在重新進行相關(guān)表的表分析后觸發(fā)數(shù)據(jù)庫更新統(tǒng)計信息從而造成在下一次硬解析時執(zhí)行計劃的改變（此時更新的執(zhí)行計劃對于大幅增加記錄數(shù)將使得SQL的執(zhí)行效率更佳）。然而，在使用嵌入式變量時，由于BINDPEEKING的特性，可能在極端情況下會生成非常不理想的執(zhí)行計劃從而對業(yè)務(wù)系統(tǒng)產(chǎn)生影響。四、嵌入變量處理對執(zhí)行計劃的影響續(xù)極端情況的發(fā)生對于優(yōu)化器而言，在一次硬解析中分析所有的嵌入變量組合，并得到適合大多數(shù)的執(zhí)行計劃是非常困難的，在邏輯上也

6、是不可能的，即使能夠做到，這個代價對于硬解析而言也過高了。優(yōu)化器可以做到的是通過BINDPEEKING技術(shù)產(chǎn)生與其更精確匹配的執(zhí)行計劃（最快的執(zhí)行計劃），但這個執(zhí)行計劃卻未必適合后來訪問的嵌入變量組合。所以就產(chǎn)生了一種極端的可能性：當某次硬解析被觸發(fā)后，優(yōu)化器要根據(jù)第一次給出的嵌入變量組合來確定一個執(zhí)行計劃，當執(zhí)行計劃確定后，它對于這個變量而言是極端好，而此后，數(shù)據(jù)庫將一直使用這個執(zhí)行計劃直到下一次硬解析被觸發(fā)。但對于隨后更多的嵌入變量組合而言它卻可能成為極端差的執(zhí)行計劃，最終導(dǎo)致業(yè)務(wù)系統(tǒng)收到影響。這就是因嵌入變量的處理而造成執(zhí)行計劃非正常改變的根本原因。五、應(yīng)對策略在設(shè)計階段確定最優(yōu)執(zhí)行計劃

7、A.根據(jù)數(shù)據(jù)分布特性得到最優(yōu)執(zhí)行計劃：在設(shè)計階段各個數(shù)據(jù)表的數(shù)據(jù)特點都是可預(yù)知的，從這些特征出發(fā)確定一個執(zhí)行計劃就最為準確，同時也避免了頻繁統(tǒng)計收集。這個執(zhí)行計劃需要與具體的數(shù)據(jù)無關(guān)，不求最快，但求最優(yōu)，即適合大多數(shù)情況。從技術(shù)上而言，可以使用嵌入變量的均值（而非理想或極端值）來創(chuàng)建執(zhí)行計劃。如果結(jié)合優(yōu)化器指令（HINT）可以更精確的固定一個最優(yōu)的執(zhí)行計劃，或者縮小BINDPEEKING所帶來的執(zhí)行計劃變化的范圍，減少BINDPEEKING帶來的副作用。 如果很好的應(yīng)用HINT和嵌入變量，BINDPEEKING就不會影響到應(yīng)用的正常運行。B.適當使用常量與變量的組合：對于數(shù)據(jù)中的極端值（最大，

8、最小等）可以使用常量，由數(shù)據(jù)庫自行選定最快執(zhí)行計劃，對于其它值則使用HINT來固定一個折中的執(zhí)行計劃。五、應(yīng)對策略續(xù)在應(yīng)用發(fā)布后強制數(shù)據(jù)庫使用最優(yōu)的執(zhí)行計劃A.使用HINT完全固定執(zhí)行計劃：屏蔽統(tǒng)計信息變化等因素對執(zhí)行計劃的影響，這種方法對HINT的使用要求較高，但僅影響單個應(yīng)用，不會對整個系統(tǒng)產(chǎn)生其他副作用，適用于應(yīng)用較為復(fù)雜的系統(tǒng)。B.不使用HINT，固定統(tǒng)計信息并關(guān)閉BIND-PEEKING特性：這樣數(shù)據(jù)庫會根據(jù)數(shù)據(jù)概率分布自動獲取一個折中的執(zhí)行計劃，避免了某些極端情況發(fā)生。這種方法不需要修改應(yīng)用代碼，適用于已經(jīng)發(fā)布無法修改的應(yīng)用。但該改動是一個數(shù)據(jù)庫全局性的改動，可能會對數(shù)據(jù)庫產(chǎn)生負面

9、影響。因此，該方法適用于應(yīng)用比較單一、風(fēng)險可控的系統(tǒng)，或修改應(yīng)用存在困難的情況。五、應(yīng)對策略續(xù)出現(xiàn)問題時的應(yīng)急解決方案續(xù)2.在歷史執(zhí)行計劃中找到引起問題和相對最佳的執(zhí)行計劃。從前面的對比情況可以看出，此例中執(zhí)行計劃267901271是引起整體性能下降的原因，它的平均邏輯讀平均CPU時間平均等待時間均8倍于其它的執(zhí)行計劃。而執(zhí)行計劃3231199102就整體而言，各方面的特征都較為平衡，并且經(jīng)過了1402次的運行檢驗，可以認為是適合于絕大多數(shù)數(shù)據(jù)的最優(yōu)的執(zhí)行計劃。相比之下對于其它執(zhí)行計劃雖然其邏輯讀CPU等較低，但執(zhí)行次數(shù)極少，或平均值過低。在打開自動統(tǒng)計收集的情況下，這些執(zhí)行計劃可能僅適用于某

10、些數(shù)據(jù)分布，應(yīng)該不是最優(yōu)的執(zhí)行計劃。五、應(yīng)對策略續(xù)出現(xiàn)問題時的應(yīng)急解決方案續(xù)3.找到各歷史執(zhí)行計劃版本所對應(yīng)的變量值。找到各歷史執(zhí)行計劃對應(yīng)的變量值，在此例中該SQL語句的變量值對應(yīng)于不同的地區(qū)號：業(yè)務(wù)模塊（SQL_ID）執(zhí)行計劃嵌入變量組合執(zhí)行計劃 gz3vqqgggar54 267901271B1:3010較差 2667863071B1:2604偏?。ㄟ\行次數(shù)少） 2893608670B1:0809較好 3231199102B1:0912好 3318325031B1:3500偏小（運行次數(shù)少） 3928782998B1:3400偏?。ㄟ\行次數(shù)少）六、總結(jié)隨著各類業(yè)務(wù)系統(tǒng)的飛速發(fā)展，其數(shù)據(jù)量和數(shù)據(jù)復(fù)雜性也不斷增加