物化視圖定義不當引發Oracle效能問題

最近在實施一個大型BI項目，其中KPI指標資料是按月存放的，但使用者要求每天進行一次計算，並替換前一天的計算結果，直到月末不再更新為止。項目初期Oracle資料庫一直效能穩定，但最近兩周效能突然大幅下降。

經檢查，伺服器的CPU和記憶體都很正常，但磁碟IO奇高。最佳化SQL相關表索引無效，收縮資料檔案也無效，最後發現會話中有很多耗時的Oracle(JXX)進程，執行：

select * from dba_jobs

發現有10幾個通過dbms_refresh重新整理物化視圖的任務，而且是每隔5分鐘執行一次。經詢問，沒有人手工定義這些任務，於是查看這些視圖的定義，果然是視圖定義SQL中要求每5分鐘重新整理一次資料。刪除這些視圖後，系統效能恢複正常。

結論：

1、物化視圖定義中的資料重新整理是通過系統計劃任務實現的。

2、物化視圖重新整理太過頻繁會造成很高的磁碟IO，尤其是當源表很大時。在本執行個體中，源表以前資料量很小，但近期開始計算KPI後該表資料量已超過百萬，因此前期效能穩定的資料庫就出現了效能問題。

3、物化視圖適用於源表不常更新的情境。在本執行個體中，可以把曆史年度的資料通過物化視圖抽取出來，本年的資料則不應該使用物化視圖，使用普通視圖即可。

調試過程中發現回退檔案超過30GB，順手予以清理，過程如下：

1、查看已有資料表空間，找到回退資料表空間

SELECT * FROM DBA_TABLESPACES WHERE CONTENTS='UNDO'

2、建立新的回退資料表空間

create undo tablespace UNDO_RBS1 datafile 'E:\oracle\product\10.2.0\oradata\orcl\UNDOTBS02.DBF' size 1000m;

3、修改系統回退資料表空間為2中建立的資料表空間

alter system set undo_tablespace=undo_rbs1;

4、從系統中刪除舊的回退資料表空間

drop tablespace undo_tbs1 including contents;

5、在作業系統中刪除舊回退資料表空間的檔案。如果不允許刪除，可以重啟Oracle服務後再刪除。