ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Materialized View Refresh : Log Population and Purge

Materialized View Refresh : Log Population and Purge

原创 Linux操作系统 作者:NinGoo 时间:2019-05-25 20:00:07 0 删除 编辑

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$_


An entry in SYS.MLOG$ defining the snapshot log.
- The snapshot log expedites the refresh process of a simple snapshot
allowing a 'FAST' refresh. is used to determine with master table rows
need to be refreshed down to the snapshot site, either by looking at the
rowids or primary keys (in the case of PK snapshots which is the default
for Oracle8).
- A snapshot that is NOT simple cannot be fast refreshed so it will NOT need
a snapshot log.
- One snapshot log is be used for multiple snapshots based on the same
master table.

For fast-refresh on ROWID snapshots, modified rows of the master table are
recorded in the snapshot log using the ROWID. The rows in the snapshot base
table contain the rows of the master table. During fast-refresh, distributed
queries based on the recorded rows in the snapshot log and the related rows in
the master table will copy the row from the master table to the snapshot base
table.
The snapshot log structure is as follows for ROWID snapshots.

m_row$$ varchar2(255) ROWID of master table (18 bytes)
snaptime$$ date first snapshot sets initial refresh time
dmltype$$ varchar2(1) Type of DML
old_new$$ varchar2(1) O , N, U
change_vector$$ raw(255) Used for subquery and LOB snapshots

Primary key snapshots use the master table’s primary key instead of ROWIDs to
drive fast-refresh. The m_row$$ column is removed from the snapshot base table
as well as from snapshot logs.

The snapshot log structure is as follows for PRIMARY KEY snapshots.

Primary key col Same as master table
snaptime$$ Date first snapshot sets initial refresh time
dmltype$$ varchar2(1) Type of DML
old_new$$ varchar2(1) O , N, U
change_vector$$ raw(255) Used for subquery and LOB snapshots.

There are 2 new columns added to the snapshot log at the master site:

- old_new$$:

This new column is added to allow the fast-refresh mechanism to distinguish
between rows inserted at the snapshot site and rows with modified primary key
values. It’s used for updateable snapshot logs.

There are 3 possible values:
‘O’: This entry logs a deleted row or the old values of a modified
primary key
‘N’: This entry logs a newly inserted row or the new values of a modified
primary key
‘U’: This entry logs an updated row where the primary key values were
modified.
Primary key snapshot logs will record updates to primary key values as two
log entries
- deletion of the row with the old primary key values.
- insertion of the row with the new primary key values.

- change_vector$$:

The change_vector$$ is added to the mlog$_t1 in order to be able to
fast-refresh subquery and LOB snapshots. The column is a bit vector that
indicates which columns in the master table were
changed by a DML statement.


1.1.3 An AFTER ROW trigger on the master table named TLOG$_.

From Oracle8 and onward this trigger is kernelized and is no longer visible in
the data dictionary. The same trigger can support both ROWID and primary key
snapshots and will populate the MLOG$_
with the proper values.
It also populates the SNAPTIME$$ column (indicating the lastest refresh time
so far a particular row) and the DMLTYPE$$ column. The snaptime$$ column is
populated based on the value of the snaptime column in snap$ table at the
snapshot site and is not updated until the log is first used by a snapshot
refresh.

1.2 REGISTRATION.

Oracle automatically tries to register a materialized view at its master site
or master materialized view site when you create the materialized view, and
unregisters when you drop it. The same applies to materialized view groups.
Registration of fast refreshable materialized view logs in the master database
(SYS.SLOG$) is needed to perform fast refreshes. This information is also used
to maintain the materialized view log of the master table.

Refer to the following article for additional information about the snapshot
registration process:

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$_
.SNAPTIME$$ are older
or equal than the oldest entry in SLOG$.SNAPTIME for this log.

MLOG$_
.SNAPTIME$$ <= MIN (SLOG$.SNAPTIME)

In another words while the oldest SNAPTIME in SLOG$, for the related snapshot
log, was older than MLOG$_
. SNAPTIME$$ column these rows were not never
purged:

MLOG$_
.SNAPTIME$$ > MIN (SLOG$.SNAPTIME)

2.1.1. HOW FAST REFRESH IS PERFORMED AND WHEN THE SNAPSHOTLOG GETS PURGED.

In this sample we show how a fast refresh is performed and when the snapshot log
gets purged.

In this case a primary key (default value) snapshot log on EMP has been created
at master site. There are 3 fast refresh snapshots based in the EMP table.

When you create a snapshot log an entry in sys.MLOG$ is added. Its YOUNGEST and
OLDEST_PK (or OLDEST if it is a rowid materialized view) columns are updated
with the creation timestamp.

