1、PLSQL程序優化和性能測試方法1. 前言1.1 目的性能測試是測試中比較重要的工作，性能測試應分為壓力的測試和性能的測試，其中性能問題中絕大部分都是由于程序編寫的不合理、不規范造成的。本文檔說明了程序中常見的不優化的腳本編寫，導致的性能問題，并且在也描述了怎樣去跟蹤和解決程序上的性能問題的方法。在最后一章里面描述了做一個白盒測試工具測試性能問題的設計思想。1.2 文檔說明本文檔只說明PLSQL編寫的優化問題，不包括ORACLE本身的性能優化（內存SGA、系統參數、表空間等）、操作系統的性能問題和硬件的性能問題。對于PLSQL程序優化方面的內容有很多，本文檔列出在我們實際工作中一些常見的情況。

2、本文檔難免有不正確的地方，也需要大家給予指正。本文檔舉例說明的問題語句不是實際程序中真正存在的，只是讓大家能看起來更容易理解，但這些語句也不代表在我們程序中其他部分語句不存在這些問題。舉例說明中的語句采用的是社保核心平臺的數據字典，在舉例描述中沒有標明表名和字段名的含義，還需單獨參考。1.3 詞匯表詞匯名稱詞匯含義備注1.4 參考資料編號資料名稱作者日期出版單位1ORACLE SQL性能優化系列232. PLSQL程序優化原則2.1 導致性能問題的內在原因導致系統性能出現問題從系統底層分析也就是如下幾個原因：l CPU占用率過高，資源爭用導致等待l 內存使用率過高，內存不足需要磁盤虛擬內存l

3、IO占用率過高，磁盤訪問需要等待2.2 PLSQL優化的核心思想PLSQL優化實際上就是避免出現“導致性能問題的內在原因”，實際上編寫程序，以及性能問題跟蹤應該本著這個核心思想去考慮和解決問題。l PLSQL程序占用CPU的情況n 系統解析SQL語句執行，會消耗CPU的使用n 運算（計算）會消耗CPU的使用l PLSQL程序占用內存的情況n 讀寫數據都需要訪問內存n 內存不足時，也會使用磁盤l PLSQL程序增大IO的情況n 讀寫數據都需要訪問磁盤IOn 讀取的數據越多，IO就越大大家都知道CPU現在都很高，計算速度非常快；訪問內存的速度也很快；但磁盤的訪問相對前兩個相比速度就差的非常大了，因

4、此PLSQL性能優化的重點也就是減少IO的瓶頸，換句話說就是盡量減少IO的訪問。性能的優先級CPU->內存->IO，影響性能的因素依次遞增。根據上面的分析，PLSQL優化的核心思想為：1. 避免過多復雜的SQL腳本，減少系統的解析過程2. 避免過多的無用的計算，例如：死循環3. 避免浪費內存空間沒有必要的SQL腳本，導致內存不足4. 內存中計算和訪問速度很快5. 盡可能的減少磁盤的訪問的數據量，該原則是PLSQL優化中重要思想。6. 盡可能的減少磁盤的訪問的次數，該原則是PLSQL優化中重要思想。下面的章節具體介紹常見影響性能的SQL語句情況。2.3 ORACLE優化器ORACLE

5、的優化器：a. RULE (基于規則) b. COST (基于成本) c. CHOOSE (選擇性) 設置缺省的優化器,可以通過對init.ora文件中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋. 為了使用基于成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運行analyze 命令,以增加數據庫中的對象統計信息(object statistics)的準確性. 如果數據庫的優化器模式設置為選擇性(CHOOSE),那么實際的

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

7、表名順序只在基于規則的優化器rule中有效，目前我們oracle選擇的優化器基本都不選擇rule，因此該問題基本不會出現，但為了安全和規范起見，建議編程習慣采用該規則。ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄

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

9、2 WHERE ab02.aae140=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子句中避免使用 * 當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 *' 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數

