一次效能優化調整過程.

oracle的效能優化.

last updated 2004-08-30

本文可以任意轉載，轉載時請務必以超連結形式標明文章原始出處和作者資訊及本宣告

http://blog.itpub.net/post/11/2200

平臺：windows 2000 server sp3

資料庫：Oracle 9.0.1.1.1

1.      在高峰時段，系統高負載的情況下用statspack一個小時做一次系統的快照。對statspack產生的報告注意其中的Top 5 Wait Events。具體可以參考eygle寫的statspace使用指南。本例的等待事件主要是db file scattered read、buffer busy waits和db file sequential read。

2.      對於db file scattered read主要是由於全表掃描造成的，db file sequential read說明當前系統負載很高或者cbo使用了不合適的索引。通過定時（2天一次在晚上12點執行dbms_stats.gather_schema_stats('')過程）統計schema資訊並用過程

dbms_support.start_trace_in_session

dbms_support.stop_trace_in_session

跟蹤客戶端的會話，用tkprof分析跟蹤檔案：

tkprof sys=no sort=prsdsk，exedsk，fchdsk

通過分析輸出檔案並獲得其中效率低下的sql語句，然後就可以對這些sql語句進行調整。

3.      一般可以通過在表上加上必要的索引來消除全表掃描，並注意調整初始化引數db_file_multiblock_read_count和optimizer_index_cost_adj，這兩個引數影響CBO選擇的執行計劃。如果希望CBO更傾向於使用索引，可以加提示/*+first_rows*/。

4.      經上述調整後，客戶仍然抱怨在應用程式中查詢某個報表的時候非常慢，一般都需要2個小時以上。對sql語句分析後，該報表的sql語句是查詢一個巢狀檢視，該巢狀檢視的結構大致如下：

create or replace view vw_v1 as select c1,c2,c3 from t1;

create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 group by c2,c3;

create or replace view vw_v3 as select *from vw_v2 union select *from vw_other;

客戶端查詢的時候是select *from vw_v3 where c1 = :b1;

其中t1表是個分割槽表，目前大概有4千萬行，每個月增長1千萬行。對sql語句分析後認為效能問題主要是由於檢視vw_v2中對t1的group by c2,c3造成的，調整如下：

create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 where c1 = pg_arg.sf_get_c1() group by c2,c3;

pg_arg是自定義的pl/sql包，在執行該查詢前需要執行pg_arg.sp_set_c1(c1)過程設定c1引數，在group by前加入where c1 = pg_arg.sf_get_c1()限定了group by的記錄數。通過這樣的調整和改變可以把查詢的耗時減少到10分鐘以內。

5.      同時使用者也在抱怨倒盤（主要是插入、刪除操作）時非常緩慢，200人的單位需要2個小時才能匯入到資料庫中。該操作主要是大表（分割槽表，每個月增加10萬條記錄，總行數現時在1千萬行左右）的插入刪除操作，在sqlplus裡面執行插入操作，高峰時候更是達到了30秒才能插入一條記錄。通過alter table modify partition freelists ;增大空閒列表的數目後對效能的提高幫助不大；同時在插入的時候加/*+append*/提示，對效能的提高也是很有限（原來兩個小時的操作在一個小時五十分鐘完成，基本上沒多大的作用）。對大表結構分析後，發現在表上建立了全域性分割槽索引，把該全域性分割槽索引刪除並建立普通索引後INSERT語句可以即時響應。另外在插入資料的時候用批量插入的方式進行插入：

   /*如下所示:

先定義陣列變數

然後把資料一次性fetch出來

最後用forall一次性插入到表中*/

   TYPE t_jzny IS TABLE OF char(6);

   b_jzny       t_jzny;           

   v_rowcount   NUMBER;           

   CURSOR cur_bulk

   IS

      SELECT ny

        FROM

WHERE ROWNUM <= 6;

…………………

-- 初始化陣列

   OPEN cur_bulk;

   FETCH cur_bulk

   BULK COLLECT INTO b_jzny;

   CLOSE cur_bulk;

      ………………………

FORALL v_i IN 1 .. v_rowcount

   Insert into

    ……………..

   values

   (

   ………….

   B_jzny(v_i);

   …………..

   )

經以上調整後，200人左右的倒盤操作可以從原來的2個小時提高到10分鐘以內。收到立竿見影的效果。

注意：加入/*+append*/提示插入資料後需要馬上commit事務，不然會出現

ERROR 位於第 1 行:

ORA-12838: 無法在並行模式下修改之後讀/修改物件

的錯誤。

6.      小結：效能調整一般是通過statspack，產生分析跟蹤檔案來定位效率低下的sql語句。然後通過各種手段，比如可以加入合適的索引，新增合適的hint來調整查詢語句；用alter index monitoring usage;跟蹤沒有使用的索引並把這些索引刪除、調整效率低下的索引（尤其使對大表上的索引）、加/*+append*/提示、批量插入等手段調整DML語句。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-21526/，如需轉載，請註明出處，否則將追究法律責任。