5
Monitoring Performance in Real Application Clusters with Oracle Performance Manager

This chapter presents the Oracle Real Application Clusters-specific Oracle Performance Manager charts. You must install and configure Oracle Performance Manager to display the charts described in this chapter. Therefore, use this chapter as a supplement to information contained in the Getting Started with the Oracle Diagnostics Pack. This chapter contains the following topics:

• Oracle Performance Manager Overview
• Displaying the Oracle Performance Manager Charts for Real Application Clusters

  See Also: Oracle9i Real Application Clusters Setup and Configuration for additional information about installing Oracle Performance Manager Oracle9i Database Performance Guide and Reference for detailed information about the statistics that the Performance Manager charts display and how to interpret the statistics Oracle9i Database Reference for more information about fields in the Performance Manager charts and the V$ views from which they are derived

Oracle Performance Manager Overview

Oracle Performance Manager, which is part of the Diagnostics Pack, displays key performance statistics that you can monitor to optimize the performance of your Real Application Clusters database. Oracle Performance Manager presents performance data that the Intelligent Agent captures and computes. The Oracle Performance Manager charts display information from instance-specific V$ views and from global GV$ views.

Using Performance Manager Charts for Previous Oracle Cluster Software Releases

Use Oracle Performance Manager to examine the performance of current as well as previous versions of Oracle cluster databases. To use Performance Manager for pre-release 2 (9.2) versions, refer to the Oracle cluster software documentation for that version.

Displaying the Oracle Performance Manager Charts for Real Application Clusters

Oracle Performance Manager has primary-level charts that you can access from icons that appear under the Cluster Database icon shown in Figure 5-1. Some of the primary charts also have lower-level charts that display performance information by instance, by file, and by object.

Figure 5-1 Oracle Performance Manager Real Application Clusters-Specific Charts

Text description of the illustration oem1.gif

Real Application Clusters-Specific Performance Manager Chart Hierarchies

Table 5-1 shows the Performance Manager chart hierarchies. You can navigate from the primary charts in the left column to lower-level charts in the right column.

Table 5-1 Real Application Clusters-Specific Chart Hierarchies

Primary Chart	Lower-Level Charts
RAC Database Health Overview	-
Total Transfer Refer to Figure 5-2 for detail about the Total Transfer chart hierarchy.	Transfer By Instance Transfer By File From these two charts you can also access: Transfer By File Per Instance Transfer By Instance Per File Transfer By Object Per File Transfer By Instance Per Object
Global Cache CR Request	Global Cache CR Request By Instance
Global Cache Convert	Global Cache Convert By Instance
Library Cache Lock	Library Cache Lock By Instance
Row Cache Lock	Row Cache Lock By Instance
Global Cache Current Block Request	Global Cache Current Block Request By Instance
File I/O Rate	File I/O Rate By Object File I/O Rate By Instance
Sessions
Users	Users By Instance
Real Application Clusters Top Sessions	-

Figure 5-2 shows the chart hierarchy for the Total Transfer chart and its lower-level charts.

Figure 5-2 Real Application Clusters-Specific Enterprise Manager Chart Hierarchy

Text description of the illustration racdp001.gif

The primary- and lower-level charts are described under the following headings:

• Total Transfer Chart
• Global Cache CR Request Chart
• Global Cache Convert Chart
• Library Cache Lock Chart
• Row Cache Lock Chart
• Global Cache Current Block Request Chart
• File I/O Rate Chart
• Sessions Chart
• Users Chart
• Real Application Clusters Top Sessions Chart

Real Application Clusters-Specific Charts in Performance Manager

The Performance Manager charts in this chapter typically display rates of activities as they occur during a time interval. This section describes the following Real Application Clusters-specific charts:

• Total Transfer Chart
• Global Cache CR Request Chart
• Global Cache Convert Chart
• Library Cache Lock Chart
• Row Cache Lock Chart
• Global Cache Current Block Request Chart

Total Transfer Chart

The Total Transfer chart displays the rates of the following activities for the entire cluster database:

• Logical Reads: number of times the consistent read and current blocks were requested (sum of values for 'consistent gets' and 'db block gets')
• Physical Reads: number of times the blocks were read from the disk, without bypassing the buffer cache (difference of values for 'physical reads' and 'physical reads direct')
• Cache Transfers: number of times consistent read and current blocks were read from the remote caches (sum of values for 'global cache cr blocks received' and 'global cache current blocks received')

To determine these rates for each instance, drill down to the Transfer By Instance chart.

You can use this chart to analyze performance in your Real Application Clusters database as follows:

• A low rate of physical reads combined with a low rate of cache-to-cache transfers indicates an ideal workload that results in scalable system if you need to add nodes.
• A high rate of physical reads combined with a high rate of cache-to-cache transfers indicates that a large working set of your application cannot be cached entirely in the cluster. Increasing the size of your buffer cache in each instance or adding another node can reduce this rate.
• If the rate of cache-to-cache transfers is a high percentage of your logical reads and the rate of physical reads is low, then drill down to the Transfer By File chart for further analysis.

Transfer By File Chart

The Transfer By File chart displays the rate of transfers for consistent read blocks as well as current blocks for each datafile of your cluster database. This statistic is derived from the sum of values for 'cr_transfers' and 'cur_transfers' from the GV$FILE_CACHE_TRANSFER view. Obtain the transfer rates on per-instance basis by drilling down to the Transfer By File Per Instance chart.

