SQLTuning如何寫出優(yōu)質(zhì)高效的數(shù)據(jù)庫程序

1、基本原則v簡單的是最有力的；v2/8原則；v性能優(yōu)化是動力是基于對美的追求?；炯s定vSQL語句書寫的約定，如關(guān)鍵字要大寫,函數(shù)，對象名，變量要小寫，一個關(guān)鍵字一行，每行以關(guān)鍵字右對齊，等號左右各空一位等，各個部分僅空一格；v在SQL語句中對于值非常多的列條件要用變量綁定，不要用常量；對于值少記錄多，且分布不均勻，如果要用上該列的索引，就要用常量。v在SQL語句中盡量不要用左值函數(shù)；v查詢條件的順序要盡量能夠利用已有索引，如果不能利用現(xiàn)有的索引而需要新建綜合索引，索引列的順序、查詢條件的順序要和表中的列順序一致。v如果能夠確保兩個輸出值集沒有重復值，Union All 比Union 有效率。一

2、些建議v某些復雜和耗時的SQL語句可以考慮采用幾個語句來完成。v對使用外連接的語句，可以嘗試用一些組合語句來代替。v如果能夠用行操作SQL語句完成，不要用集操作SQL(如count, Distinct, minus, intersect)語句完成。問題1vSELECT BOARD_BARCODE FROM SMT_BOARD_WORK_REC bwr, SMT_DEP_TASK_INFO dti WHERE BOARD_BARCODE=025767102A000842 AND dti.Second_Series=標準 and bwr.Task_No=dti.Task_No AND PROC_O

3、RDER=1;vSELECT BOARD_BARCODE FROM SMT_BOARD_WORK_REC bwr,SMT_DEP_TASK_INFO dti WHERE BOARD_BARCODE=:b1 AND dti.Second_Series=標準 and bwr.Task_No=dti.Task_No AND PROC_ORDER=1;v沒有用變量綁定，導致大量的Parse，占用CPU時間和內(nèi)存。效果v下面是使用文字變量和綁定變量通過Spotlight進行對比的觀察結(jié)果。不良代碼為執(zhí)行1500行操作，將同樣的SQL解釋了500次，對應(yīng)的在內(nèi)存中占用500倍的空間，帶來嚴重的latch爭

4、用。v在使用綁定變量的情況下，留意對應(yīng)SQL的Loads和Exections列值，運行次數(shù)逐漸增長為500、1000、1500直到2000。而變化時，Loads值始終是1，說明shared pool共享性好，同時latch爭用也會比較少。 問題2vSELECT ROWID, organization_id, wip_entity_name, cpb_bar_code, cpb_revision, cpb_item_id, zcb_bar_code, zcb_revision, zcb_item_id, created_date, created_by, last_updated_by, las

5、t_updated_date, collect_userFROM hw_cpb_relationWHERE TO_CHAR (created_date, yyyy/mm/dd hh24:mi:ss) = 2002/10/16 15:16:06 vSELECT ROWID, organization_id, wip_entity_name, cpb_bar_code, cpb_revision, cpb_item_id, zcb_bar_code, zcb_revision, zcb_item_id, created_date, created_by, last_updated_by, last

6、_updated_date, collect_userFROM hw_cpb_relationWHERE created_date =to_date(:b1, yyyy/mm/dd hh24:mi:ss) v在SQL語句的Where條件的字段上加上函數(shù)，導致索引不可用或效率低索引的創(chuàng)建v經(jīng)常在哪些字段做連接或加過濾條件v數(shù)據(jù)是否經(jīng)常做updatev值的重復頻率是否非常高v是否幾個字段一起經(jīng)常用來做組合查詢索引的建立原則v對于值不多但記錄非常多，且值分布比較均勻的字段，基于此字段的索引對查詢的優(yōu)化沒有作用，同時帶來Insert操作時索引的維護開銷；v對于狀態(tài)類字段，值不多，如果記錄多，但這些記錄

7、在狀態(tài)中的分布極不均勻，可以建立索引，索引的條件帶常量才可用到索引。索引的使用v不應(yīng)當用于表上10以上的數(shù)據(jù)。v很小的表一兩次IO就可以對其掃描也沒有必要使用索引v組合索引可以壓縮以減少IO，但會增加CPU的消耗v反序索引的使用vBitmap索引的使用v不要在表上建太多索引編寫sql的一些技巧v使用hints，讓sql按照希望的方式來執(zhí)行v不使用某些特定的索引v不要頻繁commitv業(yè)務(wù)規(guī)則定義在數(shù)據(jù)庫底層的效率高v外鍵應(yīng)當被索引v存儲過程要比外部過程效率高v盡可能使用綁定變量來減少HARD PARSEv盡可能重用游標來減少SOFT PARSE為什么索引沒有被使用v查詢條件沒有包含組合索引的主

8、要邊界v索引不包含空的條目v查詢條件對索引列使用了函數(shù)v低效的索引vCBO的統(tǒng)計信息過于陳舊DB link 的優(yōu)化(1)驅(qū)動表的選擇：對于有一個DB Link的語句，選取遠程表作為驅(qū)動表select COMPANY.Namefrom COMPANY, SALESREMOTE1where COMPANY.Company_ID = SALES.Company_IDand SALES.Period_ID =3and SALES.Sales_Total1000;vNESTED LOOPSREMOTE (TABLE ACCESS FULL SALESREMOTE1)TABLE ACCESS BY ROW

9、ID COMPANYvINDEX UNIQUE SCAN COMPANY_PKDB link 的優(yōu)化(2)多個DB Link，需要返回大量的記錄，采用Merge可能比Nest Loop快select /*+ USE_MERGE(COMPANY,SALES) */COMPANY.Namefrom COMPANYREMOTE1, SALESwhere COMPANY.Company_ID = SALES.Company_IDand SALES.Period_ID =3and SALES.Sales_Total1000;vMERGE JOINSORT JOINvTABLE ACCESS FULL S

10、ALESSORT JOINvREMOTE (TABLE ACCESS FULL COMPANYREMOTE1)DB link 的優(yōu)化(3)在同一SQL語句中對同一個數(shù)據(jù)庫的多個遠程表進行連接，有如下情況：v1、如果這個查詢中既有本地表，又有多個遠程表，如果查詢條件是基于運程表的過濾，可以考慮在遠端數(shù)據(jù)庫中建立一個View，這個View基于參與操作的表，然后在本地建立一個同義詞對應(yīng)這個運端的View。這樣可以大大減少網(wǎng)絡(luò)的IO。View操作產(chǎn)生的結(jié)果（不多）與本地表進行連接，加快速度。v如果所有的表是遠端表，這樣做的效果不明顯，因為語句都是在遠端執(zhí)行，最終的結(jié)果才返回到本地。存在性檢驗的優(yōu)化v適用場合：對于利用In , Not In的語句，一般來說都可以用Exist, Not Exist來代替，用哪一個，決定于哪一個的執(zhí)行效率高，IO小；這兩種語法的互相代替可以減少全表掃描和操作決數(shù)，有效提高處理時間。In - Exists執(zhí)行計劃大表操作優(yōu)化問題防止沒有效果的索引用到。DB Buffer對索引取到的數(shù)據(jù)和全表掃描取到的數(shù)據(jù)內(nèi)存使用策略不同。創(chuàng)建分區(qū)及分區(qū)索引，在SQL語句中