物化视图定义不当引发Oracle性能问题

经检查，服务器的CPU和内存都很正常，但磁盘IO奇高。优化SQL相关表索引无效，收缩数据文件也无效，最后发现会话中有很多耗时的Or

最近在实施一个大型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、查看已有表空间，找到回退表空间

Onlook

专为前端设计师和开发者打造的视觉编辑工具

108

查看详情

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服务后再删除。