物化视图刷新性能严重下降 [英] Materialized view refresh terrible performance degradation

问题描述

我有物化视图(它使用联接，WITH，分区依据；查询返回大约 4200万行)，上面有2个简单索引.仅使用完全刷新.

I have materialized view (it uses joins, WITH, partition by; query returns about 42 mln rows) with 2 simple indexes on it. Only full refresh is used.

第一次刷新可以正常工作(大约需要100分钟)，但是第二次刷新需要几天才能完成.

The first refresh works fine (it takes ~100 minutes) but second refresh works several days and failed to complete.

我也删除了索引并重新运行测试.它工作正常.这是所有结果(会话统计信息中的时间和重做条目):

Also I dropped indexes and re-run test. It works fine. Here is all results (time and redo entries from session statistics):

1)没有索引，首先运行时间:72分钟重做: 4200万(接近行号)

1) Without indexes, first run time: 72 min redo: 42 mln (it is close to row number)

2)没有索引，第二次运行时间:106分钟重做: 8400万(4200万删除全部，4200万插入新内容)

2) Without indexes, second run time: 106 min redo: 84 mln (42 mln to delete all and 42 mln to insert new)

3)第一次运行有2个索引时间:99分钟重做: 1.26亿(行为4200万，每个索引为4200万)

3) With 2 indexes, first run time: 99 min redo: 126 mln (42 mln for rows and 42 mln for each index)

4)有2个索引，第二次运行时间:48小时后失败重做: 4.53亿(失败时不知道为什么这么大)

4) With 2 indexes, second run time: failed after 48 hours redo: 453 mln when failed (I have no idea why it's so huge)

Oracle版本:11g企业版11.2.0.3.0

Oracle version: 11g Enterprise Edition Release 11.2.0.3.0

该问题已在其他实例和服务器上重现.我没有可以正常工作的服务器.我认为这是一种错误，但是找不到类似的错误

The issue was reproduced on different instances&servers. I have no server where it works correctly. I think that it is some kind of bug but can't find anything similar

推荐答案

要注意的一点是，在版本10和版本11之间，Oracle将可选的"atomic_refresh"参数的默认值从FALSE更改为dbms_mview.refresh()API.是的.

One thing to note, between Versions 10 and 11 Oracle changed the default value of the optional "atomic_refresh" parameter to the dbms_mview.refresh() API from FALSE to TRUE.

如果atomic_refresh = TRUE，那么将通过DELETE/INSERT进行完全刷新.如果atomic_refresh = FALSE，那么，如果可能，Oracle将通过带有并行DML的TRUNCATE/INSERT进行刷新.速度更快，但有以下警告:但是，如果您一次刷新一个以上的mview，则您需要考虑这一点，因为atomic_refresh = TRUES确保所有刷新都在单个事务中进行，而FALSE则不会-可能有问题.

If atomic_refresh = TRUE then a full refresh will be done via DELETE/INSERT. If atomic_refresh=FALSE then, if possible, Oracle will do the refresh via a TRUNCATE/INSERT with parallel DML. MUCH faster, but with the following caveat: If, however, you are refreshing more than one mview at a time then you will need to think about this because atomic_refresh=TRUES ensures that all refreshes happen in a single transaction, FALSE does not - which may be problematic.

不好，我的行为发生在Oracle 9和10之间.不是10和11之间.还有一个副作用，即截断/插入意味着MVIEW在重建过程中不包含任何数据，这可能对用户造成问题查询它.做一些研究，弄清楚您的业务需求是什么，然后再去做.您还可以删除索引，进行刷新，然后重新创建索引以加快处理速度.

My bad, that change in behaviour happened between Oracle 9 and 10. Not 10 and 11. There is also the side effect that truncate/insert means that the MVIEW contains no data over the rebuild which may be problematic for users querying it. Do some research, figure out what your business needs are, and go from there. You could also drop the indexes, do the refresh, and then recreate the indexes to speed things up.

这篇关于物化视图刷新性能严重下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！