1. Real Application Clusters Administration and Deployment Guide
  2. Monitoring Performance

14 Monitoring Performance

Learn how to monitor and tune the performance of your Oracle Real Application Clusters (Oracle RAC) database.

Note:

A multitenant container database is the only supported architecture in Oracle Database 21c. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.

Monitoring and Tuning Oracle RAC Databases

Learn about monitoring and tuning Oracle Real Application Clusters (Oracle RAC) databases, and about how you can use the Database Reliability Framework to assist you in these tasks.

  • Overview of Monitoring Oracle RAC and Oracle Clusterware
    Learn about the monitoring capabilities of Oracle Enterprise Manager, including the Cluster Database Homepage, the Interconnects page, and the Cluster Database Performance page.
  • Tuning Oracle RAC Databases
    All of the noncluster tuning practices for Oracle Database also apply to Oracle Real Application Clusters (Oracle RAC) databases.
  • Database Reliability Framework
    The Database Reliability Framework (DRF) is a proactive and automatic monitoring and correction framework for Oracle Real Application Clusters (Oracle RAC) databases.

Parent topic: Monitoring Performance

Overview of Monitoring Oracle RAC and Oracle Clusterware

Learn about the monitoring capabilities of Oracle Enterprise Manager, including the Cluster Database Homepage, the Interconnects page, and the Cluster Database Performance page.

  • Monitoring Oracle RAC and Oracle Clusterware with Oracle Enterprise Manager
    Using Oracle Enterprise Manager is the preferred method for monitoring Oracle Real Application Clusters (Oracle RAC) and Oracle Clusterware.
  • The Cluster Database Home Page
    Using the Oracle Enterprise Manager Cluster Database Home page, you can use a client browser to monitor the status of both Oracle Clusterware and Oracle Real Application Clusters (Oracle RAC) environments.
  • The Interconnects Page
    Using the Oracle Enterprise Manager Interconnects page, you can use a client browser to monitor private network status, and troubleshoot cluster wait events.
  • The Cluster Database Performance Page
    The Oracle Enterprise Manager Cluster Database Performance page provides a quick glimpse of the performance statistics for an Oracle Real Application Clusters (Oracle RAC) database.

Parent topic: Monitoring and Tuning Oracle RAC Databases

Monitoring Oracle RAC and Oracle Clusterware with Oracle Enterprise Manager

Using Oracle Enterprise Manager is the preferred method for monitoring Oracle Real Application Clusters (Oracle RAC) and Oracle Clusterware.

Oracle Enterprise Manager is an Oracle Web-based integrated management solution for monitoring and administering your computing environment. From any location where you can access a web browser, you can manage Oracle RAC databases, application servers, host computers, and Web applications, in addition to related hardware and software. For example, you can monitor your Oracle RAC database performance from your office, home, or a remote site, if you have access to a Web browser.

Oracle Enterprise Manager Cloud Control is cluster-aware and provides a central console to manage your cluster database. From the Cluster Database Home page, you can do all of the following:

  • View the overall system status, such as the number of nodes in the cluster and their current status. This high-level view capability means that you do not have to access each individual database instance for details if you just want to see inclusive, aggregated information.

  • View alert messages aggregated across all the instances with lists for the source of each alert message. An alert message is an indicator that signifies that a particular metric condition has been encountered. A metric is a unit of measurement used to report the system's conditions.

  • Review issues that are affecting the entire cluster and those issues that are affecting individual instances.

  • Monitor cluster cache coherency statistics to help you identify processing trends and optimize performance for your Oracle RAC environment. Cache coherency statistics measure how well the data in caches on multiple instances is synchronized. If the data caches are completely synchronized with each other, then reading a memory location from the cache on any instance will return the most recent data written to that location from any cache on any instance.

Oracle Enterprise Manager accumulates data over specified periods of time, called collection-based data. Oracle Enterprise Manager also provides current data, called real-time data.

The Cluster Database Home Page

Using the Oracle Enterprise Manager Cluster Database Home page, you can use a client browser to monitor the status of both Oracle Clusterware and Oracle Real Application Clusters (Oracle RAC) environments.

The Oracle Enterprise Manager Cluster Database Home Page monitors your entire cluster environment. Monitoring can include such things as:

  • Notification if there are any VIP relocations
  • Status of the Oracle Clusterware on each node of the cluster using information obtained through the Cluster Verification Utility (cluvfy)
  • Notification if node applications (nodeapps) start or stop
  • Notification of issues in the Oracle Clusterware alert log for OCR, voting disk issues (if any), and node evictions

The Cluster Database Home page is similar to a noncluster Database Home page. However, on the Cluster Database Home page, Oracle Enterprise Manager displays the system state and availability. The system state includes a summary about alert messages and job activity, and links to all the database and Oracle Automatic Storage Management (Oracle ASM) instances. For example, you can track problems with services on the cluster including when a service is not running on all of the preferred instances or when a service response time threshold is not being met.

The Interconnects Page

Using the Oracle Enterprise Manager Interconnects page, you can use a client browser to monitor private network status, and troubleshoot cluster wait events.

You can use the Oracle Enterprise Manager Interconnects page to monitor the Oracle Clusterware environment. The Interconnects page shows the public and private interfaces on the cluster and the load contributed by database instances on the interconnect, including:

  • Overall throughput across the private interconnect
  • Notification if a database instance is using public interface due to misconfiguration
  • Throughput and errors (if any) on the interconnect
  • Throughput contributed by individual instances on the interconnect

All of this information is also available as collections that have a historic view, which is useful with cluster cache coherency, such as when diagnosing problems related to cluster wait events. You can access the Interconnects page by clicking the Interconnect tab on the Cluster Database home page, or by clicking the Interconnect Alerts link under Diagnostic Findings on the Oracle RAC database home page.

The Cluster Database Performance Page

The Oracle Enterprise Manager Cluster Database Performance page provides a quick glimpse of the performance statistics for an Oracle Real Application Clusters (Oracle RAC) database.

Statistics are rolled up across all the instances in the cluster database in charts. Using the links next to the charts, you can get more specific information and perform any of the following tasks:

  • Identify the causes of performance issues.
  • Decide whether resources need to be added or redistributed.
  • Tune your SQL plan and schema for better optimization.
  • Resolve performance issues

The charts on the Cluster Database Performance page include the following:

  • Chart for Cluster Host Load Average: The Cluster Host Load Average chart in the Cluster Database Performance page shows potential problems that are outside the database. The chart shows maximum, average, and minimum load values for available nodes in the cluster for the previous hour.
  • Chart for Global Cache Block Access Latency: Each cluster database instance has its own buffer cache in its System Global Area (SGA). Using Cache Fusion, Oracle RAC environments logically combine each instance's buffer cache to enable the database instances to process data as if the data resided on a logically combined, single cache.
  • Chart for Average Active Sessions: The Average Active Sessions chart in the Cluster Database Performance page shows potential problems inside the database. Categories, called wait classes, show how much of the database is using a resource, such as CPU or disk I/O. Comparing CPU time to wait time helps to determine how much of the response time is consumed with useful work rather than waiting for resources that are potentially held by other processes.
  • Chart for Database Throughput: The Database Throughput charts summarize any resource contention that appears in the Average Active Sessions chart, and also show how much work the database is performing on behalf of the users or applications. The Per Second view shows the number of transactions compared to the number of logons, and the amount of physical reads compared to the redo size per second. The Per Transaction view shows the amount of physical reads compared to the redo size per transaction. Logons is the number of users that are logged on to the database.

In addition, the Top Activity drill down menu on the Cluster Database Performance page enables you to see the activity by wait events, services, and instances. Plus, you can see the details about SQL/sessions by going to a prior point in time by moving the slider on the chart.

The Cluster Database Performance page provides a quick glimpse of the performance statistics for an Oracle RAC database. Statistics are rolled up across all of the instances in the cluster database so that users can identify performance issues without going through all the instances. To help triage the performance issues related to services, Oracle Enterprise Manager aggregates the activity data at the following levels:

  • Aggregate by waits

    All the activity data is presented in 12 categories: CPU, Scheduler, User I/O, System I/O, Concurrency, Application, Commit, Configuration, Administrative, Network, Cluster and Other. The data presented is rolled up from all of the running instances.

  • Aggregate by services

    All the activity data is rolled up for each service. When the activity data is presented in this way, it is easy to identify which service is most active, and needs more analysis.

  • Aggregate by instances

    As a similar effort, the activity data is rolled up for each instance, if services are not the interested ones.

The aggregates are provided on the pages where the activity data is presented, including: Database Performance Page, Top Activity Page, Wait Details Page, and Service Details Page.

Tuning Oracle RAC Databases

All of the noncluster tuning practices for Oracle Database also apply to Oracle Real Application Clusters (Oracle RAC) databases.

Related Topics

Parent topic: Monitoring and Tuning Oracle RAC Databases

Database Reliability Framework

The Database Reliability Framework (DRF) is a proactive and automatic monitoring and correction framework for Oracle Real Application Clusters (Oracle RAC) databases.

The Database Reliability Framework (DRF) monitors various metrics across different layers of the database continuously to detect problems before any disruption of service occurs. DRF improves database availability by monitoring critical events across all of the database instances in the cluster to identify root causes. It then can take corrective actions when these critical events hit certain thresholds.

After a problem is identified, an action is implemented automatically. Automatic actions include resizing internal memory structures or changing the priority of Oracle RAC processes, depending on the identified problem. For example, consider a system that has high redo waits with no I/O contention based on the metrics collected over time. If there is enough CPU resources available, then a possible action plan for reducing the redo waits is to move the LGWR process to higher priority to ensure that enough CPU resources are available. DRF can take this action automatically, drawing from metrics across the entire cluster to reach the best solution available. This capability results in problem resolution with minimal service disruption, and it performs these corrective actios before the problem multiplies over time, and affects database availability.

Parent topic: Monitoring and Tuning Oracle RAC Databases

Verifying the Interconnect Settings for Oracle RAC

To verify the interconnect settings for Oracle Real Application Clusters (Oracle RAC), you can use SQL statements.

The interconnect and internode communication protocols can affect Cache Fusion performance. In addition, the interconnect bandwidth, its latency, and the efficiency of the IPC protocol determine the speed with which Cache Fusion processes block transfers.

To verify the interconnect settings of the Oracle RAC database instance to which you are connected, query the V$CLUSTER_INTERCONNECTS and V$CONFIGURED_INTERCONNECTS views. For example:

Example 14-1 Verify Interconnect Settings with V$CLUSTER_INTERCONNECTS

SQL> SELECT * FROM V$CLUSTER_INTERCONNECTS;

NAME             IP_ADDRESS       IS_PUBLIC SOURCE
---------------  --------------   ---       -------------------------------
eth2             10.137.20.181    NO        Oracle Cluster Repository

Note:

You can query the GV$CLUSTER_INTERCONNECTS view to display the entries for all of the instances in the cluster.

Example 14-2 Verify Interconnect Settings with V$CONFIGURED_INTERCONNECTS

SQL> SELECT * FROM V$CONFIGURED_INTERCONNECTS;

NAME             IP_ADDRESS       IS_PUBLIC   SOURCE
---------------  ---------------  ---         -------------------------------
eth2             10.137.20.181    NO          Oracle Cluster Repository
eth0             10.137.8.225     YES         Oracle Cluster Repository

Parent topic: Monitoring Performance

Influencing Interconnect Processing

After your interconnect is operative, you cannot significantly influence its performance. However, you can influence an interconnect protocol's efficiency by adjusting the interprocess communication (IPC) buffer sizes.

In Oracle Clusterware, the Oracle Cluster Registry (OCR) stores your system's interconnect information. To identify the interconnect for your cluster, use the Oracle Interface Configuration (OIFCFG) command-line utility oifcfg getif command, or the OCRDUMP utility. You can then change the interconnect that you are using by running an OIFCFG command.

Although you rarely need to set the CLUSTER_INTERCONNECTS parameter, you can use it to assign a private network IP address, or a network interface card (NIC). For example: 

CLUSTER_INTERCONNECTS=10.0.0.1

If you are using an operating system-specific vendor IPC protocol, then the trace information may not reveal the IP address.

Notes:

  • You can also use OIFCFG command to assign private network or private IP addresses.
  • With Oracle Clusterware releases after Oracle Clusterware 12c release 2 (12.2), you can assign either IPv4 or IPv6 addresses to multiple private networks. However, you must choose one or the other protocol, and you must and use that protocol for all of the private networks in the cluster.

