Methodology - Expert Oracle RAC Performance Diagnostics and Tuning - page 5

■ Note oracle wait interface is discussed in Chapters 6, 8, and 17.

Step 3: Reduce Physical I/O
Most database operations involve disk I/Os, and it can be an expensive operation relative to the speed of the disk
and other I/O components used on the server. Processing architectures have three major areas that would require or
demand a disk I/O operation:

1. A logical read by a query or session does not find data in the cache and hence has to

perform a disk I/O because the buffer cache is smaller than the working set.

2. SORT and JOIN operations cannot be performed in memory and need to spill to the TEMP
table space on disk.

3. Sufficient memory is not found in the buffer cache, resulting in the buffers being

prematurely written to disk; it is not able to take advantage of the lazy writing operation.

Optimizing physical I/O (PIO) or disk I/O operations is critical to achieve good response times. For disk
I/O intensive operations, high-speed storage or using storage management solutions such as Automatic Storage
Management (ASM) will help optimize PIO.

Step 4: Optimize Logical I/O
Reading from a buffer cache is faster compared to reading from a physical disk or a PIO operation. However, in
Oracle's architecture, high logical I/O (LIOs) is not so inexpensive that it can be ignored. When Oracle needs to read
a row from buffer, it needs to place a lock on the row in buffer. To obtain a lock, Oracle has to request a latch; for
instance, in the case of a consistent read (CR) request, a latch on buffer chains has to be obtained. To obtain a latch,
Oracle has to depend on the O/S. The O/S has limitations on how many latches can be made available at a given
point in time. The limited number of latches are shared by a number of processes. When the requested latch is not
available, the process will go into a sleep state and after a few nanoseconds will try for the latch again. Every time a
latch is requested there is no grantee that the requesting process may be successful in getting the latch and may have
to go into a sleep state again. The frequent trying to obtain a latch leads to high CPU utilization on the host server
and cache buffer chains latch contention as sessions try to access the same blocks. When Oracle has to scan a large
number of rows in the buffer to retrieve only a few rows that meet the search criteria, this can prove costly. LIO should
be reduced as much as possible for efficient use of CPU and other resources. In a RAC environment this becomes
even more critical because there are multiple instances in the cluster, and each instance may perform a similar kind
of operation. For example, another user maybe executing the very same statement retrieving the same set of rows and
may experience the same kind of contention. In the overall performance of the RAC, environment will indicate high
CPU usage across the cluster.

■ Note lIo is discussed in Chapter 7 and latches are discussed in Chapter 17.