1、基于Oracle的SQL優(yōu)化 SQL優(yōu)化方法論 崔華（dbsnake) dbsnake 基于基于Oracle的的SQL優(yōu)化優(yōu)化 第一章第一章 OracleOracle里的優(yōu)化器里的優(yōu)化器 第二章第二章 OracleOracle里的執(zhí)行計劃里的執(zhí)行計劃 第三章第三章 OracleOracle里的里的CursorCursor和綁定變量和綁定變量 第四章第四章 OracleOracle里的查詢轉(zhuǎn)換里的查詢轉(zhuǎn)換 第五章第五章 OracleOracle里的統(tǒng)計信息里的統(tǒng)計信息 第六章第六章 OracleOracle里的里的HintHint 第七章第七章 OracleOracle里的并行里的并行 第八章第

2、八章 OracleOracle里里SQLSQL優(yōu)化的方法論優(yōu)化的方法論SQL Tuning Methodology OracleOracle里如何做里如何做SQLSQL優(yōu)化優(yōu)化 OracleOracle里里SQLSQL優(yōu)化的方法論優(yōu)化的方法論 OracleOracle里里SQLSQL優(yōu)化的方法論在實戰(zhàn)中的驗證優(yōu)化的方法論在實戰(zhàn)中的驗證SQL Tuning GoalsOracleOracle數(shù)據(jù)庫里數(shù)據(jù)庫里SQLSQL優(yōu)化的終極目標(biāo)就是要縮短目標(biāo)優(yōu)化的終極目標(biāo)就是要縮短目標(biāo)SQLSQL語句執(zhí)語句執(zhí)行時間。要達(dá)到上述目的，我們通常只有如下三種方法可以選擇：行時間。要達(dá)到上述目的，我們通常只有如下三

3、種方法可以選擇： 降低目標(biāo)降低目標(biāo)SQLSQL語句的資源消耗語句的資源消耗 并行執(zhí)行目標(biāo)并行執(zhí)行目標(biāo)SQLSQL語句語句 平衡系統(tǒng)的資源消耗平衡系統(tǒng)的資源消耗Reduce the Workload “方法方法1 1：降低目標(biāo)：降低目標(biāo)SQLSQL語句的資源消耗語句的資源消耗”以縮短執(zhí)行時間，這以縮短執(zhí)行時間，這是最常用的是最常用的SQLSQL優(yōu)化方法。這種方法的核心是優(yōu)化方法。這種方法的核心是要么通過在不更要么通過在不更改業(yè)務(wù)邏輯的情況下改寫改業(yè)務(wù)邏輯的情況下改寫SQLSQL來降低目標(biāo)來降低目標(biāo)SQLSQL語句的資源消耗，語句的資源消耗，要么不改要么不改SQLSQL但通過調(diào)整執(zhí)行計劃或相關(guān)表的

4、數(shù)據(jù)但通過調(diào)整執(zhí)行計劃或相關(guān)表的數(shù)據(jù)來降低目標(biāo)來降低目標(biāo)SQLSQL語句的資源消耗語句的資源消耗 Reduce the Workload我們在我們在“第四章第四章 OracleOracle里的查詢轉(zhuǎn)換里的查詢轉(zhuǎn)換”中曾經(jīng)提到過一個案例，中曾經(jīng)提到過一個案例，在在那個例子里，我們在不更改原有業(yè)務(wù)邏輯的情況下通過將目標(biāo)那個例子里，我們在不更改原有業(yè)務(wù)邏輯的情況下通過將目標(biāo)SQLSQL由原語句：由原語句：select pubamnt from v_bc_lcgrppol where grppolno in (select grppolno from v_bc_lcpol where polno =

5、9022000000000388);改寫為：改寫為：select pubamnt from v_bc_lcgrppol a,(select distinct grppolno grppolno from v_bc_lcpol where polno = 9022000000000388) b where a.grppolno=b.grppolno;后就實現(xiàn)了將原后就實現(xiàn)了將原SQLSQL的邏輯讀從的邏輯讀從200200萬降低到了萬降低到了6 6，其執(zhí)行時間也，其執(zhí)行時間也從從6 6秒降低到了毫秒級這樣一個極好的優(yōu)化效果秒降低到了毫秒級這樣一個極好的優(yōu)化效果 Reduce the Workloa