10、據字典完成的, 這意味著將耗費更多的時間。2.4.4 用EXISTS替代IN 實際情況看，使用exists替換in效果不是很明顯，基本一樣。在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用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

11、 aac001 from 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的常量列表是優化的(例如：aab019 in (20,30)，不用exists替換；in列表相當于or2.4.5 用NOT EXISTS替代NOT INOracle在10g之前版本not in都是最低效的語句，雖

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

13、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 ac02 where aac001=ac01.aac001 and aab001=ac01.aab001 and aae140='31' and aae041='200801');高效：SELECT

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

15、: select distinct 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 使查詢更為迅速,因為RDBM

16、S核心模塊將在子查詢的條件一旦滿足后,立刻返回結果。因此如果不是特別研究和追求速度的話（例如：數據轉換），查詢一個表的數據需要關聯其他表的這種情況查詢，建議采用EXISTS的方式。2.4.8 減少對表的查詢該問題是我們編程中出現過的問題，請大家一定注意，并且該類問題優化可以帶來較大性能的提升。例如: 低效 cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11; cursor cur_kc24_zy isSelect akc260from kc24where akb020 =str_akb

17、020and aka130=21;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 the

18、n 住院處理.end if; end loop;高效的做法使用同樣的條件（或者說是索引）只訪問一次磁盤，低效的做法訪問了2次磁盤，這樣速度差別將近2倍。2.4.9 避免循環（游標）里面嵌查詢游標里面不能嵌入查詢(或者再嵌游標)，其實也不能有update delete等語句，只能有insert語句。但在實際的編程情況下是不可能完全避免的，但我們一定要盡量避免。該類問題也是我們程序中出現過的問題，該類問題也可以大大提升程序效率，請大家一定注意。例如：低效：Cursor cur_ac04 is Select aac001,akc010 From ac04 Where aab001= prm_aab0

19、01;For rec_ac04 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

20、 ac04,ac01 Where 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;優化的方法是盡量把游標循環中的查詢語句放到游標查詢中一起查詢出來，這樣相當于只訪問了1次磁盤讀到內存；如果放到游標中的話，假如游標有100萬數據量，那么程序

21、需要100萬次磁盤，可以想象浪費了多少IO的訪問。如果在程序編寫上沒有辦法避免游標中有查詢語句的話（一般情況是可以避免的），那么也要保證游標中的查詢使用的索引（即查詢速度非常快），例如：游標100萬數據量，游標中的查詢語句執行需要0.02秒，從這個速度上來說是很快的，但總體上看100萬*0.02秒=2萬秒=5小時33分鐘，如果寫一個不夠優化的語句需要1秒，那么需要幾天能執行完呢？2.4.10 盡量用union all替換unionUnion會去掉重復的記錄，會有排序的動作，會浪費時間。因此在沒有重復記錄的情況下或可以允許有重復記錄的話，要盡量采用union all來關聯。2.4.11 使用DE

22、CODE函數來減少處理時間使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表. 例如: （低效)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,n

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

24、c01.aac003,sum(aac040),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)

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

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 中的條件一般用于對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中2.4.15 使用表的別名(Alias) 當在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上.這樣一來,就可

27、以減少解析的時間并減少那些由Column歧義引起的語法錯誤.2.4.16 刪除重復記錄一般數據轉換的程序經常會使用到該方法。最高效的刪除重復記錄方法 ( 因為使用了ROWID) 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使用數據轉換的程序需要關注這一點。1. Commit執行也是有時間的，不過時間特別短，但提交頻率特別大，必然也會浪費時間。2. commit可以釋放資源，在大量數據

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

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

30、01;由于是一次查詢一次插入，并且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。但查詢插入的數據量過大必然會占用更多的內存和undo表空間，只能在插入完成后提交，這樣資源不能釋放，會導致回滾表空間不足和快照過舊的問題，另外一旦失敗需要全部回滾。因此建議小數據量（例如：300萬以下）的導入采用該種方式。l Insert /*+append */ into select方式該種方式同上種方式，不過由于有append的提示，這種語句不走回滾段直接插入數據文件，速度非常快。注意系統開發編程不能使用該種方式，數據轉換可以靈活使用。l Cursor方式定義游標，然后逐行進行插入，然后定量

