基于Oracle的SQL優化

－SQL優化方法論崔華（dbsnake)@dbsnake

《基于Oracle的SQL優化》第一章Oracle里的優化器第二章Oracle里的執行計劃第三章Oracle里的Cursor和綁定變量第四章Oracle里的查詢轉換第五章Oracle里的統計信息第六章Oracle里的Hint第七章Oracle里的并行第八章Oracle里SQL優化的方法論SQLTuningMethodologyOracle里如何做SQL優化Oracle里SQL優化的方法論Oracle里SQL優化的方法論在實戰中的驗證SQLTuningGoalsOracle數據庫里SQL優化的終極目標就是要縮短目標SQL語句執行時間。要達到上述目的，我們通常只有如下三種方法可以選擇：降低目標SQL語句的資源消耗并行執行目標SQL語句平衡系統的資源消耗ReducetheWorkload“方法1：降低目標SQL語句的資源消耗”以縮短執行時間，這是最常用的SQL優化方法。這種方法的核心是要么通過在不更改業務邏輯的情況下改寫SQL來降低目標SQL語句的資源消耗，要么不改SQL但通過調整執行計劃或相關表的數據來降低目標SQL語句的資源消耗

ReducetheWorkload我們在“第四章Oracle里的查詢轉換”中曾經提到過一個案例，在那個例子里，我們在不更改原有業務邏輯的情況下通過將目標SQL由原語句：selectpubamntfromv_bc_lcgrppolwheregrppolnoin(selectgrppolnofromv_bc_lcpolwherepolno='9022000000000388');改寫為：selectpubamntfromv_bc_lcgrppola,(selectdistinctgrppolnogrppolnofromv_bc_lcpolwherepolno='9022000000000388')bwherea.grppolno=b.grppolno;后就實現了將原SQL的邏輯讀從200萬降低到了6，其執行時間也從6秒降低到了毫秒級這樣一個極好的優化效果ReducetheWorkload方法1所涉及到的這兩種優化手段在Oracle數據庫中能否奏效以及效果的好壞與否很大程度上取決于對CBO和執行計劃的理解程度，對CBO和執行計劃理解的越深，這兩種優化手段的應用就會越純熟，效果就會越好。這也是這本書所要提出的Oracle數據庫里SQL優化方法論的第一點：Oracle里SQL優化的本質是基于對CBO和執行計劃的深刻理解

ReducetheWorkload在很多人眼里，SQL優化就是走索引，就是用走索引來取代全表掃描。實際上這種認識是非常膚淺的。是，大部分SQL優化的問題都可以通過增加或者減少索引的方式來解決，但這絕不是全部！ReducetheWorkload例1：用合適的索引來避免不必要的全表掃描例2：用合適的索引來避免不必要的排序例3：用合適的函數索引來避免看似無法避免的全表掃描例4：重新設計索引來避免不必要的全表掃描

用合適的索引來避免不必要的全表掃描createindexidx_t1ont1(object_id);

select*fromt1whereobject_idisnull;

用合適的索引來避免不必要的全表掃描dropindexidx_t1;createindexidx_t1ont1(object_id,1);

用合適的索引來避免不必要的排序dropindexidx_t1;select*from(selectobject_name,object_idfromt1orderbyobject_iddesc)whererownum<4;用合適的索引來避免不必要的排序createindexidx_t1ont1(object_id);