6、d 方法方法1 1所涉及到的這兩種優(yōu)化手段在所涉及到的這兩種優(yōu)化手段在OracleOracle數(shù)據(jù)庫中能否奏效數(shù)據(jù)庫中能否奏效以及效果的好壞與否很大程度上取決于對以及效果的好壞與否很大程度上取決于對CBOCBO和執(zhí)行計劃的理和執(zhí)行計劃的理解程度，對解程度，對CBOCBO和執(zhí)行計劃理解的越深，這兩種優(yōu)化手段的應(yīng)和執(zhí)行計劃理解的越深，這兩種優(yōu)化手段的應(yīng)用就會越純熟，效果就會越好。這也是這本書所要提出的用就會越純熟，效果就會越好。這也是這本書所要提出的OracleOracle數(shù)據(jù)庫里數(shù)據(jù)庫里SQLSQL優(yōu)化方法論的第一點：優(yōu)化方法論的第一點：OracleOracle里里SQLSQL優(yōu)化的優(yōu)化的本質(zhì)是

7、基于對本質(zhì)是基于對CBOCBO和執(zhí)行計劃的深刻理解和執(zhí)行計劃的深刻理解 Reduce the Workload 在很多人眼里，在很多人眼里，SQLSQL優(yōu)化就是走索引，就是用走索引來取代全優(yōu)化就是走索引，就是用走索引來取代全表掃描。實際上這種認(rèn)識是表掃描。實際上這種認(rèn)識是非常非常膚淺的。膚淺的。 是，大部分是，大部分SQLSQL優(yōu)化的問題都可以通過增加或者減少索引的方優(yōu)化的問題都可以通過增加或者減少索引的方式來解決，但這絕不是全部！式來解決，但這絕不是全部！Reduce the Workload 例例1 1：用合適的索引來避免不必要的全表掃描：用合適的索引來避免不必要的全表掃描 例例2 2：用

8、合適的索引來避免不必要的排序：用合適的索引來避免不必要的排序 例例3 3：用合適的函數(shù)索引來避免看似無法避免的全表掃描：用合適的函數(shù)索引來避免看似無法避免的全表掃描 例例4 4：重新設(shè)計索引來避免不必要的全表掃描：重新設(shè)計索引來避免不必要的全表掃描 用合適的索引來避免不必要的全表掃描用合適的索引來避免不必要的全表掃描create index idx_t1 on t1(object_id); select * from t1 where object_id is null; 用合適的索引來避免不必要的全表掃描用合適的索引來避免不必要的全表掃描drop index idx_t1; create i

9、ndex idx_t1 on t1(object_id,1); 用合適的索引來避免不必要的排序用合適的索引來避免不必要的排序drop index idx_t1; select * from (select object_name,object_id from t1 order by object_id desc) where rownum4; 用合適的索引來避免不必要的排序用合適的索引來避免不必要的排序create index idx_t1 on t1(object_id); 用合適的函數(shù)索引來避免看似無法避免的全表掃描用合適的函數(shù)索引來避免看似無法避免的全表掃描drop index idx_

