Home | Discussion Forums (Get expert advice) |
About Us | Links | Job Openings
Shared on OraFaq.com
Materialized View Fast Refreshes are Slow
By V.J. Jain, July 2007 (firstname.lastname@example.org)
Varun Jain, Inc. (an Oracle Partner)
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 (link), 9i (link), and 10g (link).
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 (link). 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.
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.
At the master site, it was verified that the snapshot log existed.
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.
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.
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.
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.
Owned and Operated by Varun Jain, Inc, www.varunjaininc.com
Copyright ©2007 Oracle-Developer.com