Re: MView fast refresh taking long time
Date: Thu, 3 Dec 2009 14:11:24 -0800 (PST)
a couple of points:
- 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.
- 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.
- 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.
- 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).
- As Mark has already pointed out - what execution plan do you get for the statement - are the estimates in the right ballpark?
Oracle related stuff blog:
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