10、t1; create index idx_t1 on t1(object_name); select object_name,object_id from t1 where object_name like %EMP; 用合適的函數(shù)索引來避免看似無法避免的全表掃描用合適的函數(shù)索引來避免看似無法避免的全表掃描 select object_name,object_id from t1 where reverse(object_name) like reverse(%EMP);create index idx_fun_t1 on t1(reverse(object_name);重新設(shè)計索引來避免不必

11、要的全表掃描重新設(shè)計索引來避免不必要的全表掃描重新設(shè)計索引來避免不必要的全表掃描重新設(shè)計索引來避免不必要的全表掃描重新設(shè)計索引來避免不必要的全表掃描重新設(shè)計索引來避免不必要的全表掃描drop index ind_cus_basdata_t_5; create index ind_cus_basdata_t_5 on cus_basdata_t (cust_uid) online;Parallelize the Workload “方法方法2 2：并行執(zhí)行目標(biāo)：并行執(zhí)行目標(biāo)SQLSQL語句語句”，這實際上是以額外的資源這實際上是以額外的資源消耗來換取執(zhí)行時間的縮短，很多情況下使用并行是針對某些消

12、耗來換取執(zhí)行時間的縮短，很多情況下使用并行是針對某些SQLSQL的唯一優(yōu)化手段的唯一優(yōu)化手段。Balance the Workload “方法方法3 3：平衡系統(tǒng)的資源消耗：平衡系統(tǒng)的資源消耗” ” 可以避免不必要的資源爭用可以避免不必要的資源爭用所導(dǎo)致的目標(biāo)所導(dǎo)致的目標(biāo)SQLSQL語句執(zhí)行時間的增長。語句執(zhí)行時間的增長。SQL Tuning Method 找到執(zhí)行時間最長、消耗系統(tǒng)資源最多的找到執(zhí)行時間最長、消耗系統(tǒng)資源最多的Top SQLTop SQL語句；語句； 查看上述查看上述Top SQLTop SQL語句的執(zhí)行計劃，并結(jié)合其資源消耗情況和語句的執(zhí)行計劃，并結(jié)合其資源消耗情況和相關(guān)統(tǒng)

13、計信息、相關(guān)統(tǒng)計信息、TraceTrace文件來文件來分析其執(zhí)行計劃是否合理；分析其執(zhí)行計劃是否合理； 通過修正措施（如調(diào)整上述通過修正措施（如調(diào)整上述Top SQLTop SQL的執(zhí)行計劃等）來的執(zhí)行計劃等）來對上述對上述Top SQLTop SQL做調(diào)整以縮短它們的執(zhí)行時間做調(diào)整以縮短它們的執(zhí)行時間，這里調(diào)整的指導(dǎo)原則，這里調(diào)整的指導(dǎo)原則就是之前剛介紹的就是之前剛介紹的OracleOracle數(shù)據(jù)庫里做數(shù)據(jù)庫里做SQLSQL優(yōu)化通常會采用的三優(yōu)化通常會采用的三種方法。種方法。How to Identify High-Load SQL 也許根本就不用定位，因為我們可能已經(jīng)知道哪些也許根本就不

14、用定位，因為我們可能已經(jīng)知道哪些SQLSQL是我們是我們的調(diào)整目標(biāo)的調(diào)整目標(biāo) 在在OracleOracle數(shù)據(jù)庫里，定位數(shù)據(jù)庫里，定位Top SQLTop SQL通常所采用的方法就是查看通常所采用的方法就是查看AWRAWR報告或者報告或者StatspackStatspack報告，從報告，從AWRAWR報告里的報告里的“SQL ordered SQL ordered by Elapsed Time”by Elapsed Time”、“SQL ordered by CPU Time”SQL ordered by CPU Time”、“SQL SQL ordered by Gets”ordered

15、by Gets”等部分就能清晰的定位出在采樣的時間段等部分就能清晰的定位出在采樣的時間段內(nèi)執(zhí)行時間最長、消耗系統(tǒng)資源最多的內(nèi)執(zhí)行時間最長、消耗系統(tǒng)資源最多的Top SQLTop SQLHow to verify the execution plan Get the real execution plan display_cursor_9i.sql printsql $ORACLE_HOME/rdbms/admin/awrsqrpt.sql $ORACLE_HOME/rdbms/admin/sprepsqlVerify statistics sosi.txt10046/10053 eventht

16、tp:/ How to Implement corrective actions 當(dāng)定位到當(dāng)定位到Top SQLTop SQL及通過上述手段分析清楚其問題所在后，接下及通過上述手段分析清楚其問題所在后，接下來來只需秉承只需秉承“對癥下藥對癥下藥”的原則采用針對性的調(diào)整措施就可以了的原則采用針對性的調(diào)整措施就可以了。這里的關(guān)鍵就在于分析清楚原因后的這里的關(guān)鍵就在于分析清楚原因后的“對癥下藥對癥下藥”，即使是同樣，即使是同樣的的癥狀、由于導(dǎo)致上述癥狀的原因的不同，依然可能會采用截然相癥狀、由于導(dǎo)致上述癥狀的原因的不同，依然可能會采用截然相反的調(diào)整手段。反的調(diào)整手段。在在OracleOracle數(shù)據(jù)

17、庫里，針對數(shù)據(jù)庫里，針對Top SQLTop SQL的調(diào)整手段是的調(diào)整手段是五花八門、不一而足的，包括但不限于如下調(diào)整措施五花八門、不一而足的，包括但不限于如下調(diào)整措施： 如果是統(tǒng)計信息不準(zhǔn)或是因為如果是統(tǒng)計信息不準(zhǔn)或是因為CBOCBO計算某些計算某些SQLSQL的執(zhí)行路徑（的執(zhí)行路徑（Access PathAccess Path）的成本所用公式的先天不足而導(dǎo)致的）的成本所用公式的先天不足而導(dǎo)致的SQLSQL性能問性能問題，我們可以通過重新收集統(tǒng)計信息或者手工修改統(tǒng)計信息或題，我們可以通過重新收集統(tǒng)計信息或者手工修改統(tǒng)計信息或者使用者使用HintHint來加以解決；來加以解決； 如果是如果是S

18、QLSQL語句的寫法問題，我們可以通過在不更改業(yè)務(wù)邏輯語句的寫法問題，我們可以通過在不更改業(yè)務(wù)邏輯的情況下改寫的情況下改寫SQLSQL來加以解決；來加以解決；How to Implement corrective actions 如果是不必要的全表掃描如果是不必要的全表掃描/ /排序而導(dǎo)致了目標(biāo)排序而導(dǎo)致了目標(biāo)SQLSQL的性能問題，我的性能問題，我們可以通過建立合適的索引（包括函數(shù)索引、位圖索引等）來加們可以通過建立合適的索引（包括函數(shù)索引、位圖索引等）來加以解決；以解決； 如果是因為各種原因?qū)е碌哪繕?biāo)如果是因為各種原因?qū)е碌哪繕?biāo)SQLSQL的執(zhí)行計劃不穩(wěn)定，我們可的執(zhí)行計劃不穩(wěn)定，我們可以