1. create snapshot log on scott.emp (at master site).

select master, to_char(oldest_pk,'mm/dd/yyyy hh24:mi:ss') OLDEST_PK,
to_char(youngest,'mm/dd/yyyy hh24:mi:ss') YOUNGEST from sys.mlog$
where master='&table_name'

MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 12-02-2003 11:04:38 12-02-2003 11:04:38


2. create snapshot emp refresh fast as select * from scott.emp@master.world;

When we create a snapshot, at master site:

2.1 If a materialized view log exists the corresponding row in SYS.MLOG$ is
updated. Its YOUNGEST column is updated with the creation timestamp. If there
are not other materialized views of this master the OLDEST_PK (or OLDEST if it
is a rowid materialized view) it also updated with the same value.

select master, to_char(oldest_pk,'mm/dd/yyyy hh24:mi:ss') OLDEST_PK,
to_char(youngest,'mm/dd/yyyy hh24:mi:ss') YOUNGEST from sys.mlog$
where master='&table_name'


MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 12-02-2003 11:19:13 12-02-2003 11:19:13


2.2 A row is inserted in SYS.SLOG$ only if a materialized view log exits. The
SNAPTIME column is updated with the materialized view creation timestamp. The
SNAPTIME column in SYS.SLOG$ is the underline column of
dba_snapshot_logs.current_snapshots.


COLUMN snapshot_id HEADING 'SnapshotID' FORMAT b9999999999
COLUMN owner HEADING 'Owner' FORMAT A6
COLUMN name HEADING 'Mview Name' FORMAT A30
COLUMN snapshot_site HEADING 'Mview Site' format a30
COLUMN current_snapshots HEADING 'Last Time Refresh' format a21

select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
and l.master='&table_name'


SnapshotID Owner Mview Name Mview Site Last Time Refresh
----------- ------ ------------------------------ ------------------------------ -------------------
331 SCOTT EMP_SNAP SNAP_SITE 02/12/2003 11:19:13


3. After creating the remaining two snapshots. We execute the same queries:

3.1 The YOUNGEST column in SYS.MLOG$ is updated with the last creation timestamp.
As there are other materialized views of this master the OLDEST_PK (or OLDEST
if it is a rowid materialized view) it does not update.

MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 02/12/2003 11:19:13 02/12/2003 11:52:21

3.2 A row for each materialized view is inserted in SYS.SLOG. The SNAPTIME
column is updated with the materialized view creation timestamp.

SnapshotID Owner Mview Name Mview Site Last Time Refresh
----------- ------ ------------------------------ ------------------------------ -------------------
331 SCOTT EMP_SNAP SNAP_SITE 02/12/2003 11:19:13
332 SCOTT EMP_SNAP1 SNAP_SITE1 02/12/2003 11:50:43
333 SCOTT EMP_SNAP2 SNAP_SITE2 02/12/2003 11:52:21

4. Changes are made to Master Table: EMP. The internalized trigger fires
and updates the snapshot log (MLOG$_EMP) .

4.1 INSERT INTO SCOTT.EMP VALUES (9969, 'SMITH', 'CLERK', 7902,
TO_DATE('17-FEB-1980', 'DD-MON-YYYY'), 800, NULL, 20);
COMMIT;

4.2 The trigger records, in this case primary key information, as well as update
MLOG$_EMP.SNAPTIME$$ with a default value 01-JAN-00 (01/01/4000 12:00:00).
In this way we indicate that no snapshots have refreshed these records yet.

5. When the first snapshot is refreshed:
EXEC DBMS_SNAPSHOT.REFRESH('SCOTT.EMP_SNAP1','F');

The MLOG$_EMP.snaptime$$ column is populated based on the value of the snaptime
column in snap_reftime$ table at the snapshot site.
At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST
or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for
a particular master table, SLOG$ table keeps track of the snapshots for a
particular master table based last refresh time SLOG$.SNAPTIME matches the
corresponding column in SNAP_REFTIME$ at the snapshot site.
After the first refresh we execute the above queries:

MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 02/12/2003 11:19:13 02/12/2003 12:18:51


SnapshotID Owner Mview Name Mview Site Last Time Refresh
---------- ------ ------------------------------ ------------------------------ -------------------
331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 11:19:13
332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51*
333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 11:52:21


The column MLOG$_EMP.SNAPTIME$$ of the related rows has been update with the
refresh time: 02/12/2003 12:18:51. In this refresh process the OLDEST_PK column
has not been update because it's still the oldest refresh time.

