Re: MView fast refresh taking long time

a couple of points:

1. Even if the query was running well: I think your query refers to the delete part of the fast refresh and the fast refresh performs single row operations with the obtained ROWIDs - so potentially 3 million single row operations by ROWID (for the delete and update part), so this part of the fast refresh might require millions of consistent gets (and potentially a lot of physical gets, too). I'm not sure if this is reasonable performance-wise, so with that change volume a full refresh might actually be faster - or as suggested perform the fast refresh more often if possible.
2. The index on the MLOG (MLOG$_BILLSUMMARY_AK1) looks like a custom index - I don't believe this is created by Oracle by default when creating a Materialized View log.
3. The subquery cannot be unnested because the M_ROW$$ column is defined as NULLable by default when creating the MLOG I think - if you define it as mandatory more transformations like unnesting might be possible - you can force them (if valid) by adding a "/*+ unnest */" hint to the subquery for testing purposes.
4. Even with unnesting possible the range of transformations is quite restricted due to the correlated NOT IN clause used by Oracle (the unnested query might still use a NESTED LOOP, which might not be much different from the FILTER performance-wise, potentially even worse due to the subquery FILTER caching performed by the Oracle runtime engine).
5. As Mark has already pointed out - what execution plan do you get for the statement - are the estimates in the right ballpark?

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Thu Dec 03 2009 - 16:11:24 CST