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

1、1. 前言1.1 目的性能測(cè)試是測(cè)試中比較重要的工作，性能測(cè)試應(yīng)分為壓力的測(cè)試和性能的測(cè)試，其中性能問(wèn)題中絕大部分都是由于程序編寫(xiě)的不合理、不規(guī)范造成的。本文檔說(shuō)明了程序中常見(jiàn)的不優(yōu)化的腳本編寫(xiě)，導(dǎo)致的性能問(wèn)題，并且在也描述了怎樣去跟蹤和解決程序上的性能問(wèn)題的方法。在最后一章里面描述了做一個(gè)白盒測(cè)試工具測(cè)試性能問(wèn)題的設(shè)計(jì)思想。1.2 文檔說(shuō)明本文檔只說(shuō)明PLSQL編寫(xiě)的優(yōu)化問(wèn)題，不包括ORACLE本身的性能優(yōu)化（內(nèi)存SGA、系統(tǒng)參數(shù)、表空間等）、操作系統(tǒng)的性能問(wèn)題和硬件的性能問(wèn)題。對(duì)于PLSQL程序優(yōu)化方面的內(nèi)容有很多，本文檔列出在我們實(shí)際工作中一些常見(jiàn)的情況。本文檔難免有不正確的地方，也需要

2、大家給予指正。本文檔舉例說(shuō)明的問(wèn)題語(yǔ)句不是實(shí)際程序中真正存在的，只是讓大家能看起來(lái)更容易理解，但這些語(yǔ)句也不代表在我們程序中其他部分語(yǔ)句不存在這些問(wèn)題。舉例說(shuō)明中的語(yǔ)句采用的是社保核心平臺(tái)的數(shù)據(jù)字典，在舉例描述中沒(méi)有標(biāo)明表名和字段名的含義，還需單獨(dú)參考。1.3 詞匯表詞匯名稱詞匯含義備注1.4 參考資料編號(hào)資料名稱作者日期出版單位1ORACLE SQL性能優(yōu)化系列232. PLSQL程序優(yōu)化原則2.1 導(dǎo)致性能問(wèn)題的內(nèi)在原因?qū)е孪到y(tǒng)性能出現(xiàn)問(wèn)題從系統(tǒng)底層分析也就是如下幾個(gè)原因：l CPU占用率過(guò)高，資源爭(zhēng)用導(dǎo)致等待l 內(nèi)存使用率過(guò)高，內(nèi)存不足需要磁盤虛擬內(nèi)存l IO占用率過(guò)高，磁盤訪問(wèn)需要等待

3、2.2 PLSQL優(yōu)化的核心思想PLSQL優(yōu)化實(shí)際上就是避免出現(xiàn)“導(dǎo)致性能問(wèn)題的內(nèi)在原因”，實(shí)際上編寫(xiě)程序，以及性能問(wèn)題跟蹤應(yīng)該本著這個(gè)核心思想去考慮和解決問(wèn)題。l PLSQL程序占用CPU的情況n 系統(tǒng)解析SQL語(yǔ)句執(zhí)行，會(huì)消耗CPU的使用n 運(yùn)算（計(jì)算）會(huì)消耗CPU的使用l PLSQL程序占用內(nèi)存的情況n 讀寫(xiě)數(shù)據(jù)都需要訪問(wèn)內(nèi)存n 內(nèi)存不足時(shí)，也會(huì)使用磁盤l PLSQL程序增大IO的情況n 讀寫(xiě)數(shù)據(jù)都需要訪問(wèn)磁盤IOn 讀取的數(shù)據(jù)越多，IO就越大大家都知道CPU現(xiàn)在都很高，計(jì)算速度非常快；訪問(wèn)內(nèi)存的速度也很快；但磁盤的訪問(wèn)相對(duì)前兩個(gè)相比速度就差的非常大了，因此PLSQL性能優(yōu)化的重點(diǎn)也就是

