Systems Engineering and RDBMS

My refresh materialized view is not refreshing – Part II

Posted by decipherinfosys on October 15, 2008

In part 1 of this post, we had discussed checking the health/state of the refresh materialized view job. In this post will discuss checking the mlog table for the materialized view.

After we have checked the general health for the materialized few, we may want to look at what is being refreshed. That is to say, how many changes are to be propagated, and what type of changes are they? When ever the source table of a materialized view is updated, inserted into, or deleted from, the associated mlog table keeps track of these for the refresh. Each time a refresh cycle comes about, the changes are propagated to the materialized view and then the changes that were successfully propagated are cleared from the mlog (i.e. the records that are no longer needed for refreshing the snapshot(s)). If there are multiple materialized views accessing the mlog table, then the mlog table will not clear until all of the data for all of the materialized views has successfully finished refreshing. Even with that said, do not expect the materialized view to always drop to zero records when all of the refreshes occur, as the refresh finish times will differ for each view, not to mention the master/source table will be streaming any pending changes to the mlog table regardless if the refreshes are finished.

The below, simple, query will allow you to see what changes are pending. (written for 10gR2). The query should be run on the location of the master/source table as the sysdba user.

(select count(*) from <schema>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘U’) UPDATES,
(select count(*) from <schema>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘I’) INSERTS,
(select count(*) from <schema>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘D’) DELETES,
(select count(*) from <schema>.MLOG$_<MASTER TABLE NAME>) TOTAL
from dual

These four columns will give the total number of transactions grouped by the transaction type. The “TOTAL” column is the sum of the UPDATE, INSERT, and DELETE columns. Please keep in mind that the values from the above query are from the source location (i.e. where the master/source table is located). The mlog table is created in the same tablespace as the source table.

It may become important for us to monitor the number of pending transactions, as it can give an indication of how well the materialized views accessing the mlog table are refreshing. If you have never monitored this before, then you will be starting from scratch and will not have a reference point of how many transactions are normally pending at any given point in time. However this will not stop you from being able to see if the materialized view(s) are not refreshing fast enough. An mlog table that is back logged with no chance of completing a refresh will continue to grow as changes are applied on the source table and never shrink significantly enough to make up for the amount of pending transactions coming in. There is a point, were there are so many pending changes in the mlog table that the refresh will not be able to complete (unless you block or stop all transactions to the master table). I call this the tipping point. The point at which normal or abnormal business activity and the number of pending transactions are so out of balance, that the rebuild of the Materialized view may be necessary. Rebuilding should be the final option chosen as rebuilding means the destruction and rebuilding of the view, which can cause down time. Other options that could be considered (after some research of your own environment) would be to modify the query for the materialized view to be more restrictive (there by a smaller data set, however a rebuild would be necessary for this) or increase the parallelism on the mlog table (to be discussed in another blog). CPU activity, I/O activity, and Network activity can also play a major role in performance issues.

Some simple things you look for are below:

1) If you have never monitored this before, then start monitoring the mlog transaction growth now. About every “X” minutes or so run the above query to see how the numbers look (everyone’s environment is different, so you can choose an interval you feel would suit you best; I use 10-15 minutes to start off). If you monitor for a while and the only change you see in growth is an upward trend with only small dips, then you may have an issue. This is where previous monitoring helps, because that would give you and idea of where the tipping point would be.

2) If you have been monitoring and you see that there is an upwards trend in transactions, but it is still below the tipping point of total transactions, then continue to monitor or look into the options above. If it gets past the tipping point, then rebuilding the materialized views may be an option. You will have a good idea of what the tipping point is if this has happened before.

3) If the data from the two previous options are giving growth trends that are making you nervous, then check the below options.

a. Check the Server(s). You may need an System Admin to help you with this. Check the load on the source (where the master/source table is located) and also check the target (where the materialized view is created). Other applications of or jobs running one the database server or even against the database itself can cause issues. CPU load and I/O load from external sources can have a big impact an the performance of the refresh. Keep in mind that even if you pass the tipping point, it is still possible for the refresh to catch back up once it gets the resources it needs. It may be necessary for you to ask the offending party(ies) to stop or at least back down on what they are doing, at least until the materialized view is able to catch back up.

b. Check the Database. If there are a lot of changes or queries (more than normal) on the master table, that can cause issues as well. When using Materialized views, be cautious about bulk data loads/updates, as this can result in a back log. It would be best to apply the data in smaller batches over time, with refresh gaps in between, to avoid a tipping point scenario. A high number of I/O’s on the master/source table can cause refresh slow downs if occurring simultaneously.

c. Check Network response times and connectivity (if the Materialized view is on another machine. Low bandwidth can cause refresh issues. You may need a Network admin to assist you with this.

Tips on interpreting the data:

Between refreshes, you will see the numbers in this query grow. When a successful refresh occurs, you will see the numbers drop. With single materialized views or even multiple materialized views, associated with this mlog table, you may not see the record counts drop completely, as the materialized views will complete refreshing at different rates; the mlog table will also be getting written to simultaneously as the master table is having changes applied to it. It is only the changes that are no longer needed, for updating any of the registered materialized views, that gets cleaned out.

Like wise, you will see the mlog record counts constantly go up even if only 1 out of 4 materialized views are not refreshing. It may be that 3 of the 4 are refreshing fine, but one is not. This can cause the mlog table to grow rapidly if there is a high volume of transactions. Try to be aware of the tipping point (more of a range based on known behavior). If the “back log” in the mlog table gets too high, then it may become necessary to rebuild the failed materialized view. The others, if they have been refreshing successfully, will not need to be rebuilt. Once you drop the failed materialized view, you will see that the counts from the mlog table have dropped.

What I like to do is create a job that will run the above query every 15 minutes and record the counts, for that point in time, into a table. I prefer this as I can put the monitoring on auto pilot. You can do this more frequently if the refresh cycle is smaller than this, or if you do not notice any additional over head by running the query more frequently. It really gives a good picture of the behavior of the refresh abilities of your materialized views, especially if the data is transposed to a graph.

In the next post of this series, we will check the target location. That is where the materialized views are located. We will examine the receiving end of the refresh.

One Response to “My refresh materialized view is not refreshing – Part II”

  1. chong said

    This is a very helpful advice, thank you very much, I have posted some helpful tips on oracle 10g troubleshooting as well in my website

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: