1
Summary: Materialized View across DB link eating up too many system resources
Question: Is there a way to throttle a materialized view refresh?

We have some fairly large tables that we copy from one oracle DB to several others as a cached backup if the network to the central database is down (or the central database is down). The data copy runs hourly or daily (depending on the data). It's not mission critical or real-time.

Our first idea was materialized views on the receiving databases to pull the data from the central database across a database link. If I remember right we couldn't do incremental refreshes because of the database links so the refreshes are full table refreshes.

We discovered when the materialized view refreshes run they eat up lots of system resources sometimes overloading the system and slowing critical stuff. (I'm a programmer not DBA so I'm not entirely sure what resource was constrained, but I think disk access, possibly memory.)

Is there any way to throttle a materialized view refresh so it won't do that?

|improve this question
  • Depending on the importance of availability of the tables better hardware is probably cheaper and more effective. I see many production Oracle databases getting by on just enough memory when there is more that could be allocated. – kevinsky Nov 27 '15 at 17:15
3

Materialized views over a DB link most certainly can be set up as fast refreshes (i.e. only changes since the last refresh) as we use this approach ourselves. However, what cannot be done are refresh on commit mviews over a DB link, so the mview will have to be on a refresh schedule (unless you simply want to refresh on demand, of course).

This works on Oracle Database Versions >= 10g (personally tested). I can't vouch for < 10g versions since I started using Oracle Database in 10g.

We see very little database impact on both of our source DB's and destination DB's using this approach with roughly 30 mviews (most are medium size tables, a few million rows each) that refresh every 15 minutes.

A few notes/gotchas: The mview log has to be created on the source DB, not the destination DB (which is usually a point of confusion with this setup).

Create the DB link on the destination DB that connects to the source DB. If the usercode that the DB link is using to connect to the source DB is different than the schema where the table/mview log table resides, then the DB link usercode on the source DB will have to be granted select privileges on both the source table and the source mview log table.

In addition, the mview log deletions don't move the HWM, so for large transactional tables, you may want to consider having a nightly process that locks the mview log table, then checks if the mview log table is empty, then truncate the mview log table in order to reset the HWM to keep fast refreshes as fast as possible.

|improve this answer
  • I second this. We are running ~ 100 MVIEWS over a DBLink using fast refresh with a lot of tables that have more than 20 million rows. No problems. Neither on the source nor on the target. – a_horse_with_no_name Nov 27 '15 at 19:07
1

We also have a lot of MVs that are refreshed with data coming over a database link. Do you do an atomic refresh? If you do then switch it off. An atomic refresh does a delete from table while the other option does a truncate table. The last option is faster but (nothing for free :-)) when the refresh fails you end up with no data in your MV.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.