Related Topics

Parent topic: Monitoring Performance

Performance Views in Oracle RAC

To obtain performance information about your Oracle Real Application Clusters (Oracle RAC) database, you can query either instance-specific views, or dynamic performance views for the entire cluster.

Each instance in an Oracle Real Application Clusters (Oracle RAC) database has a set of instance-specific views, which are prefixed with V$.You can also query global dynamic performance views to retrieve performance information from all of the qualified instances. Global dynamic performance view names are prefixed with GV$.

Querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of data type NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.

You can use the INST_ID column as a filter to retrieve V$ information from a subset of available instances. For example, the following query retrieves the information from the V$LOCK view for instances 2 and 5: 

SQL> SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5;

Related Topics

Parent topic: Monitoring Performance

Creating Oracle RAC Data Dictionary Views with CATCLUST.SQL

If you did not create your Oracle RAC database with DBCA, then you must run the CATCLUST.SQL script to create views and tables related to Oracle RAC.

If you did not create your Oracle Real Application Clusters (Oracle RAC) database by using Database Configuration Assistant (DBCA), then the data dictionary setup for Oracle RAC is incomplete. To create the views and tables related to Oracle RAC, you must run the CATCLUST.SQL script. To run the CATCLUST.SQL script, the user account you use must be granted SYSDBA privileges.

Related Topics

Parent topic: Monitoring Performance

Oracle RAC Performance Statistics

Oracle Real Application Clusters (Oracle RAC) statistics appear either as message request counters, or as timed statistics.

Message request counters include statistics showing the number of certain types of block mode conversions. Timed statistics reveal the total or average time waited for read and write I/O for particular types of operations.

Parent topic: Monitoring Performance

Automatic Workload Repository in Oracle RAC Environments

You can use Automatic Workload Repository to monitor performance statistics related to Oracle RAC databases.

Automatic Workload Repository (AWR) automatically generates snapshots of the performance data once every hour and collects the statistics in the workload repository. In Oracle RAC environments, each AWR snapshot captures data from all active instances in the cluster. The data for each snapshot set is captured from the same point in time. AWR stores the snapshot data for all instances in the same table and the data is identified by an instance qualifier. For example, the BUFFER_BUSY_WAIT statistic shows the number of buffer waits on each instance. AWR does not store data that is aggregated from across the entire cluster. In other words, the data is stored for each individual instance.

Using the Automatic Database Diagnostic Monitor (ADDM), you can analyze the information collected by AWR for possible performance problems with Oracle Database. ADDM presents performance data from a cluster-wide perspective, thus enabling you to analyze performance on a global basis. In an Oracle RAC environment, ADDM can analyze performance using data collected from all instances and present it at different levels of granularity, including:

  • Analysis for the entire cluster

  • Analysis for a specific database instance

  • Analysis for a subset of database instances

To perform these analyses, you can run the ADDM Advisor in ADDM for Oracle RAC mode to perform an analysis of the entire cluster; in Local ADDM mode to analyze the performance of an individual instance; or in Partial ADDM mode to analyze a subset of instances. Activate ADDM analysis using the advisor framework through Advisor Central in Oracle Enterprise Manager, or through the DBMS_ADVISOR and DBMS_ADDM PL/SQL packages.

Related Topics

Parent topic: Monitoring Performance

Active Session History Reports for Oracle RAC

Learn about the ways that you can check the status of your Oracle Real Application Clusters (Oracle RAC) database by using Active Session History (ASH) reports.

Parent topic: Monitoring Performance

Overview of ASH Reports for Oracle RAC

To diagnose performance issues, Active Session History (ASH) reports provide information about all active sessions in Oracle Real Application Clusters (Oracle RAC) databases.

ASH is an integral part of the Oracle Database self-management framework and is useful for diagnosing performance problems in Oracle RAC environments. ASH report statistics provide details about Oracle Database session activity. Oracle Database records information about active sessions for all active Oracle RAC instances, and stores this data in the System Global Area (SGA). Any session that is connected to the database and using CPU is considered an active session. The exception to this is sessions that are waiting for an event that belongs to the idle wait class.

