Database Reference
In-Depth Information
Chapter 10
RAC Database Optimization
by Riyaj Shamsudeen
The strategy and methods employed to optimize an application in a Real Application Cluster (RAC) database are
similar to those for tuning a single instance. However, in RAC, performance problems in one instance can affect the
performance of other instances. In addition to single-instance wait events, RAC introduces numerous RAC-specific
wait events. Time model-based tuning is employed to tune RAC databases too.
In this chapter, I discuss RAC-related wait events, statistics, Automatic Workload Repository (AWR) report
sections applicable to a RAC database, etc. Almost everything in this chapter is tested with and applicable to Oracle
Database release 11.2 and version 12c. There may be minor differences in earlier versions.
Introduction to Cache Fusion
To understand RAC wait events, you should first understand the internal workings of cache fusion. In RAC, buffer
cache memory areas from all instances are effectively shared to create an effect of fused buffer cache. Some
background processes play critical roles in managing the processing of cache fusion.
A database block holds row pieces of a database table and a change atomically modifies a database block.
Database blocks are modified in the buffer cache of an instance System Global Area (SGA) (excluding direct mode
writes). Since a database block can be modified in any instance, changes to the buffers in the SGA must be globally
coordinated. This coordination mechanism is implemented using resources and locks; these are allocated as memory
structures in the Global Resource Directory (GRD).
A resource is allocated in GRD to protect a database block. Before modifying a block, a lock on that resource must
be acquired in an exclusive mode. Further, a disk read of a block into the buffer cache might require a lock on the
resource too. This scheme of reads needing to acquire buffer locks (BL) is required to prevent one instance modifying
the block while another instance is holding the block in its buffer cache in read-only mode.
A resource name is coined in such a way that it uniquely identifies a block. An instance is also chosen to serve as
a master instance of a resource to track the resource state.
■
Chapter 11 discusses resources and locks in more detail. I will briefly introduce resources and locks in this
chapter, but a much more elaborate description can be found in Chapter 11.
Note
You should realize that cache fusion locks do not replace single-instance locks, and both single-instance and
cache fusion locks coexist in RAC database. Row-level locks protect rows from concurrent changes, and similarly,
cache-fusion BL locks protect buffers from inconsistent concurrent changes. Essentially, row-level locks and
buffer-level locks coexist.
