PLSQL程序優(yōu)化和性能分析方法要點(diǎn)

前言目的性能測試是測試中比較重要的工作，性能測試應(yīng)分為壓力的測試和性能的測試，其中性能問題中絕大部分都是由于程序編寫的不合理、不規(guī)范造成的。本文檔說明了程序中常見的不優(yōu)化的腳本編寫，導(dǎo)致的性能問題，并且在也描述了怎樣去跟蹤和解決程序上的性能問題的方法。在最后一章里面描述了做一個(gè)白盒測試工具測試性能問題的設(shè)計(jì)思想。文檔說明本文檔只說明PLSQL編寫的優(yōu)化問題，不包括ORACLE本身的性能優(yōu)化（內(nèi)存SGA、系統(tǒng)參數(shù)、表空間等）、操作系統(tǒng)的性能問題和硬件的性能問題。對于PLSQL程序優(yōu)化方面的內(nèi)容有很多，本文檔列出在我們實(shí)際工作中一些常見的情況。本文檔難免有不正確的地方，也需要大家給予指正。本文檔舉例說明的問題語句不是實(shí)際程序中真正存在的，只是讓大家能看起來更容易理解，但這些語句也不代表在我們程序中其他部分語句不存在這些問題。舉例說明中的語句采用的是社保核心平臺的數(shù)據(jù)字典，在舉例描述中沒有標(biāo)明表名和字段名的含義，還需單獨(dú)參考。詞匯表詞匯名稱詞匯含義備注參考資料編號資料名稱作者日期出版單位1?ORACLESQ住能優(yōu)化系歹Q〉23PLSQL程序優(yōu)化原則導(dǎo)致性能問題的內(nèi)在原因?qū)е孪到y(tǒng)性能出現(xiàn)問題從系統(tǒng)底層分析也就是如下幾個(gè)原因：CPU占用率過高，資源爭用導(dǎo)致等待內(nèi)存使用率過高，內(nèi)存不足需要磁盤虛擬內(nèi)存IO占用率過高，磁盤訪問需要等待PLSQL優(yōu)化的核心思想PLSQL優(yōu)化實(shí)際上就是避免出現(xiàn)“導(dǎo)致性能問題的內(nèi)在原因"，實(shí)際上編寫程序，以及性能問題跟蹤應(yīng)該本著這個(gè)核心思想去考慮和解決問題。PLSQL程序占用CPU的情況系統(tǒng)解析SQL語句執(zhí)行，會消耗CPU的使用運(yùn)算（計(jì)算）會消耗CPU的使用PLSQL程序占用內(nèi)存的情況讀寫數(shù)據(jù)都需要訪問內(nèi)存內(nèi)存不足時(shí)，也會使用磁盤PLSQL程序增大IO的情況讀寫數(shù)據(jù)都需要訪問磁盤IO讀取的數(shù)據(jù)越多，IO就越大大家都知道CPU現(xiàn)在都很高，計(jì)算速度非常快；訪問內(nèi)存的速度也很快；但磁盤的訪問相對前兩個(gè)相比速度就差的非常大了，因此PLSQL性能優(yōu)化的重點(diǎn)也就是減少IO的瓶頸，換句話說就是盡量減少IO的訪問。性能的優(yōu)先級CPU->內(nèi)存->IO,影響性能的因素依次遞增。根據(jù)上面的分析，PLSQL優(yōu)化的核心思想為：.避免過多復(fù)雜的SQL腳本，減少系統(tǒng)的解析過程.避免過多的無用的計(jì)算，例如：死循環(huán).避免浪費(fèi)內(nèi)存空間沒有必要的SQL腳本，導(dǎo)致內(nèi)存不足.內(nèi)存中計(jì)算和訪問速度很快.盡可能的減少磁盤的訪問的數(shù)據(jù)量，該原則是PLSQL優(yōu)化中重要思想。.盡可能的減少磁盤的訪問的次數(shù)，該原則是PLSQL優(yōu)化中重要思想。下面的章節(jié)具體介紹常見影響性能的SQL語句情況。ORACLE優(yōu)化器ORACLE的優(yōu)化器：a.RULE（基于規(guī)則）b.COST（基于成本）c.CHOOSE（選擇性）設(shè)置缺省的優(yōu)化器，可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你當(dāng)汕在SQL句級或是會話（session跋對其進(jìn)行覆蓋.為了使用基于成本的優(yōu)化器（CBO,Cost-BasedOptimizer）,你必須經(jīng)常運(yùn)行analyze命令,以增加數(shù)據(jù)庫中的對象統(tǒng)計(jì)信息（objectstatistics）的準(zhǔn)確性.如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性（CHOOSE）,那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān).如果table已經(jīng)被analyze過，優(yōu)化器模式將自動成為CBO,反之,數(shù)據(jù)庫將采用RULE形式的優(yōu)化器.在缺省情況下，ORACLE采用CHOOSE優(yōu)化器，為了避免那些不必要的全表掃描（fulltablescan）,你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器.在oracle10g前默認(rèn)的優(yōu)化模式是CHOOSE,10g默認(rèn)是ALL_ROWS,我不建議大家去改動ORACLE的默認(rèn)優(yōu)化模式。PLSQL優(yōu)化主要說明了在SQL編寫上和PLSQL程序編寫上可以優(yōu)化的地方。選擇最有效率的表名順序只在基于規(guī)則的優(yōu)化器rule中有效，目前我們oracle選擇的優(yōu)化器基本都不選擇rule,因此該問題基本不會出現(xiàn)，但為了安全和規(guī)范起見，建議編程習(xí)慣采用該規(guī)則。ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表（基礎(chǔ)表drivingtable）將被最先處理.在FROM子句中包含多個(gè)表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí)，會運(yùn)用排序及合并的方式連接它們.首先，掃描第一個(gè)表（FROM子句中最后的那個(gè)表）并對記錄進(jìn)行派序，然后掃描第二個(gè)表（FROM子句中最后第二個(gè)表，最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并^例如：表ac01有16,384條記錄表ab01有1條記錄選才iab01作為基礎(chǔ)表（好的方法）selectcount（*）fromac01,ab01執(zhí)行時(shí)間0.96秒選才iac01作為基礎(chǔ)表（不好的方法）selectcount(*)fromab01,ac01執(zhí)行時(shí)間26.09秒WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其他WHERE條件之前例如：(低效)SELECTab01.aab001,ab02.aab051FROMab01,ab02WHEREab02.aae140='31'ANDab01.aab001=ab02.aab001;(高效)SELECTab01.aab001,ab02.aab051FROMab01,ab02WHEREab01.aab001=ab02.aab001ANDab02.aae140='31'；SELECT子句中避免使用'*'當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí)，使用動態(tài)SQL列引用‘*'是一個(gè)方便的方法.不幸的是，這是一個(gè)非常低效的方法.實(shí)際上，ORACLE在解析的過程中，會將'*'依次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時(shí)間。用EXISTS替代IN實(shí)際情況看，使用exists替換in效果不是很明顯，基本一樣。在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對另一個(gè)表進(jìn)行聯(lián)接.在這種情況下，使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.低效：SELECT*FROMac01Whereaac001in(selectaac001fromac02whereaab001=str_aab001andaae140='31');或SELECT*FROMac01Whereaac001in(selectdistinctaac001fromac02whereaab001=str_aab001andaae140='31');注意使用distinct也會影響速度局效:SELECT*FROMac01Whereexists(select1fromac02whereaac001=ac01.aac001andaab001=str_aab001andaae140='31');in的常量列表是優(yōu)化的(例如：aab019in(20','30')),不用exists替換；in列表相當(dāng)于or用NOTEXISTS替代NOTINOracle在10g之前版本notin都是最低效的語句，雖然在10g上notin做到了一些改進(jìn)，但仍然還是存在一些問題，因此我們一定要使用notexists來替代notin的寫法。在子查詢中,NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并.無論在哪種情況下,NOTIN都是最低效的(因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個(gè)全表遍歷).為了避免使用NOTIN，我們可以把它改寫成NOTEXISTS.例如：SELECT*FROMac01WHEREaab001NOTIN(SELECTaab001fromab01whereaab020='100');為了提高效率.改寫為：SELECT*FROMac01WHEREnotexists(SELECT1fromab01whereaab001=ac01.aab001andaab020='100');用表連接替換EXISTS在子查詢的表和主表查詢是多對一的情況，一般采用表連接的方式比EXISTS更有效率。例如：低效：SELECTac01.*FROMac01Whereexists(select1fromac02whereaac001=ac01.aac001andaab001=ac01.aab001andaae140='31'andaae041='200801');局效:SELECTac01.*FROMac02,ac01Whereac02.aac001=ac01.aac001andac02.aab001=ac01.aab001andac02.aae140='31'andaae041='200801';到底exists和表關(guān)聯(lián)哪種效率高，其實(shí)是根據(jù)兩個(gè)表之間的數(shù)據(jù)量差別大小是有關(guān)的，如果差別不大實(shí)際上速度基本差不多。用EXISTS替換DISTINCT當(dāng)提交一個(gè)包含一對多表信息（比如個(gè)人基本信息表和個(gè)人參保信息表）的查詢時(shí)，避免在SELECT子句中使用DISTINCT.一般可以考慮用EXISTS替換例如：低效：selectdistinctac01.aac001fromac02,ac01whereac02.aac001=ac01.aac001andac02.aae140='31'andac01.aab001='100100,;局效:selectac01.aac001fromac01whereexists（select1fromac02whereaac001=ac01.aac001andaae140='31'）andac01.aab001='100100,;EXISTS使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢白^條件一旦滿足后，立刻返回結(jié)果。因此如果不是特別研究和追求速度的話（例如：數(shù)據(jù)轉(zhuǎn)換），查詢一個(gè)表的數(shù)據(jù)需要關(guān)聯(lián)其他表的這種情況查詢，建議采用EXISTS的方式。減少對表的查詢該問題是我們編程中出現(xiàn)過的問題，請大家一定注意，并且該類問題優(yōu)化可以帶來較大性能的提升。例如：低效cursorcur_kc24_mzisSelectakc260fromkc24whereakb020=str_akb020andaka130='11';cursorcur_kc24_zyisSelectakc260fromkc24whereakb020=str_akb020andaka130='21';forrec_mzincur_kc24_mzloop門診處理…..endloop;forrec_mzincur_kc24_zyloop住院處理???..endloop;高效cursorcurkc24isSelectakc260,aka130fromkc24whereakb020=str_akb020andaka130in('11','21');forrec_kc24incur_kc24loopifrec_kc24.aka130='11'then門診處理…..endif;ifrec_kc24.aka130='21'then住院處理???..endif;endloop;2次磁盤，這樣速度差高效的做法使用同樣的條件（或者說是索引）只訪問一次磁盤，低效的做法訪問了別將近22次磁盤，這樣速度差避免循環(huán)（游標(biāo)）里面嵌查詢游標(biāo)里面不能嵌入查詢（或者再嵌游標(biāo)）,其實(shí)也不能有updatedelete等語句，只能有insert語句。但在實(shí)際的編程情況下是不可能完全避免的，但我們一定要盡量避免。該類問題也是我們程序中出現(xiàn)過的問題，該類問題也可以大大提升程序效率，請大家一定注意。例如:低效：Cursorcur_ac04isSelectaac001,akc010Fromac04Whereaab001=prm_aab001;Forrec_ac04incur_ac04loopSelectaac008Intostr_aac008fromac01whereaac001=rec_ac04.aac001;ifstr_aac008='1'thenn_jfje:=rec_ac04.akc010*0.08;endif;ifstr_aac008='2'thenn_jfje:=rec_ac04.akc010*0.1;endif;Endloop;局效:Cursorcur_ac04isSelectac01.aac001,ac04.akc010,ac01.aac008Fromac04,ac01Whereac04.aac001=ac01.aac001andaab001=prm_aab001;Forrec_ac04incur_ac04loopifrec.aac008='1'thenn_jfje:=rec_ac04.akc010*0.08;endif;ifrec.aac008='2'thenn_jfje:=rec_ac04.akc010*0.1;endif;endloop;優(yōu)化的方法是盡量把游標(biāo)循環(huán)中的查詢語句放到游標(biāo)查詢中一起查詢出來，這樣相當(dāng)于只訪問了1次磁盤讀到內(nèi)存；如果放到游標(biāo)中的話，假如游標(biāo)有100萬數(shù)據(jù)量，那么程序需要100萬次磁盤，可以想象浪費(fèi)了多少IO的訪問。如果在程序編寫上沒有辦法避免游標(biāo)中有查詢語句的話（一般情況是可以避免的），那么也要保證游標(biāo)中的查詢使用的索引（即查詢速度非?？欤纾河螛?biāo)100萬數(shù)據(jù)量，游標(biāo)中的查詢語句執(zhí)行需要0.02秒,從這個(gè)速度上來說是很快的，但總體上看100萬*0.02秒=2萬秒=5小時(shí)33分鐘，如果寫一個(gè)不夠優(yōu)化的語句需要1秒，那么需要幾天能執(zhí)行完呢？盡量用unionall替換unionUnion會去掉重復(fù)的記錄，會有排序的動作，會浪費(fèi)時(shí)間。因此在沒有重復(fù)記錄的情況下或可以允許有重復(fù)記錄的話，要盡量采用unionall來關(guān)聯(lián)。使用DECODE函數(shù)來減少處理時(shí)間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表^例如：(低效)selectcount(1)fromac01whereaab001='100001'andaac008='1';selectcount(1)fromac01whereaab001='100001'andaac008='2';(低效)Selectcount(1),aac008Fromac01Whereaab001='100001'andaac008in(T,'2')groupbyaac008;(iWj效)selectcount(decode(aac008,’1','1[null))zz,count(decode(aac008,’2','11null))txfromac01whereaab001='100001';特別說明：groupby和orderby都會影響性能，編程時(shí)盡量避免沒有必要的分組和排序，或者通過其他的有效的編程辦法去替換，比如上面的處理辦法。groupby優(yōu)化Groupby需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，并且分組復(fù)雜，這樣的查詢語句在性能上是有問題的。盡量避免使用分組或者采用上面的一節(jié)的辦法去代替。采用groupby的也一定要進(jìn)行優(yōu)化。例如：低效selectac04.aac001,ac01.aac002,ac01.aac003,sum(aac040),ac01.aab001fromac04,ac01whereac04.aac001=ac01.aac001andac01.aab001='1000000370,groupbyac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;局效:selectac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001from(selectaac001,sum(aac040)gzzefromac04groupbyaac001)ac04,ac01whereac04.aac001=ac01.aac001andaab001='1000000370';盡量避免用orderbyOrderby需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，排序的時(shí)間就很長。但我們也不能避免不使用，這樣大家一定注意一點(diǎn)的是如果使用orderby那么排序的列表必須符合索引，這樣在速度上會得到很大的提升。用Where子句替換HAVING子句避免使用HAVING子句，HAVING只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾.這個(gè)處理需要排序總計(jì)等操作.如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷.例如：低效：SELECTaac008,count(1)FROMac01GROUPBYaac008HAVINGaac008in(1','2');局效SELECTaac008,count(1)FROMac01Whereaac008in(1','2')GROUPBYaac008;HAVING中的條件一般用于對一些集合函數(shù)的比較，如COUNT()等等.除此而外，一般的條件應(yīng)該寫在WHERE子句中使用表的別名(Alias)當(dāng)在SQL語句中連接多個(gè)表時(shí)，請使用表的別名并把別名前綴于每個(gè)Column上.這樣一來，就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤.刪除重復(fù)記錄一般數(shù)據(jù)轉(zhuǎn)換的程序經(jīng)常會使用到該方法。最高效的刪除重復(fù)記錄方法(因?yàn)槭褂昧薘OWID)DELETEFROMac01aWHEREa.rowid>(SELECTMIN(b.rowid)FROMac01bWHEREa.aac002=b.aac002anda.aac003=b.aac003);COMMIT使用數(shù)據(jù)轉(zhuǎn)換的程序需要關(guān)注這一點(diǎn)。Commit執(zhí)行也是有時(shí)間的，不過時(shí)間特別短，但提交頻率特別大，必然也會浪費(fèi)時(shí)間。commit可以釋放資源，在大量數(shù)據(jù)更新時(shí)，必須及時(shí)提交。a.回滾段上用于恢復(fù)數(shù)據(jù)的信息.b.被程序語句獲得的鎖redologbuffer中的空間ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)例如：Cur_ac20有5000萬數(shù)據(jù)n_count:=0;Forarecincur_ac20loopInsertintoac20n_count:=n_count+1;Ifn_count==100000then--10萬一提交commit;n_count:=0;Endif;Endloop;Commit;如果1條一提交，需要提交5000萬必然浪費(fèi)時(shí)間；如果整體提交，資源不能釋放，性能必須下降。在實(shí)際編程時(shí)，應(yīng)注意提交的次數(shù)和提交的數(shù)據(jù)量的平衡關(guān)系。減少多表關(guān)聯(lián)表關(guān)聯(lián)的越多，查詢速度就越慢，盡量減少多個(gè)表的關(guān)聯(lián)，建議表關(guān)聯(lián)不要超過3個(gè)(子查詢也屬于表關(guān)聯(lián))。數(shù)據(jù)轉(zhuǎn)換上會存在大數(shù)據(jù)量表的關(guān)聯(lián)，關(guān)聯(lián)多了會影響索引的效率，可以采用建立臨時(shí)表的辦法，有時(shí)更能提高速度。批量數(shù)據(jù)插入數(shù)據(jù)轉(zhuǎn)換時(shí)或者大業(yè)務(wù)數(shù)據(jù)插入時(shí)，有以下幾種辦法進(jìn)行數(shù)據(jù)插入(不包括imp、impdp和sqlloader)Insertinto…select方式將查詢的結(jié)果一次插入到目標(biāo)表中。例如：Insertintoac01_bakselect*fromac01;由于是一次查詢一次插入，并且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。但查詢插入的數(shù)據(jù)量過大必然會占用更多的內(nèi)存和undo表空間，只能在插入完成后提交，這樣資源不能釋放，會導(dǎo)致回滾表空間不足和快照過舊的問題，另外一旦失敗需要全部回滾。因此建議小數(shù)據(jù)量（例如：300萬以下）的導(dǎo)入采用該種方式。Insert/*+append*/into…select方式該種方式同上種方式，不過由于有append的提示，這種語句不走回滾段直接插入數(shù)據(jù)文件，速度非常快。注意系統(tǒng)開發(fā)編程不能使用該種方式，數(shù)據(jù)轉(zhuǎn)換可以靈活使用。Cursor方式定義游標(biāo)，然后逐行進(jìn)行插入，然后定量提交。例如：Cusorcur_ac20isSelect*fromac20;n_count:=0;Forrec_ac20incur_ac20loopInsertintoac20_bak(aac001,…….)Values(rec_ac20.aac001,???.)；Ifn_count:==100000thenCommit;n_count:=0;Endif;Endloop;批綁定的方式通過游標(biāo)查詢將數(shù)據(jù)逐行寫到數(shù)組里（實(shí)際上就是內(nèi)存），然后通過批綁定的語句forall…in…insertinto???values…；將內(nèi)存的數(shù)據(jù)一次寫入到數(shù)據(jù)文件中。相比cursor的方式減少了對io的訪問次數(shù)，提高了速度，但注意內(nèi)存別溢出了。索引使用優(yōu)化80%,在程序優(yōu)化上索引問題是需要我們特別關(guān)注80%,在程序優(yōu)化上索引問題是需要我們特別關(guān)注避免在索引列上使用函數(shù)或運(yùn)算這個(gè)問題是在我們實(shí)際編程中出現(xiàn)過的，請大家一定注意。在索引列上使用函數(shù)或運(yùn)算，查詢條件都不會使用索引。例如：不使用索引Select*fromka02whereaka060='10001000'andto_char(aae030,'yyyymm')='200801';使用索引Select*fromka02whereaka060='10001000'andaae030=to_date('200801','yyyymm');不使用索引Select*fromka02whereaka060='10001000'andaae031+1=sysdate;使用索引Select*fromac04whereaac001='10001000'andaae031=sysdate-1;如果一定要對使用函數(shù)的列啟用索引，ORACLE新的功能：基于函數(shù)的索引(Function-BasedIndex)CREATEINDEXIDX_KA02_AKA066ONKA02(UPPER(AKA066));/*建立基于函數(shù)的索引*/SELECT*FROMKA02WHEREUPPER(AKA066)=ASPL';而使用索弓I*/不是極特殊情況，建議不要使用。避免改變索引列的類型.索引列的條件如果類型不匹配，則不能使用索引。例如：不使用索引Select*fromac01whereaac001=10001000;使用索引Select*fromac01whereaac001='10001000';避免在索引列上使用NOT避免在索引列上使用NOT,NOT不會使查詢條件使用索引。對于！=這樣的判斷也是不能使用索引的，索引只能告訴你什么存在于表中，而不能告訴你什么不存在于表中例如：低效：(這里，不使用索引)select*Fromac02Wherenotaab019='10'；高效：(這里，使用了索引)select*Fromac02Whereaab019in('20','30');用>=替代>雖然效果不是特別明顯，但建議采用這種方式低效：SELECT*FROMab01WHEREaab019>10'局效:SELECT*FROMab01WHEREaab019>='20'兩者的區(qū)別在于，前者DBMS首先定位到aab019=10的記錄并且向前掃描到第一個(gè)aab019大于10的記錄，而后者DBMS將直接跳到第一個(gè)aab019等于10的記錄避免在索引列上使用ISNULL和ISNOTNULL對于索引列使用isnull或isnotnull不會使用上索引.因?yàn)榭罩挡淮嬖谟谒饕兄校訵HERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引.舉例：低效：(索引失效)select*fromab01whereaab019isnotnull;高效：(索引有效)select*fromab01whereaab019in(10',‘20','30');在實(shí)際開發(fā)中，對于這類的問題很難避免，如果不是特別影響速度或者要求速度的，可以忽略。帶通配符(%)的like語句%在常量前面索引就不會使用。例如：不使用索引Select*fromac01whereaac002like%210104';Select*fromac01whereaac002like%210104%';使用索引Select*fromac01whereaac002like210104%';總是使用索引的第一個(gè)列如果索引是建立在多個(gè)列上，只有在它的第一個(gè)列被where子句引用時(shí)，優(yōu)化器才會選擇使用該索引。例如：Ac02的復(fù)合索引：aacOOI、aae140、aae041Select*fromac02whereaae140='31'andaae041='200801';--不會使用索引Select*fromac02whereaac001='10001000';--可以使用索引如果不使用索引第一列基本上不會使用索引，使用索引要按照索引的順序使用，另外使用復(fù)合索引的列越多，查詢的速度就越快多個(gè)平等的索引當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí)，ORACLE會同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對它們的記錄進(jìn)行合并，檢索出僅對全部索引有效的記錄.在ORACLE選擇執(zhí)行路徑時(shí)，唯一性索引的等級高于非唯一性索引.然而這個(gè)規(guī)則只有當(dāng)WHERE子句中索引列和常量比較才有效,如果索引列和其他表的索引類相比較,這種子句在優(yōu)化器中的等級是非常低的.如果不同表中兩個(gè)相同等級的索引將被引用，F(xiàn)ROM子句中表的順序?qū)Q定哪個(gè)會被率先使用.FROM子句中最后的表的索引將有最高的優(yōu)先級.如果同一表中有兩個(gè)相同等級的索引被引用，oracle會分析最有效的索引去引用，其他的索引不會使用，如果這些相同等級的索引效果差不多，oracle可能會自動合并進(jìn)行使用。不明確的索引等級當(dāng)ORACLE無法判斷索引的等級高低差別，優(yōu)化器將只使用一個(gè)索引，它就是在WHERE子句中被列在最前面的.自動選擇索引如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性.在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引.使用提示（Hints）對于表白^訪問，可以使用兩種Hints.FULL和ROWIDFULLhint告訴ORACLE使用全表掃描的方式訪問指定表例如：SELECT/*+FULL(AC01)*/*FROMAC01WHEREAAC001=10001000';如果一個(gè)大表沒有被設(shè)定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留在SGA中,你就可以使用CACHEhint來告訴優(yōu)化器把數(shù)據(jù)保留在SGA中.通常CACHEhint和FULLhint一起使用.例如：SELECT/*+FULL(AC01)CACHE(AC01)*/*FROMAC01;ROWIDhint告訴ORACLE使用TABLEACCESSBYROWID的操作訪問表.采用TABLEACCESSBYROWID的方式特別是當(dāng)訪問大表的時(shí)候，使用這種方式，你需要知道ROIWD的值或者使用索引.索引hint告訴ORACLE使用基于索引的掃描方式.你不必說明具體的索引名稱例如：SELECT/*+index(IDX_AC01_AAC002)*/aac001FROMAC01WHEREaac002='2101111111111111111';在不使用hint的情況下，以上的查詢應(yīng)該也會使用索引，然而，如果該索引的重復(fù)值過多而你的優(yōu)化器是CBO,優(yōu)化器就可能忽略索引.在這種，f#況下，你可以用INDEXhint強(qiáng)制ORACLE使用該索引.ORACLEhints還包括ALL_ROWS,FIRST_ROWS,RULE,USE_NL,USE_MERGE,USE_HASH使用hint,表示我們對ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意，需要手工修改.這是一個(gè)很有技巧性的工作.除非特定的情況，例如：數(shù)據(jù)轉(zhuǎn)換，其他情況最好不用表上存在過舊的分析我們現(xiàn)在很多項(xiàng)目都存在性能問題，其中有很多種情況都是由于分析過舊導(dǎo)致ORACLE判斷索引級別和資源成本上出現(xiàn)問題，會導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。我個(gè)人覺得這是ORACLE不夠完善的地方。解決辦法：第一種辦法：刪除分析，停止oracle10g的自動分析，但不使用分析，oracle訪問數(shù)據(jù)的CPU消耗就過大。第二種辦法：重新分析，但過長時(shí)間后，索引是否會再次失效，沒有驗(yàn)證過。表上存在并行表上存在并行，ORACLE判斷索引級別和資源成本上出現(xiàn)問題，會導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。這個(gè)問題我不知道有什么好的處理辦法，從現(xiàn)場實(shí)際應(yīng)用速度角度比較，我還是選擇去掉并行，因?yàn)椴皇褂盟饕M(jìn)行全表掃描肯定是不能忍受的。關(guān)于索引建立索引的使用是肯定會大大提高查詢的速度，但索引其實(shí)也是一種數(shù)據(jù)，它也是存放的用戶類型的表空間下的，索引建立的越多越大，占用的空間也越大，從用戶的環(huán)境來說這也不是問題，但如果一個(gè)表有過多過大的查詢，必然會影響insert、delete和update索引列的速度，因?yàn)檫@些操作改變了整個(gè)表的索引順序，oracle需要進(jìn)行調(diào)整，這樣性能就下降了。因此我們一定要合理的建立好有效的索引，編程也要符合索引的規(guī)則，而不能是索引符合編程的規(guī)則。案例：某項(xiàng)目數(shù)據(jù)轉(zhuǎn)換，采用游標(biāo)循環(huán)insert的方式，總共2000萬的數(shù)據(jù)，總共用了4個(gè)小時(shí)，原因就是目標(biāo)表里面有很多索引。解決方法是先刪除索引再執(zhí)行轉(zhuǎn)換腳本，結(jié)果不用1小時(shí)就完成了，建立全部的索引不到半個(gè)小時(shí)。原因就是第一種方式每次insert都改變索引順序，共執(zhí)行改變2000萬次，而第二種方式整體上執(zhí)行索引順序就一次。PLSQL程序性能問題分析方法本章主要介紹怎樣找到出現(xiàn)性能問題PLSQL程序或語句的方法。性能問題分析出現(xiàn)性能問題，我先要從整體進(jìn)行分析，一般總體上會有幾種現(xiàn)象：整個(gè)系統(tǒng)運(yùn)行速度都慢在業(yè)務(wù)不忙的時(shí)候，所有模塊都慢只有在業(yè)務(wù)繁忙的時(shí)候，所有模塊都慢時(shí)快時(shí)慢個(gè)別業(yè)務(wù)模塊運(yùn)行速度慢在業(yè)務(wù)不忙的時(shí)候，該模塊就慢只有在業(yè)務(wù)繁忙的時(shí)候，該模塊才慢時(shí)快時(shí)慢一般導(dǎo)致系統(tǒng)性能慢的因素：硬件：客戶端、服務(wù)器CPU、內(nèi)存和存儲設(shè)備配置不符合應(yīng)用系統(tǒng)要求網(wǎng)絡(luò)：網(wǎng)速低、丟包操作系統(tǒng)參數(shù)設(shè)置：參數(shù)設(shè)置不合理受到其他軟件干擾：例如：防火墻、病毒W(wǎng)eblogic設(shè)置：參數(shù)設(shè)置不合理Oracle設(shè)置：內(nèi)存、表空間、redolog、系統(tǒng)參數(shù)設(shè)置不合理等在不同現(xiàn)象下，可能導(dǎo)致性能問題的因素：.一般來說，如果在不辦理業(yè)務(wù)的情況下，整個(gè)系統(tǒng)性能就慢的話，應(yīng)該和PLSQL程序優(yōu)化是沒有關(guān)系的?？赡艿囊蛩貫橛布?、網(wǎng)絡(luò)、操作系統(tǒng)、其他軟件干擾、ORACLE設(shè)置。.只有在業(yè)務(wù)繁忙的時(shí)候，整體系統(tǒng)性能都慢，有可能的因素有硬件、操作系統(tǒng)設(shè)置、WEBLOGIC設(shè)置、ORACLE設(shè)置、PLSQL程序。如果在sqlplus下做查詢都慢，那么就和weblogic沒有關(guān)系。.一般來說，如果在不辦理業(yè)務(wù)的情況下，個(gè)別業(yè)務(wù)模塊速度就慢的話，那么基本上就是PLSQL程序不優(yōu)化或未使用索引造成的。.只有在業(yè)務(wù)繁忙的時(shí)候，個(gè)別模塊慢，有可能的因素有硬件、操作系統(tǒng)設(shè)置、WEBLOGIC設(shè)置、ORACLE設(shè)置、PLSQL程序、鎖表。這里我們重點(diǎn)是說明PLSQL優(yōu)化、索引優(yōu)化的問題，其他例如：硬件、網(wǎng)絡(luò)、操作系統(tǒng)和oracle設(shè)置等因素我們不進(jìn)行說明。PLSQL優(yōu)化、索引不使用的問題產(chǎn)生的影響：.對于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個(gè)表時(shí)，它訪問磁盤的時(shí)間和占用的吞吐量是很高的，這就導(dǎo)致系統(tǒng)IO長時(shí)間處于忙的狀態(tài)，導(dǎo)致整個(gè)系統(tǒng)性能下降。.對于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個(gè)表時(shí)，其他的業(yè)務(wù)程序也訪問同一個(gè)表時(shí)，速度將大大下降。.如果是更新表操作時(shí)間長，還可能會導(dǎo)致鎖等待，導(dǎo)致會話堵塞，weblogic端也出現(xiàn)壓力問題，導(dǎo)致這個(gè)系統(tǒng)性能下降。我們一般根據(jù)這些現(xiàn)象、以及一些方法判斷，來初步分析產(chǎn)生性能問題的大致原因的范圍。不過對于這一點(diǎn)，還是比較困難的，因?yàn)楫a(chǎn)生問題的原因是多種的，并且還有一定的關(guān)聯(lián)。下面的章節(jié)介紹我們已經(jīng)斷定是PLSQL優(yōu)化、索引不使用的問題，我們通過什么方法來具體定位問題。3.2ExpainPlan分析索引使用在PL/SQLDeveloper等工具有一個(gè)ExpainPlan分析的功能，這個(gè)功能可以幫助我們分析SQL語句是否使用了索引、使用哪些索引和使用索引的效果。選擇explainplan的窗口ProgramWindowTestWindow5QL\MndowReportWindowCommandWindovjExplainPlanWindovtdiagramWindowViewMaberializedviewTableSequenceSynonymLibfaryDirectoryJob?曰u已QueuetableUserRoleProfileDatabaseliQk.在上面欄中輸入SQL語句，然后點(diǎn)擊工具欄上的EXECUTE執(zhí)行（或按F8）,就會在下面顯示Optmizergoal優(yōu)化器的默認(rèn)方式（也可手工選擇），以及下面的解釋計(jì)劃，從解釋計(jì)劃上能看到哪個(gè)條件語句使用了索引，哪個(gè)沒有使用；哪個(gè)表使用了索引，使用了哪個(gè)索引，哪些表是全表掃描的（TABLEACCESSFULL）