4、減少IO的瓶頸，換句話說(shuō)就是盡量減少IO的訪問(wèn)。性能的優(yōu)先級(jí)CPU->內(nèi)存->IO，影響性能的因素依次遞增。根據(jù)上面的分析，PLSQL優(yōu)化的核心思想為：1. 避免過(guò)多復(fù)雜的SQL腳本，減少系統(tǒng)的解析過(guò)程2. 避免過(guò)多的無(wú)用的計(jì)算，例如：死循環(huán)3. 避免浪費(fèi)內(nèi)存空間沒(méi)有必要的SQL腳本，導(dǎo)致內(nèi)存不足4. 內(nèi)存中計(jì)算和訪問(wèn)速度很快5. 盡可能的減少磁盤的訪問(wèn)的數(shù)據(jù)量，該原則是PLSQL優(yōu)化中重要思想。6. 盡可能的減少磁盤的訪問(wèn)的次數(shù)，該原則是PLSQL優(yōu)化中重要思想。下面的章節(jié)具體介紹常見(jiàn)影響性能的SQL語(yǔ)句情況。2.3 ORACLE優(yōu)化器ORACLE的優(yōu)化器：a. RULE (基于

5、規(guī)則) b. COST (基于成本) c. CHOOSE (選擇性) 設(shè)置缺省的優(yōu)化器,可以通過(guò)對(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-Based Optimizer) , 你必須經(jīng)常運(yùn)行analyze 命令,以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性. 如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(CHOOSE),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過(guò)anal

6、yze命令有關(guān). 如果table已經(jīng)被analyze過(guò), 優(yōu)化器模式將自動(dòng)成為CBO , 反之,數(shù)據(jù)庫(kù)將采用RULE形式的優(yōu)化器. 在缺省情況下,ORACLE采用CHOOSE優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器.在oracle10g前默認(rèn)的優(yōu)化模式是CHOOSE，10g默認(rèn)是ALL_ROWS，我不建議大家去改動(dòng)ORACLE的默認(rèn)優(yōu)化模式。2.4 PLSQL優(yōu)化主要說(shuō)明了在SQL編寫(xiě)上和PLSQL程序編寫(xiě)上可以優(yōu)化的地方。2.4.1 選擇最有效率的表名順序只在基于規(guī)則的優(yōu)化器ru

7、le中有效，目前我們oracle選擇的優(yōu)化器基本都不選擇rule，因此該問(wèn)題基本不會(huì)出現(xiàn)，但為了安全和規(guī)范起見(jiàn)，建議編程習(xí)慣采用該規(guī)則。ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在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)行合并.

8、例如: 表 ac01有 16,384 條記錄 表 ab01 有1 條記錄 選擇ab01作為基礎(chǔ)表 (好的方法) select count(*) from ac01,ab01 執(zhí)行時(shí)間0.96秒 選擇ac01作為基礎(chǔ)表 (不好的方法) select count(*) from ab01,ac01 執(zhí)行時(shí)間26.09秒 2.4.2 WHERE子句中的連接順序 ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫(xiě)在其他WHERE條件之前例如:(低效) SELECT ab01.aab001,ab02.aab051 FROM ab01,ab02 WHERE ab02.aae

9、140=31 AND ab01.aab001=ab02.aab001; (高效) SELECT ab01.aab001,ab02.aab051 FROM ab01,ab02 WHERE ab01.aab001=ab02.aab001 AND ab02.aae140=31;2.4.3 SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用 *' 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過(guò)程中, 會(huì)將'*' 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更

10、多的時(shí)間。2.4.4 用EXISTS替代IN 實(shí)際情況看，使用exists替換in效果不是很明顯，基本一樣。在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(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 ac0

11、2 where aab001=str_aab001 and aae140=31);注意使用distinct也會(huì)影響速度高效: 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列表相當(dāng)于or2.4.5 用NOT EXISTS替代NOT INOracle在10g之前版本not in都是最低效的語(yǔ)句，雖然在10g上not in做到了一

12、些改進(jìn)，但仍然還是存在一些問(wèn)題，因此我們一定要使用not exists來(lái)替代not in的寫(xiě)法。在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫(xiě)成NOT EXISTS. 例如: SELECT * FROM ac01 WHERE aab001 NOT IN (SELECT aab001 from ab01 where aab020=100);為了提高效率.改寫(xiě)為: SELECT * FROM ac01 WHERE not exists (SELECT 1

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

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

15、t ac01.aac001from ac02,ac01where ac02.aac001 = ac01.aac001and ac02.aae140='31'and ac01.aab001='100100'高效: select ac01.aac001from ac01where exists(select 1 from ac02 where aac001 = ac01.aac001and aae140='31')and ac01.aab001='100100'EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿

16、足后,立刻返回結(jié)果。因此如果不是特別研究和追求速度的話（例如：數(shù)據(jù)轉(zhuǎn)換），查詢一個(gè)表的數(shù)據(jù)需要關(guān)聯(lián)其他表的這種情況查詢，建議采用EXISTS的方式。2.4.8 減少對(duì)表的查詢?cè)搯?wèn)題是我們編程中出現(xiàn)過(guò)的問(wèn)題，請(qǐng)大家一定注意，并且該類問(wèn)題優(yōu)化可以帶來(lái)較大性能的提升。例如: 低效 cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11; cursor cur_kc24_zy isSelect akc260from kc24where akb020 =str_akb020and aka130=21

17、;for rec_mz in cur_kc24_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 loop if rec_kc24.aka130=11 then 門診處理. end if; if rec_kc24.aka130=21 then 住院處理.end if; e

18、nd loop;高效的做法使用同樣的條件（或者說(shuō)是索引）只訪問(wèn)一次磁盤，低效的做法訪問(wèn)了2次磁盤，這樣速度差別將近2倍。2.4.9 避免循環(huán)（游標(biāo)）里面嵌查詢游標(biāo)里面不能嵌入查詢(或者再嵌游標(biāo))，其實(shí)也不能有update delete等語(yǔ)句，只能有insert語(yǔ)句。但在實(shí)際的編程情況下是不可能完全避免的，但我們一定要盡量避免。該類問(wèn)題也是我們程序中出現(xiàn)過(guò)的問(wèn)題，該類問(wèn)題也可以大大提升程序效率，請(qǐng)大家一定注意。例如：低效：Cursor cur_ac04 is Select aac001,akc010 From ac04 Where aab001= prm_aab001;For rec_ac04

19、in cur_ac04 loop Select aac008 Into str_aac008 from ac01where aac001=rec_ac04.aac001; if str_aac008=1 then n_jfje := rec_ac04.akc010*0.08; end if; if str_aac008=2 then n_jfje := rec_ac04.akc010*0.1; end if;End loop;高效：Cursor cur_ac04 is Select ac01.aac001,ac04.akc010,ac01.aac008 From ac04,ac01 Where

20、 ac04.aac001=ac01.aac001and aab001= prm_aab001;For rec_ac04 in cur_ac04 loop if rec.aac008=1 then n_jfje := rec_ac04.akc010*0.08; end if; if rec.aac008=2 then n_jfje := rec_ac04.akc010*0.1; end if;end loop;優(yōu)化的方法是盡量把游標(biāo)循環(huán)中的查詢語(yǔ)句放到游標(biāo)查詢中一起查詢出來(lái)，這樣相當(dāng)于只訪問(wèn)了1次磁盤讀到內(nèi)存；如果放到游標(biāo)中的話，假如游標(biāo)有100萬(wàn)數(shù)據(jù)量，那么程序需要100萬(wàn)次磁盤，可以想象浪費(fèi)

21、了多少IO的訪問(wèn)。如果在程序編寫(xiě)上沒(méi)有辦法避免游標(biāo)中有查詢語(yǔ)句的話（一般情況是可以避免的），那么也要保證游標(biāo)中的查詢使用的索引（即查詢速度非?？欤?，例如：游標(biāo)100萬(wàn)數(shù)據(jù)量，游標(biāo)中的查詢語(yǔ)句執(zhí)行需要0.02秒，從這個(gè)速度上來(lái)說(shuō)是很快的，但總體上看100萬(wàn)*0.02秒=2萬(wàn)秒=5小時(shí)33分鐘，如果寫(xiě)一個(gè)不夠優(yōu)化的語(yǔ)句需要1秒，那么需要幾天能執(zhí)行完呢？2.4.10 盡量用union all替換unionUnion會(huì)去掉重復(fù)的記錄，會(huì)有排序的動(dòng)作，會(huì)浪費(fèi)時(shí)間。因此在沒(méi)有重復(fù)記錄的情況下或可以允許有重復(fù)記錄的話，要盡量采用union all來(lái)關(guān)聯(lián)。2.4.11 使用DECODE函數(shù)來(lái)減少處理時(shí)間使用D

