Oracle優(yōu)化案例分享

Oracle優(yōu)化案例分享李俊逸(SAKYA)QQ:40259908使用反向索引減少索引熱點(diǎn)塊索引壓縮技術(shù)及優(yōu)缺點(diǎn)總結(jié)位圖索引的鎖代價(jià)全表掃描比索引掃描效率更高？解決UNDO表空間過(guò)大故障使用綁定變量防止使用“*”減少訪問(wèn)表次數(shù)防止索引列計(jì)算ISNULL和ISNOTNULLLIKEvsINDEXCOMMIT逐條？批量？SQL優(yōu)化實(shí)例Oracle案例分享22024/6/3反向索引概念將正常的鍵值頭尾調(diào)換后再進(jìn)行存儲(chǔ)反向索引應(yīng)用場(chǎng)合索引塊成為熱點(diǎn)塊RAC環(huán)境中反向索引優(yōu)缺點(diǎn)優(yōu)點(diǎn)：減少索引葉子塊的爭(zhēng)用缺點(diǎn)：不適合范圍掃描〔<,>,betweenand〕使用反向索引減少索引熱點(diǎn)塊32024/6/3反向索引創(chuàng)立及維護(hù)方法使用反向索引減少索引熱點(diǎn)塊〔續(xù)〕42024/6/3sec@ora10g>createtablet(xint);sec@ora10g>createindexidx_tont(x)reverse;sec@ora10g>colTABLE_NAMEfora10sec@ora10g>colINDEX_NAMEfora10sec@ora10g>selecttable_name,index_name,index_typefromuser_indexeswheretable_name='T';TABLE_NAMEINDEX_NAMEINDEX_TYPE-----------------------------------TIDX_TNORMAL/REVsec@ora10g>alterindexidx_trebuildnoreverse;Indexaltered.sec@ora10g>selecttable_name,index_name,index_typefromuser_indexeswheretable_name='T';TABLE_NAMEINDEX_NAMEINDEX_TYPE-----------------------------------TIDX_TNORMAL索引壓縮創(chuàng)立演示索引壓縮技術(shù)及優(yōu)缺點(diǎn)總結(jié)52024/6/3createtablet_compress_indexasselect*fromall_objects;createindexidx_t_compress_indexont(owner,object_type,object_name)compress2;analyzeindexidx_t_compress_indexvalidatestructure;selectheight,lf_blks,br_blks,btree_space,opt_cmpr_count,opt_cmpr_pctsavefromindex_stats;HEIGHTLF_BLKSBR_BLKSBTREE_SPACEOPT_CMPR_COUNTOPT_CMPR_PCTSAVE-------------------------------------------------------------246137566020索引壓縮系數(shù)確定方法實(shí)踐中反復(fù)測(cè)試，得出最正確的壓縮系數(shù)索引壓縮優(yōu)點(diǎn)減少索引占用的磁盤空間數(shù)據(jù)庫(kù)緩沖區(qū)緩存能存放更多的索引條目緩存命中率較高物理I/O較少索引壓縮技術(shù)及優(yōu)缺點(diǎn)總結(jié)〔續(xù)1〕62024/6/3索引壓縮缺點(diǎn)維護(hù)索引更耗時(shí)需要更多的計(jì)算檢索索引需要較長(zhǎng)的時(shí)間需要更多的CPU處理索引增加了塊競(jìng)爭(zhēng)DISK與CPU間的權(quán)衡使用場(chǎng)景當(dāng)聯(lián)合索引前幾列存在大量重復(fù)數(shù)據(jù)時(shí)使用索引壓縮技術(shù)及優(yōu)缺點(diǎn)總結(jié)〔續(xù)2〕72024/6/3位圖索引被存儲(chǔ)為壓縮的索引值不支持行級(jí)別的鎖定一個(gè)鍵指向多行〔成百上千〕“牽一發(fā)而動(dòng)全身”較之B*Tree索引優(yōu)點(diǎn)壓縮存放，占用磁盤空間小較之B*Tree索引缺點(diǎn)鎖代價(jià)昂貴嚴(yán)重影響更新和刪除效率位圖索引的鎖代價(jià)82024/6/3位圖索引使用原那么用于決策支持系統(tǒng)用于數(shù)據(jù)倉(cāng)庫(kù)絕大局部數(shù)據(jù)為靜態(tài)數(shù)據(jù)低cardinality列適用〔性別等〕位圖索引的鎖代價(jià)〔續(xù)〕92024/6/3考慮下面的場(chǎng)景思考一下，有何結(jié)論？全表掃描比索引掃描效率更高？102024/6/3假設(shè)一張表含有10萬(wàn)行數(shù)據(jù)-100000行

我們要讀取其中20%(2萬(wàn))行數(shù)據(jù)-20000行

表中每行數(shù)據(jù)大小80字節(jié)-80bytes

