Spotlight on Oracle 10.5 - Getting Started Guide

Interconnect Load: Oracle uses the Interconnect for block movement and cache coherency between instances in the Oracle RAC cluster. An Interconnect Load indicates that a session is unable to find a required block on the local instance, and has to make a request for the block from the remote instance.

This indicates one of the following:

• Frequent block changes (requiring blocks to be reloaded), or
• Constant flushing of the System Global Area [SGA] (requiring blocks to be requested again

Interconnect Load may be an indication of the overall performance of the cluster.

Within an Oracle RAC cluster, the cluster interconnect transfers blocks that have been requested by sessions on one instance from other instances that currently hold those blocks. The performance of the interconnect is crucial for the performance of the Oracle RAC cluster and, more specifically, for the movement of cached data between instances.

The health of the movement of cache between instances is measured by the average elapsed time from when the requesting instance requests the block to the time when the requesting instance receives the block.

In any database instance, we can expect occasional spikes of user activity. However, when the average of such spikes continues to be high for an extended period of time, it may indicate a degradation in the overall performance of the database cluster.

When a user on a single-instance Oracle configuration requests a block, Oracle will first look for the block in its buffer; if the block is not found in the buffer, Oracle retrieves the data from disk.

In an Oracle RAC environment (where blocks are shared across multiple instances), a user on one instance can get a block being read by another session on another instance only by requesting the block from that other instance. The GCS (Global Cache Service) has to transfer the block across the interconnect, and the block can then be viewed by the user.

The additional time used to request a block from another instance is called cluster overhead and is measured as a percentage of the total time waited.

Shows the amount of memory allocated to the In-Memory column store aggregated over the instances in the RAC, and how much of that memory is in use.

Note: Oracle In-Memory is available for Oracle 12c Release 1 (12.1.0.2) and above. Data is displayed for Oracle instances where In-Memory is available and has been enabled. In-Memory is disabled by default.

When a user requests a data block, the block is cached in the SGA for reuse by other sessions until such time as the block is not used and the space is required for other data. A "cache miss" occurs when a data block requested by the user session is not found in the local SGA, and the block has to be obtained from a remote instance.

The Cache Miss Rate metric indicates the frequency of such requests, and it directly affects the overall performance of the cluster

The rate of Logical I/O (LIO) performed across all instances in the Oracle RAC cluster. LIO occurs when Oracle finds data in the local buffer cache of an instance without needing to read the data from physical disk.

Reading data from the buffer is less expensive than reading it from disk, but still the cost of LIO in an Oracle environment cannot be ignored. Oracle's architecture depends on underlying operating systems for several of its operations, including performing an LIO. To read a block from buffer, Oracle requires that a lock be placed on the row. To obtain a lock, Oracle requires a latch, for which it depends on the operating system.

When the operating system has no available latches (because other processes have taken them), the Oracle process has to sleep for a while before it can acquire one. This entire operation means that the LIO operation in an Oracle architecture is not cheap.

The rate of physical reads and physical writes performed across ALL instances in the Oracle RAC cluster.

The average CPU load across ALL active nodes in the cluster. It displays the CPU consumption by all processes across all nodes in the cluster. (Most nodes run both Oracle and non-Oracle processes, and because all processes consume CPU cycles, the tuning of non-Oracle factors can also improve Oracle performance in the cluster.)

Note: Oracle statistics report only CPU utilization by Oracle sessions, but the CPU percentage reported by Spotlight on Oracle RAC covers both Oracle and non-Oracle processes.

Because Oracle uses the CPU during several of its operations, extensive CPU usage by a few processes may cause a performance bottleneck that prevents other regular processes from using the CPU. Such processes should be identifed and tuned to avoid the bottleneck.

For example, when Oracle executes a SQL statement, it parses the statement to determine whether its syntax and contents are correct. This process can cause significant overhead and consume CPU. Once a statement has been parsed, Oracle does not parse the statement again unless the parsing information is aged from the memory cache and is no longer available. Ineffective memory-sharing among SQL statements – including insufficient shared pool size and the non-use of bind variables – is a major cause of frequent reparsing.

Oracle processes also consume significant CPU when they perform high levels of logical I/O (LIO). Wherever possible, you should tune SQL statements to avoid high LIO. See Total Logical Reads for more information.

This control is applicable to environments where Oracle Data Guard is installed. It shows the overhead of Data Guard on the primary database.

The design is similar to the Average Active Sessions (AAS) guage.

• Dark Shaded Area - Amount of time sessions spend waiting on data guard.
• Light Shaded Area - Amount of time sessions spend on other tasks.
• White Line - CPU Limit.

A value greater than the CPU limit indicates potential performance issues. This is shown on the Average Active Sessions gauge as either of the colored bars passing the vertical white line. How far the colored bar has passed the vertical white line indicates the severity of the performance issue. A value far greater than the CPU limit indicates that the database is experiencing bottlenecks.

Note: Alarms on this component are raised on the Spotlight on Oracle Data Guard Home Page, not the Spotlight on Oracle RAC home page.

This control is applicable to environments where Oracle Data Guard is installed. The apply lag measures the difference in elapsed time from when the last applied change became visible on the standby and that same change was first visible on the primary.

An alarm raised on this control indicates an alarm on Spotlight on Oracle Data Guard - NOT necessarily an alarm on the apply lag. For more information on the alarm raised, open the Spotlight on Oracle Data Guard home page(s) for this Primary database. The severity of the alarm on this control matches the highest severity alarm raised on Spotlight on Oracle Data Guard. The Standby connections are ordered in the related drilldowns list according to severity, the Standby with the highest severity alarm raised against it is listed first.

The Uptime for the Oracle RAC cluster.

Note: The Uptime for the Oracle RAC cluster is that of the database instance with the longest uptime. This may underestimate the actual uptime of the cluster.

The name of the container for the pluggable database.

Note: Applicable to Oracle 12c.