22、ECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表. 例如: （低效)select count(1) from ac01 where aab001=100001 and aac008=1; select count(1) from ac01 where aab001=100001 and aac008=2;（低效)Select count(1),aac008 From ac01Where aab001=100001 and aac008 in (1,2)group by aac008;（高效）select count(decode(aac008,1,1,null) zz,count(de

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

24、ac040),ac01.aab001 from ac04,ac01 where ac04.aac001=ac01.aac001 and ac01.aab001='1000000370' group by ac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;高效：select ac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001 from (select aac001,sum(aac040) gzze from ac04 group by aac001) ac04,ac01 where

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

26、. 例如: 低效: SELECT aac008,count(1) FROM ac01GROUP BY aac008 HAVING aac008 in (1,2); 高效 SELECT aac008,count(1) FROM ac01Where aac008 in (1,2)GROUP BY aac008 ;HAVING 中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫(xiě)在WHERE子句中2.4.15 使用表的別名(Alias) 當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Co

27、lumn歧義引起的語(yǔ)法錯(cuò)誤.2.4.16 刪除重復(fù)記錄一般數(shù)據(jù)轉(zhuǎn)換的程序經(jīng)常會(huì)使用到該方法。最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID) DELETE FROM ac01 aWHERE 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)注這一點(diǎn)。1. Commit執(zhí)行也是有時(shí)間的，不過(guò)時(shí)間特別短，但提交頻率特別大，必然也會(huì)浪費(fèi)時(shí)間。2. commit可以釋放資源，在大量數(shù)據(jù)更新時(shí)，必須及時(shí)提交。a. 回滾

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

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

30、且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。但查詢插入的數(shù)據(jù)量過(guò)大必然會(huì)占用更多的內(nèi)存和undo表空間，只能在插入完成后提交，這樣資源不能釋放，會(huì)導(dǎo)致回滾表空間不足和快照過(guò)舊的問(wèn)題，另外一旦失敗需要全部回滾。因此建議小數(shù)據(jù)量（例如：300萬(wàn)以下）的導(dǎo)入采用該種方式。l Insert /*+append */ into select方式該種方式同上種方式，不過(guò)由于有append的提示，這種語(yǔ)句不走回滾段直接插入數(shù)據(jù)文件，速度非?？?。注意系統(tǒng)開(kāi)發(fā)編程不能使用該種方式，數(shù)據(jù)轉(zhuǎn)換可以靈活使用。l Cursor方式定義游標(biāo)，然后逐行進(jìn)行插入，然后定量提交。例如：Cusor cur_

31、ac20 is Select * from ac20;.n_count :=0;For rec_ac20 in cur_ac20 loop Insert into ac20_bak (aac001,.) Values (rec_ac20.aac001, .); If n_count :=100000 then Commit; n_count :=0; End if;End loop;l 批綁定的方式通過(guò)游標(biāo)查詢將數(shù)據(jù)逐行寫(xiě)到數(shù)組里（實(shí)際上就是內(nèi)存），然后通過(guò)批綁定的語(yǔ)句forall in insert intovalues;將內(nèi)存的數(shù)據(jù)一次寫(xiě)入到數(shù)據(jù)文件中。相比cursor的方式減少了對(duì)io的

32、訪問(wèn)次數(shù)，提高了速度，但注意內(nèi)存別溢出了。2.5 索引使用優(yōu)化在實(shí)際的應(yīng)用系統(tǒng)中索引問(wèn)題導(dǎo)致性能問(wèn)題可能能占到80%，在程序優(yōu)化上索引問(wèn)題是需要我們特別關(guān)注的。本節(jié)主要描述什么情況索引會(huì)不生效。2.5.1 避免在索引列上使用函數(shù)或運(yùn)算這個(gè)問(wèn)題是在我們實(shí)際編程中出現(xiàn)過(guò)的，請(qǐng)大家一定注意。在索引列上使用函數(shù)或運(yùn)算，查詢條件都不會(huì)使用索引。例如：不使用索引Select * from ka02 where aka060=10001000 and to_char(aae030,yyyymm)=200801;使用索引Select * from ka02 where aka060=10001000 and

