Oracle Distributed Systems

Management and Optimization

Snapshots require a certain amount of DBA attention in order to keep them running optimally. In addition, Oracle provides packaged procedures to render the DBA’s responsibilities less taxing. This section discusses your options for squeezing optimal performance out of snapshots and offers some common solutions to common problems.

Tuning Snapshots

Section 11.3 recommended that you select PCTFREE and PCTUSED settings that will pack the data in your read-only snapshot base tables as tightly as possible, thus preserving disk space and reducing the expense of scanning the table. You can also take the following steps to enhance the performance of queries against the snapshot base tables and the snapshot refresh itself:

Index the snapshot base table

You can place indexes on the columns of the snapshot base table to enhance the performance of your application’s queries. Note, however, that you cannot use unique indexes if you are using Oracle7; if you are using Oracle8, unique constraints must be deferrable. This restriction exists because uniqueness is not guaranteed during the period of the actual snapshot refresh.

Cluster read-only snapshot base tables

If you snapshot several tables that share common keys, consider using a cluster index for the key as was done in the CUSTOMERS, ORDERS, ORDER_ITEMS example.

Tune the defining query

When you create subquery subset snapshots, be sure that the defining query is optimized. You can create the appropriate indexes on the master ...