CHAPTER 7
Performance And Tuning For Distributed Oracle Databases

Performance and tuning of Oracle systems is anything but trivial with distributed database environments, since so many components within the database software contribute to the overall performance. The number of concurrent users, the availability of space within the buffer and lock pools, and the balancing of application access can all affect database performance. This chapter will review the following topics:

•  The role of data replication—Oracle snapshots

•  Replication with Oracle snapshots

•  Asynchronous replication techniques

•  Using Oracle’s parallel facilities

•  Planning for growth

•  Designing expert systems for distributed database performance and tuning

•  Disk I/O issues with distributed databases

When a client/server application must access several remote databases in a single transaction, another dimension of complexity is added to the system. The database administrator (DBA) must look at more than each individual database, and must consider transactions that span different servers. While accessing several servers in a distributed transaction may seem trivial, performance problems can be introduced by PC hardware, LAN and network bottlenecks, router overloads, and a plethora of other sources. Only by examining the relevant components of distributed databases can we gain a broad understanding of the issues.

Replication With Oracle Snapshots

Oracle snapshots are used to create read-only copies of tables in other Oracle databases. This is a highly effective way to avoid expensive cross-database joins of tables. As we know, an SQL join with a table at a remote server will be far slower than a join with a local table: SQL*Net overhead increases as it retrieves and transfers the data across the network.

It is interesting to note that the general attitude about data replication has shifted dramatically in the past 10 years. In the 1980s, replication was frowned upon. Database designers believed that there was no substitute for the Third Normal Form database. Today, the practical realities of distributed processing have made replication a cheap and viable alternative to expensive cross-database joins.

Table replication is so stable and has been so successful within Oracle version 7 that Oracle is now introducing the concept of updatable snapshots with Oracle version 7.3. However, replication is not to be used indiscriminately, and guidelines exist for using replicated tables to the best advantage:

•  The replicated table is read-only—Obviously, a table snapshot cannot be updated since the master copy of the table is on another server.

•  The replicated table is relatively small—Ideally, a replicated table is small enough that the table can be dropped and re-created each night, or the REFRESH COMPLETE option can be used. Of course, large tables can be replicated with the REFRESH FAST option, but this involves a complicated mechanism for holding table changes and propagating them to the replicated table.

•  The replicated table is frequently used—It does not make sense to replicate a table if it is only referenced a few times per day, and the cost of the replication would outweigh the cost of the cross-database join.

Despite any claims by Oracle to the contrary, snapshots are not to be used indiscriminately. Only those tables that meet the above criteria should be placed in snapshots. In practice, snapshots are not maintenance-free, and many points of failure are possible—especially if the snapshot is created with the REFRESH FAST option. Problems can occur writing to the snapshot_log table, and SQL*Net errors can cause failures of updates to transfer to the replicated tables.

How Oracle Snapshots Work

A snapshot is created on the destination system with the CREATE SNAPSHOT command, and the remote table is immediately defined and populated from the master table.

After creation, a snapshot may be refreshed periodically. There are two types of refreshing: complete and fast. A complete refresh can be done in several ways, but most savvy Oracle developers drop and re-create the snapshots with a Unix cron job to achieve full refreshes, especially if the table is small and easily re-created. Optionally, tables can be refreshed with only the changes that are made to the master table. This requires additional work on the slave database to create an Oracle refresh process (in the init.ora), and the definition of a snapshot log on the master database (Figure 7.1).

Figure 7.1  A high-level overview of Oracle snapshots.

Several steps need to be completed before your Oracle system is ready to use snapshots. First you need to run catsnap.sql, which can be found in your $ORACLE_HOME/rdbms/admin directory. This script will populate the Oracle dictionary with the necessary system tables to manage the snapshots. You’ll also need to run dbmssnap.sql, which can be found in the $ORACLE_HOME/rdbms/admin directory. This script creates the stored procedures that can be used to manipulate the snapshots.

The following parameters must also be added to the init.ora file:

•  snapshot_refresh_interval=60—This sets the interval (in minutes) for the refresh process to wake up.

•  snapshot_refresh_processes=1—This is the number of refresh processes on the instance (minimum is 1).

•  snapshot_refresh_keep_connections=false—This specifies whether the database should keep remote connections after refreshing the tables. Always use FALSE.