33、aae030=to_date(200801,yyyymm);不使用索引Select * from ka02 where aka060=10001000 and aae031+1=sysdate;使用索引Select * from ac04 where aac001=10001000 and aae031=sysdate -1;如果一定要對(duì)使用函數(shù)的列啟用索引, ORACLE新的功能: 基于函數(shù)的索引(Function-Based Index) CREATE INDEX IDX_KA02_AKA066 ON KA02 (UPPER(AKA066); /*建立基于函數(shù)的索引*/ SELECT *

34、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不會(huì)使查詢條件使用索引。對(duì)于!=這樣的判斷也是不能使用索引的，索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中例如: 低效: (

35、這里,不使用索引) select *From ac02Where not aab019=10; 高效: (這里,使用了索引) select *From ac02Where aab019 in (20,30);2.5.4 用>=替代>雖然效果不是特別明顯，但建議采用這種方式低效: SELECT *FROM ab01 WHERE aab019 > 10 高效: SELECT * FROM ab01 WHERE aab019 >=20 兩者的區(qū)別在于, 前者DBMS首先定位到aab019=10的記錄并且向前掃描到第一個(gè)aab019大于10的記錄,而后者DBMS將直接跳到第一個(gè)

36、aab019等于10的記錄2.5.5 避免在索引列上使用IS NULL和IS NOT NULL對(duì)于索引列使用is null或is not null不會(huì)使用上索引 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引. 舉例: 低效: (索引失效) select * from ab01 where aab019 is not null;高效: (索引有效) select * from ab01 where aab019 in(10,20,30);在實(shí)際開(kāi)發(fā)中，對(duì)于這類的問(wèn)題很難避免，如果不是特別影響速度或者要求速度的，可以忽略。2.5.6 帶通配符（%）的

37、like語(yǔ)句%在常量前面索引就不會(huì)使用。例如：不使用索引Select * from ac01 where aac002 like %210104;Select * from ac01 where aac002 like %210104%;使用索引Select * from ac01 where aac002 like 210104%;2.5.7 總是使用索引的第一個(gè)列如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引。例如：Ac02的復(fù)合索引：aac001、aae140、aae041Select * from ac02 where aae140=31

38、 and aae041=200801; -不會(huì)使用索引Select * from ac02 where aac001=10001000; -可以使用索引如果不使用索引第一列基本上不會(huì)使用索引，使用索引要按照索引的順序使用，另外使用復(fù)合索引的列越多，查詢的速度就越快2.5.8 多個(gè)平等的索引 當(dāng)SQL語(yǔ)句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí), ORACLE會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并, 檢索出僅對(duì)全部索引有效的記錄. 在ORACLE選擇執(zhí)行路徑時(shí),唯一性索引的等級(jí)高于非唯一性索引. 然而這個(gè)規(guī)則只有 當(dāng)WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類

39、相比較. 這種子句在優(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)行使用。 2.5.9 不明確的索引等級(jí)當(dāng)ORACLE無(wú)法判斷索引的等級(jí)高低差別,優(yōu)化器將只使用一個(gè)索引,它就是在WHERE子句中被列在最前面的.2.5.10 自動(dòng)選擇索引如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性 在這種情

40、況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引2.5.11 使用提示(Hints)對(duì)于表的訪問(wèn),可以使用兩種Hints. FULL 和 ROWID FULL hint 告訴ORACLE使用全表掃描的方式訪問(wèn)指定表. 例如: SELECT /*+ FULL(AC01) */ * FROM AC01 WHERE AAC001 = 10001000; 如果一個(gè)大表沒(méi)有被設(shè)定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留 在SGA中,你就可以使用CACHE hint 來(lái)告訴優(yōu)化器把數(shù)據(jù)保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用. 例如: SEL

41、ECT /*+ FULL(AC01) CACHE(AC01)*/ * FROM AC01; ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問(wèn)表. 采用TABLE ACCESS BY ROWID的方式特別是當(dāng)訪問(wèn)大表的時(shí)候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說(shuō)明具體的索引名稱 例如: SELECT /*+index(IDX_AC01_AAC002)*/ aac001在不使用hint的情況下, 以上的查詢應(yīng)該也會(huì)使用索引,然而,如果該索引的重復(fù)值過(guò)多而你的優(yōu)化器是CBO

