Previous | Table of Contents | Next

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

Understanding Limitations of Snapshots

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:

  1. In the production database, create a snapshot log for each of the master tables (the tables that will have snapshots in the remote site).
  2. Using a new schema and in the test database, create a snapshot referencing the production database containing the master table. The name must be unique. Ideally, it should be the name that will be used at the remote site.
  3. If this process must be done in the production database (not recommended), the link in the CREATE SNAPSHOT statement will refer to the current database. Oracle refers to this capability as a loopback link.
  4. Export the new schema. The same schema as the owner of the new snapshots should perform the export.
  5. Drop the newly created snapshots. Be sure that this is done by hand (using API calls). If not, ensure that only the snapshots, and not the corresponding objects, are created.

To create a snapshot at the remote site:

  1. Create an empty snapshot group. This is necessary to support the procedure call in the next step.
  2. Use the following procedure:
    DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD
    (gname='[groupname]'.sname=>'snapshotname',)
    
    This will create a snapshot "shell" for the data about to be imported.
  3. Import the snapshot base table. This is identified by the preface SNAP$ table.

Page 723

  1. Once the import is complete, use the procedure DBMS_OFFLINE_SNAPSHOT.END_LOAD to indicate to the system that the import is complete. The easiest way to visualize this process is the hot backup process, where the command ALTER TABLESPACE BEGIN BACKUP is issued.

NOTE
Just as tables cannot be created using a select on a LONG datatype, snapshots cannot support this select statement.n

Tuning Snapshots

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 help—particularly 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 Replication—Replication Manager API Reference.

Page 724

Using Initialization Parameters for Snapshots

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 Snapshots
Parameter 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

PART VIII

Performance Tuning

  1. Performance Tuning Fundamentals
  2. Application Tuning
  3. Tuning Memory
  4. Tuning I/O

Page 726

Previous | Table of Contents | Next