Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh

Complete Refresh of Materialized View is taking time? Here is a trick/feature which can help you in tuning the complete refresh of Materialized View.

Recently I was assigned a task to tune some of the Materialized Views which were taking time to refresh on our Oracle 11g Data-Warehouse Server. I choose two mviews which were scheduled to get complete refreshed and their Materialized Views tables were having huge data (more than 7 million records).

Immediately I noticed that those two Materialized Views were getting Atomic Refreshed. What is Atomic Refreshed? DBMS_MVIEW.REFRESH has a parameter "atomic_refresh" with default value of "true", which means:
- Materialized View is refreshed as a whole, as a single transaction.
- It ask Oracle to delete the data and then Insert it in Materialized View.
- Because of Delete on Materialized View a lot of Undo is generated.
- After Delete, A Insert on Materialized View is done which also generates alot of Redo.
- After completing the refresh process of Materialized View, commit is issued.
- So Data is available during Materialized View refresh.
- It makes overall Materialized View Refresh process slow.

Because my database was a warehouse and Materialized Views were refreshing after DAC/ETL Job/Load executions in off hours. I decided to refresh Materialized View with Non-Atomic refresh (atomic_refresh => false), which means
- Materialized View is not refreshed as single transaction.
- It ask Oracle to perform Truncate + Append data.
- Oracle performs TRUNCATE on Materialized View instead of a DELETE at complete refresh.
- Because of TRUNCATE (DDL), REDO/UNDO is minimized at maximum.
- After Truncate, A direct Path Insert (insert /*+ append */)is done on Materialized View.
- Because of Direct Path Insert, Oracle may bypasses the buffer cache and write directly to the datafiles
- Because of Direct Path Insert, A far less REDO may be generated, if object is in NOLOGGING mode or Database is in NOARCHIVELOG mode.
- And then commit is issued.
- Data in Materialized View is not available during the refresh.
- Boost up the Speed of Materialized View Refresh.

After refreshing Materialized View with atomic_refresh => false:
- Refresh time of first Materialized View is reduced to 21 minutes from 45 minutes.
- Refresh time of Second Materialized View is reduced to 10 minutes from 22 minutes.

Syntax of Non Atomic Materialized View Refresh

DBMS_MVIEW.REFRESH('MVIEW_NAME',atomic_refresh=>false);

I hope you have enjoyed reading this article and may have learnt something new. Please post you feedback in comment-box.

Read More on Tuning SQL
- Why Primary Key Foreign Key Relationship and Join Elimination
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- Why my distinct query is not using index?
- Avoiding unnecessary function calls to optimize SQL statements
- Why do I prefer COALESCE over NVL
- Query optimization tips for Oracle
- How to Fast Refresh Materialized View with Joins
Read More on Tuning PL/SQL
- Reduce database calls by posting Multiple Records
- Append String to CLOB in Optimized way
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- PLSQL Tuning: Bind Variables and execute immediate
Read More on Analyzing SQL & PL/SQL
- How to Get Execution Plan and Statistics of SQL Query
- DBMS_PROFILER: Overview and How to Install
- TKPROF: Generating and analyzing trace file