Page 722
unmanageable, the DBA should purge the logs. This can be done via the enterprise management tool, or the DBA can perform this with the stored procedure or API call DBMS_SNAPSHOT.PURGE_LOG. The syntax for this is as follows:
Execute dmbs_snapshot.purge_log(`[master table name]',number,'flag');
number is the number of historical snapshots to remove. If the DBA wants to remove all the logs in the snapshot log, this number should be very high. Care must be taken when using this. If all the logs are removed, the snapshot will have to be completely refreshed. This can be a problem if the table is very large.
NOTE |
The flag is an override value. If set to DELETE, then even if the number is set to 0, the logs will be deleted from the least recently refreshed snapshot.n |
The initial creation of a snapshot of a large table may be beyond the capabilities of the system. For example, if a snapshot of a large table with several million rows of data had to be replicated over unstable communication lines, it might be better to perform an offline instantiation. To perform this, several steps must be taken. This must be done carefully and, if possible, in a test environment with a great deal of storage space. Use these steps:
To create a snapshot at the remote site:
DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (gname='[groupname]'.sname=>'snapshotname',)This will create a snapshot "shell" for the data about to be imported.
Page 723
NOTE |
Just as tables cannot be created using a select on a LONG datatype, snapshots cannot support this select statement.n |
Complex snapshots consisting of multiple table joins can severely degrade performance. It is imperative that the query used to create a snapshot be thoroughly tested and tuned. This includes utilizing explain plan and tkprof prior to creating the snapshot. If required, create several single table snapshots at the child site and then create a view locally based on the physical snapshots. Performance will be significantly enhanced and network traffic will be reduced.
In order to keep the packages used to refresh a database in the library cache, it helps to pin these packages in memory. This will help prevent the package from being removed from memory. To pin a package, call the package and then use the package DBMS_SHARED_POOL. Prior to pinning a package, it must be referenced. The easiest way to reference a package is simply to recompile it. The syntax for this is
ALTER PACKAGE DBMS_SNAPSHOT.I_AM_A_REFRESH COMPILE;
There will be a response:
Package altered.
Now, pin the package with the following statement:
Execute DBMS_SHARED_POOL.KEEP(`DBMS_SNAPSHOT');
The response will be
PL/SQL procedure successfully completed.
This will improve performance. It may not significantly enhance performance, however, it does helpparticularly with those snapshots that are frequently refreshed.
Other packages that can be pinned for performance are
For more information on these packages, refer to the Oracle8 Server ReplicationReplication Manager API Reference.
Page 724
The init.ora table contains several parameters that directly impact the snapshot process. These parameters are defined in Table 28.2.
Table 28.2 Initialization Parameters for SnapshotsParameter | Description |
JOB_QUEUE_INTERVAL (1_3600) | The interval that the snapshot process wakes up. Care must be taken to ensure that this is not set so high as to interfere with the interval of the snapshot itself. |
JOB_QUEUE_PROCESSES (0_36) | Limits the number of processes for the snapshot process. Normally, one should be sufficient unless there are several snapshots or large snapshots that may interfere with the snapshot process. |
Page 725
Page 726