Re: MView fast refresh taking long time
Date: Tue, 8 Dec 2009 09:49:20 -0800 (PST)
Message-ID: <17c59aa8-30af-43be-82f3-cb08a08e6b5e_at_b15g2000yqd.googlegroups.com>
> Five hours seems an alful long time for updating only 3 million rows.
> Have you considered trying to perform the fast refresh more frequently
> to keep the amount of data that needs refreshing smaller?
>
> HTH -- Mark D Powell --
Problem is that when I do a full refresh it can take 5 or 6 hours. By then there are often several million changed rows to update and it just never catches up.
The stats are good on the master table, but the MView logs have stats locked to make it look as though they only have 1 row (work around for a bug apparently) - I did try gathering stats and manually setting the number of rows etc, but the query plan wouldn't change.
On other test environments I can do several million changes on identical tables in 15 minutes. I think we are suffering from too many changes on the table causing problems getting a consistent read on it, and a buffer cache on the replicated database that is too small.
Tracing the sessions, they are doing lots of DB File Sequential Reads - each one is about 10ms, which is fairly good, but not when there are so many of them to do I guess. Received on Tue Dec 08 2009 - 11:49:20 CST