用合適的函數索引來避免看似無法避免的全表掃描dropindexidx_t1;createindexidx_t1ont1(object_name);selectobject_name,object_idfromt1whereobject_namelike'%EMP';用合適的函數索引來避免看似無法避免的全表掃描selectobject_name,object_idfromt1wherereverse(object_name)likereverse('%EMP');createindexidx_fun_t1ont1(reverse(object_name));重新設計索引來避免不必要的全表掃描重新設計索引來避免不必要的全表掃描重新設計索引來避免不必要的全表掃描dropindexind_cus_basdata_t_5;createindexind_cus_basdata_t_5oncus_basdata_t(cust_uid)online;ParallelizetheWorkload“方法2：并行執行目標SQL語句”，這實際上是以額外的資源消耗來換取執行時間的縮短，很多情況下使用并行是針對某些SQL的唯一優化手段。BalancetheWorkload“方法3：平衡系統的資源消耗”可以避免不必要的資源爭用所導致的目標SQL語句執行時間的增長。SQLTuningMethod找到執行時間最長、消耗系統資源最多的TopSQL語句；查看上述TopSQL語句的執行計劃，并結合其資源消耗情況和相關統計信息、Trace文件來分析其執行計劃是否合理；通過修正措施（如調整上述TopSQL的執行計劃等）來對上述TopSQL做調整以縮短它們的執行時間，這里調整的指導原則就是之前剛介紹的Oracle數據庫里做SQL優化通常會采用的三種方法。HowtoIdentifyHigh-LoadSQL也許根本就不用定位，因為我們可能已經知道哪些SQL是我們的調整目標在Oracle數據庫里，定位TopSQL通常所采用的方法就是查看AWR報告或者Statspack報告，從AWR報告里的“SQLorderedbyElapsedTime”、“SQLorderedbyCPUTime”、“SQLorderedbyGets”等部分就能清晰的定位出在采樣的時間段內執行時間最長、消耗系統資源最多的TopSQLHowtoverifytheexecutionplan

Gettherealexecutionplan

－

display_cursor_9i.sql

－

printsql

－

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

－

$ORACLE_HOME/rdbms/admin/sprepsqlVerifystatistics

－

sosi.txt10046/10053event/books

HowtoImplementcorrectiveactions

當定位到TopSQL及通過上述手段分析清楚其問題所在后，接下來只需秉承“對癥下藥”的原則采用針對性的調整措施就可以了。這里的關鍵就在于分析清楚原因后的“對癥下藥”，即使是同樣的癥狀、由于導致上述癥狀的原因的不同，依然可能會采用截然相反的調整手段。在Oracle數據庫里，針對TopSQL的調整手段是五花八門、不一而足的，包括但不限于如下調整措施：如果是統計信息不準或是因為CBO計算某些SQL的執行路徑（AccessPath）的成本所用公式的先天不足而導致的SQL性能問題，我們可以通過重新收集統計信息或者手工修改統計信息或者使用Hint來加以解決；如果是SQL語句的寫法問題，我們可以通過在不更改業務邏輯的情況下改寫SQL來加以解決；HowtoImplementcorrectiveactions

如果是不必要的全表掃描/排序而導致了目標SQL的性能問題，我們可以通過建立合適的索引（包括函數索引、位圖索引等）來加以解決；如果是因為各種原因導致的目標SQL的執行計劃不穩定，我們可以通過使用Hint或SQLProfile/SPM來加以解決；如果是表或者索引的不良設計導致的目標SQL的性能問題，我們可以通過重新設計表/索引，重新組織表里的數據來加以解決；如果上述調整措施都失效，我們可以考慮用并行來縮短目標SQL的執行時間；如果上述調整措施、包括并行都失效，我們還可以在聯系實際業務的基礎上更改目標SQL的執行邏輯，甚至不執行目標SQL，這是最徹底的優化:)HowtoImplementcorrectiveactions

在Oracle數據庫里做SQL優化是一個不斷迭代、循序漸進的過程。當你解決了執行時間最長、消耗系統資源最多的TopSQL后，系統里原先一些執行時間不那么長、消耗資源不那么多的SQL此時可能就會變成執行時間最長、消耗系統資源最多的TopSQL了。所以上述三個SQL優化步驟需要被不斷的重復執行下去，直到系統性能已經達到預期目標或者再沒有SQL可以被調整SQLTuningmaydependsonyourAPPLogic這里我想再提出Oracle數據庫里SQL優化方法論的第二點：Oracle里SQL優化需要聯系實際的業務SQLTuningmaydependsonyourAPPLogicOracle里SQL優化需要聯系實際的業務的實例SQLTuningmaydependsonyourAPPLogicSQLTuningmaydependsonyourAPPLogicSQLTuningmaydependsonyourAPPLogic這套系統的主要業務功能之一是“及時地插入數據”，表中的數據活動可以說是99%是插入，不到1%的查詢，沒有delete和update操作由于要插入的大表比較多，索引也很大，而buffercache有限，因此插入時的索引維護對插入性能有非常關鍵的影響如果貿然地增加一個acct_id1列的索引，這個操作可能需要很久才會體現出其負面后果UseBindVariablesatAppropriateTime