19、通過使用以通過使用HintHint或或SQL ProfileSQL Profile/ /SPMSPM來加以解決；來加以解決； 如果是表或者索引的不良設(shè)計導(dǎo)致的目標(biāo)如果是表或者索引的不良設(shè)計導(dǎo)致的目標(biāo)SQLSQL的性能問題，我們的性能問題，我們可以通過重新設(shè)計表可以通過重新設(shè)計表/ /索引，重新組織表里的數(shù)據(jù)來加以解決；索引，重新組織表里的數(shù)據(jù)來加以解決； 如果上述調(diào)整措施都失效，我們可以考慮用并行來縮短目標(biāo)如果上述調(diào)整措施都失效，我們可以考慮用并行來縮短目標(biāo)SQLSQL的執(zhí)行時間；的執(zhí)行時間； 如果上述調(diào)整措施、包括并行都失效，我們還可以在聯(lián)系實際業(yè)如果上述調(diào)整措施、包括并行都失效，我們還可以

20、在聯(lián)系實際業(yè)務(wù)的基礎(chǔ)上更改目標(biāo)務(wù)的基礎(chǔ)上更改目標(biāo)SQLSQL的執(zhí)行邏輯，的執(zhí)行邏輯，甚至不執(zhí)行目標(biāo)甚至不執(zhí)行目標(biāo)SQLSQL，這是，這是最徹底的優(yōu)化最徹底的優(yōu)化 :):)How to Implement corrective actions 在在OracleOracle數(shù)據(jù)庫里做數(shù)據(jù)庫里做SQLSQL優(yōu)化是一個不斷迭代、循序漸進的過程優(yōu)化是一個不斷迭代、循序漸進的過程。當(dāng)你解決了執(zhí)行時間最長、消耗系統(tǒng)資源最多的。當(dāng)你解決了執(zhí)行時間最長、消耗系統(tǒng)資源最多的Top SQLTop SQL后，后，系統(tǒng)里原先一些執(zhí)行時間不那么長、消耗資源不那么多的系統(tǒng)里原先一些執(zhí)行時間不那么長、消耗資源不那么多的SQL

21、SQL此此時可能就會變成執(zhí)行時間最長、消耗系統(tǒng)資源最多的時可能就會變成執(zhí)行時間最長、消耗系統(tǒng)資源最多的Top SQLTop SQL了了。所以。所以上述三個上述三個SQLSQL優(yōu)化步驟需要被不斷的重復(fù)執(zhí)行下去，直到優(yōu)化步驟需要被不斷的重復(fù)執(zhí)行下去，直到系統(tǒng)性能已經(jīng)達(dá)到預(yù)期目標(biāo)或者再沒有系統(tǒng)性能已經(jīng)達(dá)到預(yù)期目標(biāo)或者再沒有SQLSQL可以被調(diào)整可以被調(diào)整SQL Tuning may depends on your APP Logic 這里我想再提出這里我想再提出OracleOracle數(shù)據(jù)庫里數(shù)據(jù)庫里SQLSQL優(yōu)化方法論的第二點：優(yōu)化方法論的第二點：OracleOracle里里SQLSQL優(yōu)化需要聯(lián)系實際的業(yè)務(wù)優(yōu)化需要聯(lián)系實際的業(yè)務(wù)SQL Tuning may depends on your APP Logic OracleOracle里里SQLSQL優(yōu)化需要聯(lián)系實際的業(yè)務(wù)的實例優(yōu)化需要聯(lián)系實際的業(yè)務(wù)的實例SQL Tuning may depends on your APP LogicSQL Tuning may depends on your APP LogicSQL Tuning may depends on your APP Logic 這套系統(tǒng)的主要業(yè)務(wù)功能之一是這套系統(tǒng)的主要業(yè)務(wù)功能之一是“及時地插入數(shù)據(jù)及時