Use this chart to identify the file(s) with the highest transfer rates. After selecting a file you can drill down to the Transfer By Object Per File chart to identify the objects that are subject to maximum transfers. This chart also shows you how these objects are accessed from each node. This enables you to identify the hot objects such as tables and indexes. Then you can identify a corrective action such as the use of Oracle hash or range partitioning.

Global Cache CR Request Chart

The Global Cache CR Request chart displays the following statistic for the entire cluster database:

• The average global cache CR request time which is a ratio of the 'global cache cr block receive time' multiplied by 10, divided by the amount of 'global cache cr blocks received'

To determine the average CR request time for each instance, drill down to the Global Cache CR Request By Instance Chart.

If the chart displays a high value for CR request time, then investigate the possible causes such as a high system load, using a public interconnect instead of a private network, network errors, or poor CPU utilization by the LMS processes.

Global Cache Convert Chart

The Global Cache Convert chart displays the following statistics for the entire cluster database:

• The average global cache convert time which is a ratio of 'global cache convert time' multiplied by 10, divided by the 'global cache converts'
• The average global cache get time which is a ratio of the 'global cache get time' multiplied by 10 divided by the 'global cache gets'

To determine these statistics for each instance, drill down to the Global Cache Convert By Instance chart and consider the following points:

• A high average convert time can result from right-growing index trees. If this is true for your situation, then distribute access to the index leaf blocks by using hash or range partitioning on the index.
• High values for both convert and get times can be attributed to all the system, interconnect, network, LMS process, and CPU utilization-related problems discussed previously for the Global Cache CR Request By Instance Chart.

Library Cache Lock Chart

The Library Cache Lock chart displays the following statistics for the entire cluster database:

• The dlm_lock_requests which is the number of times a lock was requested for a database object
• The dlm_pin_requests which is the number of times a pin was requested for a database object
• The dlm_invalidations which is the number of invalidations received from other instances

To determine these statistics for each instance, drill down to the Library Cache Lock By Instance chart.

If you observe high number of lock and pin requests, then try to locate the sources for frequent PL/SQL unit execution, high amount of parsing, or the inefficient use of bind variables.

If you observe a high number of invalidations, then try to locate DDL statements or PL/SQL compile commands that result in schema objects being analyzed, dropped, or altered.

Row Cache Lock Chart

The Row Cache Lock chart displays the following statistics for the entire cluster database:

• The dlm_requests which is the number of times DLM lock requests are generated for consistent read blocks
• The dlm_conflicts which is the number of times when another instance already owned a lock on a consistent read block in a mode that conflicts with the mode requested by the instance
• The percentage of dlm_requests that result in dlm_conflicts

To determine these statistics for each instance, drill down to the Row Cache Lock By Instance chart. The statistics on this chart highlight any dictionary contention. If you observe a high percentage of DLM conflicts, then investigate the sources of dictionary contention, such as dictionary managed tablespaces, uncached sequences, or the frequent creation and dropping of database objects.

Global Cache Current Block Request Chart

The Global Cache Current Block Request chart displays the following statistics for the entire cluster database:

• The average global cache current block request time which is the ratio of 'global cache current block receive time' multiplied by 10, divided by 'global cache current blocks received'.
• The average global cache current block serve time which is the ratio of 'global cache current block serve time' multiplied by 10, divided by 'global cache current blocks served'.

To determine these statistics for each instance, drill down to the Global Cache Current Block Request By Instance chart. A high value for the serve time is generally attributed to poor performance of the LMSn processes. Just as for the average global cache CR request time, high values of average current block request and serve times can be caused by a high system load, the use of a public interconnect, network errors, the low priority of LMSn processes , or poor CPU utilization by the LMSn processes.

Real Application Clusters-Specific Versions of Oracle Charts

The remaining charts in this chapter are clusterized versions of single-instance Oracle database charts. The charts described in this section are:

• File I/O Rate Chart
• Sessions Chart
• Users Chart
• Real Application Clusters Top Sessions Chart

File I/O Rate Chart

The File I/O Rate chart displays the rate of physical reads and writes for all files in the cluster database. To determine the rate of reads and writes per datafile, drill down to the File I/O Rate By Object chart. To determine the rate of reads and writes per instance, drill down to the File I/O Rate By Instance chart.

Sessions Chart

The Sessions chart displays the sessions attached to the entire cluster database as well as related information, such as instance name, session ID, process ID, status, and user name.

Users Chart

The Users chart displays the following for the entire cluster database:

• The total number of user sessions logged on regardless of whether activity is generated
• The total number of active user sessions

To determine the number of logged-on and active users for each instance, drill down to the Users By Instance Chart.

Real Application Clusters Top Sessions Chart

The Real Application Clusters Top Sessions chart displays a set of sessions with the highest contribution to the cluster database instance activity based on selected sort statistics.

Real Application Clusters Database Health Overview Chart

Figure 5-3 displays the Real Application Clusters Database Health Overview chart which is a group of standalone charts that shows key performance statistics for the entire cluster database. This chart consists of the following standalone charts:

• Total Transfer
• Global Cache Convert
• Global Cache Current Block Request
• Library Cache Lock
• Row Cache Lock

Drill downs from these charts are available in the same manner as the drill downs are available when these charts are used as standalone charts. In addition, the center of the overview chart contains a clusterized version of session information that is not available as standalone chart. You can customize the display of sessions information based on CPU, Memory, or I/O.

Figure 5-3 Real Application Clusters Database Health Overview Chart

Text description of the illustration oemoverv.gif