Materialized View Refresh in 10g generates a massive number of redo log files

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Materialized View Refresh in 10g generates a massive number of redo log files






Symptoms:
I use a MV to enforce declarative integrity, but I ran into the increasing logging problem (@18 MB per transaction). Archive log sizes for a day increased to @5 GB
from @0.7 GB.
Metalink Note 365157.1 says that in 10g, a complete refresh will do a delete instead of a truncate and that this is expected behavior. To force the refresh to do a truncate instead of a delete, the ATOMIC_REFRESH parameter needs to be set to FALSE.
I converted the MV from REFRESH ON COMMIT to REFRESH ON DEMAND and set the ATOMIC_REFRESH to FALSE in the DBMS_MVIEW.REFRESH procedure. This brought down the logging to almost negligible levels, but it commits data to the base table before the check constraint on the MV fires.








Solution:



Change in behavior of complete refresh between 9i and 10g.

9i: TRUNCATE->INSERT direct path [INSERT /*+ APPEND */]
10g: DELETE->standard INSERT [INSERT /*+ BYPASS_RECURSIVE_CHECK */

The side-effects: performance deterioration, excessive redo log information being generated even for NOLOGGING MVs, data blocks not being compressed etc..


There is a way to revert to 9i logic of complete MV refresh.

Option
ATOMIC_REFRESH=>false drives the refresh to run in TRUNCATE->direct path INSERT [INSERT /*+
BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */].


It currently says:

= atomic_refresh =
“If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the
Refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction.”


BTW - Once we observed pitiful performance of complte MV refresh on 10g, we reverted to "manual,roll-your-own" refresh based on TRUNCATE->INSERT direct path->CREATE MV on prebuilt table. We can
outperform the complete refresh in both ATOMIC_REFRESH=>TRUE and FALSE. The reason for this is that complex MV refresh (the actual INSERT) gets rewritten in rather unfortunate way (using number of
virtual subqueries like "from$_subquery$_005"..). I am not going to insert the whole trace but it is worth to trace your MV complete refresh to see what is going on.









exec DBMS_MVIEW.REFRESH('owner.Name_MV','C',ATOMIC_REFRESH=>FALSE);




References:

Get Oracle Certifications for all Exams
Free Online Exams.com