Skip navigation.

Materialized View Fast Refreshes are Slow

vjain's picture

A materialized view that is verified to be fast refresh should update relatively fast. But, what happens when there are few changes to the master table, no network issues, no aggregation in the snapshot query and the refresh still runs slow?

About Materialized Views

One of the most useful replication and data warehousing features in Oracle is materialized views. Materialized views, also known as snapshots, have been a feature of Oracle for several years. A simple way to conceptualize this is to think of a view of a master table that has actual data that can be refreshed. Since the introduction in 8i, Oracle has consistently enhanced the technology for each subsequent release, including 8i, 9i, and 10g.

The data in a materialized view is updated by either a complete or incremental refresh. An incremental or fast refresh uses a log table to keep track of changes on the master table. A materialized view log (snapshot log) is a schema object that records changes to a master table's data so that a materialized view defined on that master table can be refreshed incrementally. The frequency of this refresh can be configured to run on-demand or at regular time intervals.

In practice, many Oracle customers use materialized views outside of the data warehousing environment. Some companies use fast refresh materialized views on remote databases in order to improve performance and security when using distributed computing for online transaction processing. The speed of a fast refresh will be determined by how much data has changed since the last refresh. If the master table's data is updated very often, then the log table will have more recorded changes to process in order to update the materialized view.

However, the entire concept of the fast refresh is that it should be a relatively quick operation. There is a substantial volume of documentation regarding how to ensure that the refresh is actually doing a fast refresh. New features in 10g including DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_ADVISOR.TUNE_MVIEW provide insight and advice on materialized views. While these features can help you to get an optimal materialized view, it cannot help when the underlying problem is not the materialized view. In fact, the underlying problem might not even be on the consuming site.

A Real Life Example...

A recent experience with a client exposed such a situation. The client complained that a user process was running slow. After a quick analysis, the culprit was determined to be a materialized view refresh run on-demand from a trigger in the process. The master table of the materialized view was a remote table in the same data center. The user process inserted a row into the master table and then refreshed the materialized view. Testing revealed that even when there were only a few changes on the master table, the refresh would still take 10-20 seconds. In this situation I would generally consider the following possibilities: complete versus fast refresh, network bound, many changes on master table, and complex aggregation on MV query. The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.

SELECT mview_name, refresh_mode, refresh_method,
            last_refresh_type, last_refresh_date
       FROM user_mviews;

At the master site, it was verified that the snapshot log existed.

SELECT log_owner, master, log_table
       FROM dba_mview_logs;

So, the two basic requirements for a fast refresh were confirmed. Next, I tested the network bound by running copying 30,000 rows from all_objects from the master to the consumer site in 1-2 seconds. Then, I checked the query of the materialized view and confirmed that it was a simple select from the master table without any aggregation or sorting. To be prudent, DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems. Furthermore, the master table had approximately 10-30 changes per minute while the refresh was happening 1-2 times per minute. There was no doubt that a fast refresh was occurring, there were no aggregations in the query, there was a small number of changes to the master table, and network issues were not the problem. So what was causing this fast refresh to go so slow?

After determining that the problem was definitely not on the consumer side, the focus of the analysis shifted to the master site. We had already confirmed that the snapshot logs were present so the next step was to see what was contained in those logs. Much to our surprise, there were over 13 million rows in the snapshot log dating back several months. This was unexpected since the user process was refreshing the materialized view a few times every minute. After the refresh, why were the records in that table not being deleted? One possibility was that there was another snapshot using that log. Multiple simple snapshots can use the same snapshot log, meaning that records already used to refresh one snapshot might still be needed to refresh another snapshot. Therefore, Oracle does not delete rows from the log until all snapshots have used them. If the snapshot log has grown very large, then the time to complete a fast refresh will increase as well since more records must be scanned by the consuming site before determining which records to use for the refresh. This explained why a fast refresh with almost no changes on the master table would still take 10-20 seconds to complete.

While the client insisted that no other snapshots existed for this table, the evidence showed that to be the most likely cause. A query of DBA_REGISTERED_SNAPSHOTS seemed to support the client's position since no other snapshots appeared to be using this log.

SELECT owner, NAME, snapshot_site, TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots
FROM dba_registered_snapshots, dba_snapshot_logs
WHERE dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id (+)
AND dba_snapshot_logs.MASTER='&table_name'
/

However, the one risk with using materialized views over remote databases is that sometimes a network or site failure can prevent a master of becoming aware that a snapshot has been dropped. Oracle keeps track of snapshots that are using entries in SYS.SLOG$ at the master site. In fact, when deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.SNAPTIME for the table with MLOG$_.SNAPTIME$$. The rows with a MLOG$_.SNAPTIME$$ equal to or older than the oldest SYS.SLOG$.SNAPTIME for the table are purged from the log. If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated. Consequently, any records in the snapshot log will never be purged during a refresh.

