Problem solve Get help with specific problems with your technologies, process and projects.

Oracle RAC performance tuning

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 provides expert tips and techniques used by real-world RAC professionals for tuning and optimizing even the most complex RAC system.

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.

This was last published in March 2007

Dig Deeper on Oracle database performance problems and tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close