MV Refresh
PURPOSE
The purpose of this article is to provide an understanding of the locking and
performance issue related to materialized view refreshes. How a refresh process can impact and be impacted by other refresh processes and user
processes is also discussed. This article also provides some basic queries that can be used to monitor materialized view refreshes.
SCOPE & APPLICATION
This article is intended for DBA's and support analysts who are familiar with distributed materialized view concepts. Note 258021.1 How to monitor the progress of a materialized view refresh can be reviewed along with this article to gain a full understanding of the materialized view refresh process.
The information in this article is specific to distributed materialized views. while much of the information is applicable to local materialized views, there
may be differences in locking and performance expectations for local materialized views. For information specific to local materialized view
refreshes, please see the Oracle Data Warehousing Manual. From this point on, the term 'mview' will be used in place of 'materialized
view'.
'Materialized view' and 'Snapshot' are synonymous as of 8i.
CONTENTS
==
1. Refresh Locking
1.1 Locks Taken out by a Refresh Operation
1.2 Refresh Lock Duration
2. Refresh Performance
2.1 SQL Tuning for Refreshes
2.1.1 Tune the Mview Defining Query
2.1.2 Indexes and Refreshes
2.2 Avoid Locking Issues during Refreshes
2.3 System Performance Impact on Refreshes
2.3.1 Network Performance
2.3.2 I/O Performance
2.4 Rollback Segments (RBSs) and Refreshes
2.5 Mview log Maintenance
2.5.1 Modifications to the Mview Log
2.5.2 Orphaned Entries in the Mview Log
2.6 Updateable Refresh Optimization
3. Monitoring Refreshes
1. Refresh Locking
======
When an mview refresh operation takes place, the following happens:
1. SYS.SNAP$ and SYS.MLOG$ are updated to show the time of refresh.
2. If a complete refresh on only one mview is performed using the
DBMS_MVIEW.REFRESH() api, the mview base table is truncated.
If the refresh is fast, or involves multiple mviews, or is being
performed on a refresh group, rows in the mview base table(s) are deleted.
3. All rows selected from the master table are inserted into the snapshot
base table.
4. SYS.SLOG$ is updated with the time of refresh.
In the case of a fast refresh, an additional step is performed:
5. Rows which are no longer needed for a refresh by any mview are
deleted from the mview log - <schema>.MLOG$_<table>.
Note: For more information on refresh steps, please see Note 258021.1
How to monitor the progress of a materialized view refresh
The Oracle read consistent mechanism is used to select a consistent set of
rows from the MASTER table, therefore it is NOT necessary to take out any
locks against the MASTER table.
NOTE: In Oracle 8i locks on the master table were added to support query
rewrite functionality on mviews. This has resulted in deadlocks
during fast refreshes and is addressed in Bug 1376209.
Should you experience this during a fast refresh and you are NOT
using query rewrite, you can set an event to circumvent this.
Please contact Oracle support for more information on this event.
1.1 Locks Taken out by a Refresh Operation
To facilitate the dml activity required by a refresh, locks on the relevant
rows in the following tables will be required:
At the master site:
Associated rows in
SYS.MLOG$
SYS.SLOG$
for the snapshot being refreshed are locked.
for fast refresh:
<schema>.MLOG$_<table> (base table for the mview log)
In 8i+ to support query rewrite feature if the above mentioned
event is not set:
Master table (shared, then exclusive locks are taken at different
points during the refresh)
At the Snapshot site:
SYS.SNAP$
SYS.SNAP_REFTIME$
The base table for the mview (exclusive table lock is taken)
In pre 8.1 compatible instances, the base table name is
<schema>.SNAP$_<mview_NAME>.
In 8.1+ compatible instances, the base table name is
<schema>.<mview_name>
If an updateable mview:
<schema>.USLOG$_<mview_NAME> (exclusive table lock is taken)
If a refresh group is used:
SYS.RGROUP$
Locking the base table of read-only mviews should not have any impact on the
users as the mviews will not have DML performed against them. However, with
updateable mviews, it is necessary to temporarily prevent user changes during a
refresh. The mview base table and the updateable mview log will be locked
exclusively during a refresh to accomplish this. It is possible to see a
temporary hang on mview updates while the updateable mview is being refreshed.
As a result of the refresh locking behavior, user applications may encounter
waits and possibly time-outs if they are accessing mviews that are being
refreshed. The reverse is also true; mview refreshes may encounter waits and
time-outs if a user application holds locks that the refresh operation needs.
1.2 Refresh Lock Duration
The locks taken out by the refresh are held for the duration of the refresh
transaction.
The refresh transaction duration is dictated by the atomic_refresh parameter
in the dbms_mview.refresh() api. The default value for atomic_refresh is TRUE.
If atomic_refresh is set to TRUE, all of the mviews listed in the list/tab
parameter will be refreshed in one single transaction. The larger the number
of mviews in the list, and the larger the amount of data that must be
transferred, will directly impact the length of time the refresh holds the
locks. This could cause other refreshes to wait, and possibly time-out if they
must access the same rows. This can be the case when multiple mviews mastered
by the same table are refreshed concurrently.
If atomic_refresh is set to FALSE, each mview listed in the list/tab parameter
will be refreshed in a single transaction. Thus the locks will be released
after each mview is refreshed. If another session takes the lock before the
next mview in the refresh list begins refreshing, then that refresh must wait
for the session to release the lock. This could result in slower than expected
performance or possibly a time-out.
Note: For more information on atomic_refresh, please see the Oracle
Replication API manual.
It should be noted that using refresh groups does not allow for atomic_refresh
to be set to FALSE as dbms_refresh.refresh and dbms_refresh.make do not supply
the atomic_refresh parameter. Therefore, all members of the refresh group will
be refreshed in a single transaction.
2. Refresh Performance
==========
The performance of a refresh operation is affected by a number of factors.
The most common being SQL tuning, locking, system performance, hardware,
and the amount of data being moved as part of the refresh. Each situation
will vary as they depend on the factors listed. Below are the considerations
a DBA should be aware of when attempting to improve refresh performance.
2.1 SQL Tuning for Refreshes
The Refresh operation is comprised of a series of selects, updates, inserts
and deletes on various tables. The performance of these dml operations can
be improved with the same SQL tuning methods used for user dml operations.
The following explains what you can do at different levels to help optimize
refresh dml.
2.1.1 Tune the Mview Defining Query
The refresh of an mview will never be run faster than it's defining query runs
on the master site. In fact, it can be expected to run slightly slower due to
additional operations that must take place along with running the defining
query, and the additional transfer of data required.
The first step to optimizing refresh performance is to tune the defining query
for a distributed environment. Information on the methods used to do this can
be found in the Oracle Tuning Manual under 'Tuning Distributed Queries'.
2.1.2 Indexes and Refreshes
One tool used to improve SQL performance is the INDEX. Users should be aware of
the positive and negative impacts of indexes on refresh performance.
MVIEW Indexes
When mviews are created, certain indexes are also created at the mview site to
assist in data retrieval for refresh operations. Users can add additional
indexes to the mview base table to further optimized data retrieval from the
mivew. However these indexes can have a negative impact on refresh performance.
As mentioned in section 1, the refresh will remove and replace rows in the
mview base table during refresh, and thus must also maintain any indexes on the
mview base table. More indexes require more work for the refresh, which
requires more time. Additionally, restrictive indexes (unique, constraint) can
interfere with the refresh operation and keep it from completing successfully;
especially if those restrictions are not enforced on the master data.
MVIEW LOG Indexes
The creation of an mview log does not include the creation of indexes on the
mview log base table. This means that every fast refresh that accesses the
mview log must do a full table scan. This can be costly to performance if
the mview log is large. It has proven beneficial for users to create indexes
on the mview log base table to improve the dml operations performed on it by
the fast refresh operation. Adding an index to the pk column in mlog$_<table>,
and an index on snaptime$$ in mlog$_<table> will avoid full table scans on
mlog$_<table> during fast refresh. The impact of these indexes is positive for
the fast refresh option, but may have a negative performance impact on heavy
loads to the master table as the indexes must be updated for every row updated
in the master table, and subsequently, the mview log base table. However, the
gains can significantly out-weigh the loses in most cases.
2.1.3 MVIEW Log Storage Parameters
If the MVIEW will be refreshed via complete refresh then create it using
PCTFREE set to 0 and PCTUSED set to 99.
Avoid using CLOB columns to store chars less than 4K, which can be represented
using VARCHAR2. During the refresh, for each LOB row there is one network
roundtrip. For the case where LOBs are not involved more than one record is
transmitted in one network package, depending on the rowsize.
Set the PCTFREE parameter to 0 for a log on a master table that is used by only
one materialized view. Set the PCTFREE parameter higher for a log on a master
table that has multiple materialized views, to prevent row chaining within the
log table. Remember, the log is updated each time there is a change to a master
table which will be used during the next refresh operation. This update
activity requires some PCTFREE block space.
In addition, it may improve I/O performance to store the materialized view log
in a different tablespace from the master table, on a different disk drive
accessed by a different I/O channel.
2.2 Avoid Locking Issues during Refreshes
The impact of multiple concurrent mview refreshes on the same master table
can significantly impact the performance of refreshes due to the locking
issues discussed in section 1. You can avoid slow refresh performance or
time-outs due to locking issues by staggering refreshes to the same master
site, and/or the same master tables.
Additionally, avoid bulk updates to the master tables while a refresh is being
performed on mviews mastered by those tables. If a bulk update is started
while a refresh is active, the refresh must retrieve the undo generated by the
update in order to maintain transactional consistency. This can significantly
increase the work done by the refresh which in turn increases the time needed.
It could also result in a failed refresh due to ora-1555.
If a refresh is in progress, users can expect to see a slow down or
interruption in data retrieval/changes on mviews. Data is deleted or truncated
from the mview base table during refresh depending on the conditions covered
in step 2 of the refresh operation in section 1, and the mview is locked. If
the data has not been truncated, selects can be performed on the mview during
refresh, but will result in the 'pre refresh' data as the row changes are
rolled back to the values just prior to the refresh. This rollback can add
time to the retrieval performance. If the data in the mview was truncated, no
rows will be returned from the retrieval. User changes to updateable mviews
will be delayed if made while the mview is being refreshed. This is due to
the exclusive lock on the mview base table.
2.3 System Performance Impact on Refreshes
The refresh operation will only be as fast as the system allows.
2.3.1 Network Performance
The speed and accessibility of the network connection used by the dblink to
transfer data has a direct impact on refresh performance. If the network
is slow, the data transfer is slow, and thus the refresh is slow.
To avoid refresh performance problems due to network issues, make sure that
your network connection is tuned for optimum throughput and performance.
You can work with your network administrator to achieve this.
Busy networks can also have a negative impact refresh performance. To avoid
this, consider refreshing large mviews during low traffic times. Additionally
stagger the interval of refresh groups to the same master site to reduce the
contention of multiple refresh operations for network resources.
2.3.2 I/O Performance
If multiple refreshes are running concurrently and must read/write to the same
disk, I/O performance can have an impact on refresh performance. While this
is not the largest contributing factor to poor refresh performance, it is worth
consideration in heavy data change environments. If possible, consider disk
stripping with mviews belonging to different refresh groups on the mview site,
and the mview logs at the master site. For more information on optimizing I/O,
please see the Oracle Performance Tuning Manual.
In many cases, there may be nothing that can be done to improve I/O due to
hardware and o/s limitations. If this is the case, you can improve refresh
performance by staggering the interval of refresh groups with mviews that
reside on the same disk, so that only one refresh operation is utilizing I/O
at a given time.
2.4 Rollback Segments (RBSs) and Refreshes
Due to all the dml involved with a refresh operation, a significant amount
of undo is generated by a refresh. To avoid performance issues and errors
during a refresh, consider the following:
1) Make sure RBSs are large enough to handle the refresh
Rule of thumb:
Master site: RBS 5.5 times the size of the mview log
Mview site: RBS 2.5 times the size of the mview
2) Avoid using 'optimal' on the master site RBSs as this could result in
ora-1555 on long running refreshes.
If optimal must be used:
a) Stagger user updates and refreshes on the same master tables to
avoid the 'optimal' shrinking the user update RBS before the
refresh can access it.
b) Reduce the number of mviews being refreshed in a refresh operation
to reduce the time needed to complete the refresh.
c) Reduce the interval of the refresh operation for fast refreshes to
reduce the amount of data needed to be retrieved and time needed
to complete each refresh. You should not reduce the interval lower
than the time it takes for the refresh to complete. Also keep in
mind intervals of other refresh groups for staggering purposes.
Note: For more information on ORA-1555 please see
Note 18954.1OERR: ORA 1555 "snapshot too old (rollback segment
too small)"
2.5 Mview log Maintenance
The 'health' of the mview log for the master table can have an impact on
refreshes.
2.5.1 Modifications to the Mview Log
If the mview log has been created, recreated, or altered since the last refresh
or creation of an mview that would use it for a fast refresh, it can not be
used until that mview has performed a complete refresh. This is to ensure that
the next refresh will pick up all changes made to the master table since the
mview last refreshed. If the mview log has changed since that last refresh,
we can not guarantee that it contains all the required changes. Therefore, a
complete refresh is necessary. For an illustration of this concept, please
see Note 216279.1 Effect of "alter snapshot log" on fast refresh capability. The
DBMS_MVIEW api package provides procedures that will allow maintenance the
mview log and still maintain 'fast refreshability'. For more information on
the DBMS_MVIEW package, please see the Oracle Replication API Manual, and the
Oracle Supplied PL/SQL Packages Manual.
2.5.2 Orphaned Entries in the Mview Log
Another 'health' consideration for mview logs is the actual rows it contains.
A healthy mview log will only contain rows that are needed by mviews that
still need to pick up those changes on their next refresh. If a row is not
needed, it is purged from the mview log to reduce the amount of data.
However, it is possible for the mview log to believe that rows are still
needed by an mview, when that mview no longer exists. This is because the
mview was not properly unregistered from the master site when it was dropped
or lost. The result is that the mview log continues to collect data but does
not delete it, so each subsequent refresh must traverse more and more data.
This will eventually impact the performance of the refresh operations. To
fix this, the lost or 'orphaned' mview must be unregistered at the master
site manually. For information on how to do this, please see Note 1031924.6
SNAPSHOT LOGS GROWS DUE TO MISSING/INVALID SNAPSHOT
2.6 Updateable Refresh Optimization
Because updateable mviews are locked during refresh, it is desirable to reduce
the refresh time required as much as possible. When an updateable mview is
refreshed, it must first push it's data changes to the master site, then pull
the master table data down. This is actually done in two phases, the push and
pull phase (for more information on these phases, please see Note 258021.1
How to monitor the progress of a materialized view refresh). If there are a
number of changes at the mview site, this can take a considerable amount of
time. To reduce the amount of work that the refresh must complete, you can
schedule a separate job to send the changes to the master site without a
refresh. This job would be the advanced replication 'push', the same used for
master-to-master replication. This push will send the changes to the master
site without locking the updateable mview that generated them. Then, when a
refresh is started, the push phase of the refresh will be considerably reduced
as most of the changes have already been pushed to the master table. This in
turn reduces the amount of time needed to perform the refresh, and the amount
of time the updateable mview is locked. The pull phase is not affected by the
extra push job. It will still need to pull down all changes made by it's own
push and those made by the additional push job, since the last refresh.
To schedule an additional push job for updateable mviews, use the
DBMS_DEFER_SYS.SCHEDULE_PUSH() api. For more information on this api, please
see the Oracle Replication API Manual.
Another consideration for updateable mviews is to refresh each in it's
own refresh group. If the mview is the only member of the group, the refresh
transaction will be completed when the mview has finished it's refresh. This
is due to the transaction duration of refresh groups. The more members, the
longer the refresh transaction, the longer the mview base tables are locked.
3. Monitoring Refreshes
===========
The following queries can be used to monitor mview refreshes.
When an mview was last successfully refreshed
The following query will indicate when an mview was last successfully
refreshed and what type of refresh was done.
column last_refresh_type format a18
column owner format a7
column mview_name format a12
select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews;
OWNER MVIEW_NAME LAST_REFRESH_TYPE LAST_REFRESH_DATE
------- ------------ ------------------ ------------------
SCOTT CREF_DEPT COMPLETE 24-JAN-03 22:17:25
SCOTT MYDEPT FAST 23-JAN-03 15:44:24
SCOTT MY_EMP FAST 27-JAN-03 21:40:13
SCOTT UPSNP_DEPT COMPLETE 24-JAN-03 14:35:37
Status of a Refresh Group Using the Job Queue
The following query shows all the jobs scheduled in the job queue to perform
refreshes. It shows when the last refresh was run, the total amount of time
spent by the system running the job since the instance started, if the job is
broken or has failures, and when the next refresh will run.
column broken format a6
alter session set nls_date_format='DD-MON-YY hh24:MI:SS';
select job, last_date last_refresh,
next_date next_refresh, total_time,
broken, failures, what
from dba_jobs
where what like '%dbms_refresh%';
JOB LAST_REFRESH NEXT_REFRESH TOTAL_TIME BROKEN FAILURES
----- ------------------ ------------------ ---------- ------ ----------
WHAT
1 05-FEB-03 16:37:57 05-FEB-03 16:47:57 2 N 0
dbms_refresh.refresh('"SYS"."REFRESHG1"');
Note: TOTAL_TIME - you can determine how long (in seconds) a refresh job takes
to run by querying total_time for the job before and after the job runs,
and calculating the difference. This is helpful in determining what
interval to use to stager refreshes.
Refreshes Currently Running
The following query shows all refresh jobs that are currently running, when
they started, and if any have failed.
select r.job, r.this_date, r.failures
from all_jobs_running r, all_jobs j
where j.job = r.job
and j.what like '%dbms_refresh%';
JOB THIS_DATE FAILURES
---------- ------------------ ----------
1 05-FEB-03 16:37:57 0
If an mview belongs to a refresh group, you can also use the ALL_REFRESH
and ALL_REFRESH_CHILDREN to obtain information about the mview refresh
select r.rowner, r.rname, r.job, c.name,
r.next_date next_refresh, r.broken
from all_refresh r, all_refresh_children c
where r.job = c.job;
ROWNER RNAME JOB NAME NEXT_REFRESH BROKEN
---------- --------------- ----- ---------- ------------------ ------
SYS REFRESHG1 1 MYDEPT 05-FEB-03 16:47:57 N
Last refresh times can also be seen at the master site. To do this, please
see Note 114743.1 How to Check the Last Refresh Time of Snapshots from
the Master Site
New 9i Refresh Views and Procedure
9i introduces two new views that can be used to monitor refreshes:
V$REPLPROP - monitor the push phase of an updateable refresh
and any scheduled push jobs
V$MVREFRESH - monitor the pull phase of an updateable refresh or
a read-only refresh
9i also introduces the DBMS_MVIEW.EXPLAIN_MVIEW procedure to determine
refresh capabilities (fast vs complete) for an mview. For more information
on this procedure, please see the Oracle 9i Replication API Manual, and the
Oracle 9i Supplied PL/SQL Packages Manual
RELATED DOCUMENTS
For more Information on monitoring refreshes, please reference
Note 258021.1 How to monitor the progress of a materialized view
refresh
The test case information used for this bulletin can be found in
Note 258258.1 Monitoring Locks During Materialized View Refreshes
Additional References
============
Oracle Replication Manual
Oracle Replication API Manual
Oracle Tuning Manual
No comments:
Post a Comment