ASH reports present a manageable set of data by capturing only information about active sessions. The amount of the data is directly related to the work being performed, rather than the number of sessions allowed on the system.

ASH statistics that are gathered over a specified duration can be put into ASH reports. Each ASH report is divided into multiple sections to help you identify short-lived performance problems that do not appear in the ADDM analysis. Two ASH report sections that are specific to Oracle RAC are Top Cluster Events and Top Remote Instance as described in the next two sections.

Related Topics

Parent topic: Active Session History Reports for Oracle RAC

ASH Report for Oracle RAC: Top Cluster Events

To identify which events and instances cause a high percentage of cluster wait events, use the Active Sessions History (ASH) Top Cluster Events report.

The ASH report Top Cluster Events section is part of the Top Events report that is specific to Oracle RAC. The Top Cluster Events report lists events that account for the highest percentage of session activity in the cluster wait class event along with the instance number of the affected instances. You can use this information to identify which events and instances caused a high percentage of cluster wait events.

Parent topic: Active Session History Reports for Oracle RAC

ASH Report for Oracle RAC: Top Remote Instance

To identify specific instances that cause extended cluster wait periods, use the Active Sessions History (ASH) Top Remote Instance report.

The ASH report Top Remote Instance section is part of the Top Load Profile report that is specific to Oracle Real Application Clusters (Oracle RAC). The Top Remote Instance report shows cluster wait events along with the instance numbers of the instances that accounted for the highest percentages of session activity. You can use this information to identify the instance that caused the extended cluster wait period.

Parent topic: Active Session History Reports for Oracle RAC

Monitoring Oracle RAC Statistics and Wait Events

Learn about wait events and statistics specific to Oracle RAC and how to interpret them when assessing performance data generated by the Automatic Workload Repository (AWR), Statspack, or by ad-hoc queries of the dynamic performance views.

Parent topic: Monitoring Performance

Oracle RAC Statistics and Events in AWR and Statspack Reports

To evaluate statistics snapshots generated by AWR and Statspack, you can produce summary data reports.

The statistics snapshots generated by Automatic Workload Repository (AWR) and the Statspack package set of SQL, PL/SQL, and SQL*Plus scripts can be evaluated by producing reports displaying summary data. For example, you can produce summary reports that show such data as load and cluster profiles based on regular statistics, and wait events gathered on each instance.

Most of the relevant data is summarized on the Oracle Real Application Clusters (Oracle RAC) Statistics Page. This information includes:

  • Global cache load profile
  • Global cache efficiency percentages—workload characteristics
  • Global cache and Enqueue Service (GES)—messaging statistics

Additional Oracle RAC sections appear later in the report:

  • Global enqueue statistics
  • Global CR statistics
  • Global CURRENT served statistics
  • Global cache transfer statistics.

Related Topics

Parent topic: Monitoring Oracle RAC Statistics and Wait Events

Oracle RAC Wait Events

Analyzing and interpreting what causes sessions to wait is an important method to determine where time is spent.

In Oracle RAC, the wait time is attributed to an event which reflects the exact outcome of a request. For example, when a session on an instance is looking for a block in the global cache, it does not know whether it will receive the data cached by another instance or whether it will receive a message to read from disk. The wait events for the global cache convey precise information and waiting for global cache blocks or messages is:

  • Summarized in a broader category called Cluster Wait Class

  • Temporarily represented by a placeholder event which is active while waiting for a block, for example:

    • gc current block request

    • gc cr block request

  • Attributed to precise events when the outcome of the request is known, for example:

    • gc current block 3-way

    • gc current block busy

    • gc cr block grant 2-way

  • Multi-block read request events when all disk reads are preferred, for example:

    • gc cr multi block grant
    • gc cr multi block mixed

In summary, the wait events for Oracle RAC convey information valuable for performance analysis. They are used in Automatic Database Diagnostic Monitor (ADDM) to enable precise diagnostics of the effect of cache fusion.

Parent topic: Monitoring Oracle RAC Statistics and Wait Events

Monitoring Performance by Analyzing GCS and GES Statistics

Learn how to determine the amount of work and cost related to inter-instance messaging and contention, examine block transfer rates and remote requests made by each transaction, and determine the number and time waited for global cache events.

