PLSQL程序優(yōu)化與性能測試方法

1、NeusoftPLSQL程序優(yōu)化和性能分析方法沈陽東軟軟件股份有限公司文件編號:TD文檔編號版本號1. 0分冊名稱第冊/共nn 冊總頁數(shù)正文附錄編制張悅審批生效日期沈陽東軟軟件股份有限公司版本號更改條款及內(nèi)容更改人審批人更改日期1. 前言31.1目的31.2文檔說明41.3詞匯表41.4參考資料42. PLSQL程序優(yōu)化原則52.1導(dǎo)致性能問題的內(nèi)在原因52.2 PLSQL優(yōu)化的核心思想 52.3 ORACLE 優(yōu)化器 52.4 PLSQL 優(yōu)化 62.4.1選擇最有效率的表名順序62.4.2 WHERE 子句中的連接順序 62.4.3 SELECT子句中避免使用72.4.4 用 EXISTS

2、 替代 IN72.4.5 用 NOT EXISTS 替代 NOT IN72.4.6用表連接替換 EXISTS82.4.7 用 EXISTS 替換 DISTINCT82.4.8 減少對表的查詢 92.4.9 避免循環(huán)(游標)里面嵌查詢 102.4.10 盡量用 union all 替換 union112.4.11 使用 DECODE 函數(shù)來減少處理時間 112.4.12 group by 優(yōu)化 112.4.13 盡量避免用 order by122.4.14 用 Where 子句替換 HAVING 子句 122.4.15 使用表的別名 (Alias)122.4.16 刪除重復(fù)記錄 132.4.17

3、 COMMIT 使用 132.4.18 減少多表關(guān)聯(lián) 132.4.19 批量數(shù)據(jù)插入 142.5 索引使用優(yōu)化 142.5.1 避免在索引列上使用函數(shù)或運算 152.5.2 避免改變索引列的類型 .152.5.3 避免在索引列上使用 NOT152.5.4 用=替代 162.5.5 避免在索引列上使用 IS NULL 和 IS NOT NULL162.5.6 帶通配符( % )的 like 語句 162.5.7 總是使用索引的第一個列 172.5.8 多個平等的索引 172.5.9 不明確的索引等級 172.5.10 自動選擇索引 172.5.11 使用提示 (Hints)172.5.12 表上

4、存在過舊的分析 182.5.13 表上存在并行 182.5.14 關(guān)于索引建立 193. PLSQL 程序性能問題測試方法 193.1 性能問題分析 193.2 EXPAIN PLAN 分析索引使用 203.3 TOPSQL 分析 213.4 針對性語句搜索 253.5 后臺存儲過程跟蹤 263.6 性能監(jiān)控 錯誤！未定義書簽。4. 性能測試工具設(shè)計思想 錯誤！未定義書簽。1. 前言1.1 目的 性能測試是測試中比較重要的工作，性能測試應(yīng)分為壓力的測試和性能的測試，其中性 能問題中絕大部分都是由于程序編寫的不合理、不規(guī)范造成的。本文檔說明了程序中常見的 不優(yōu)化的腳本編寫，導(dǎo)致的性能問題，并且在

5、也描述了怎樣去跟蹤和解決程序上的性能問題的方法。在最后一章里面描述了做一個白盒測試工具測試性能問題的設(shè)計思想。1.2文檔說明本文檔只說明PLSQL編寫的優(yōu)化問題，不包括 ORACLE本身的性能優(yōu)化（內(nèi)存SGA、 系統(tǒng)參數(shù)、表空間等）、操作系統(tǒng)的性能問題和硬件的性能問題。對于PLSQL程序優(yōu)化方面的內(nèi)容有很多，本文檔列出在我們實際工作中一些常見的情況。本文檔難免有不正確的地方，也需要大家給予指正。本文檔舉例說明的問題語句不是實際程序中真正存在的，只是讓大家能看起來更容易理 解，但這些語句也不代表在我們程序中其他部分語句不存在這些問題。舉例說明中的語句采用的是社保核心平臺的數(shù)據(jù)字典，在舉例描述中沒

6、有標明表名和字 段名的含義，還需單獨參考。1.3詞匯表詞匯名稱詞匯含義備注1.4參考資料編號資料名稱作者日期出版單位10 RACLE SQ性能優(yōu)化系列232. PLSQL 程序優(yōu)化原則2.1 導(dǎo)致性能問題的內(nèi)在原因?qū)е孪到y(tǒng)性能出現(xiàn)問題從系統(tǒng)底層分析也就是如下幾個原因：CPU 占用率過高，資源爭用導(dǎo)致等待 內(nèi)存使用率過高，內(nèi)存不足需要磁盤虛擬內(nèi)存 IO 占用率過高，磁盤訪問需要等待2.2 PLSQL 優(yōu)化的核心思想PLSQL 優(yōu)化實際上就是避免出現(xiàn)“導(dǎo)致性能問題的內(nèi)在原因” ，實際上編寫程序，以及 性能問題跟蹤應(yīng)該本著這個核心思想去考慮和解決問題。PLSQL 程序占用 CPU 的情況 系統(tǒng)解讀