42、, 優(yōu)化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強(qiáng)制ORACLE使用該索引. ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等. 使用hint , 表示我們對(duì)ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改. 這是一個(gè)很有技巧性的工作. 除非特定的情況，例如：數(shù)據(jù)轉(zhuǎn)換，其他情況最好不用. 2.5.12 表上存在過(guò)舊的分析我們現(xiàn)在很多項(xiàng)目都存在性能問(wèn)題，其中有很多種情況都是由于分析過(guò)舊導(dǎo)致ORACLE判斷索引級(jí)別和資源成本上出現(xiàn)問(wèn)題，會(huì)導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。

43、我個(gè)人覺(jué)得這是ORACLE不夠完善的地方。解決辦法：第一種辦法：刪除分析，停止oracle10g的自動(dòng)分析，但不使用分析，oracle訪問(wèn)數(shù)據(jù)的CPU消耗就過(guò)大。第二種辦法：重新分析，但過(guò)長(zhǎng)時(shí)間后，索引是否會(huì)再次失效，沒(méi)有驗(yàn)證過(guò)。2.5.13 表上存在并行表上存在并行，ORACLE判斷索引級(jí)別和資源成本上出現(xiàn)問(wèn)題，會(huì)導(dǎo)致ORACLE判斷錯(cuò)誤不使用索引。這個(gè)問(wèn)題我不知道有什么好的處理辦法，從現(xiàn)場(chǎng)實(shí)際應(yīng)用速度角度比較，我還是選擇去掉并行，因?yàn)椴皇褂盟饕M(jìn)行全表掃描肯定是不能忍受的。2.5.14 關(guān)于索引建立索引的使用是肯定會(huì)大大提高查詢的速度，但索引其實(shí)也是一種數(shù)據(jù)，它也是存放的用戶類型的表空間下

44、的，索引建立的越多越大，占用的空間也越大，從用戶的環(huán)境來(lái)說(shuō)這也不是問(wèn)題，但如果一個(gè)表有過(guò)多過(guò)大的查詢，必然會(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萬(wàn)的數(shù)據(jù)，總共用了4個(gè)小時(shí)，原因就是目標(biāo)表里面有很多索引。解決方法是先刪除索引再執(zhí)行轉(zhuǎn)換腳本，結(jié)果不用1小時(shí)就完成了，建立全部的索引不到半個(gè)小時(shí)。原因就是第一種方式每次insert都改變索引順序，共

45、執(zhí)行改變2000萬(wàn)次，而第二種方式整體上執(zhí)行索引順序就一次。3. PLSQL程序性能問(wèn)題分析方法本章主要介紹怎樣找到出現(xiàn)性能問(wèn)題PLSQL程序或語(yǔ)句的方法。3.1 性能問(wèn)題分析出現(xiàn)性能問(wèn)題，我先要從整體進(jìn)行分析，一般總體上會(huì)有幾種現(xiàn)象：l 整個(gè)系統(tǒng)運(yùn)行速度都慢n 在業(yè)務(wù)不忙的時(shí)候，所有模塊都慢n 只有在業(yè)務(wù)繁忙的時(shí)候，所有模塊都慢n 時(shí)快時(shí)慢l 個(gè)別業(yè)務(wù)模塊運(yùn)行速度慢n 在業(yè)務(wù)不忙的時(shí)候，該模塊就慢n 只有在業(yè)務(wù)繁忙的時(shí)候，該模塊才慢n 時(shí)快時(shí)慢一般導(dǎo)致系統(tǒng)性能慢的因素：l 硬件：客戶端、服務(wù)器CPU、內(nèi)存和存儲(chǔ)設(shè)備配置不符合應(yīng)用系統(tǒng)要求l 網(wǎng)絡(luò)：網(wǎng)速低、丟包l 操作系統(tǒng)參數(shù)設(shè)置：參數(shù)設(shè)置不合

46、理l 受到其他軟件干擾：例如：防火墻、病毒l Weblogic設(shè)置：參數(shù)設(shè)置不合理l Oracle設(shè)置：內(nèi)存、表空間、redolog、系統(tǒng)參數(shù)設(shè)置不合理等l PLSQL程序：plsql不優(yōu)化、未使用索引、鎖表在不同現(xiàn)象下，可能導(dǎo)致性能問(wèn)題的因素：1. 一般來(lái)說(shuō)，如果在不辦理業(yè)務(wù)的情況下，整個(gè)系統(tǒng)性能就慢的話，應(yīng)該和PLSQL程序優(yōu)化是沒(méi)有關(guān)系的。可能的因素為硬件、網(wǎng)絡(luò)、操作系統(tǒng)、其他軟件干擾、ORACLE設(shè)置。2. 只有在業(yè)務(wù)繁忙的時(shí)候，整體系統(tǒng)性能都慢，有可能的因素有硬件、操作系統(tǒng)設(shè)置、WEBLOGIC設(shè)置、ORACLE設(shè)置、PLSQL程序。如果在sqlplus下做查詢都慢，那么就和webl

47、ogic沒(méi)有關(guān)系。3. 一般來(lái)說(shuō)，如果在不辦理業(yè)務(wù)的情況下，個(gè)別業(yè)務(wù)模塊速度就慢的話，那么基本上就是PLSQL程序不優(yōu)化或未使用索引造成的。4. 只有在業(yè)務(wù)繁忙的時(shí)候，個(gè)別模塊慢，有可能的因素有硬件、操作系統(tǒng)設(shè)置、WEBLOGIC設(shè)置、ORACLE設(shè)置、PLSQL程序、鎖表。這里我們重點(diǎn)是說(shuō)明PLSQL優(yōu)化、索引優(yōu)化的問(wèn)題，其他例如：硬件、網(wǎng)絡(luò)、操作系統(tǒng)和oracle設(shè)置等因素我們不進(jìn)行說(shuō)明。PLSQL優(yōu)化、索引不使用的問(wèn)題產(chǎn)生的影響：1. 對(duì)于某段不優(yōu)化的程序或語(yǔ)句頻繁或者全表掃描一個(gè)表時(shí)，它訪問(wèn)磁盤的時(shí)間和占用的吞吐量是很高的，這就導(dǎo)致系統(tǒng)IO長(zhǎng)時(shí)間處于忙的狀態(tài)，導(dǎo)致整個(gè)系統(tǒng)性能下降。2.

48、 對(duì)于某段不優(yōu)化的程序或語(yǔ)句頻繁或者全表掃描一個(gè)表時(shí)，其他的業(yè)務(wù)程序也訪問(wèn)同一個(gè)表時(shí)，速度將大大下降。3. 如果是更新表操作時(shí)間長(zhǎng)，還可能會(huì)導(dǎo)致鎖等待，導(dǎo)致會(huì)話堵塞，weblogic端也出現(xiàn)壓力問(wèn)題，導(dǎo)致這個(gè)系統(tǒng)性能下降。我們一般根據(jù)這些現(xiàn)象、以及一些方法判斷，來(lái)初步分析產(chǎn)生性能問(wèn)題的大致原因的范圍。不過(guò)對(duì)于這一點(diǎn)，還是比較困難的，因?yàn)楫a(chǎn)生問(wèn)題的原因是多種的，并且還有一定的關(guān)聯(lián)。下面的章節(jié)介紹我們已經(jīng)斷定是PLSQL優(yōu)化、索引不使用的問(wèn)題，我們通過(guò)什么方法來(lái)具體定位問(wèn)題。3.2 Expain Plan分析索引使用在PL/SQL Developer等工具有一個(gè)Expain Plan分析的功能，這

49、個(gè)功能可以幫助我們分析SQL語(yǔ)句是否使用了索引、使用哪些索引和使用索引的效果。1. 選擇explain plan的窗口2. 在上面欄中輸入SQL語(yǔ)句，然后點(diǎn)擊工具欄上的EXECUTE執(zhí)行（或按F8），就會(huì)在下面顯示Optmizergoal優(yōu)化器的默認(rèn)方式（也可手工選擇），以及下面的解釋計(jì)劃，從解釋計(jì)劃上能看到哪個(gè)條件語(yǔ)句使用了索引，哪個(gè)沒(méi)有使用；哪個(gè)表使用了索引，使用了哪個(gè)索引，哪些表是全表掃描的(TABLE ACCESS FULL)3. 分析內(nèi)容說(shuō)明：l COST：根據(jù)優(yōu)化程序的基于開(kāi)銷的方法所估計(jì)出的操作開(kāi)銷值對(duì)于使用基于規(guī)則方法的語(yǔ)句該列為空該列值沒(méi)有特定的測(cè)量單位它只是一個(gè)用于比較執(zhí)行