31、提交。例如：Cusor cur_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 批綁定的方式通過游標查詢將數據逐行寫到數組里（實際上就是內存），然后通過批綁定的語句forall in insert intovalues;將內存的數據一次寫入到數據文件中。相比

32、cursor的方式減少了對io的訪問次數，提高了速度，但注意內存別溢出了。2.5 索引使用優化在實際的應用系統中索引問題導致性能問題可能能占到80%，在程序優化上索引問題是需要我們特別關注的。本節主要描述什么情況索引會不生效。2.5.1 避免在索引列上使用函數或運算這個問題是在我們實際編程中出現過的，請大家一定注意。在索引列上使用函數或運算，查詢條件都不會使用索引。例如：不使用索引Select * from ka02 where aka060=10001000 and to_char(aae030,yyyymm)=200801;使用索引Select * from ka02 where aka0

33、60=10001000 and 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;如果一定要對使用函數的列啟用索引, ORACLE新的功能: 基于函數的索引(Function-Based Index) CREATE INDEX IDX_KA02_AKA066 ON KA02 (UPPER(AKA066); /*建立基于函

34、數的索引*/ 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不會使查詢條件使用索引。對于!=這樣的判斷也是不能使用索引的，索引只能告訴你什么存在于表中, 而不能告訴你什

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 兩者的區別在于, 前者DBMS首先定位到aab019=10的記錄并且向前掃描到第一個aab019大于10的記錄

36、,而后者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);在實際開發中，對于這類的問題很難避免，如果不是特別影響速度或者要求速度的，可以忽

37、略。2.5.6 帶通配符（%）的like語句%在常量前面索引就不會使用。例如：不使用索引Select * from ac01 where aac002 like %210104;Select * from ac01 where aac002 like %210104%;使用索引Select * from ac01 where aac002 like 210104%;2.5.7 總是使用索引的第一個列如果索引是建立在多個列上, 只有在它的第一個列被where子句引用時,優化器才會選擇使用該索引。例如：Ac02的復合索引：aac001、aae140、aae041Select * from ac02

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

39、有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的. 如果不同表中兩個相同等級的索引將被引用, FROM子句中表的順序將決定哪個會被率先使用. FROM子句中最后的表的索引將有最高的優先級. 如果同一表中有兩個相同等級的索引被引用，oracle會分析最有效的索引去引用，其他的索引不會使用，如果這些相同等級的索引效果差不多，oracle可能會自動合并進行使用。 2.5.9 不明確的索引等級當ORACLE無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.2.5.10 自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性

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

41、nt 一起使用. 例如: SELECT /*+ FULL(AC01) CACHE(AC01)*/ * FROM AC01; ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表. 采用TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說明具體的索引名稱 例如: SELECT /*+index(IDX_AC01_AAC002)*/ aac001 FROM AC01 WHERE aac002='2101111

42、111111111111'在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重復值過多而你的優化器是CBO, 優化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引. ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等. 使用hint , 表示我們對ORACLE優化器缺省的執行路徑不滿意,需要手工修改. 這是一個很有技巧性的工作. 除非特定的情況，例如：數據轉換，其他情況最好不用. 2.5.12 表上存在過舊的分析我們現在很多項

43、目都存在性能問題，其中有很多種情況都是由于分析過舊導致ORACLE判斷索引級別和資源成本上出現問題，會導致ORACLE判斷錯誤不使用索引。我個人覺得這是ORACLE不夠完善的地方。解決辦法：第一種辦法：刪除分析，停止oracle10g的自動分析，但不使用分析，oracle訪問數據的CPU消耗就過大。第二種辦法：重新分析，但過長時間后，索引是否會再次失效，沒有驗證過。2.5.13 表上存在并行表上存在并行，ORACLE判斷索引級別和資源成本上出現問題，會導致ORACLE判斷錯誤不使用索引。這個問題我不知道有什么好的處理辦法，從現場實際應用速度角度比較，我還是選擇去掉并行，因為不使用索引進行全表掃