Can we purge the snapshot log now?
The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$.
Since the oldest SLOG$.SNAPTIME is before 02/12/2003 12:18:51 , it does not
purge the snapshot log. (There are still 2 snapshots that have not yet refreshed
the rows in MLOG$_EMP)

6. When the second snapshot is refreshed.
At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST
or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for
a particular master table, SLOG$ table keeps track of the snapshots for a
particular master table based last refresh time SLOG$.SNAPTIME matches the
corresponding column in SNAP_REFTIME$ at the snapshot site.
The MLOG$_EMP.snaptime$$ column is not updated. In this refresh process the
OLDEST_PK column has been updated with the oldest refresh time in SLOG$.SNAPTIME.

MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 02/12/2003 11:52:21 02/12/2003 12:40:39


SnapshotID Owner Mview Name Mview Site Last Time Refresh
---------- ------ ------------------------------ ------------------------------ -------------------
331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 12:40:39*
332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51
333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 11:52:21


Can we purge the snapshot log now?
The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$.
Since the oldest SLOG$.SNAPTIME is before 02/12/2003 12:18:51 , it does not
purge the snapshot log. (There are still 1 snapshot that has not yet refreshed
the rows in MLOG$_EMP)


7. When the last snapshot is refreshed.
At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST
or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for
a particular master table, SLOG$ table keeps track of the snapshots for a
particular master table based last refresh time SLOG$.SNAPTIME matches the
corresponding column in SNAP_REFTIME$ at the snapshot site.
In this refresh process the OLDEST_PK column has been updated with the oldest
refresh time in SLOG$.SNAPTIME.

MASTER OLDEST_PK YOUNGEST
------------------------------ ------------------- -------------------
EMP 02/12/2003 12:18:51 02/12/2003 12:50:27


SnapshotID Owner Mview Name Mview Site Last Time Refresh
---------- ------ ------------------------------ ------------------------------ -------------------
331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 12:40:39
332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51
333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 12:50:27

Can we purge the snapshot log now?

The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$.
Since the oldest SYS.SLOG$.SNAPTIME is now the same as that in MLOG$_EMP, Oracle
knows that all snapshots have refreshed those records in MLOG$_EMP.
The rows with a MLOG$_EMP.SNAPTIME$$ equal to or older than the oldest
SYS.SLOG$.SNAPTIME for EMP are purged.

2.1.2 COMPLETE REFRESH.

In an environment with multiples snapshots for a master table. The complete
refresh does the same mlog management than fast refresh.

2.1.3 PURGE PROBLEMS.

2.1.3.1. MATERIALIZED VIEW LOGS GROWING TOO LARGE.


As a result of how Oracle purges rows from a snapshot log, unwanted situations
can occur that cause a snapshot log to grow indefinitely when multiple snapshots
are based on the same master table. For example, such situations can occur when
more than one snapshot is based on a master table and one of the following
conditions is true:

- One snapshot is not configured for automatic refreshes and has not been
manually refreshed for a long time.

- One snapshot has an infrequent refresh interval, such as every year
(365 days).

- A network failure has prevented an automatic refresh of one or more of the
snapshots based on the master table.

- A network or site failure has prevented a master from becoming aware that
a snapshot has been dropped. There is still an orphan entry for this
snapshot in SYS.SLOG$ at master site and then its SNAPTIME in SLOG$ will
not longer be updated. The oldest value for SNAPTIME will always be older
than the MLOG$_EMP.SNAPTIME$$. This means that the entries in MLOG$_EMP
will never be purged during a refresh.

Refer to the following articles for additional information about to find orphan
snapshots.

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$_
)
as well as remove the entry of that snapshot from the sys.slog$ table.

This procedure has two interfaces:

PROCEDURE purge_snapshot_from_log( snapowner IN VARCHAR2,
snapname IN VARCHAR2,
snapsite IN VARCHAR2);

PROCEDURE purge_snapshot_from_log( snapshot_id IN BINARY_INTEGER);

If the snapshot has been purged due a mistake and the related entry in SYS.SLOG$
has been deleted. Next fast refresh will fail, you must execute a complete refresh
that will once again register the snapshot by inserting an entry into the sys.slog$
table.


2.1.3.2.3 To unregister the snapshot from regsnap$ it is necessary to execute.

DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (snapowner IN VARCHAR2, snapname IN VARCHAR2,
snapsite IN VARCHAR2);


2.2 MANAGING SNAPSHOT LOG SPACE. PERFORMANCE.