Parent topic: Monitoring Oracle RAC Statistics and Wait Events

Analyzing the Effect of Cache Fusion in Oracle RAC

Learn about Global Cache Service (GCS) statistics, GCS wait events, and their relation to Cache Fusion in Oracle Real Application Clusters (Oracle RAC) databases.

The effect of accessing blocks in the global cache and maintaining coherency is represented by:

  • The Global Cache Service (GCS) statistics for current and cr blocks. For example: gc current blocks received, gc cr blocks received, and so on
  • The GCS wait events, for gc current block 3-way, gc cr grant 2-way, and so on

The response time for cache fusion transfers is determined by the messaging and processing times imposed by the physical interconnect components, the IPC protocol and the GCS protocol. Cache Fusion response time is not affected by disk I/O factors, other than occasional log writes. The Cache Fusion protocol requires no I/O resources to guarantee cache coherency for data files (the synchronization of data in multiple caches so that reading a memory location through any cache will return the most recent data written to that location through any other cache). Oracle RAC inherently requires no more I/O to disk than a nonclustered instance requires.

Analyzing Performance Using GCS and GES Statistics

You can monitor GCS performance by identifying data blocks and objects which are frequently used (hot) by all instances.

High concurrency on certain blocks may be identified by GCS wait events and times.

The gc current block busy wait event indicates that the access to cached data blocks was delayed because they were busy either in the remote or the local cache. This could be caused by any of the following:

  • The blocks were pinned

  • The blocks were held up by sessions

  • The blocks were delayed by a log write on a remote instance

  • A session on the same instance was already accessing a block which was in transition between instances and the current session needed to wait behind it (for example, gc current block busy)

Use the V$SESSION_WAIT view to identify objects and data blocks with contention. The GCS wait events contain the file and block number for a block request in p1 and p2, respectively.

An additional segment statistic, gc buffer busy, has been added to quickly determine the busy objects without having to query the V$SESSION_WAIT view mentioned earlier.

The AWR infrastructure provides a view of active session history which can also be used to trace recent wait events and their arguments. It is therefore useful for hot block analysis. Most of the reporting facilities used by AWR and Statspack contain the object statistics and cluster wait class category, so that sampling of the views mentioned earlier is largely unnecessary.

Note:

Oracle recommends using ADDM and AWR. However, Statspack is available for backward compatibility. Statspack provides reporting only. You must run Statspack at level 7 to collect statistics related to block contention and segment block waits.

It is advisable to run ADDM on the snapshot data collected by the AWR infrastructure to obtain an overall evaluation of the impact of the global cache. The advisory will also identify the busy objects and SQL highest cluster wait time.

Analyzing Cache Fusion Transfer Impact Using GCS Statistics

Learn how to monitor and tune GCS performance by identifying objects read and modified frequently and the service times imposed by the remote access.

Waiting for blocks to arrive can constitute a significant portion of the response time, in the same way that reading from disk can increase the block access delays. The difference is that Cache Fusion transfers are usually faster than disk access latencies.

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:

  • gc current block 2-way
  • gc current block 3-way
  • gc cr block 2-way
  • gc cr block 3-way

The object statistics for gc current blocks received and gc cr blocks received enable quick identification of the indexes and tables which are shared by the active instances. As mentioned earlier, creating an Automatic Database Diagnostic Monitor (ADDM) analysis usually points you to the SQL statements and database objects that can be impacted by inter-instance contention.

Any increases in the average wait times for the events mentioned in the preceding list can be caused by the following occurrences:

  • High load: CPU shortages, long run queues, scheduling delays
  • Misconfiguration: using public instead of private interconnect for message and block traffic

If the average wait times are acceptable, and you can diagnose no interconnect or load issues, then the accumulated time waited can usually be attributed to a few SQL statements that need to be tuned to minimize the number of blocks accessed.

The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL statements for global cache events. By reviewing this column, you can identify the SQL that may need to be tuned.

Parent topic: Monitoring Oracle RAC Statistics and Wait Events

Analyzing Response Times Based on Wait Events

Learn how to analyze global cache wait events that may present themselves as the top database time consumers without actually indicating a problem.

Parent topic: Monitoring Oracle RAC Statistics and Wait Events

