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

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


  1. hai, sir these is althaf i prepare oracle dba with 4 year experience added i attend interview with next weak please send it to me any real time scenario and interview questions experience, and material any other send it to me thank you regard.

    1. Mainly DBA needs to do installation, patching, backup recovery and other similar stuff. I am a Database Tuning Expert and Developer.

  2. After long time .. I saw new article from your end. .. !!! Nice one ...Thanks

  3. Nice explanation Nimish Garg!

  4. I don't really enjoy mat views. Atomic refresh is VERY COSTLY, and non-atomic refresh leaves the table empty for some time. I'd rather use a table managed by a stored procedure written by me and exchange partition (so that the data will be changed almost instantly without having delete and then insert select).

  5. An alternative we used to workaround this problem, was replacing the materialized view with a table with two partitions. You define one partition as active and the other as inactive. In a small table you keep which partition is active. You then build a view on the active partition. Your logic uses this view and is not aware of the underlying active and inactive partitions. You can then simply refresh the data by modifying the data of the inactive partition (in the way that suits your needs and seems the most performant to you). When you're done, you simply switch the active and inactive partition by updating the flag (or whatever) of the corresponding record in the small table. Your view will then show the new refreshed data.

  6. This comment has been removed by the author.

  7. Only Caveat would be "Truncate will cause MV to go blank". If developer let business community to run report while MV is getting refreshed it will create a wrong result or no result. Oracle did address this issue with Database 12c by providing another method "OUT OF PLACE".