.分析內(nèi)容說明：COST:根據(jù)優(yōu)化程序的基于開銷的方法所估計(jì)出的操作開銷值對于使用基于規(guī)則方法的語句該列為空該列值沒有特定的測量單位它只是一個(gè)用于比較執(zhí)行規(guī)劃開銷大小的權(quán)重值Cardinality:根據(jù)基于開銷的方法對操作所訪問行數(shù)的估計(jì)值Bytes:根據(jù)基于開銷的方法對操作所訪問字節(jié)的估計(jì)通過設(shè)置，我們還能看到更多的信息，例如：CPU使用、時(shí)間等等全表掃描的(TABLEACCESSFULL)肯定是速度慢的，如果是大數(shù)據(jù)量的表，那么這個(gè)語句是絕對影響性能的。另外使用了索引也不一定性能就高，因?yàn)樗饕褂靡灿行实那闆r，下面列出索引常見的使用類型：INDEXUNIQUESCAN:唯一索引掃描，速度最快INDEXRANGESCAN:范圍索引掃描，使用這個(gè)索引時(shí)，就需要看COST、Cardinality>Bytes的大小了，如果特別大，有時(shí)候還可能速度低于全表掃描的速度。我們在知道語句有問題，或者我們對語句進(jìn)行優(yōu)化時(shí)，這個(gè)工具是非常有用的。3TOPSQL分析程序有性能問題的時(shí)候，我們是不知道哪些語句存在性能的問題，尤其你不是開發(fā)人員。幸好有一些工具可以幫助我們找到這些存在性能問題的語句。