Understanding Normal and Problem Wait Event Response Times

To distinguish between normal wait events, and wait events that indicate a problem, review routine performance statistics, and then look for the frequent wait events that you should be aware of when interpreting performance data.

When you review the Automatic Workload Repository (AWR) reports and the Statspack set of SQL, PL/SQL, and SQL*Plus reports, or review the dynamic performance views, most global cache wait events that show a high total time in these reports are normal. These normal wait events can present themselves as the top database time consumers, without actually indicating a problem.

If user response times increase and a high proportion of time waited is for global cache, then you should determine the cause. Most reports include a breakdown of events sorted by percentage of the total time.

It is useful to start with an Automatic Database Diagnostic Monitor (ADDM) report. The ADDM report analyzes the routinely collected performance statistics with respect to their impact, points to the objects and SQL contributing most to the time waited, and moves on to the more detailed reports produced by AWR and Statspack.

Block-Related Wait Events

Learn about the main wait events associated with block-related waits.

The main wait events for block-related waits are:

  • gc current block 2-way
  • gc current block 3-way
  • gc cr block 2-way
  • gc cr block 3-way

The block-related wait event statistics indicate that a block was received as either the result of a 2-way or a 3-way message, that is, the block was sent from either the resource master requiring 1 message and 1 transfer, or was forwarded to a third node from which it was sent, requiring 2 messages and 1 block transfer.

Message-Related Wait Events

Learn about the main wait events associated with message-related waits.

The main wait events for message-related waits are:

  • gc current grant 2-way
  • gc cr grant 2-way

The message-related wait event statistics indicate that no block was received, because it was not cached in any instance. Instead, a global grant was given, enabling the requesting instance to read the block from disk, or to modify it.

If the time consumed by these events is high, then you can assume that the frequently used SQL causes a lot of disk I/O (in the event of the cr grant), or that the workload inserts a lot of data, and needs to find and format new blocks frequently (in the event of the current grant).

Contention-Related Wait Events

Learn about the main wait events associated with contention-related waits.

The main wait events for contention-related waits are:

  • gc current block busy
  • gc cr block busy
  • gc buffer busy acquire/release

The contention-related wait event statistics indicate that a block was received that was pinned by a session on another node, or was deferred because a change had not yet been flushed to disk, or was deferred because of high concurrency, and therefore could not be shipped immediately. A buffer can also be busy locally when a session has already initiated a Cache Fusion operation, and is waiting for its completion when another session on the same node is trying to read or modify the same data. High service times for blocks exchanged in the global cache can exacerbate the contention, which can be caused by frequent concurrent read and write accesses to the same data.

The gc current block busy and gc cr block busy wait events indicate that the local instance that is making the request did not immediately receive a current or consistent read block. The term busy in these events names indicates that the sending of the block was delayed on a remote instance. For example, a block cannot be shipped immediately if Oracle Database has not yet written the redo for the block's changes to a log file.

In comparison to block busy wait events, a gc buffer busy event indicates that Oracle Database cannot immediately grant access to data that is stored in the local buffer cache. This is because a global operation on the buffer is pending and the operation has not yet completed. In other words, the buffer is busy and all other processes that are attempting to access the local buffer must wait to complete.

The existence of gc buffer busy events also means that there is block contention that is resulting in multiple requests for access to the local block. Oracle Database must queue these requests. The length of time that Oracle Database needs to process the queue depends on the remaining service time for the block. The service time is affected by the processing time that any network latency adds, the processing time on the remote and local instances, and the length of the wait queue.

The average wait time and the total wait time should be considered when being alerted to performance issues where these particular waits have a high impact. Usually, either interconnect or load issues or SQL execution against a large shared working set can be found to be the root cause.

Load-Related Wait Events

Learn about the main wait events associated with load-related waits.

The main wait events for load-related waits are:

  • gc current block congested
  • gc cr block congested

The load-related wait events indicate that a delay in processing has occurred in the GCS, which is usually caused by high load or CPU saturation. To solve this kind of wait event, you add additional CPUs, provide greater load-balancing, or offload processing to different times, or to a new cluster node. For the two events mentioned here, the wait time encompasses the entire round trip from the time a session starts to wait after initiating a block request until the block arrives.