The space used by the snapshot is not a function of the refresh workload in the sense
that the snapshot log does not grow as the number of snapshots based on the master table
increase. However, if some of the snapshots based on the same master table are not
refreshed at the same time as the others, the rows required to refresh that snapshot
will still be kept in the snapshot log, causing it to grow. In our test environment
all the snapshots are refreshed concurrently which eliminated that possibility.
The size of the snapshot log for ROWID snapshots is only a function of number of rows
in the log, as the log does not depend on the actual row size in the table.
Primary Key snapshot logs, on the contrary, include the primary key of the table in the
snapshot log and this makes the snapshot log space usage a function of primary key column
length combined with the number of rows in the snapshot log.
When designing snapshots there are a few things to keep in mind regardless of the
snapshot type:

- Keep the snapshot log as small as possible. The size of the snapshot log directly
has an effect on the fast refresh performance. Since there is no index on
the snapshot log, each refresh operation has to do a full table scan on this table.
If there are some snapshots that are not fast-refreshed for a long time,
this may cause the log to grow out of bounds. In this case it is advisable to purge
the log at the expense of a complete refresh of a few snapshots. The snapshot
registration feature in Oracle8 may come very handy in identifying these snapshots.

- Tune disk IO for the master tables and snapshot logs. Striping the snapshot
log and the master table datafiles is as important as striping other data and
index datafiles. When multiple snapshots start refreshing from the same snapshot
concurrently the heavy disk read requests of full scans can easily saturate the
disk IO subsystem.

2.2.1 TRUNCATING A SNAPSHOT LOG.

If a snapshot log grows and allocates many extents, purging the log of rows
does not reduce the amount of space allocated for the log. To reduce the space
allocated for a snapshot log:

1. Acquire an exclusive lock on the master table to prevent updates from occurring
during the following process. For example, issue a statement similar to the following:

LOCK TABLE scott.emp IN EXCLUSIVE MODE;

2. Using a second database session, copy the rows in the snapshot log (in other words,
the MLOG$ base table) to a temporary table. For example, issue a statement similar
to the following:

CREATE TABLE scott.templog AS SELECT * FROM scott.mlog$_emp;

3. Using the second session, truncate the log using the SQL statement TRUNCATE.
For example, issue a statement similar to the following:

TRUNCATE scott.mlog$_emp;

4. Using the second session, reinsert the old rows so that you do not have to perform
a complete refresh of the dependent snapshots. For example, issue a statement similar
to the following:

INSERT INTO scott.mlog$_emp SELECT * FROM scott.templog;
DROP TABLE scott.templog;

5.Using the first session, release the exclusive lock on the master table by performing
a rollback:

ROLLBACK;

Note: Any changes made to the master table between the time you copy the rows to a new
location and when you truncate the log do not appear until after you perform a complete
refresh. Then it’s better to truncate the snapshot log when it is empty.
Only the owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can
truncate a snapshot log.

2.2.2 REORGANIZING MASTER TABLES THAT HAVE SNAPSHOT LOGS.

To improve performance and optimize disk use, you can periodically reorganize tables.
This section discusses how to reorganize a master table and preserve the fast refresh
capability of associated snapshots.

- Reorganization Notification.

When you reorganize a table, any ROWID information of the snapshot log must be invalidated.
Oracle detects a table reorganization automatically only if the table is truncated as part
of the reorganization. See "Method 2 for Reorganizing Table T".

If the table is not truncated, Oracle must be notified of the table reorganization.
To support table reorganizations, two procedures, DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION
and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION, notify Oracle that the specified table has
been reorganized. The procedures perform clean-up operations, verify the integrity of the
logs and triggers that the fast refresh mechanism needs, and invalidate the ROWID information
in the table's snapshot log. The inputs are the owner and name of the master table to be
reorganized. There is no output.

- Truncating Master Tables.

When a table is truncated, its snapshot log is also truncated. However, for primary key
snapshots, you can preserve the snapshot log, allowing fast refreshes to continue.
Although the information stored in a snapshot log is preserved, the snapshot log
becomes invalid with respect to ROWIDs when the master table is truncated.
The ROWID information in the snapshot log will seem to be newly created and cannot be
used by ROWID snapshots for fast refresh.

If you specify the PRESERVE SNAPSHOT LOG option or no option, the information in
the master table's snapshot log is preserved, but current ROWID snapshots can use
the log for a fast refresh only after a complete refresh has been performed.
This is the default behaviour.


If the PURGE SNAPSHOT LOG option is specified, the snapshot log is purged along with
the master table.

Examples:

Either of the following two statements preserves snapshot log information when the
master table named ORDERS is truncated:

TRUNCATE TABLE orders PRESERVE SNAPSHOT LOG;
TRUNCATE TABLE orders;

The following statement truncates the snapshot log along with the master table:

TRUNCATE TABLE orders PURGE SNAPSHOT LOG;


3. RELATED BUGS.

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    129210