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

精選優(yōu)質(zhì)文檔-----傾情為你奉上精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)專心---專注---專業(yè)精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)前言目的性能測(cè)試是測(cè)試中比較重要的工作，性能測(cè)試應(yīng)分為壓力的測(cè)試和性能的測(cè)試，其中性能問題中絕大部分都是由于程序編寫的不合理、不規(guī)范造成的。本文檔說明了程序中常見的不優(yōu)化的腳本編寫，導(dǎo)致的性能問題，并且在也描述了怎樣去跟蹤和解決程序上的性能問題的方法。在最后一章里面描述了做一個(gè)白盒測(cè)試工具測(cè)試性能問題的設(shè)計(jì)思想。文檔說明本文檔只說明PLSQL編寫的優(yōu)化問題，不包括ORACLE本身的性能優(yōu)化（內(nèi)存SGA、系統(tǒng)參數(shù)、表空間等）、操作系統(tǒng)的性能問題和硬件的性能問題。對(duì)于PLSQL程序優(yōu)化方面的內(nèi)容有很多，本文檔列出在我們實(shí)際工作中一些常見的情況。本文檔難免有不正確的地方，也需要大家給予指正。本文檔舉例說明的問題語句不是實(shí)際程序中真正存在的，只是讓大家能看起來更容易理解，但這些語句也不代表在我們程序中其他部分語句不存在這些問題。舉例說明中的語句采用的是社保核心平臺(tái)的數(shù)據(jù)字典，在舉例描述中沒有標(biāo)明表名和字段名的含義，還需單獨(dú)參考。詞匯表詞匯名稱詞匯含義備注參考資料編號(hào)資料名稱作者日期出版單位《ORACLESQL性能優(yōu)化系列》PLSQL程序優(yōu)化原則導(dǎo)致性能問題的內(nèi)在原因?qū)е孪到y(tǒng)性能出現(xiàn)問題從系統(tǒng)底層分析也就是如下幾個(gè)原因：CPU占用率過高，資源爭(zhēng)用導(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í)行，會(huì)消耗CPU的使用運(yùn)算（計(jì)算）會(huì)消耗CPU的使用PLSQL程序占用內(nèi)存的情況讀寫數(shù)據(jù)都需要訪問內(nèi)存內(nèi)存不足時(shí)，也會(huì)使用磁盤PLSQL程序增大IO的情況讀寫數(shù)據(jù)都需要訪問磁盤IO讀取的數(shù)據(jù)越多，IO就越大大家都知道CPU現(xiàn)在都很高，計(jì)算速度非?？?；訪問內(nèi)存的速度也很快；但磁盤的訪問相對(duì)前兩個(gè)相比速度就差的非常大了，因此PLSQL性能優(yōu)化的重點(diǎn)也就是減少IO的瓶頸，換句話說就是盡量減少IO的訪問。性能的優(yōu)先級(jí)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)化器,可以通過對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋.

為了使用基于成本的優(yōu)化器(CBO,Cost-BasedOptimizer),你必須經(jīng)常運(yùn)行analyze命令,以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(objectstatistics)的準(zhǔn)確性.

如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(CHOOSE),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān).如果table已經(jīng)被analyze過,優(yōu)化器模式將自動(dòng)成為CBO,反之,數(shù)據(jù)庫(kù)將采用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，我不建議大家去改動(dòng)ORACLE的默認(rèn)優(yōu)化模式。PLSQL優(yōu)化主要說明了在SQL編寫上和PLSQL程序編寫上可以優(yōu)化的地方。選擇最有效率的表名順序只在基于規(guī)則的優(yōu)化器rule中有效，目前我們oracle選擇的優(yōu)化器基本都不選擇rule，因此該問題基本不會(huì)出現(xiàn)，但為了安全和規(guī)范起見，建議編程習(xí)慣采用該規(guī)則。ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理.在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí),會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并.

例如:

表ac01有16,384條記錄

表ab01有1條記錄

選擇ab01作為基礎(chǔ)表(好的方法)

selectcount(*)fromac01,ab01執(zhí)行時(shí)間0.96秒

選擇ac01作為基礎(chǔ)表(不好的方法)

