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

如何寫出優(yōu)質(zhì)高效的數(shù)據(jù)庫程序基本原則簡單的是最有力的；2/8原則；性能優(yōu)化是動力是基于對美的追求?；炯s定SQL語句書寫的約定，如關鍵字要大寫,函數(shù)，對象名，變量要小寫，一個關鍵字一行，每行以關鍵字右對齊，等號左右各空一位等，各個部分僅空一格；在SQL語句中對于值非常多的列條件要用變量綁定，不要用常量；對于值少記錄多，且分布不均勻，如果要用上該列的索引，就要用常量。在SQL語句中盡量不要用左值函數(shù)；查詢條件的順序要盡量能夠利用已有索引，如果不能利用現(xiàn)有的索引而需要新建綜合索引，索引列的順序、查詢條件的順序要和表中的列順序一致。如果能夠確保兩個輸出值集沒有重復值，UnionAll比Union有效率。一些建議某些復雜和耗時的SQL語句可以考慮采用幾個語句來完成。對使用外連接的語句，可以嘗試用一些組合語句來代替。如果能夠用行操作SQL語句完成，不要用集操作SQL(如count,Distinct,minus,intersect)語句完成。問題1SELECTBOARD_BARCODEFROMSMT_BOARD_WORK_RECbwr,SMT_DEP_TASK_INFOdtiWHEREBOARD_BARCODE='025767102A000842'ANDdti.Second_Series='標準'andbwr.Task_No=dti.Task_NoANDPROC_ORDER=1;SELECTBOARD_BARCODEFROMSMT_BOARD_WORK_RECbwr,SMT_DEP_TASK_INFOdtiWHEREBOARD_BARCODE=:b1ANDdti.Second_Series='標準'andbwr.Task_No=dti.Task_NoANDPROC_ORDER=1;沒有用變量綁定，導致大量的Parse，占用CPU時間和內(nèi)存。效果下面是使用文字變量和綁定變量通過Spotlight進行對比的觀察結(jié)果。不良代碼為執(zhí)行1500行操作，將同樣的SQL解釋了500次，對應的在內(nèi)存中占用500倍的空間，帶來嚴重的latch爭用。

。在使用綁定變量的情況下，留意對應SQL的Loads和Exections列值，運行次數(shù)逐漸增長為500、1000、1500直到2000。而變化時，Loads值始終是1，說明sharedpool共享性好，同時latch爭用也會比較少。問題2SELECT

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_updated_date,

collect_user

FROMhw_cpb_relation

WHERETO_CHAR(created_date,'yyyy/mm/ddhh24:mi:ss')

>=

'2002/10/1615:16:06'SELECT

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_updated_date,

collect_user

FROMhw_cpb_relation

WHEREcreated_date>=to_date(:b1,'yyyy/mm/ddhh24:mi:ss’)

在SQL語句的Where條件的字段上加上函數(shù)，導致索引不可用或效率低索引的創(chuàng)建經(jīng)常在哪些字段做連接或加過濾條件數(shù)據(jù)是否經(jīng)常做update值的重復頻率是否非常高是否幾個字段一起經(jīng)常用來做組合查詢索引的建立原則對于值不多但記錄非常多，且值分布比較均勻的字段，基于此字段的索引對查詢的優(yōu)化沒有作用，同時帶來Insert操作時索引的維護開銷；對于狀態(tài)類字段，值不多，如果記錄多，但這些記錄在狀態(tài)中的分布極不均勻，可以建立索引，索引的條件帶常量才可用到索引。索引的使用不應當用于表上10％以上的數(shù)據(jù)。很小的表一兩次IO就可以對其掃描也沒有必要使用索引組合索引可以壓縮以減少IO，但會增加CPU的消耗反序索引的使用Bitmap索引的使用不要在表上建太多索引編寫sql的一些技巧使用hints，讓sql按照希望的方式來執(zhí)行不使用某些特定的索引不要頻繁commit業(yè)務規(guī)則定義在數(shù)據(jù)庫底層的效率高外鍵應當被索引存儲過程要比外部過程效率高盡可能使用綁定變量來減少HARDPARSE盡可能重用游標來減少SOFTPARSE為什么索引沒有被使用查詢條件沒有包含組合索引的主要邊界索引不包含空的條目查詢條件對索引列使用了函數(shù)低效的索引CBO的統(tǒng)計信息過于陳舊DBlink的優(yōu)化(1)驅(qū)動表的選擇：對于有一個DBLink的語句，選取遠程表作為驅(qū)動表selectCOMPANY.NamefromCOMPANY,SALES@REMOTE1whereCOMPANY.Company_ID=SALES.Company_IDandSALES.Period_ID=3andSALES.Sales_Total>1000;NESTEDLOOPSREMOTE(TABLEACCESSFULLSALES@REMOTE1)TABLEACCESSBYROWIDCOMPANYINDEXUNIQUESCANCOMPANY_PKDBlink的優(yōu)化(2)多個DBLink，需要返回大量的記錄，采用Merge可能比NestLoop快select/*+USE_MERGE(COMPANY,SALES)*/COMPANY.NamefromCOMPANY@REMOTE1,SALESwhereCOMPANY.Company_ID=SALES.Company_IDandSALES.Period_ID=3andSALES.Sales_Total>1000;MERGEJOINSORTJOINTABLEACCESSFULLSALESSORTJOINREMOTE(TABLEACCESSFULLCOMPANY@REMOTE1)DBlink的優(yōu)化(3)在同一SQL語句中對同一個數(shù)據(jù)庫的多個遠程表進行連接，有如下情況：1、如果這個查詢中既有本地表，又有多個遠程表，如果查詢條件是基于運程表的過濾，可以考慮在遠端數(shù)據(jù)庫中建立一個View，這個View基于參與操作的表，然后在本地建立一個同義詞對應這個運端的View。這樣可以大大減少網(wǎng)絡的IO。View操作產(chǎn)生的結(jié)果（不多）與本地表進行連接，加快速度。如果所有的表是遠端表，這樣做的效果不明顯，因為語句都是在遠端執(zhí)行，最終的結(jié)果才返回到本地。存在性檢驗的優(yōu)化適用場合：對于利用In,NotIn的語句，一般來說都可以用Exist,NotExist來代替，用哪一個，決定于哪一個的執(zhí)行效率高，IO??；這兩種語法的互相代替可以減少全表掃描和操作決數(shù)，有效提高處理時間。In->Exists執(zhí)行計劃大表操作優(yōu)化問題防止沒有效果的索引用到。DBBuffer對索引取到的數(shù)據(jù)和全表掃描取到的數(shù)據(jù)內(nèi)存使用策略不同。創(chuàng)建分區(qū)及分區(qū)索引，在S