Asynchronous I/O

Asynchronous I/O is available on most UNIX platforms for both reads and writes. Asynchronous I/O specifies that Oracle does not have to wait for read and write instructions to be completed by the operating system, but can do other work in the meantime. The use of asynchronous I/O for both reads and writes is highly recommended. Unfortunately, the mechanics of implementing asynchronous I/O differs between platforms. When using asynchronous I/O, set DB_WRITERS = 1. There is no danger of creating a performance bottleneck by having only one DBWR process, because all I/O instructions are non-blocking.

Checkpoints

I normally recommend setting the CHECKPOINT_PROCESS parameter to True so that the log writer process doesn’t have to handle checkpoints. Setting the CHECKPOINT_PROCESS parameter to True means that checkpoints are handled by the CKPT (or checkpoint) process instead of a DBWR (or database writer) process. I usually recommend setting the CHECKPOINT_PROCESS to True for all applications.

Rollback Segments

Because most OLTP applications have large numbers of small transactions, most rollback segments should be defined with a relatively small initial and extents size (such as 20KB). Each extent should be sized so that on average each transaction requires only one extent. With large numbers of users, we should have large numbers of rollback segments to avoid contention. Please refer to Chapter 24 for ways to detect rollback segment contention.

I usually preallocate one rollback segment with large initial and next extents (1MB) for long-running transactions. For example, batch processing typically issues longer transactions and requires more rollback segment space than OLTP transactions. Long-running transactions can specify that Oracle is to use the large rollback segment. To specify a specific rollback segment, issue a SET TRANSACTION instruction as the first SQL statement of the transaction.

For example, to specify that rollback segment RBS_BIG is to be used for a long-running transaction, issue the following statement:

Set transaction use rollback segment rbs_big

This statement must be the first SQL statement in the transaction, so it must be reissued immediately after any rollback or commit.

If the application is OPS, each instance in the database should have a large number of private rollback segments. That is, each instance should have a set of rollback segments that is not used by any of the other instances. Furthermore, to alleviate PCM locking concerns (yes, PCM locking occurs when rollback segments are allocated as well), each set of private rollback segments should have a separate tablespace.

Memory Allocation Issues

The memory allocation issues for OLTP applications are no different than for other types of databases. Be sure to pay close attention to buffer cache, shared pool, and sort area memory allocation, as described in Chapter 24. Because sort_area_size specifies the amount of memory used for sorting per user (as a result of order by or group by clauses in SQL statements), tuning sort_area_size can have a great effect on machine memory allocation.

Clusters

Clusters are a way to physically control the ordering of rows according to a common key value. Multiple tables can reside in a cluster, but all tables must contain the common key columns. Clusters are frequently used to reduce the I/O associated with joins.

Furthermore, a cluster index (an index that is placed on a cluster instead of an individual table) is required. Cluster indexes are used in much the same way that regular indexes are.

OLTP applications have I/O loads that are largely random, not sequential. Clusters make random reads more efficient, impair writes, and impair sequential reads. One must be very careful in using hash clusters. Hence, clusters are effective constructs to use on sets of tables in which most operations are random reads. Furthermore, all tables in this set have to share a common column or set of columns that can be used as a key.

Hash clusters are similar to ordinary clusters, except that lookups occur based on a hashed value of the key. This can shorten query time for random selects but is harder to tune. Hash clusters also bypass the need for a clustered index, so the number of physical I/Os needed for each select is less than in a regular cluster.

Please refer to the Oracle SQL Reference for a complete syntax. Overall, the syntax of the CREATE CLUSTER command is similar to the creation of tables in that it provides for tablespace and storage clauses. The cluster key is contained within parentheses immediately after the cluster name. The size clause refers to the amount of space in bytes to reserve for each cluster key and its associated rows. If the size estimate is too low, retrieval from the cluster becomes resource intensive.

Here’s an example of a cluster:

create cluster customer_data (customer_nbr number)

pctfree 5

size 2000

tablespace user_data

storage (initial 120M);

Only use clusters or hash clusters on sets of tables that are frequently joined and where random reads are the majority of the application load. I only consider using clusters in situations where random reads are over 75% of the load, but this is an arbitrary percentage on my part.

Do not use clusters for tables that are dynamic (that dramatically grow and shrink during processing). Also, do not use clusters in situations where any of the columns within the common key are updated, because Oracle may have to relocate the row. Depending on the growth rate of tables within the cluster, you may have to drop, re-create, and reload tables within the cluster periodically.