44、描肯定是不能忍受的。2.5.14 關于索引建立索引的使用是肯定會大大提高查詢的速度，但索引其實也是一種數據，它也是存放的用戶類型的表空間下的，索引建立的越多越大，占用的空間也越大，從用戶的環境來說這也不是問題，但如果一個表有過多過大的查詢，必然會影響insert、delete和update索引列的速度，因為這些操作改變了整個表的索引順序，oracle需要進行調整，這樣性能就下降了。因此我們一定要合理的建立好有效的索引，編程也要符合索引的規則，而不能是索引符合編程的規則。案例：某項目數據轉換，采用游標循環insert的方式，總共2000萬的數據，總共用了4個小時，原因就是目標表里面有很多索引。解

45、決方法是先刪除索引再執行轉換腳本，結果不用1小時就完成了，建立全部的索引不到半個小時。原因就是第一種方式每次insert都改變索引順序，共執行改變2000萬次，而第二種方式整體上執行索引順序就一次。3. PLSQL程序性能問題分析方法本章主要介紹怎樣找到出現性能問題PLSQL程序或語句的方法。3.1 性能問題分析出現性能問題，我先要從整體進行分析，一般總體上會有幾種現象：l 整個系統運行速度都慢n 在業務不忙的時候，所有模塊都慢n 只有在業務繁忙的時候，所有模塊都慢n 時快時慢l 個別業務模塊運行速度慢n 在業務不忙的時候，該模塊就慢n 只有在業務繁忙的時候，該模塊才慢n 時快時慢一般導致系統

46、性能慢的因素：l 硬件：客戶端、服務器CPU、內存和存儲設備配置不符合應用系統要求l 網絡：網速低、丟包l 操作系統參數設置：參數設置不合理l 受到其他軟件干擾：例如：防火墻、病毒l Weblogic設置：參數設置不合理l Oracle設置：內存、表空間、redolog、系統參數設置不合理等l PLSQL程序：plsql不優化、未使用索引、鎖表在不同現象下，可能導致性能問題的因素：1. 一般來說，如果在不辦理業務的情況下，整個系統性能就慢的話，應該和PLSQL程序優化是沒有關系的。可能的因素為硬件、網絡、操作系統、其他軟件干擾、ORACLE設置。2. 只有在業務繁忙的時候，整體系統性能都慢，有

47、可能的因素有硬件、操作系統設置、WEBLOGIC設置、ORACLE設置、PLSQL程序。如果在sqlplus下做查詢都慢，那么就和weblogic沒有關系。3. 一般來說，如果在不辦理業務的情況下，個別業務模塊速度就慢的話，那么基本上就是PLSQL程序不優化或未使用索引造成的。4. 只有在業務繁忙的時候，個別模塊慢，有可能的因素有硬件、操作系統設置、WEBLOGIC設置、ORACLE設置、PLSQL程序、鎖表。這里我們重點是說明PLSQL優化、索引優化的問題，其他例如：硬件、網絡、操作系統和oracle設置等因素我們不進行說明。PLSQL優化、索引不使用的問題產生的影響：1. 對于某段不優化的

48、程序或語句頻繁或者全表掃描一個表時，它訪問磁盤的時間和占用的吞吐量是很高的，這就導致系統IO長時間處于忙的狀態，導致整個系統性能下降。2. 對于某段不優化的程序或語句頻繁或者全表掃描一個表時，其他的業務程序也訪問同一個表時，速度將大大下降。3. 如果是更新表操作時間長，還可能會導致鎖等待，導致會話堵塞，weblogic端也出現壓力問題，導致這個系統性能下降。我們一般根據這些現象、以及一些方法判斷，來初步分析產生性能問題的大致原因的范圍。不過對于這一點，還是比較困難的，因為產生問題的原因是多種的，并且還有一定的關聯。下面的章節介紹我們已經斷定是PLSQL優化、索引不使用的問題，我們通過什么方法來