數(shù)據(jù)庫(kù)中的數(shù)據(jù)塊大小8K-8000bytes

簡(jiǎn)單計(jì)算后有以下結(jié)果：

每個(gè)數(shù)據(jù)塊包含100行數(shù)據(jù)-100行

這張表一共有1000個(gè)數(shù)據(jù)塊-1000塊分析：通過(guò)索引獲取２００００行數(shù)據(jù)＝約２００００個(gè)tableaccessbyrowid＝處理２００００個(gè)塊；此表僅占用了１０００個(gè)塊。結(jié)論：通過(guò)全表掃描方式代價(jià)更低，速度更快。全表掃描比索引掃描效率更高？〔續(xù)〕112024/6/3重建UNDO表空間方法1.查詢undo表空間大小，及系統(tǒng)可用空間；2.創(chuàng)立一個(gè)新undo表空間；3.將默認(rèn)的undo表空間設(shè)置調(diào)整到UNDO_TBS1；4.刪除原有的undo表空間；5.最后確認(rèn)undo表空間的設(shè)置情況；6.使用重新啟動(dòng)數(shù)據(jù)庫(kù)的方法釋放空間。解決UNDO表空間過(guò)大故障122024/6/3應(yīng)使用變量綁定實(shí)現(xiàn)SQL語(yǔ)句共享，防止使用硬編碼。SQL語(yǔ)句能夠被共享的三個(gè)必要條件：SQL寫(xiě)法需完全一樣〔大小寫(xiě)及空格數(shù)量都要一致〕SQL引用的的對(duì)象必須一樣〔不同用戶下的〕綁定變量名字必須一樣使用綁定變量132024/6/3SELECTemp_nameFROMempWHEREemp_no=:B1;//Bindvalue:123SELECT子句中防止使用“*”原因一：Oracle會(huì)將“*”依次轉(zhuǎn)換成所有的列名，需要查詢Oracle數(shù)據(jù)字典完成上面的轉(zhuǎn)換，因此效率極其低下原因二：不利于程序擴(kuò)展原那么：SQL中明確指定所需的字段防止使用“*”142024/6/3功能需求：得到7369和7844兩位員工的根本信息比較一下，哪個(gè)效率更高？減少訪問(wèn)表次數(shù)152024/6/3SELECTENAME,SAL,HIREDATEFROMEMPWHEREEMPNO=7369/SELECTENAME,SAL,HIREDATEFROMEMPWHEREEMPNO=7844/DECLARECURSORC1(E_NONUMBER)ISSELECTENAME,SAL,HIREDATEFROMEMPWHEREEMPNO=E_NO;BEGINOPENC1(7369);FETCHC1INTO...,...,...;OPENC1(7844);FETCHC1INTO...,...,...;CLOSEC1;END;/SELECTA.ENAME,A.SAL,A.HIREDATE,B.ENAME,B.SAL,B.HIREDATEFROMEMPA,EMPBWHEREA.EMPNO=7369ANDB.EMPNO=7844/WHERE子句中，如果索引列是函數(shù)的一局部，那么優(yōu)化器無(wú)法使用索引，而會(huì)以全表掃描方式來(lái)獲取數(shù)據(jù)例一：不當(dāng)?shù)乃阈g(shù)計(jì)算例二：隱式轉(zhuǎn)換〔EMPNO是NUMBER類型〕應(yīng)將SQL語(yǔ)句中的函數(shù)、計(jì)算表達(dá)式等放置在等號(hào)右邊無(wú)法防止時(shí)可考慮FBI函數(shù)索引〔Function-BasedIndex〕或物化視圖防止索引列計(jì)算162024/6/3SELECT…FROMDEPTWHERESAL*12>25000;SELECT…FROMDEPTWHERESAL>25000/12;SELECT...FROMEMPWHEREEMPNO='123'/SELECT...FROMEMPWHEREEMPNO=TO_NUMBER('123')/注意：Oracle的索引不記錄NULL值因此，WHERE子句中使用ISNULL和ISNOTNULL不會(huì)使用到索引轉(zhuǎn)換一例ISNULL和ISNOTNULL172024/6/3SELECT...FROMEMPWHEREEMPNOISNOTNULL;SELECT...FROMEMPWHEREEMPNO>=0;LIKE‘%***’、LIKE‘%***%’及NOTLIKE語(yǔ)句無(wú)法使用到索引，盡量不使用LIKE語(yǔ)句，不可防止時(shí)，可使用like’***%’LIKEvsINDEX182024/6/3sec@ora10g>select*fromtwherexlike'SEC%';------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1214|101K|51(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|T|1214|101K|51(0)|00:00:01||*2|INDEXRANGESCAN|I_T|1214||5(0)|00:00:01|------------------------------------------------------------------------------------sec@ora10g>select*fromtwherexlike'%SEC';--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||50103|4207K|2826(1)|00:00:34||*1|TABLEACCESSFULL|T|50103|4207K|2826(1)|00:00:34|--------------------------------------------------------------------------Oracle的COMMIT會(huì)觸發(fā)RedoLog的物理寫(xiě)Oracle數(shù)據(jù)庫(kù)頻繁的COMMIT會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)性能故障：ORA-00257:archivererror.ConnectInternalonly,untillfreedCOMMIT逐條？批量？192024/6/3DECLAREBEGINFORcurIN(SELECT*FROMt_old)LOOPINSERTINTOtVALUEScur;COMMIT;ENDLOOP;END;/Elapsed:00:03:12.77插入88萬(wàn)條記錄3分鐘以上！DECLAREv_countNUMBER;BEGINFORcurIN(SELECT*FROMt_old)LOOPINSERTINTOtVALUEScur;v_count:=v_count+1;IFv_count>=100THENCOMMIT;ENDIF;ENDLOOP;COMMIT;END;/Elapsed:00:01:27.69插入88萬(wàn)條記錄減少到1.5分鐘以內(nèi)！批量提交再提速——使用嵌套表(數(shù)組)特性COMMIT逐條？批量？〔續(xù)〕202024/6/3DECLARECURSORcurISSELECT*FROMt_old;

