Materialized View Refresh : Log Population and Purge

最新推荐文章于 2023-09-07 01:25:09 发布

congzhan1377

最新推荐文章于 2023-09-07 01:25:09 发布

阅读量171

点赞数

Doc ID: Note:236233.1 Type: BULLETIN
Last Revision Date: 09-DEC-2003 Status: PUBLISHED

Overview
========

The purpose of this article is to explain Oracle’s materialized view fast
refresh mechanism. It also discusses the performance, problems related to
materialized view log management.

NB: The terms ´snapshot’ and ‘materialized view’ are synonymous.

Article Contents

1. Fast Refresh.
1.1 Database Objects Involved In The Fast Refresh Mechanism.
1.2 Registration.
1.3 Fast Refresh Operation.

2. Snaphot Log Management.
2.1 When Snapshot Log Entries Can Be Purged ?.
2.1.1 How Fast Refresh Is Performed And When The Snapshotlog Gets Purged.
2.1.2 Complete Refresh.
2.1.3 Purge Problems.
2.1.3.1 Materialized View Logs Growing Too Large.
2.1.3.2 How To Purge Manually Snapshot Logs.
2.2 Managing Snapshot Log Space. Performance.
2.2.1 Truncating A Snapshot Log.
2.2.2 Reorganizing Master Tables That Have Snapshot Logs.

3. Related Bugs.
4. Reference.

1. FAST REFRESH.

To perform a fast refresh, the server that manages the snapshot first identifies
the changes that occurred in the master since the most recent refresh of the
snapshot and then applies them to the snapshot. Fast refreshes are more
efficient than complete refreshes when there are few changes to the master
table(s) because the participating server and network replicate a smaller amount
of data. You can perform fast refreshes of snapshots only when the master table
has a snapshot log.

1.1 DATABASE OBJECTS INVOLVED IN THE FAST REFRESH MECHANISM.

It is useful do understand the structure underlying snapshots and snapshot logs.

1.1.1 A snapshot consists of:

- A table in the snapshot site, which is referred to as the snapshot base
table.
- A materialized view, which has the same name of the snapshot and is based on the
snapshot base table
- An unique index on the snapshot base table
- An entry in SYS.SNAP$ defining the snapshot.
- An entry in SYS.SLOG$ at master site.

1.1.2 A snapshot log on a master table consists of:

- A master table on which the snapshot is based on.
- A log table to hold the information about the changed rows in the
master table. This table is referred to as the snapshot log table.
The snapshot log is a table named MLOG$_

Note 258634.1 . Materialized View registration at Master Site.

1.3 FAST REFRESH OPERATION.

The snapshot fast-refresh mechanism in Oracle7 is based on the ROWIDs of master
tables. When the master table is updated, a trigger will be fired which inserts
the ROWID of the inserted, updated or deleted rows into the snapshot log at the
master site.
Using the ROWID of the master table has the following disadvantages:

- ROWIDs do not persist through a reorganization of master tables, forcing
all snapshots to perform a complete refresh after a table reorganization.
- It is not possible to perform fast-refresh of simple snapshots with
subqueries.

Oracle8 introduces Primary Key snapshots. The fast-refresh mechanism
in Oracle8 uses the primary key of the master table rather than ROWIDs for
fast-refresh. ROWID based snapshot logs and refresh mechanism is still available
in Oracle8.

A snapshot can perform a fast refresh only if it can use the snapshot log. This
is determined during the setup phase by verifying the following conditions:

- The snapshot’s identifier must be present in SYS.SLOG$
- The snapshot’s last refresh time must be later than the oldest entry in
the snapshot log.

Basically the refresh operation consists of 3 phases:
1. Setup.
During this phase the PL/SQL RPC dbms_snapshot.set_up is called from the
snapshot site. Setup has to verify if the snapshot being refreshed is a
ROWID or primary key snapshot. Then it is necessary to verify if a
fast-refresh can be performed. Afterwards the snaptime$$ column is
updated in the snapshot log mlog$_t1 of the altered rows to its own
refresh date and time for the first snapshot that refreshes. This value
does not change until the rows are eventually purged from the log.

2. Refresh Operation.
2.1 Call dbms_snapshot.verify_log and determine if a fast-refresh can
take place. After dbms_snapshot.set_up is called a second check is made
by calling the PL/SQL RPC dbms_snapshot.verify_log which is also called
from the snapshot site to ensure that each refreshing snapshot can use
the snapshot log. In order for the snapshot log to be used the timestamp
of oldest/oldest_pk column in mlog$ must be older than the time of last
refresh.

2.2 Delete the rows that are no longer in the master table.

2.3 Applying modifications from the master.