50、規(guī)劃開(kāi)銷大小的權(quán)重值l Cardinality：根據(jù)基于開(kāi)銷的方法對(duì)操作所訪問(wèn)行數(shù)的估計(jì)值l Bytes：根據(jù)基于開(kāi)銷的方法對(duì)操作所訪問(wèn)字節(jié)的估計(jì)l 通過(guò)設(shè)置，我們還能看到更多的信息，例如：CPU使用、時(shí)間等等全表掃描的(TABLE ACCESS FULL)肯定是速度慢的，如果是大數(shù)據(jù)量的表，那么這個(gè)語(yǔ)句是絕對(duì)影響性能的。另外使用了索引也不一定性能就高，因?yàn)樗饕褂靡灿行实那闆r，下面列出索引常見(jiàn)的使用類型：1. INDEX UNIQUE SCAN：唯一索引掃描，速度最快2. INDEX RANGE SCAN：范圍索引掃描，使用這個(gè)索引時(shí)，就需要看COST、Cardinality、Bytes的

51、大小了，如果特別大，有時(shí)候還可能速度低于全表掃描的速度。我們?cè)谥勒Z(yǔ)句有問(wèn)題，或者我們對(duì)語(yǔ)句進(jìn)行優(yōu)化時(shí)，這個(gè)工具是非常有用的。3.3 TOPSQL分析程序有性能問(wèn)題的時(shí)候，我們是不知道哪些語(yǔ)句存在性能的問(wèn)題，尤其你不是開(kāi)發(fā)人員。幸好有一些工具可以幫助我們找到這些存在性能問(wèn)題的語(yǔ)句。Toad工具和ORACLE9I的企業(yè)管理控制臺(tái)工具可以捕獲到這些問(wèn)題語(yǔ)句（oracle10g中em的功能不夠好）。在TOAD和ORACLE9I工具中可以查詢到TOPSQL頂級(jí)SQL的內(nèi)容，通過(guò)CPU、IO吞吐量、占用時(shí)間等信息的排序，我們可以找到最影響系統(tǒng)性能的語(yǔ)句，通過(guò)分析我們可以看到這些語(yǔ)句的解釋計(jì)劃。根據(jù)解釋計(jì)

52、劃，我們可以進(jìn)行語(yǔ)句的優(yōu)化，我們知道語(yǔ)句后，我們通過(guò)plsql的搜索功能就知道存在問(wèn)題語(yǔ)句的程序了。這個(gè)辦法使用有個(gè)前提條件就是這些問(wèn)題語(yǔ)句在系統(tǒng)上運(yùn)行過(guò)，并且沒(méi)有間隔過(guò)長(zhǎng)的時(shí)間，因此最好是在實(shí)際出現(xiàn)性能問(wèn)題的ORACLE上不斷的去監(jiān)控，才能捕獲到最全的問(wèn)題語(yǔ)句。1. ORACLE9i企業(yè)管理控制臺(tái)工具的topsql2. 系統(tǒng)運(yùn)行中的所有TOPSQL3. 分析具體的SQL語(yǔ)句4. Sql分析的解釋計(jì)劃3.4 針對(duì)性語(yǔ)句搜索TOPSQL分析也只能是找到未使用索引的語(yǔ)句（實(shí)際上這一點(diǎn)基本就足夠解決性能的問(wèn)題了），但是對(duì)于2.4章節(jié)中的很多內(nèi)容，沒(méi)有辦法進(jìn)行捕獲。我們可以采用針對(duì)性語(yǔ)句搜索，來(lái)盡量找到一些問(wèn)題語(yǔ)句進(jìn)行優(yōu)化。在PLSQL工具中有一個(gè)Find DB Object的功能，可以進(jìn)行搜索。我們可以搜索的關(guān)鍵特征信息：l NOT INl UNIONl GROUP BYl ORDER BYl > 、 < 、<>l Like %l From tab 、Update tab、delete tab，其中tab是數(shù)據(jù)量特別大的表，我們可以針對(duì)性的檢查對(duì)大表訪