物化视图的全量刷新引起的问题

最近对客户的数据库系统进行升级，其中一个重要的更新说增加了物化视图，由于种种原因，这几个物化视图的刷新方式采取了全量刷新。系统刚升级两天后，临时表空间的占用率就达到了3G左右，由于担心临时表空间被撑爆，在网上搜集了些关于临时表空间增大的几种情况，记录如下：

引起临时表空间增大主要使用在以下几种情况：
1、order by or group by (disc sort占主要部分)；
2、索引的创建和重创建；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些异常也会引起TEMP的暴涨：a. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试；b.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间；c.对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。
 针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。

针对客户的实际情况，高度怀疑说由于物化视图的全部刷新引起的（这几个物化视图都有索引），也咨询过几个数据库的大师级人物，他们都不建议在OLTP系统中使用物化视图。难道物化视图用在数据仓库中的效果比较好？就在此时，我们也准备了第二套方案，以便临时表空间利用率超过80%时带来附加的性能为题。就在我们准备放弃物化视图的方案时，发现临时表空间并没有再继续增长，而是维持在3G左右。

本以为相安无事了，结果另一个潜在的问题出现了，由于物化视图刷新时，产生了大量的redo，由于生产库运行在Archivelog模式，导致数据库频繁归档。不知是不是归档太频繁了还是什么原因，导致备份用的TSM设备出现故障，造成Rman无法进行，归档日志也就无法删除以前的Archive Log，归档无法进行，最终导致DB hang住（目前已经通过增加归档日志的磁盘空间解决）。

为了解决以上问题，通过查找资料，想采取如下方案来解决：即对物化视图的刷新采用Nologging模式，步骤如下：

--step1：将物化视图表改为nologging模式

alter table userA.mv_Talbe nologging;

--step2：disable物化视图的索引

alter index userA.IDX_MV_Table_cloName unuseable;

--step3：对物化视图进行刷新

exec dbms_mview.refresh('mv_Table');

--step4：重建物化视图的索引

alter index userA.IDX_MV_Table_cloName  rebuild index_tbs nologging;

这个方案还需要在测试环境上进行试验，不知道具体效果如何？

请各位高手批评指正！

 

附：下面的SQL是查询哪些SQL占用了较大的临时表空间

Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26566546/viewspace-714530/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26566546/viewspace-714530/