7、SQL 語句執(zhí)行，會消耗 CPU 的使用 運算（計算）會消耗 CPU 的使用PLSQL 程序占用內(nèi)存的情況 讀寫數(shù)據(jù)都需要訪問內(nèi)存 內(nèi)存不足時，也會使用磁盤PLSQL 程序增大 IO 的情況 讀寫數(shù)據(jù)都需要訪問磁盤 IO 讀取的數(shù)據(jù)越多， IO 就越大大家都知道 CPU 現(xiàn)在都很高，計算速度非?？?；訪問內(nèi)存的速度也很快；但磁盤的訪問 相對前兩個相比速度就差的非常大了，因此 PLSQL 性能優(yōu)化的重點也就是減少 IO 的瓶頸， 換句話說就是盡量減少 IO 的訪問。性能的優(yōu)先級CPU-內(nèi)存-10，影響性能的因素依次遞增。根據(jù)上面的分析，PLSQL優(yōu) 化的核心思想為：1. 避免過多復(fù)雜的 SQL 腳

8、本，減少系統(tǒng)的解讀過程2. 避免過多的無用的計算，例如：死循環(huán)3. 避免浪費內(nèi)存空間沒有必要的 SQL 腳本，導(dǎo)致內(nèi)存不足4. 內(nèi)存中計算和訪問速度很快5. 盡可能的減少磁盤的訪問的數(shù)據(jù)量，該原則是 PLSQL 優(yōu)化中重要思想。6. 盡可能的減少磁盤的訪問的次數(shù)，該原則是 PLSQL 優(yōu)化中重要思想。 下面的章節(jié)具體介紹常見影響性能的 SQL 語句情況。2.3 0RACLE 優(yōu)化器0RACLE 的優(yōu)化器：a. RULE （基于規(guī)則） b. C0ST （基于成本） c. CH00SE （選擇性）設(shè)置缺省的優(yōu)化器 ,可以通過對 init.ora 文件中 0PTIMIZER_M0DE 參數(shù)的各種聲明

9、 ,如 RULE,C0ST,CH00SE,ALL_R0WS,FIRST_R0WS . 你當然也在 SQL 句級或是會話（session級對其進行覆蓋.為了使用基于成本的優(yōu)化器 （CBO, Cost-Based Optimizer） , 你必須經(jīng)常運行 analyze 命令, 以增加數(shù)據(jù)庫中的對象統(tǒng)計信息（object statistics）的準確性.如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性（CHOOSE）,那么實際的優(yōu)化器模式將和是否運行 過analyze命令有關(guān).如果table已經(jīng)被analyze過,優(yōu)化器模式將自動成為 CBO ,反之, 數(shù)據(jù)庫將采用 RULE 形式的優(yōu)化器 .在缺省情況下 ,O

10、RACLE 采用 CHOOSE 優(yōu)化器 , 為了避免那些不必要的全表掃描 （full table scan），你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的 優(yōu)化器.在oracle10g前默認的優(yōu)化模式是 CHOOSE，10g默認是ALL_ROWS，我不建議大家去 改動 ORACLE 的默認優(yōu)化模式。2.4 PLSQL 優(yōu)化主要說明了在 SQL 編寫上和 PLSQL 程序編寫上可以優(yōu)化的地方。2.4.1 選擇最有效率的表名順序只在基于規(guī)則的優(yōu)化器 rule 中有效，目前我們 oracle 選擇的優(yōu)化器基本都不選擇 rule ，因此該問題基 本不會出現(xiàn)，但為了安全和規(guī)范起

11、見，建議編程習慣采用該規(guī)則。ORACLE 的解讀器按照從右到左的順序處理 FROM 子句中的表名 ,因此 FROM 子句中寫在最后的表 （基 礎(chǔ)表 driving table） 將被最先處理 . 在 FROM 子句中包含多個表的情況下 ,你必須選擇記錄條數(shù)最少的表作 為基礎(chǔ)表 .當 ORACLE 處理多個表時 , 會運用排序及合并的方式連接它們 .首先 ,掃描第一個表 （FROM 子句 中最后的那個表 ） 并對記錄進行派序 ,然后掃描第二個表 （FROM 子句中最后第二個表 ）,最后將所有從第二個 表中檢索出的記錄與第一個表中合適記錄進行合并 .例如 :表 ac01 有 16,384 條記錄表