selectcount(*)fromab01,ac01執(zhí)行時(shí)間26.09秒WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前

例如:(低效)

SELECTab01.aab001,ab02.aab051

FROMab01,ab02

WHEREab02.aae140=’31’

ANDab01.aab001=ab02.aab001;

(高效)

SELECTab01.aab001,ab02.aab051

FROMab01,ab02

WHEREab01.aab001=ab02.aab001

ANDab02.aae140=’31SELECT子句中避免使用‘*‘當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用‘*'是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法.實(shí)際上,ORACLE在解析的過程中,會(huì)將'*'依次轉(zhuǎn)換成所有的列名,這個(gè)工作是通過查詢數(shù)據(jù)字典完成的,這意味著將耗費(fèi)更多的時(shí)間。用EXISTS替代IN實(shí)際情況看，使用exists替換in效果不是很明顯，基本一樣。在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下,使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.

低效:

SELECT*

FROMac01Whereaac001in(selectaac001fromac02whereaab001=str_aab001andaae140=’31’或SELECT*

FROMac01Whereaac001in(selectdistinctaac001fromac02whereaab001=str_aab001andaae140=’31’注意使用distinct也會(huì)影響速度

高效:

SELECT*

FROMac01Whereexists(select1fromac02whereaac001=ac01.aac001andaab001=str_aab001andaae140=’31’in的常量列表是優(yōu)化的(例如：aab019in(‘20’,’30

用NOTEXISTS替代NOTINOracle在10g之前版本notin都是最低效的語句，雖然在10g上notin做到了一些改進(jìn)，但仍然還是存在一些問題，因此我們一定要使用notexists來替代notin的寫法。在子查詢中,NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并.無論在哪種情況下,NOTIN都是最低效的(因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷).為了避免使用NOTIN,我們可以把它改寫成NOTEXISTS.

例如:

SELECT*

FROMac01

WHEREaab001NOTIN(SELECTaab001fromab01whereaab020=’100’);

為了提高效率.改寫為:

SELECT*

FROMac01

WHEREnotexists(SELECT1fromab01whereaab001=ac01.aab001andaab020=用表連接替換EXISTS在子查詢的表和主表查詢是多對(duì)一的情況，一般采用表連接的方式比EXISTS更有效率。

例如：

低效：SELECTac01.*

FROMac01

Whereexists(select1fromac02

whereaac001=ac01.aac001

andaab001=ac01.aab001

andaae140='31'

andaae041='');

高效：

SELECTac01.*

FROMac02,ac01

Whereac02.aac001=ac01.aac001

andac02.aab001=ac01.aab001

andac02.aae140='31'

andaae041='';到底exists和表關(guān)聯(lián)哪種效率高，其實(shí)是根據(jù)兩個(gè)表之間的數(shù)據(jù)量差別大小是有關(guān)的，如果差別不大實(shí)際上速度基本差不多。用EXISTS替換DISTINCT當(dāng)提交一個(gè)包含一對(duì)多表信息(比如個(gè)人基本信息表和個(gè)人參保信息表)的查詢時(shí),避免在SELECT子句中使用DISTINCT.一般可以考慮用EXISTS替換

例如:

低效:

selectdistinctac01.aac001

fromac02,ac01

whereac02.aac001=ac01.aac001

andac02.aae140='31'

andac01.aab001='';

高效:

selectac01.aac001

fromac01

whereexists(select1fromac02whereaac001=ac01.aac001

andaae140='31')

andac01.aab001='';

EXISTS使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果。因此如果不是特別研究和追求速度的話（例如：數(shù)據(jù)轉(zhuǎn)換），查詢一個(gè)表的數(shù)據(jù)需要關(guān)聯(lián)其他表的這種情況查詢，建議采用EXISTS的方式。

減少對(duì)表的查詢?cè)搯栴}是我們編程中出現(xiàn)過的問題，請(qǐng)大家一定注意，并且該類問題優(yōu)化可以帶來較大性能的提升。

例如:

低效

cursorcur_kc24_mzisSelectakc260fromkc24whereakb020=str_akb020andaka130=’11’;cursorcur_kc24_zyisSelectakc260fromkc24whereakb020=str_akb020andaka130=’21’forrec_mzincur_kc24_mzloop門診處理…..endloop;

forrec_mzincur_kc24_zyloop住院處理…..endloop;

高效

cursorcur_kc24isSelectakc260,aka130fromkc24whereakb020=str_akb020andaka130in(’11’,’21forrec_kc24incur_kc24loopifrec_kc24.aka130=’11’門診處理…..endif;ifrec_kc24.aka130=’21’住院處理…..endif;endloop;高效的做法使用同樣的條件（或者說是索引）只訪問一次磁盤，低效的做法訪問了2次磁盤，這樣速度差別將近2倍。避免循環(huán)（游標(biāo)）里面嵌查詢游標(biāo)里面不能嵌入查詢(或者再嵌游標(biāo))，其實(shí)也不能有updatedelete等語句，只能有insert語句。但在實(shí)際的編程情況下是不可能完全避免的，但我們一定要盡量避免。該類問題也是我們程序中出現(xiàn)過的問題，該類問題也可以大大提升程序效率，請(qǐng)大家一定注意。例如：低效：Cursorcur_ac04isSelectaac001,akc010Fromac04Whereaab001=prm_aab001;……Forrec_ac04incur_ac04loopSelectaac008Intostr_aac008fromac01whereaac001=rec_ac04.aac001;ifstr_aac008=’1’n_jfje:=rec_ac04.akc010*0.08;endif;ifstr_aac008=’2’n_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’n_jfje:=rec_ac04.akc010*0.08;endif;ifrec.aac008=’2’n_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會(huì)去掉重復(fù)的記錄，會(huì)有排序的動(dòng)作，會(huì)浪費(fèi)時(shí)間。因此在沒有重復(fù)記錄的情況下或可以允許有重復(fù)記錄的話，要盡量采用unionall來關(guān)聯(lián)。使用DECODE函數(shù)來減少處理時(shí)間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.

例如:（低效)

selectcount(1)fromac01whereaab001=’’andaac008=’1’;

selectcount(1)fromac01whereaab001=’’andaac008=’2’;Selectcount(1),aac008Fromac01Whereaab001=’’andaac008in(’1’,’groupbyaac008;（高效）

selectcount(decode(aac008,’1’,’1’count(decode(aac008,’2’,’1’fromac01whereaab001=’’;

特別說明：groupby和orderby都會(huì)影響性能，編程時(shí)盡量避免沒有必要的分組和排序，或者通過其他的有效的編程辦法去替換，比如上面的處理辦法。groupby優(yōu)化Groupby需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，并且分組復(fù)雜，這樣的查詢語句在性能上是有問題的。盡量避免使用分組或者采用上面的一節(jié)的辦法去代替。采用groupby的也一定要進(jìn)行優(yōu)化。例如：低效selectac04.aac001,ac01.aac002,ac01.aac003,sum(aac040),ac01.aab001

fromac04,ac01

whereac04.aac001=ac01.aac001andac01.aab001=''

groupbyac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;高效：selectac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001

from(selectaac001,sum(aac040)gzzefromac04groupbyaac001)ac04,ac01

whereac04.aac001=ac01.aac001

andaab001='';盡量避免用orderbyOrderby需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，排序的時(shí)間就很長(zhǎng)。但我們也不能避免不使用，這樣大家一定注意一點(diǎn)的是如果使用orderby那么排序的列表必須符合索引，這樣在速度上會(huì)得到很大的提升。用Where子句替換HAVING子句避免使用HAVING子句,HAVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾.這個(gè)處理需要排序,總計(jì)等操作.如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.

例如:

低效:

SELECTaac008,count(1)

FROMac01

GROUPBYaac008

HAVINGaac008in(‘1’,’2’);

高效

SELECTaac008,count(1)

FROMWhereaac008in(‘1’,’2’)

GROUPBYaac008;使用表的別名(Alias)當(dāng)在SQL語句中連接多個(gè)表時(shí),請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來,就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤.刪除重復(fù)記錄一般數(shù)據(jù)轉(zhuǎn)換的程序經(jīng)常會(huì)使用到該方法。最高效的刪除重復(fù)記錄方法(因?yàn)槭褂昧薘OWID)

DELETEFROMac01a

WHEREa.rowid>(SELECTMIN(b.rowid)

FROMac01banda.aac003=b.aac003);COMMIT使用數(shù)據(jù)轉(zhuǎn)換的程序需要關(guān)注這一點(diǎn)。Commit執(zhí)行也是有時(shí)間的，不過時(shí)間特別短，但提交頻率特別大，必然也會(huì)浪費(fèi)時(shí)間。commit可以釋放資源，在大量數(shù)據(jù)更新時(shí)，必須及時(shí)提交。

a.回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b.被程序語句獲得的鎖

c.redologbuffer中的空間

d.ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)例如：Cur_ac20有5000萬數(shù)據(jù)n_count:=0;Forarecincur_ac20loopInsertintoac20……n_count:=n_count+1;Ifn_count==then--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)換上會(huì)存在大數(shù)據(jù)量表的關(guān)聯(lián)，關(guān)聯(lián)多了會(huì)影響索引的效率，可以采用建立臨時(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ù)量過大必然會(huì)占用更多的內(nèi)存和undo表空間，只能在插入完成后提交，這樣資源不能釋放，會(huì)導(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:==thenCommit;n_count:=0;Endif;Endloop;批綁定的方式通過游標(biāo)查詢將數(shù)據(jù)逐行寫到數(shù)組里（實(shí)際上就是內(nèi)存），然后通過批綁定的語句forall…in…insertinto…values…;將內(nèi)存的數(shù)據(jù)一次寫入到數(shù)據(jù)文件中。相比cursor的方式減少了對(duì)io的訪問次數(shù)，提高了速度，但注意內(nèi)存別溢出了。索引使用優(yōu)化在實(shí)際的應(yīng)用系統(tǒng)中索引問題導(dǎo)致性能問題可能能占到80%，在程序優(yōu)化上索引問題是需要我們特別關(guān)注的。本節(jié)主要描述什么情況索引會(huì)不生效。避免在索引列上使用函數(shù)或運(yùn)算這個(gè)問題是在我們實(shí)際編程中出現(xiàn)過的，請(qǐng)大家一定注意。在索引列上使用函數(shù)或運(yùn)算，查詢條件都不會(huì)使用索引。例如：不使用索引Select*fromka02whereaka060=’’andto_char(aae030,’yyyymm’)=’’;使用索引Select*fromka02whereaka060=’’andaae030=to_date(’’,’yyyymm’);不使用索引Select*fromka02whereaka060=’’andaae031+1=sysdate;使用索引Select*fromac04whereaac001=’’andaae031=sysdate-1;如果一定要對(duì)使用函數(shù)的列啟用索引,ORACLE新的功能:基于函數(shù)的索引(Function-BasedIndex)

CREATEINDEXIDX_KA02_AKA066ONKA02(UPPER(AKA066));/*建立基于函數(shù)的索引*/

SELECT*FROMKA02WHEREUPPER(AKA066)=‘ASPL’;/*將使用索引*/不是極特殊情況，建議不要使用。避免改變索引列的類型.索引列的條件如果類型不匹配，則不能使用索引。例如：不使用索引Select*fromac01whereaac001=;使用索引Select*fromac01whereaac001=’’;避免在索引列上使用NOT避免在索引列上使用NOT,NOT不會(huì)使查詢條件使用索引。對(duì)于!=這樣的判斷也是不能使用索引的，索引只能告訴你什么存在于表中,而不能告訴你什么不存在于表中例如:

低效:(這里,不使用索引)

select*Fromac02Wherenotaab019=’10’;

高效:(這里,使用了索引)

Fromac02Whereaab019in(’20’,’30用>=替代>雖然效果不是特別明顯，但建議采用這種方式低效:

SELECT*

FROMab01

WHEREaab019>‘10’

高效:

SELECT*

FROMab01

WHEREaab019>=’20’

兩者的區(qū)別在于,前者DBMS首先定位到aab019=10的記錄并且向前掃描到第一個(gè)aab019大于10的記錄,而后者DBMS將直接跳到第一個(gè)aab019等于10避免在索引列上使用ISNULL和ISNOTNULL對(duì)于索引列使用isnull或isnotnull不會(huì)使用上索引．因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引.

舉例:

低效:(索引失效)

select*fromab01whereaab019isnotnull;

高效:(索引有效)

select*fromab01whereaab019in(‘10’,’20’,’30在實(shí)際開發(fā)中，對(duì)于這類的問題很難避免，如果不是特別影響速度或者要求速度的，可以忽略。帶通配符（%）的like語句%在常量前面索引就不會(huì)使用。例如：不使用索引Select*fromac01whereaac002like‘%’;Select*fromac01whereaac002like‘%%’;使用索引Select*fromac01whereaac002like‘%’;總是使用索引的第一個(gè)列如果索引是建立在多個(gè)列上,只有在它的第一個(gè)列被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引。例如：Ac02的復(fù)合索引：aac001、aae140、aae041Select*fromac02whereaae140=’31’andaae041=’’Select*fromac02whereaac001=’’;--可以使用索引如果不使用索引第一列基本上不會(huì)使用索引，使用索引要按照索引的順序使用，另外使用復(fù)合索引的列越多，查詢的速度就越快多個(gè)平等的索引當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí),ORACLE會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并,檢索出僅對(duì)全部索引有效的記錄.

在ORACLE選擇執(zhí)行路徑時(shí),唯一性索引的等級(jí)高于非唯一性索引.然而這個(gè)規(guī)則只有

當(dāng)WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較.這種子句在優(yōu)化器中的等級(jí)是非常低的.

如果不同表中兩個(gè)相同等級(jí)的索引將被引用,FROM子句中表的順序?qū)Q定哪個(gè)會(huì)被率先使用.FROM子句中最后的表的索引將有最高的優(yōu)先級(jí).

如果同一表中有兩個(gè)相同等級(jí)的索引被引用，oracle會(huì)分析最有效的索引去引用，其他的索引不會(huì)使用，如果這些相同等級(jí)的索引效果差不多，oracle可能會(huì)自動(dòng)合并進(jìn)行使用。

不明確的索引等級(jí)當(dāng)ORACLE無法判斷索引的等級(jí)高低差別,優(yōu)化器將只使用一個(gè)索引,它就是在WHERE子句中被列在最前面的.自動(dòng)選擇索引如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性．

在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．使用提示(Hints)對(duì)于表的訪問,可以使用兩種Hints.FULL和ROWID

FULLhint告訴ORACLE使用全表掃描的方式訪問指定表.

例如:

SELECT/*+FULL(AC01)*/*

FROMAC01

WHEREAAC001=‘’;

如果一個(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)*/aac001

FROMAC01

WHEREaac002='';

在不使用hint的情況下,以上的查詢應(yīng)該也會(huì)使用索引,然而,如果該索引的重復(fù)值過多而你的優(yōu)化器是CBO,優(yōu)化器就可能忽略索引.在這種情況下,你可以用INDEXhint強(qiáng)制ORACLE使用該索引.

ORACLEhints還包括ALL_ROWS,FIRST_ROWS,RULE,USE_NL,USE_MERGE,USE_HASH等等.

使用hint,表示我們對(duì)ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改.

這是一個(gè)很有技巧性的工作.除非特定的情況，例如：數(shù)據(jù)轉(zhuǎn)換，其他情況最好不用.表上存在過舊的分析我們現(xiàn)在很多項(xiàng)目都存在性能問題，其中有很多種情況都是由于分析過舊導(dǎo)致ORACLE判斷索引級(jí)別和資源成本上出現(xiàn)問題，會(huì)導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。我個(gè)人覺得這是ORACLE不夠完善的地方。解決辦法：第一種辦法：刪除分析，停止oracle10g的自動(dòng)分析，但不使用分析，oracle訪問數(shù)據(jù)的CPU消耗就過大。第二種辦法：重新分析，但過長(zhǎng)時(shí)間后，索引是否會(huì)再次失效，沒有驗(yàn)證過。表上存在并行表上存在并行，ORACLE判斷索引級(jí)別和資源成本上出現(xiàn)問題，會(huì)導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。這個(gè)問題我不知道有什么好的處理辦法，從現(xiàn)場(chǎng)實(shí)際應(yīng)用速度角度比較，我還是選擇去掉并行，因?yàn)椴皇褂盟饕M(jìn)行全表掃描肯定是不能忍受的。關(guān)于索引建立索引的使用是肯定會(huì)大大提高查詢的速度，但索引其實(shí)也是一種數(shù)據(jù)，它也是存放的用戶類型的表空間下的，索引建立的越多越大，占用的空間也越大，從用戶的環(huán)境來說這也不是問題，但如果一個(gè)表有過多過大的查詢，必然會(huì)影響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)行分析，一般總體上會(huì)有幾種現(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)存和存儲(chǔ)設(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è)置不合理等PLSQL程序：plsql不優(yōu)化、未使用索引、鎖表在不同現(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)生的影響：對(duì)于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個(gè)表時(shí)，它訪問磁盤的時(shí)間和占用的吞吐量是很高的，這就導(dǎo)致系統(tǒng)IO長(zhǎng)時(shí)間處于忙的狀態(tài)，導(dǎo)致整個(gè)系統(tǒng)性能下降。對(duì)于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個(gè)表時(shí)，其他的業(yè)務(wù)程序也訪問同一個(gè)表時(shí)，速度將大大下降。如果是更新表操作時(shí)間長(zhǎng)，還可能會(huì)導(dǎo)致鎖等待，導(dǎo)致會(huì)話堵塞，weblogic端也出現(xiàn)壓力問題，導(dǎo)致這個(gè)系統(tǒng)性能下降。我們一般根據(jù)這些現(xiàn)象、以及一些方法判斷，來初步分析產(chǎn)生性能問題的大致原因的范圍。不過對(duì)于這一點(diǎn)，還是比較困難的，因?yàn)楫a(chǎn)生問題的原因是多種的，并且還有一定的關(guān)聯(lián)。下面的章節(jié)介紹我們已經(jīng)斷定是PLSQL優(yōu)化、索引不使用的問題，我們通過什么方法來具體定位問題。ExpainPlan分析索引使用在PL/SQLDeveloper等工具有一個(gè)ExpainPlan分析的功能，這個(gè)功能可以幫助我們分析SQL語句是否使用了索引、使用哪些索引和使用索引的效果。選擇explainplan的窗口在上面欄中輸入SQL語句，然后點(diǎn)擊工具欄上的EXECUTE執(zhí)行（或按F8），就會(huì)在下面顯示Optmizergoal優(yōu)化器的默認(rèn)方式（也可手工選擇），以及下面的解釋計(jì)劃，從解釋計(jì)劃上能看到哪個(gè)條件語句使用了索引，哪個(gè)沒有使用；哪個(gè)表使用了索引，使用了哪個(gè)索引，哪些表是全表掃描的(TABLEACCESSFULL)分析內(nèi)容說明：COST：根據(jù)優(yōu)化程序的基于開銷的方法所估計(jì)出的操作開銷值對(duì)于使用基于規(guī)則方法的語句該列為空該列值沒有特定的測(cè)量單位它只是一個(gè)用于比較執(zhí)行規(guī)劃開銷大小的權(quán)重值Cardinality：根據(jù)基于開銷的方法對(duì)操作所訪問行數(shù)的估計(jì)值Bytes：根據(jù)基于開銷的方法對(duì)操作所訪問字節(jié)的估計(jì)通過設(shè)置，我們還能看到更多的信息，例如：CPU使用、時(shí)間等等全表掃描的(TABLEACCESSFULL)肯定是速度慢的，如果是大數(shù)據(jù)量的表，那么這個(gè)語句是絕對(duì)影響性能的。另外使用了索引也不一定性能就高，因?yàn)樗饕褂靡灿行实那闆r，下面列出索引常見的使用類型：INDEXUNIQUESCAN：唯一索引掃描，速度最快INDEXRANGESCAN：范圍索引掃描我們?cè)谥勒Z句有問題，或者我們對(duì)語句進(jìn)行優(yōu)化時(shí)，這個(gè)工具是非常有用的。TOPSQL分析程序有性能問題的時(shí)候，我們是不知道哪些語句存在性能的問題，尤其你不是開發(fā)人員。幸好有一些工具可以幫助我們找到這些存在性能問題的語句。Toad工具和ORACLE9I的企業(yè)管理控制臺(tái)工具可以捕獲到這些問題語句（oracle