Toad工具和ORACLE9I的企業(yè)管理控制臺工具可以捕獲到這些問題語句（oracle10g中em的功能不夠好）在TOAD和ORACLE9I工具中可以查詢到TOPSQL頂級SQL的內(nèi)容，通過CPU、IO吞吐量、占用時(shí)間等信息的排序，我們可以找到最影響系統(tǒng)性能的語句，通過分析我們可以看到這些語句的解釋計(jì)劃。根據(jù)解釋計(jì)劃，我們可以進(jìn)行語句的優(yōu)化，我們知道語句后，我們通過plsql的搜索功能就知道存在問題語句的程序了。這個(gè)辦法使用有個(gè)前提條件就是這些問題語句在系統(tǒng)上運(yùn)行過，并且沒有間隔過長的時(shí)間，因此最好是在實(shí)際出現(xiàn)性能問題的ORACLE上不斷的去監(jiān)控，才能捕獲到最全的問題語句。.ORACLE9i企業(yè)管理控制臺工具的topsqlEnlerpriselmaif；erCnn二帽工七.獨(dú)立ORACUEnlftrpisfiA^ajraaj已￥網(wǎng)期今」的括莊a>I尋？一但一警一^feOflCL-SysAS5YSDBA汽◎解回配置巧■」金話＜津日ORACUEnlftrpisfiA^ajraaj已￥網(wǎng)期今」的括莊a>I尋？一但一警一^feOflCL-SysAS5YSDBA汽◎解回配置巧■」金話＜津日a_）資熊快用若翎M野酎劃L史更誦計(jì)期調(diào)度S旗安全性存謖工作空間管理工作空間是一個(gè)或多個(gè)用戶可以共享的，用來對救據(jù)庫中的救據(jù)進(jìn)行更改的虛擬環(huán)境,二作空間管理涉及菅理可以供許多用戶共享的一個(gè)或多個(gè)工作空間。使用WorkspiceMlanager可執(zhí)行以下任務(wù)：?啟用和禁用表版本化.?創(chuàng)建，編孱和移去工作空間，?回退或刷新于工作空間.?查看兩個(gè)工作空間之間的差別或者工作空間中兩個(gè)保存點(diǎn)之間的差別.,查看并解決子工作空間和它的父工作空同之間的沖突.卜分配西琳稍用戶的WcirkspaizEManager權(quán)限.有關(guān)工作空間管理的詳綱信息語單U速成載學(xué)技鈕，.系統(tǒng)運(yùn)行中的所有TOPSQL頂層SQL：hssiBisiMIKAlDG^l^,L6B.131.Z53諄生00:00:06IE&><[IEhP審曲圜圓諄生00:00:06IE成L文本每汝我行的瞧型佳聯(lián)票！每汶執(zhí)行的黑沖區(qū)友朝s執(zhí)行數(shù)穌推哲即數(shù)歐沖區(qū)若琨數(shù)每一行的操噓荻聯(lián)SELECT...FROMAC01,ACOJVVHEREAC01AdCOOI=RC口4A*C001..02.607,198.0054133599J67SSELECTA6Q1.AAB001.AB01AA0OQ3,砥口1舶B004.ABDrAABO...■<n14J42.4IO5406737127?JSELECTFROMIC1DsACD1JC13,A0D1WHEREIC13/ACD01=IC1...67.403,557.005337177B81(SELECT.FROMSYS.EKU9STMJORDERBYEYNTIHE65.113?07?,5695的2770751■SELECT..FROMSYSEXU8VE網(wǎng)ORDERBYVLEVEL.VOBJID5M9XBO0.22953926H41.09(UPDATEGOA_EMPL_BARGSETeARGAIN_SLWE=:b29.50233.5025g佃23-SELECT■AA1(r."AAAiaDf,1BAA1DfAAAIDZ1.MAA1Of'.'AAAI...19.50123.00239246(INSERTINTOAC20VAUUfS(Bi,；B2,63,94.1^,06,17.69.B9'1631764.771310J02■>inEBri!inloLCDS^ACDOI^LCO20,AAE01JM-CQBO.AABOOI.AL..140023.QO1I4232-INSERTINTOAC11VALUES￡：B1；02；.B3..04.：B5,：B6,W腳,.B913.001.277.60565日汨日dUPDATEKCO*SETft^SOOl=T,0^500=：J,OfllE30l=3WHEREA13.0014.0011314VinBerl!inloAB61(0AE001期E151那?200ME063>AE01l3^AE221pOA..10.0090.00110如9(INSERTINTOAC2DH(OAE0D1,AAC001A^E0Q2ME003ME14OAAE2.?9.0D71.0732721571insertinioKCGShvalueselect*tomKCOBwhereOAE001=109667049.0065.001966BEin^rtinloac2>Q(CAE001AACQQ1,AAEQO'J.AAEQQ3.MEI4fl,AAE210...e.5o32.002it|643；s&leel...from一亡。而占sMeg自B3屯m一出外i白面包出swh白他s.depLc._8.007.0021614[INSERTINTOcom=sh(ft(Jata(e.oo4O5O2IS814tSelectCQnirrt_n^mefrom舒導(dǎo)向1|_1。11導(dǎo)train^wheir審Qwn^r=QHmct600534.673ie1574(SELECT*FROMMl0WHEREMA1D0=^1C13D,B.OD也00164[insertmvoA@oi(OAEOOi.AABDaimb002mb003/^0004^042.^..600110O212291-select*ftomac056.0010.001e10(selectcan&iraint_namefrom&yE.all_canE：iraint5whereownersobjectS.S7471.333171414[IhgerlInloA06O(OAED01AAB001MEI40^027^029^029,^..5z005.00211130肥insertinloAC02hvalueselectOomACO2wheneO￡0Di=iQM的白1SOO11oo151111insertinaoKC04.hvalueseled*fromKCO4.ere0AE001=104464^050015.0015151;由年改執(zhí)行的岫讀取數(shù)相帙的熊3,5個(gè)數(shù)據(jù)聰更鼾時(shí)間口了小月々口。白。3:d6:1B下午.分析具體的SQL語句湘頂層SUL：hssiBisfW￡AlDGIffi.16B.1SL.Z53文件仟）查看收柒下苗帚購亨>,[I曰立2即曲回屋?SQL文本每演執(zhí)行的睡望杏取數(shù)修③1行的摭幗蓑幅（執(zhí)行數(shù)社仲區(qū)聯(lián)取數(shù)賽一行的狀抻區(qū)/映SELECT..FROM^C01,RCQ4VVHEREACQ1.MC001=AC04A*C001..82.607.198.60541335S94675SELECT^BOIMBOOI,ABQ1AAGOO3.ABO11MBM4ABOl打印屏幕…CMI+P報(bào)告El餐…Ctrl-p-R-IJ.7J2.4O540073H2SELECTFROMIC1D,AC01JC13,ABDIWHEREIC13-AAC003.557.605337177B91[SELECTFROMSYS.EXUSSYNUORDERBYS^NTIME查看*暇象>3.078.5695隨2770751■SELECTFROM8VSEXU8VEWJORDER@YVLEVEL.V(1.90S.2295392617J1s09(UPDATEGOA_EMPL_BARGSET6ARGAIN_SUWE=:b下站歷囪記錄數(shù)據(jù)Clrl-fO594ET23-SELECT?*1OT“AMI0Of'.BAA1DfAAAlOTH^AA!T疑于回定幫劭C1rl+Shifl*H當(dāng)前執(zhí)行此SQL醫(yī)句的含話出港詢所引用的表SGIL亂的再厘的默K困裂SOL語句詳細(xì)埼料39246【INSERTINTOAC20VALUES(01,：02,：G3,&4,B5,：06.6?,B氏期.103121210202-insertinlgLCOQ^ACOOI^020^002^1^041^kLCQB0.AABQO1,AL14QC14232-INSERTINTOAC11VALUES￡.B11舊2..日？「B4,.B5JB6一日了.：.?13.0C6563B3■■UPDATEKC048ETAAB001=：1.Q*E3D0=：2.QAE301=：3WHEREA..13.0C1314V解譙過則insertintoAB61(0AE001心E151即？叩刖EQS序￡53>WE221B0A..10QC優(yōu)化SQL語句109091INSERTINTO20H(OAE0D1,AAC00H1AAEDQ2rMEOd?朋El4O/AE2...71.6732721571InseriInloKCOBhvalueselBct"fromKCOBwhereOAE001=109667040.0065.00l|g65就InsertInlDd門口(OAE001.MC001.ME002.Afl€0O3tME140,AAE2W...8.5032.0026J3：select..frompha_cam_5ioragefe,ccim_deparlrTierit&wheres.dept_c...B.007.Q0211S14[insertintocom^shifWata(8.0040.50216814[selectconstraanknamefroms^s^lLconstralntswUereowner=:objecf.6.00524.6731015NtSELECT*FROMMl0WHEREAM-100=7\MC1州6.004.001Q4[ineerlinloAB01(OAEDO1MB001MB002*D03>^OD4tMBO42.M..6.0D14.0021229卜select^ftomacD5e.oo10.00l|010tselectconsirsint^namefromsys.all^consiraint?whereowner=object6.C7471.333171414LihEeri!inloAB0O(OAE001MBD01MEI40^027^029^029^..5.5D65.00211130晚1insertInloACD2hvalueselect■fromAC02whereOAEOOI-104464815.0011.00l|51111InserlIntoKC04hvalueseiecl*fromKC04whereOAEOOI=104460050015.0015151J由尋次執(zhí)行的喋里談眼蛙掛供的前15個(gè)期據(jù)通速率00;。0力速率00;。0力6kjni.E*lhi**典0丁開始|盤JLj}“三口、1OracleIniErpriieH.Sql分析的解釋計(jì)劃

.4針對性語句搜索TOPSQL分析也只能是找到未使用索引的語句（實(shí)際上這一點(diǎn)基本就足夠解決性能的問題了），但是對于.4章節(jié)中的很多內(nèi)容，沒有辦法進(jìn)行捕獲。我們可以采用針對性語句搜索，來盡量找到一些問題語句進(jìn)行優(yōu)化。在PLSQL工具中有一個(gè)FindDBObject的功能，可以進(jìn)行搜索。-IJIMUfiJ25J:氏才?￡?因阜世君用唁困—:4FuilealSe-aich&?gHieEdR:更螯ionDebug-IJIMUfiJ25J:氏才?￡?因阜世君用唁困—:4FuilealSe-aich&?gHieEdR:更螯ionDebug值*MlacraDoCLm?Ht^艮中/始WindowHdp?■Pt/SQLDeveloiper-hsslmd5i?253-[HndDatatxaseObfectf]產(chǎn)鉛1制4出a白hgfie居二HHTE-'Effl|A03UAEK宜卻9LT5ETHAB9Q嗎用孫重陽不2—1E田田：圖由畝田田田SI:H:因畝田?1工|tABS3HM96AS96HABB?AB97HAB99AB99HAeonAC01HAC(EACD2HACMACDiHAC06ACffiHAC05LOGAC?Te?ltofhd|jroHn「CaseicnsiirveBicwsfiiFiLeiO^nerJ^CURRENTU$ER>:]Name|StatusOrBaledafterModfedflltwJi匚2003-|日引詞匚300B-7工］P2008-8-7二]|能舊耳匚30冷8-7二］「Whdewmfcod^口egMuprew整ms后F訪ctiggTypeSpeciliMiimsFQueuesrSequencer回FYEceduriK?Type日Ddia金廠Tabb*rSwionjwqPackageSpecificaiicirBPTriggeis廠Viaws：rLbi國,金Pack沖Bodes占stiLrteM^lefwisedViews-11:E咽鼻nPkrtWirri&w:CcmmandWindow-New「FindDaidMscObjedsNameTypeCompiledI震斗匚HE匚KJHDEZPROCEDURE2003-7151627:25WD0MS_0iTCHjn&PACKX3EBODY茄崢86g56；54商DBMSJJOGPACKAGEBODY2003^3-6Bi96:54◎FUH龍成通1主建FUNCTION3008-74516,27:27FUNCTION2009^1516-27:27。FKG5=COMMPACOWGEBODY3008-7-1516:3ft2DAPKGjMHl處理PACKAGEBODV2009-7151629:04/FK6/_單位變更PACKAGEBODY