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

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

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