49、具體定位問題。3.2 Expain Plan分析索引使用在PL/SQL Developer等工具有一個Expain Plan分析的功能，這個功能可以幫助我們分析SQL語句是否使用了索引、使用哪些索引和使用索引的效果。1. 選擇explain plan的窗口2. 在上面欄中輸入SQL語句，然后點擊工具欄上的EXECUTE執行（或按F8），就會在下面顯示Optmizergoal優化器的默認方式（也可手工選擇），以及下面的解釋計劃，從解釋計劃上能看到哪個條件語句使用了索引，哪個沒有使用；哪個表使用了索引，使用了哪個索引，哪些表是全表掃描的(TABLE ACCESS FULL)3. 分析內容說明：l

50、COST：根據優化程序的基于開銷的方法所估計出的操作開銷值對于使用基于規則方法的語句該列為空該列值沒有特定的測量單位它只是一個用于比較執行規劃開銷大小的權重值l Cardinality：根據基于開銷的方法對操作所訪問行數的估計值l Bytes：根據基于開銷的方法對操作所訪問字節的估計l 通過設置，我們還能看到更多的信息，例如：CPU使用、時間等等全表掃描的(TABLE ACCESS FULL)肯定是速度慢的，如果是大數據量的表，那么這個語句是絕對影響性能的。另外使用了索引也不一定性能就高，因為索引使用也有效率的情況，下面列出索引常見的使用類型：1. INDEX UNIQUE SCAN：唯一索引

51、掃描，速度最快2. INDEX RANGE SCAN：范圍索引掃描，使用這個索引時，就需要看COST、Cardinality、Bytes的大小了，如果特別大，有時候還可能速度低于全表掃描的速度。我們在知道語句有問題，或者我們對語句進行優化時，這個工具是非常有用的。3.3 TOPSQL分析程序有性能問題的時候，我們是不知道哪些語句存在性能的問題，尤其你不是開發人員。幸好有一些工具可以幫助我們找到這些存在性能問題的語句。Toad工具和ORACLE9I的企業管理控制臺工具可以捕獲到這些問題語句（oracle10g中em的功能不夠好）。在TOAD和ORACLE9I工具中可以查詢到TOPSQL頂級SQL

52、的內容，通過CPU、IO吞吐量、占用時間等信息的排序，我們可以找到最影響系統性能的語句，通過分析我們可以看到這些語句的解釋計劃。根據解釋計劃，我們可以進行語句的優化，我們知道語句后，我們通過plsql的搜索功能就知道存在問題語句的程序了。這個辦法使用有個前提條件就是這些問題語句在系統上運行過，并且沒有間隔過長的時間，因此最好是在實際出現性能問題的ORACLE上不斷的去監控，才能捕獲到最全的問題語句。1. ORACLE9i企業管理控制臺工具的topsql2. 系統運行中的所有TOPSQL3. 分析具體的SQL語句4. Sql分析的解釋計劃3.4 針對性語句搜索TOPSQL分析也只能是找到未使用索引的語句（實際上這一點基本就足夠解決性能的問題了），但是對于2.4章節中的很多內容，沒有辦法進行捕獲。我們可以采用針對性語句搜索，來盡量找到一些問題語句進行優化。在PLSQL工具中有一個Find DB Object的功能，可以進行搜索。我們可以搜索的關鍵特征信息：l NOT INl UNIONl GROUP BYl ORDER BYl > 、 < 、<>l Like %l From tab 、Update tab、delete tab，其中tab是數據量特別大的表，我們可以針對性的檢查對大表訪問的語句（例如：ac2