Oracle's Real Application Clusters (RAC) is the most robust and complex of all Oracle environments, and the inter-instance communications makes RAC tuning especially
challenging. This book excerpt shows expert tips and techniques used by real-world RAC professionals for tuning and optimizing even the most complex RAC system.
This is an excerpt from the bestselling Oracle 10g RAC book Oracle 10g Grid & Real Application Clusters by Mike Ault and Madhu Tumma.
The focus of this chapter is on RAC tuning issues. Tuning a RAC database is very similar to tuning a non-RAC database;
however, there are some major differences that will be covered.
This chapter will also cover the views used to monitor the RAC environment.
Finally, the Oracle Enterprise Manager features that automate some of the DBA's
RAC monitoring tasks will be introduced.
Analysis of performance issues
The analysis of performance issues in RAC involves several key areas:
- Normal database tuning and monitoring.
- Monitoring RAC cluster interconnect performance.
- Monitoring workloads.
- Monitoring RAC-specific contention.
Normal database monitoring is covered thoroughly in any number of other texts.
Thus, aspects of database tuning such as SQL tuning or standard SGA and
internals tuning are not covered in this text other than the required extensions to
normal database monitoring.
Monitoring RAC cluster interconnect performance
The most important aspects of RAC tuning are the monitoring and tuning of the
global services directory processes. The processes in the Global Service Daemon
(GSD) communicate through the cluster interconnects. If the cluster interconnects
do not perform properly, the entire RAC structure will suffer no matter how well
everything else is tuned. The major processes of concern are the Global Enqueue
Services (GES) and Global Cache Services (GCS) processes.
The level of cluster interconnect performance can be determined by monitoring
GCS waits that show how well data is being transferred. The waits that need to be
monitored are shown in v$session_wait, v$obj_stats, and v$enqueues_stats. The major
waits to be concerned with for RAC are:
- global cache busy
- buffer busy global cache
- buffer busy global cr
In later versions of Oracle, the global cache is shortened to just gc. To find the
values for these waits, the gv$session_wait view is used to identify objects that have
performance issues.
New in 10g is the wait_class column which is used to restrict returned values based
on 12 basic wait classes, one of which is the cluster wait class.
The following wait events indicate that the remotely cached blocks were shipped
to the local instance without having been busy, pinned or requiring a log flush and
can safely be ignored:
- gc current block 2-way
- gc current block 3-way
- gc cr block 2-way
- gc cr block 3-way
However, the object level statistics for gc current blocks received and gc cr blocks received
enable the rapid identification of the indexes and tables which are shared by the
active instances.
The columns p1 and p2 identify the file and block number of any object
experiencing the above waits for the events, as shown in the following queries:
SELECT
INST_ID,
EVENT,
P1 FILE_NUMBER,
P2 BLOCK_NUMBER,
WAIT_TIME
FROM
GV$SESSION_WAIT
WHERE
EVENT IN ('buffer busy global cr', 'global cache busy',
'buffer busy global cache');
The output from this query should resemble the following:
INST_ID EVENT FILE_NUMBER BLOCK_NUMBER WAIT_TIME
------- ----------------------- ----------- ------------ ----------
1 global cache busy 9 150 0
2 global cache busy 9 150 0
In order to find the object that corresponds to a particular file and block, the
following query can be issued for the first combination on the above list:
SELECT
OWNER,
SEGMENT_NAME,
SEGMENT_TYPE
FROM
DBA_EXTENTS
WHERE
FILE_ID = 9
AND 150 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
SEE CODE DEPOT FOR COMPLETE SCRIPT
In this example, there is no need to worry about the instance as this SELECT is
issued from within the cluster because they all see the same tables, indexes and
objects.
Click here to read the rest of this chapter.