12、 ab01 有 1 條記錄選擇 ab01 作為基礎(chǔ)表 （好的方法 ）select count（*） from ac01,ab01 執(zhí)行時間 0.96 秒選擇 ac01 作為基礎(chǔ)表 （不好的方法 ）select count（*） from ab01,ac01 執(zhí)行時間 26.09 秒2.4.2 WHERE 子句中的連接順序ORACLE 采用自下而上的順序解讀 WHERE 子句 ,根據(jù)這個原理 ,表之間的連接必須寫在其他 WHERE 條件之前 例如 :（低效）SELECT ab01.aab001,ab02.aab051FROM ab01,ab02WHERE ab02.aae140= 31 AND

13、ab01.aab001=ab02.aab001 。(高效 )SELECT ab01.aab001,ab02.aab051FROM ab01,ab02WHERE ab01.aab001=ab02.aab001 AND ab02.aae140= 31。2.4.3 SELECT 子句中避免使用 * 當你想在 SELECT 子句中列出所有的 COLUMN 時 ,使用動態(tài) SQL 列引用 *是一個方便的方法 .不幸 的是 ,這是一個非常低效的方法 . 實際上 ,ORACLE 在解讀的過程中 , 會將 * 依次轉(zhuǎn)換成所有的列名 , 這個 工作是通過查詢數(shù)據(jù)字典完成的 , 這意味著將耗費更多的時間。2.4.

