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

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

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

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

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

簡單計算后有以下結(jié)果：

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

這張表一共有1000個數(shù)據(jù)塊-1000塊分析：通過索引獲?。玻埃埃埃靶袛?shù)據(jù)＝約２００００個tableaccessbyrowid＝處理２００００個塊；此表僅占用了１０００個塊。結(jié)論：通過全表掃描方式代價更低，速度更快。全表掃描比索引掃描效率更高？〔續(xù)〕112024/6/3重建UNDO表空間方法1.查詢undo表空間大小，及系統(tǒng)可用空間；2.創(chuàng)立一個新undo表空間；3.將默認(rèn)的undo表空間設(shè)置調(diào)整到UNDO_TBS1；4.刪除原有的undo表空間；5.最后確認(rèn)undo表空間的設(shè)置情況；6.使用重新啟動數(shù)據(jù)庫的方法釋放空間。解決UNDO表空間過大故障122024/6/3應(yīng)使用變量綁定實現(xiàn)SQL語句共享，防止使用硬編碼。SQL語句能夠被共享的三個必要條件：SQL寫法需完全一樣〔大小寫及空格數(shù)量都要一致〕SQL引用的的對象必須一樣〔不同用戶下的〕綁定變量名字必須一樣使用綁定變量132024/6/3SELECTemp_nameFROMempWHEREemp_no=:B1;//Bindvalue:123SELECT子句中防止使用“*”原因一：Oracle會將“*”依次轉(zhuǎn)換成所有的列名，需要查詢Oracle數(shù)據(jù)字典完成上面的轉(zhuǎn)換，因此效率極其低下原因二：不利于程序擴(kuò)展原那么：SQL中明確指定所需的字段防止使用“*”142024/6/3功能需求：得到7369和7844兩位員工的根本信息比較一下，哪個效率更高？減少訪問表次數(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)化器無法使用索引，而會以全表掃描方式來獲取數(shù)據(jù)例一：不當(dāng)?shù)乃阈g(shù)計算例二：隱式轉(zhuǎn)換〔EMPNO是NUMBER類型〕應(yīng)將SQL語句中的函數(shù)、計算表達(dá)式等放置在等號右邊無法防止時可考慮FBI函數(shù)索引〔Function-BasedIndex〕或物化視圖防止索引列計算162024/6/3SELECT…FROMDEPTWHERESAL*12>25000;SELECT…FROMDEPTWHERESAL>25000/12;SELECT...FROMEMPWHEREEMPNO='123'/SELECT...FROMEMPWHEREEMPNO=TO_NUMBER('123')/注意：Oracle的索引不記錄NULL值因此，WHERE子句中使用ISNULL和ISNOTNULL不會使用到索引轉(zhuǎn)換一例ISNULL和ISNOTNULL172024/6/3SELECT...FROMEMPWHEREEMPNOISNOTNULL;SELECT...FROMEMPWHEREEMPNO>=0;LIKE‘%***’、LIKE‘%***%’及NOTLIKE語句無法使用到索引，盡量不使用LIKE語句，不可防止時，可使用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會觸發(fā)RedoLog的物理寫Oracle數(shù)據(jù)庫頻繁的COMMIT會嚴(yán)重影響數(shù)據(jù)庫性能故障：ORA-00257:archivererror.ConnectInternalonly,untillfreedCOMMIT逐條？批量？192024/6/3DECLAREBEGINFORcurIN(SELECT*FROMt_old)LOOPINSERTINTOtVALUEScur;COMMIT;ENDLOOP;END;/Elapsed:00:03:12.77插入88萬條記錄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萬條記錄減少到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)化后：插入效率由原來的3分鐘縮短到了現(xiàn)在不到10秒！SQL優(yōu)化實例--優(yōu)化前212024/6/3selectlist_nofromTRAN_LOG_LISTwheremark='1'andlist_nonotin(selectlist_nofromTRAN_LOG_HEADwhereoper_name='000');其中：TRAN_LOG_LIST表中20萬記錄

TRAN_LOG_HEAD表中3萬條記錄優(yōu)化前：1分鐘30秒SQL優(yōu)化實例--優(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萬記錄TRAN_LOG_HEAD表中3萬條記錄優(yōu)化措施：利用索引優(yōu)化后：20秒同時INSERT多表根據(jù)不同的條件，用一條SQL實現(xiàn)把組合的數(shù)據(jù)插入到多個表。如果不提供WHEN條件，就插入所有的表Scenario:父子表同時插入數(shù)據(jù)；操作成功的同時寫日志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(