excessive redo on snapshot/mv refresh

I'm kinda screwed but thought I'd post a message here to see if anyone has any options...

From Oracle Support, the NOLOGGING option on a snapshot/materialized view only applies to the initial create. All refreshes against the materialized view will be logged.

On a dev system we had 5 redo log groups sized at 10 MB each. Everything was running just fine. Log switches were occurring on avg every 40 mins (not too bad for the dev system). We recently added some features that required using fast refreshable materialized view logs.

At this point the log switches were happening every minute, sometimes every 10 seconds! The database even has hung a few times because the redo system got so overwhelmed.

I dropped and recreated the redo logs, increasing them twenty fold. 5 groups at 200 MB each. The Log switches are still happening every 2-3 minutes.

I don't have anymore diskspace to allocate on the dev system and on the production system if i did the math, with the goal of trying to get a log switch per hour, I'd need my redo log groups sized at something like 6Gbs each. I definitely don't have the space for that.

Is it me or does this amount of redo activity seem excessive? I'm not sure how i can reduce it. We're supposed to deploy this application to our production system soon, so I'm hoping someone might have some ideas?

Thanks,
Gavin Received on Thu May 09 2002 - 20:21:37 CDT