14、4 用 EXISTS 替代 IN實際情況看，使用 exists 替換 in 效果不是很明顯，基本一樣。 在許多基于基礎(chǔ)表的查詢中,為了滿足一個條件 , 往往需要對另一個表進行聯(lián)接 .在這種情況下 , 使用EXISTS( 或 NOT EXISTS) 通常將提高查詢的效率 .低效 :SELECT *FROM ac01Where aac001 in (select aac001 from ac02 where aab001=str_aab001 and aae140=31) 。或SELECT *FROM ac01Where aac001 in (select distinct aac001 from

15、 ac02 where aab001=str_aab001 and aae140= 31)。注意使用 distinct 也會影響速度高效 :SELECT *FROM ac01Where exists (select 1 from ac02 where aac001=ac01.aac001 and aab001=str_aab001 and aae140=31)。in 的常量列表是優(yōu)化的 (例如： aab019 in (20,30) ，不用 exists 替換； in 列表相當于 or2.4.5 用 NOT EXISTS 替代 NOT INOracle 在 10g 之前版本 not in 都是最

16、低效的語句，雖然在 10g 上 not in 做到了一些改進，但仍然還是存在 一些問題，因此我們一定要使用not exists 來替代 not in 的寫法。在子查詢中 ,NOT IN 子句將執(zhí)行一個內(nèi)部的排序和合并 . 無論在哪種情況下 ,NOT IN 都是最低效的 ( 因為 它對子查詢中的表執(zhí)行了一個全表遍歷 ). 為了避免使用 NOT IN , 我們可以把它改寫成 NOT EXISTS.例如 :SELECT *FROM ac01WHERE aab001 NOT IN (SELECT aab001 from ab01 where aab020= 100)。為了提高效率 .改寫為 :SELE

17、CT *FROM ac01WHERE not exists (SELECT 1 from ab01 where aab001=ac01.aab001 and aab020= 100) 。2.4.6 用表連接替換 EXISTS 在子查詢的表和主表查詢是多對一的情況，一般采用表連接的方式比 EXISTS 更有效率。 例如：低效： SELECT ac01.*FROM ac01Where exists (select 1 from ac02where aac001=ac01.aac001and aab001=ac01.aab001and aae140=31and aae041=200801)。高效：

18、SELECT ac01.*FROM ac02,ac01Where ac02.aac001=ac01.aac001and ac02.aab001=ac01.aab001and ac02.aae140=31and aae041=200801。到底 exists 和表關(guān)聯(lián)哪種效率高，其實是根據(jù)兩個表之間的數(shù)據(jù)量差別大小是有關(guān)的，如果差別不大實際 上速度基本差不多。2.4.7 用 EXISTS 替換 DISTINCT 當提交一個包含一對多表信息 (比如個人基本信息表和個人參保信息表 )的查詢時 ,避免在 SELECT 子句中 使用 DISTINCT. 一般可以考慮用 EXISTS 替換 例如 :低效

19、:select distinct ac01.aac001from ac02,ac01where ac02.aac001 = ac01.aac001and ac02.aae140=31and ac01.aab001=100100。高效 :select ac01.aac001from ac01where exists(select 1 from ac02 where aac001 = ac01.aac001and aae140=31)and acOl.aabOO仁100100。EXISTS使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后 立刻返回結(jié)果。因此如果不是特別研究和追求速度

20、的話(例如：數(shù)據(jù)轉(zhuǎn)換)，查詢一個表的數(shù)據(jù)需要關(guān)聯(lián)其他表的這種情況查詢，建議采用 EXISTS 的方式。2.4.8 減少對表的查詢該問題是我們編程中出現(xiàn)過的問題，請大家一定注意，并且該類問題優(yōu)化可以帶來較大性能的提升。例如 :低效 cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11。cursor cur_kc24_zy isSelect akc260from kc24where akb020 =str_akb020and aka130=21。for rec_mz in cur_kc24_

21、mz loop門診處理.end loop。for rec_mz in cur_kc24_zy loop住院處理 .end loop。高效 cursor cur_kc24 isSelect akc260，aka130from kc24where akb020 =str_akb020and aka130 in (11，21)。for rec_kc24 in cur_kc24 loopif rec_kc24.aka130= 11 then 門診處理 .end if 。if rec_kc24.aka130= 21 then住院處理.end if 。end loop 。高效的做法使用同樣的條件（或者說是

22、索引）只訪問一次磁盤，低效的做法訪問了 別將近 2 倍。2 次磁盤，這樣速度差2.4.9 避免循環(huán)（游標）里面嵌查詢游標里面不能嵌入查詢 （或者再嵌游標 ），其實也不能有 update delete 等語句，只能有 insert 語句。但在實際 的編程情況下是不可能完全避免的，但我們一定要盡量避免。該類問題也是我們程序中出現(xiàn)過的問題，該 類問題也可以大大提升程序效率，請大家一定注意。例如： 低效：Cursor cur_ac04 isSelect aac001,akc010From ac04Where aab001= prm_aab001 。For rec_ac04 in cur_ac04 lo

23、opSelect aac008Into str_aac008from ac01where aac001=rec_ac04.aac001。 if str_aac008= 1thenn_jfje := rec_ac04.akc010*0.08 。end if 。if str_aac008= 2thenn_jfje := rec_ac04.akc010*0.1 。end if 。End loop 。高效：Cursor cur_ac04 isSelect ac01.aac001,ac04.akc010,ac01.aac008From ac04,ac01Where ac04.aac001=ac01.aa

24、c001and aab001= prm_aab001。For rec_ac04 in cur_ac04 loopif rec .aac008= 1thenn_jfje := rec_ac04.akc010*0.08 。 end if 。if rec.aac008= 2thenn_jfje :=rec_ac04.akc010*0.1 。end if 。 end loop 。優(yōu)化的方法是盡量把游標循環(huán)中的查詢語句放到游標查詢中一起查詢出來， 這樣相當于只訪問了 1 次磁盤 讀到內(nèi)存；如果放到游標中的話，假如游標有100 萬數(shù)據(jù)量，那么程序需要 100 萬次磁盤，可以想象浪費了多少 IO 的訪問。如

25、果在程序編寫上沒有辦法避免游標中有查詢語句的話(一般情況是可以避免的) ，那么也要保證游標中 的查詢使用的索引(即查詢速度非?？? ，例如：游標 100 萬數(shù)據(jù)量，游標中的查詢語句執(zhí)行需要 0.02 秒， 從這個速度上來說是很快的，但總體上看 100萬*0.02 秒=2萬秒=5小時 33分鐘，如果寫一個不夠優(yōu)化的 語句需要 1 秒，那么需要幾天能執(zhí)行完呢？2.4.10 盡量用 union all 替換 unionUnion 會去掉重復(fù)的記錄，會有排序的動作，會浪費時間。因此在沒有重復(fù)記錄的情況下或可以允許有重 復(fù)記錄的話，要盡量采用 union all 來關(guān)聯(lián)。2.4.11 使用 DECODE

26、 函數(shù)來減少處理時間使用 DECODE 函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表例如 :(低效)select count(1) from ac01 where aabOO仁100001 and aac008=1。select count(1) from ac01 where aab001=100001and aac008=2。(低效)Select count(1),aac008From ac01Where aab00仁100001and aac008 in ( 。1 。, 。2 。)group by aac008。(高效) select count(decode(aac008,1,1,n

27、ull) zz, count(decode(aac008,2,1,null) txfrom ac01where aab001=100001。特別說明：group by 和 order by 都會影響性能，編程時盡量避免沒有必要的分組和排序，或者通過其他的有效的編程 辦法去替換，比如上面的處理辦法。2.4.12 group by 優(yōu)化Group by 需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，并且分組復(fù)雜，這樣的查詢語句在性能 上是有問題的。盡量避免使用分組或者采用上面的一節(jié)的辦法去代替。采用 group by 的也一定要進行優(yōu)化。例如：低效select ac04.aac001,ac01

28、.aac002,ac01.aac003,sum(aac040),ac01.aab001from ac04,ac01where ac04.aac001=ac01.aac001 and ac01.aab001=1000000370group by ac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001。高效：select ac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001from (select aac001,sum(aac040) gzze from ac04 group by aac001) ac04

29、,ac01where ac04.aac001=ac01.aac001and aab001=1000000370。2.4.13 盡量避免用 order byOrder by 需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，排序的時間就很長。但我們也不能避免 不使用，這樣大家一定注意一點的是如果使用 order by 那么排序的列表必須符合索引，這樣在速度上會得 到很大的提升。2.4.14 用 Where 子句替換 HAVING 子句避免使用 HAVING 子句 , HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾 . 這個處理需要排序 總計等操作 . 如果能通過 WHERE 子句限制記

30、錄的數(shù)目 ,那就能減少這方面的開銷 .例如 :低效 :SELECT aac008,count(1)FROM ac01GROUP BY aac008HA VING aac008 in ( 1,2)。高效SELECT aac008,count(1)FROM ac01Where aac008 in ( 1,2)GROUP BY aacO0& HAVING中的條件一般用于對一些集合函數(shù)的比較，如COUNT()等等.除此而外，般的條件應(yīng)該寫在 WHERE 子句中2.4.15 使用表的別名 (Alias)當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個 Column上這樣一來，就可以減少解讀

31、 的時間并減少那些由 Column 歧義引起的語法錯誤 .2.4.16 刪除重復(fù)記錄一般數(shù)據(jù)轉(zhuǎn)換的程序經(jīng)常會使用到該方法。最高效的刪除重復(fù)記錄方法 ( 因為使用了 ROWID)DELETE FROM ac01aWHERE a.rowid (SELECT MIN(b.rowid)FROM ac01 bWHERE a.aac002=b.aac002and a.aac003=b.aac003 )。2.4.17 COMMIT 使用數(shù)據(jù)轉(zhuǎn)換的程序需要關(guān)注這一點。1. Commit 執(zhí)行也是有時間的，不過時間特別短，但提交頻率特別大，必然也會浪費時間。2. commit 可以釋放資源，在大量數(shù)據(jù)更新時，必

32、須及時提交。a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息 .b. 被程序語句獲得的鎖c. redo log buffer 中的空間d. ORACLE 為管理上述 3 種資源中的內(nèi)部花費例如：Cur_ac20 有 5000 萬數(shù)據(jù)n_count :=0 。For arec in cur_ac20 loopInsert into ac20 n_count := n_count + 1 。If n_count = = 100000 then-10萬一提交commit。n_count := 0。End if 。End loop 。Commit。如果 1 條一提交，需要提交 5000 萬必然浪費時間；如果整體提交，

33、資源不能釋放，性能必須下降。 在實際編程時，應(yīng)注意提交的次數(shù)和提交的數(shù)據(jù)量的平衡關(guān)系。2.4.18 減少多表關(guān)聯(lián)3 個（子查詢也屬于表關(guān)表關(guān)聯(lián)的越多，查詢速度就越慢，盡量減少多個表的關(guān)聯(lián)，建議表關(guān)聯(lián)不要超過 聯(lián)）。數(shù)據(jù)轉(zhuǎn)換上會存在大數(shù)據(jù)量表的關(guān)聯(lián)，關(guān)聯(lián)多了會影響索引的效率，可以采用建立臨時表的辦法，有時更能提高速度。2.4.19 批量數(shù)據(jù)插入數(shù)據(jù)轉(zhuǎn)換時或者大業(yè)務(wù)數(shù)據(jù)插入時，有以下幾種辦法進行數(shù)據(jù)插入（不包括imp、 impdp 和 sqlloader）Insert into select 方式將查詢的結(jié)果一次插入到目標表中。例如：Insert into ac01_bak select * fr

34、om ac01 。 由于是一次查詢一次插入，并且最后一次提交，他的速度要比下面描述的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 方式定義游標，然

35、后逐行進行插入，然后定量提交。 例如：Cusor cur_ac20 isSelect * from ac20 。n_count :=0 。For rec_ac20 in cur_ac20 loopInsert into ac20_bak（aac001, .）Values（rec_ac20.aac001, .） 。If n_count :=100000 thenCommit。n_count :=0 。End if 。End loop。，然后通過批綁定的語句forallininsert相比 cursor 的方式減少了對 io 的訪問次數(shù)， 提高了80%，在程序優(yōu)化上索引問題是需要我們特別關(guān)注批綁定

36、的方式 通過游標查詢將數(shù)據(jù)逐行寫到數(shù)組里（實際上就是內(nèi)存） intovalues。將內(nèi)存的數(shù)據(jù)一次寫入到數(shù)據(jù)文件中。 速度，但注意內(nèi)存別溢出了。2.5 索引使用優(yōu)化 在實際的應(yīng)用系統(tǒng)中索引問題導(dǎo)致性能問題可能能占到 的。本節(jié)主要描述什么情況索引會不生效。2.5.1 避免在索引列上使用函數(shù)或運算 這個問題是在我們實際編程中出現(xiàn)過的，請大家一定注意。在索引列上使用函數(shù)或運算，查詢條件都不會 使用索引。例如：不使用索引Select * from ka02 where aka060= 10001000and to_char(aae030,yyyymm )= 200801。 使用索引Select * f

37、rom ka02 where aka060= 10001000and aae030=to_date(200801,yyyymm )。 不使用索引Select * from ka02 where aka060= 10001000and aae031+1=sysdate。使用索引Select * from ac04 where aac001= 10001000and aae031=sysdate -1。如果一定要對使用函數(shù)的列啟用索引 , ORACLE 新的功能 : 基于函數(shù)的索引 (Function-Based Index) CREATE INDEX IDX_KA02_AKA066 ON KA0

38、2 (UPPER(AKA066)。 /* 建立基于函數(shù)的索引 */SELECT * FROM KA02 WHERE UPPER(AKA066 ) = ASPL。 /* 將使用索引 */ 不是極特殊情況，建議不要使用。2.5.2 避免改變索引列的類型 .索引列的條件如果類型不匹配，則不能使用索引。例如：不使用索引Select * from ac01 where aac001=10001000 。使用索引Select * from ac01 where aac001= 10001000。2.5.3 避免在索引列上使用 NOT避免在索引列上使用 NOT, NOT 不會使查詢條件使用索引。對于 !=

39、這樣的判斷也是不能使用索引的，索引 只能告訴你什么存在于表中 , 而不能告訴你什么不存在于表中例如 :低效 : (這里 ,不使用索引 )select *From ac02Where not aab019=10。高效 : (這里 ,使用了索引 ) select *From ac02Where aab019 in （20,30）。2.5.4 用=替代雖然效果不是特別明顯，但建議采用這種方式 低效 :SELECT *FROM ab01WHERE aab019 10高效 :SELECT * FROM ab01WHERE aab019 = O兩者的區(qū)別在于，前者DBMS首先定位到aab019=10的記錄

40、并且向前掃描到第一個 aab019大于10的記錄,而后者DBMS將直接跳到第一個 aab019等于10的記錄2.5.5 避免在索引列上使用 IS NULL 和 IS NOT NULL對于索引列使用 is null 或 is not null 不會使用上索引因為空值不存在于索引列中 ,所以 WHERE 子句中對 索引列進行空值比較將使 ORACLE 停用該索引 .舉例 :低效 : （索引失效 ）select * from ab01 where aab019 is not null 。高效 : （索引有效 ）select * from ab01 where aab019 in（ 10,20,30）

41、。在實際開發(fā)中，對于這類的問題很難避免，如果不是特別影響速度或者要求速度的，可以忽略。2.5.6 帶通配符（ %）的 like 語句%在常量前面索引就不會使用。例如： 不使用索引 Select * from ac01 where aac002 like %210104。Select * from ac01 where aac002 like %21 01 04% 。 使用索引Select * from ac01 where aac002 like 210104% 。2.5.7 總是使用索引的第一個列如果索引是建立在多個列上 , 只有在它的第一個列被 where 子句引用時 ,優(yōu)化器才會選擇使用

42、該索引。例如：Ac02 的復(fù)合索引： aac001、 aae140、aae041Select * from ac02 where aae140= 31and aae041=200801。 - 不會使用索引Select * from ac02 where aac001= 10001000。 - 可以使用索引如果不使用索引第一列基本上不會使用索引，使用索引要按照索引的順序使用，另外使用復(fù)合索引的列越 多，查詢的速度就越快2.5.8 多個平等的索引當 SQL 語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時 對它們的記錄進行合并 , 檢索出僅對全部索引有效的記錄, ORACLE 會同時使用多個索引并

43、在運行時在 ORACLE 選擇執(zhí)行路徑時 ,唯一性索引的等級高于非唯一性索引. 然而這個規(guī)則只有當 WHERE 子句中索引列和常量比較才有效 等級是非常低的 .如果索引列和其他表的索引類相比較. 這種子句在優(yōu)化器中的如果不同表中兩個相同等級的索引將被引用句中最后的表的索引將有最高的優(yōu)先級, FROM 子句中表的順序?qū)Q定哪個會被率先使用 . FROM 子如果同一表中有兩個相同等級的索引被引用，如果這些相同等級的索引效果差不多，oracle 會分析最有效的索引去引用，其他的索引不會使用， oracle 可能會自動合并進行使用。2.5.9 不明確的索引等級當 ORACLE 無法判斷索引的等級高低差

44、別 , 優(yōu)化器將只使用一個索引 ,它就是在 WHERE 子句中被列在最前 面的 .2.5.10 自動選擇索引如果表中有兩個以上(包括兩個)索引，其中有一個唯一性索引，而其他是非唯一性在這種情況下， ORACLE 將使用唯一性索引而完全忽略非唯一性索引2.5.11 使用提示 (Hints)對于表的訪問 ,可以使用兩種 Hints. FULL 和 ROWID FULL hint 告訴 ORACLE 使用全表掃描的方式訪問指定表例如 :SELECT /*+ FULL(AC01) */ *FROM AC01 WHERE AAC001 = 10001000 。如果一個大表沒有被設(shè)定為緩存 (CACHED

45、) 表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留在 SGA 中 ,你就可以使用 CACHE hint 來告訴優(yōu)化器把數(shù)據(jù)保留在 SGA 中 . 通常 CACHE hint 和 FULL hint 一起使用 .例如 :SELECT /*+ FULL(AC01) CACHE(AC01)*/ * FROM AC01 。ROWID hint 告訴 ORACLE 使用 TABLE ACCESS BY ROWID 的操作訪問表 . 采用 TABLE ACCESS BY ROWID 的方式特別是當訪問大表的時候 , 使用這種方式 , 你需要知道 ROIWD 的值或者使用索引 .索引 hint 告訴 ORACLE

46、使用基于索引的掃描方式 . 你不必說明具體的索引名稱例如 :SELECT /*+index(IDX_AC01_AAC002)*/ aac001FROM AC01WHERE aac002=2101111111111111111。 在不使用 hint 的情況下 , 以上的查詢應(yīng)該也會使用索引 ,然而 ,如 果該索引的重復(fù)值過多而你的優(yōu)化器是 CBO, 優(yōu)化器就可能忽略索引 . 在這種情況下 , 你可以用 INDEX hint 強制 ORACLE 使用該索引 .ORACLE hints 還包括 ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HAS

47、H 等等 .使用 hint , 表示我們對 ORACLE 優(yōu)化器缺省的執(zhí)行路徑不滿意 ,需要手工修改 . 這是一個很有技巧性的工作 . 除非特定的情況，例如：數(shù)據(jù)轉(zhuǎn)換，其他情況最好不用 .2.5.12 表上存在過舊的分析 我們現(xiàn)在很多工程都存在性能問題，其中有很多種情況都是由于分析過舊導(dǎo)致 ORACLE 判斷索引級別和 資源成本上出現(xiàn)問題， 會導(dǎo)致 ORACLE 判斷錯誤不使用索引。 我個人覺得這是 ORACLE 不夠完善的地方。解決辦法：第一種辦法：刪除分析，停止 oracle10g 的自動分析，但不使用分析， oracle 訪問數(shù)據(jù)的 CPU 消耗就 過大。第二種辦法：重新分析，但過長時間

48、后，索引是否會再次失效，沒有驗證過。2.5.13 表上存在并行表上存在并行， ORACLE 判斷索引級別和資源成本上出現(xiàn)問題，會導(dǎo)致 ORACLE 判斷錯誤不使用索引。這個問題我不知道有什么好的處理辦法，從現(xiàn)場實際應(yīng)用速度角度比較，我還是選擇去掉并行，因為不使用索引進行全表掃描肯定是不能忍受的。2.5.14 關(guān)于索引建立 索引的使用是肯定會大大提高查詢的速度，但索引其實也是一種數(shù)據(jù)，它也是存放的用戶類型的表空間下 的，索引建立的越多越大，占用的空間也越大，從用戶的環(huán)境來說這也不是問題，但如果一個表有過多過 大的查詢，必然會影響 insert、delete 和 update 索引列的速度， 因為

49、這些操作改變了整個表的索引順序， oracle 需要進行調(diào)整， 這樣性能就下降了。 因此我們一定要合理的建立好有效的索引， 編程也要符合索引的規(guī)則， 而不能是索引符合編程的規(guī)則。案例：某工程數(shù)據(jù)轉(zhuǎn)換，采用游標循環(huán) insert 的方式，總共 2000 萬的數(shù)據(jù)，總共用了 4 個小時，原因就是目標表 里面有很多索引。解決方法是先刪除索引再執(zhí)行轉(zhuǎn)換腳本，結(jié)果不用1 小時就完成了，建立全部的索引不到半個小時。原因就是第一種方式每次 insert 都改變索引順序， 共執(zhí)行改變 2000 萬次， 而第二種方式整體上執(zhí)行索引順 序就一次。3. PLSQL 程序性能問題分析方法本章主要介紹怎樣找到出現(xiàn)性能問

50、題 PLSQL 程序或語句的方法。3.1 性能問題分析出現(xiàn)性能問題，我先要從整體進行分析，一般總體上會有幾種現(xiàn)象：整個系統(tǒng)運行速度都慢 在業(yè)務(wù)不忙的時候，所有模塊都慢 只有在業(yè)務(wù)繁忙的時候，所有模塊都慢 時快時慢個別業(yè)務(wù)模塊運行速度慢 在業(yè)務(wù)不忙的時候，該模塊就慢 只有在業(yè)務(wù)繁忙的時候，該模塊才慢 時快時慢一般導(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è)置不

51、合理等PLSQL 程序： plsql 不優(yōu)化、未使用索引、鎖表在不同現(xiàn)象下，可能導(dǎo)致性能問題的因素： 設(shè)置、ORACLE設(shè)置、PLSQL程序。如果在sqlplus下做查詢都慢，那么就和weblogic沒有關(guān)系。1.般來說，如果在不辦理業(yè)務(wù)的情況下，整個系統(tǒng)性能就慢的話，應(yīng)該和PLSQL 程序優(yōu)化是沒有關(guān)系的?？赡艿囊蛩貫橛布?、網(wǎng)絡(luò)、操作系統(tǒng)、其他軟件干擾、ORACLE 設(shè)置。2. 只有在業(yè)務(wù)繁忙的時候，整體系統(tǒng)性能都慢， 有可能的因素有硬件、操作系統(tǒng)設(shè)置、 WEBLOGIC3. 一般來說，如果在不辦理業(yè)務(wù)的情況下，個別業(yè)務(wù)模塊速度就慢的話，那么基本上就是PLSQL程序不優(yōu)化或未使用索引造成的。

52、WEBLOGIC 設(shè)置、oracle設(shè)置等4. 只有在業(yè)務(wù)繁忙的時候，個別模塊慢，有可能的因素有硬件、操作系統(tǒng)設(shè)置、ORACLE設(shè)置、PLSQL程序、鎖表。這里我們重點是說明 PLSQL優(yōu)化、索引優(yōu)化的問題，其他例如：硬件、網(wǎng)絡(luò)、操作系統(tǒng)和 因素我們不進行說明。PLSQL優(yōu)化、索引不使用的問題產(chǎn)生的影響：1. 對于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個表時，它訪問磁盤的時間和占用的吞吐量是很高的，這就導(dǎo)致系統(tǒng)IO長時間處于忙的狀態(tài)，導(dǎo)致整個系統(tǒng)性能下降。2. 對于某段不優(yōu)化的程序或語句頻繁或者全表掃描一個表時，其他的業(yè)務(wù)程序也訪問同一個表時， 速度將大大下降。3. 如果是更新表操作時間長，

53、還可能會導(dǎo)致鎖等待，導(dǎo)致會話堵塞，weblogic端也出現(xiàn)壓力問題,導(dǎo)致這個系統(tǒng)性能下降。我們一般根據(jù)這些現(xiàn)象、以及一些方法判斷，來初步分析產(chǎn)生性能問題的大致原因的范圍。不過對于這一 點，還是比較困難的，因為產(chǎn)生問題的原因是多種的，并且還有一定的關(guān)聯(lián)。下面的章節(jié)介紹我們已經(jīng)斷 定是PLSQL優(yōu)化、索引不使用的問題，我們通過什么方法來具體定位問題。3.2 Expain Plan分析索引使用在PL/SQL Developer等工具有一個 Expain Plan分析的功能，這個功能可以幫助我們分析SQL語句是否使用了索引、使用哪些索引和使用索引的效果。1. 選擇 explain plan 的窗口Pr

54、ogram Window Test Window$QL WindowReport WindowCommand WindowExplain Plan Windovtdiagram WindowViewMaterialized viewTableSequenceSynonymLibfaryDirectoryJobLieueQueue tableUserRoleProfileDatabase liik2. 在上面欄中輸入 SQL語句，然后點擊工具欄上的 EXECUTE執(zhí)行（或按 F8），就會在下面顯示Optmizergoal優(yōu)化器的默認方式 (也可手工選擇)，以及下面的解釋計劃，從解釋計劃上能看到哪個條 件語句使用了索引，哪個沒有使用；哪個表使用了索引，使用了哪個索引，哪些表是全