The following query can be useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.

SELECT r.NAME snapname, snapid, 
NVL(r.snapshot_site, 'not registered') snapsite,
snaptime
FROM   sys.slog$ s, dba_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+) AND
		 mowner LIKE UPPER('&owner') 		   
AND	 MASTER LIKE UPPER('&table_name');

Solution

After verifying the existing snapshots on the consumer site by querying SYS.SNAP$, it was easy to determine which entries in SYS.SLOG$ at the master site were no longer being used. After using DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG to remove the orphaned entries, the old records from the snapshot log were finally purged upon the next refresh. In order to realize this improvement, the high watermark of the materialized view log needed to be reset by using ALTER MATERIALIZED VIEW LOG '&snapshot_log' SHRINK SPACE. It was later found that these orphaned snapshots were the result of obsolete development instances having materialized views on this production table.

Upon realizing the root cause, it was apparent that the impact of this issue was likely to be broader than a slow user process. If the database were scanning 13 million rows about 2 times per minute, then this should have caught the attention of the DBAs who were monitoring the database activity. A recent Statspack report on the master site showed that the MLOG$ table was among the top I/O consumers. The Active Workload Repository (AWR) segment statistics revealed that the logical and physical reads on the MLOG$ table had consistently increased since the earliest measurement. The moment that an MLOG$ table shows up on a Statspack report, it is prudent to determine if there is a problem. In this case, the problem could have been identified much earlier.

Conclusion

Oracle’s materialized views are a great tool for replication and each subsequent release has proven to add new features and enhancements. As with any technology, you must be certain that you are using the features correctly and have implemented a clearly defined and strictly enforced change management policy. While these new technologies will always introduce new possibilities for problems, the old standards of a strict operating procedure can mitigate these risks.

About the author

V.J. Jain is an Oracle Database and Applications Consultant and owner of Varun Jain, Inc. - Oracle Consulting. With over 12 years of experience with database systems, he specializes in database performance, development, interfaces, and high performance solutions. Based in Orange County, California, he actively evaluates Oracle's newest technologies and is a member of the Oracle Partner Network and Beta program. Additional material by him can be found at http://www.oracle-developer.com.

shrink space on materialized view log

I don't seem to be able to get your syntax to work on my system. Besides, would you not have to enable row movement for the mlog, and would this sort of operation be quite risky on an mview log? I'm quite interested in this as large mlogs are the bane of my life, and I usually have to resort to dropping and rebuilding them. It would be great if I didn't :)

OK, so I've just gone back to

OK, so I've just gone back to my test system, enabled row movement on the mlog
and tried:

alter materialized view log on

shrink space;

This seems to work. I will monitor mview refreshes for the next few days to ensure they are still working correctly.

same problem, refresh fast is slow!

I have create a materialized view(MV) with large query based on 10 tables to replicate data for some report queries. This MV is refresh fast on commit but it takes about 4 seconds to update after a simple change in only one of the base tables. I considered all of the issues which discussed in this article but it did not help me.
Would you please guide me through this problem? what are the exact factors to impact response time?

MV update

If you are joining 10 tables for your MV and you are noticing that the update occurs slowly, then you should probably look at the query being used to join your 10 tables. I would look at the explain plan at the time you create the MV and then after some updates occur in the base tables, make sure that the query being used is still optimal. Also, I would only trust the internal views when figuring out what the optimizer is doing. When the CBO initially creates the plan, it is based on the data at that time. That plan is saved into the cache and used over and over again until the cache is cleared or the plan becomes invalidated. When data changes in the tables, it is possible that the optimal plan might change as well. It really depends on the nature of your tables and how they are being updated.

Oracle replication/MV technology

I am investigating a scenario where Oracle replication/MV technology can be used to refresh tables from one db to another db. The key to this feature needed is the data should be consistent at point in time for the refresh across multiple tables. Also, after the refresh it should not implicitly commit and change the target database, but we should have control to explicit commit. Both db are Oracle 10.2.

Oracle Materialized View | Deletion of Records, Oracle Materiali

One question reg Materialized views.

If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion?

I believe the answer is yes. In that case can we ensure that this delete does not happen?

Is there anyway we can prevent MView refresh from deleting the records that is once inserted even if we delete the same records in source DB?

archive vs. materialized view

The purpose of a materialized view is to provide near real-time snapshots for a set of data. Upon each refresh, the MV is refreshed to represent the real-time data. The real-time data would reflect any inserts, updates, or deletions to the base table at the time of refresh.

The materialized view isn't meant to be a standalone archive, even though it can be very useful for creating one. It sounds like you have a data warehousing requirement. You can use the MV as a component to meet your requirement but it won't maintain historical data by itself.

- VJ