3. Wrap-up.
The PL/SQL RPC dbms_snapshot.wrap_up called from the snapshot site checks
if the least recently updated snapshot has refreshed and therefore the
snapshot log entries can be purged.
If the log gets purged and an error occurs after this routine the next
time this snapshot refreshes, it will need to be entirely reinstantiated.
Then it is checked if registration of the snapshot is required on the
master. This is only the case if the snapshot id was increased/changed at
some point.

2. SNAPHOT LOG MANAGEMENT.

2.1 WHEN SNAPSHOT LOG ENTRIES CAN BE PURGED ?

Oracle automatically tracks which rows in a snapshot log have been used during
the refreshes of snapshots, and purges these rows from the log so that the log
does not grow endlessly. Because multiple simple snapshots can use the same
snapshot log, rows already used to refresh one snapshot may still be needed to
refresh another snapshot. Oracle does not delete rows from the log until all
snapshots have used them.

In the wrap-up phase of the refresh process, the master snapshot log is purged.
It deletes all unnecessary rows from the snapshot log. Rows in the snapshot log
are unnecessary if their refresh timestamps MLOG$_ Note 258634.1 . Materialized View registration at Master Site.
Note 236292.1 . Scripts to Report Information about Materialized View Logs
at the Master Site.

In this situation you may need to purge part of the materialized view log or
unregister the unused materialized view. The clean up of a snapshot at master
site includes. For each 'orphan' snapshot.

a. You should purge the associated snapshot log of the entries that were marked
for the target snapshot

BEGIN
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (SNAPOWNER => 'SCOTT',
SNAPNAME => 'EMP', SNAPSITE => 'SNAP_SITE');
END;
/

b. Unregister the snapshot:

BEGIN
DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (SNAPOWNER => 'SCOTT', SNAPNAME => 'EMP',
SNAPSITE => 'SNAP_SITE');
END;
/

If you execute the procedure DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG, after
unregistering the snapshot, with the following parameters:

BEGIN
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (SNAPOWNER => 'SCOTT',
SNAPNAME => 'EMP', SNAPSITE => 'SNAP_SITE');
END;
/

It will fail with error ORA-23424.
In this case you'd need to use the procedure based on the identification of the
target snapshot.

BEGIN
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id);
END;
/
You can find the snapshot id in dba_snapshot_logs view or dba_base_table_mviews view in Oracle9
and onwards.

2.1.3.2 HOW TO PURGE MANUALLY SNAPSHOT LOGS.

The Snapshot Logs can be purged using the following Oracle Replication
procedures within the DBMS_SNAPSHOT package through one of the following interfaces:

Note: DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.

2.1.3.2.1 To manually purge rows from a snapshot log, execute the PURGE_LOG stored
procedure of the DBMS_SNAPSHOT package at the database that contains the
log. For example, to purge entries from the snapshot log of the CUSTOMERS table
that are necessary only for the least recently refreshed snapshot, execute the
following procedure:

BEGIN
DBMS_SNAPSHOT.PURGE_LOG (
master => 'sales.customers',
num => 1,
flag => 'DELETE');
END;
/

2.1.3.2.2. The procedure DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG is called on the
master site to delete the rows in snapshot refresh related data dictionary tables
maintained at the master site for the specified snapshot identified by its snapshot_id
or the combination of the snapowner, snapname, and the snapsite. If the snapshot
specified is the oldest snapshot to have refreshed from any of the master tables,
then the snapshot log is also purged. This procedure does not unregister the snapshot.
In case there is an error while purging one of the snapshot logs, the successful
pur snapshot logs are not rolled back.
This is to minimize the size of the snapshot logs. In case of an error,
this procedure can be invoked again until all the snapshot logs are purged
Then this procedure will remove entries from the snapshot log (MLOG$_ Bug 2259259 . MLOG$ entries may not be purged after refresh for a 9i master site
with an 8i snapshot site. This can result in snapshot refreshes taking longer
and longer each time.
Fixed-Releases: 9.0.1.4, 9.2.0.2, 10.
Bug 1282043 . FAST REFRESH ON MATERIALIZED VIEWS TOO SLOW COMPARED TO COMPLETE REFRESH.
Fixed-Releases. 9.0.1.x
Bug 2685250 . DBMS_REPCAT_RGT.INSTANTIATE_ONLINE ADD ENTRIES TO SLOG$.
Fixed-Releases. 10.0
Bug 945661 . SNAPSHOT STILL FAST REFRESHED AFTER DBMS_SNAPSHOT.PURGE_LOG.
Fixed-Releases. 8.1.6

4. REFERENCES.

See also:

Oracle9i Replication.
Oracle9i Replication Management API Reference.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50262/，如需转载，请注明出处，否则将追究法律责任。