TYPErecISTABLEOFt_old%ROWTYPE;recsrec;BEGINOPENcur;WHILE(TRUE)LOOPFETCHcurBULKCOLLECTINTOrecsLIMIT100;FORALLiIN1..recs.COUNTINSERTINTOtVALUESrecs(i);COMMIT;EXITWHENcur%NOTFOUND;ENDLOOP;CLOSEcur;END;/Elapsed:00:00:09.75優(yōu)化后：插入效率由原來(lái)的3分鐘縮短到了現(xiàn)在不到10秒！SQL優(yōu)化實(shí)例--優(yōu)化前212024/6/3selectlist_nofromTRAN_LOG_LISTwheremark='1'andlist_nonotin(selectlist_nofromTRAN_LOG_HEADwhereoper_name='000');其中：TRAN_LOG_LIST表中20萬(wàn)記錄

TRAN_LOG_HEAD表中3萬(wàn)條記錄優(yōu)化前：1分鐘30秒SQL優(yōu)化實(shí)例--優(yōu)化后222024/6/3selecta.list_nofromTRAN_LOG_LISTawherenotexists(selectb.list_nofromTRAN_LOG_HEADbwherea.list_no=b.list_noandb.oper_name='0000')anda.mark='1';其中：TRAN_LOG_LIST表中20萬(wàn)記錄TRAN_LOG_HEAD表中3萬(wàn)條記錄優(yōu)化措施：利用索引優(yōu)化后：20秒同時(shí)INSERT多表根據(jù)不同的條件，用一條SQL實(shí)現(xiàn)把組合的數(shù)據(jù)插入到多個(gè)表。如果不提供WHEN條件，就插入所有的表Scenario:父子表同時(shí)插入數(shù)據(jù)；操作成功的同時(shí)寫(xiě)日志INSERTALL

WHEN(object_typeIN('TABLE'))

THENINTOt_1(OBJECT_NAME,OBJECT_TYPE)VALUES(OBJECT_NAME,OBJECT_TYPE)

WHEN(object_typeIN('SEQUENCE'))

THENINTOt_2(OBJECT_NAME,OBJECT_TYPE)VALUES(OBJECT_NAME,OBJECT_TYPE)

ELSEINTOt_3(OBJECT_NAME,OBJECT_TYPE)VALUES(OBJECT_NAME,OBJECT_TYPE)SELECTOBJECT_NAME,OBJECT_TYPEFROMuser_objects/關(guān)聯(lián)更新目標(biāo)：表T_REF的數(shù)據(jù)更新到T表中思考：什么情況下可以考慮MERGE注意：條件限制控制更新范圍UPDATEtSETy=(SELECTyFROMt_refWHEREt.x=t_ref.x)/UPDATEtSETy=(SELECTyFROMt_refWHEREt.x=t_ref.x)

WHEREt.xIN(SELECTt_ref.xFROMt_ref)/SQL>select*fromt;XY--------------------ASecoolerBSecoolerCSecoolerDSecoolerSQL>select*fromt_ref;XY--------------------AAndyBAndyCAndySQL>select*fromt;XY--------------------AAndyBAndyCAndyDSecooler關(guān)聯(lián)更新與插入并舉MERGE可以高效的完成需求存在即更新，不存在那么插入MERGEINTOt_mergeaUSING(SELECTcode,nameFROMt_using)bON(a.code=b.code)WHENMATCHEDTHENUPDATESET=WHENNOTMATCHEDTHENINSERT(code,name)VALUES(