Home Ask Login Register

Developers Planet

Your answer is one click away!

Vladislav February 2016

Materialized view refresh terrible performance degradation

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.

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) Without indexes, first run time: 72 min redo: 42 mln (it is close to row number)

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

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

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 version: 11g Enterprise Edition Release

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


Michael Broughton February 2016

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.

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.

EDIT: 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.

Post Status

Asked in February 2016
Viewed 1,065 times
Voted 4
Answered 1 times


Leave an answer

Quote of the day: live life