Overall CPU

The amount of time Oracle spent both waiting for CPU and using CPU during current interval.

CPU Usage

When SQL statements and other types of calls are made to Oracle, an amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.

CPU utilization is a key operating system statistic in the tuning process. Excessive CPU could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.

CPU Wait

Wait Time until the CPU resource is available. Time spent by the session waiting in the system's run queue to be granted for CPU cycles. The amount of time is dependent upon the number of concurrent processes and threads requesting CPU time. The metric value should be inspected in conjunction with the value of the "Run Queue Length" metric.

I/O Wait

Time spent waiting for disk input/output operations to complete.

Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources.

Generally, I/O Wait is caused by poorly-tuned SQLs or applications which generate a significant amount of logical I/O translating into excessive physical disk usage. In this case, SQL/Application tuning can reduce the logical I/O- induced load. However, it could also be caused by poorly-configured disks or storage sub-systems.

Datafile
Write

The DBWR is performing a write to files and blocks (for example, flushing dirty data buffers from the SGA to the data files).

The db file writes are not experienced by user sessions.

Multi
Block
Read

The session is reading multiple data blocks from the data files into the SGA buffer.

Multi-Block Read waits usually reflects full table or index scans. Generally full scans of large segments should be avoided, although they are sometimes unavoidable, as in data warehouse environments.

Single
Block
Read

The session is reading a single block from a data file into the SGA buffer.

Large values can be acceptable, reflecting a high-transaction, well-tuned system with significant index utilization.  However, a large number of waits here could also indicate poor joining orders of tables, or unselective indexing.

Direct
Path
I/O

The session has issued asynchronous I/O requests that bypass the buffer cache and is waiting for them to complete.

Direct Path I/O usually reflects disk sorts and bulk loads.

Control
File I/O

The session has issued I/O requests to control file blocks, and is waiting for all of the reads and writes to complete.

DB File
Parallel
Read

The session has issued multiple I/O requests in parallel to read blocks from data files into memory and is waiting for all requests to complete. This may occur during recovery or during regular activity when a session batches many single block I/O requests together and issues them in parallel.

External
File I/O

Session waits because of an I/O request on an external large object (LOB), such as a BFILE.

Miscellaneous
I/O

Sporadic I/O activity, such as opening a data file. Usually negligible.

Network Wait

Time spent waiting for messages to be sent or received over the network interface.

Network performance is measured in number (per second) of packets sent and received. SQL*Net is the Oracle network interface that allows Oracle tools running on network workstations and servers to access, modify, share, and store data on other servers. SQL*Net is considered part of the program interface in network communications.

Network statistics can be used in much the same way as disk statistics to determine if a network or network interface is overloaded or not performing optimally.

Excessive network wait can be caused by either:

• excessive network usage originating in the application

• physical problems, identifiable by network errors and network collisions

SQL*Net

The wait time it takes for messages, data or break reset to be transmitted over the network interface to the client.

DB
Link

The wait time it takes for messages, data or break reset to be transmitted over the network interface to another server process on a remote database.

Buffer Wait

Buffer waits occur because of a contention in accessing database block buffers. For example, when a session wants to access a data block that is in an incompatible mode (buffer busy) or wants to read a data block into the buffer, but it has no available space (free buffer).

Buffer
Busy

The session wants to access a data block that is either:

• currently not in memory, but another process has already issued an I/O request to read the block into memory.

• in memory but in an incompatible mode (current versus consistent, for example).

The session will wait until the block becomes available.

Buffer busy waits should not be greater than 1 percent of the entire activity.

Free
Buffer

The session needs a free buffer so it can bring a data block into the buffer cache and is waiting for a buffer that is not dirty to become available. This can occur if DBWR is not writing dirty buffers to disk fast enough.

This wait usually indicates a non-optimal physical I/O configuration.

Free buffer waits can be minimized by increasing the checkpoint frequency, using more DBWR processes, or increasing the number of physical disks.

Other DB
Buffer

Sporadic buffer waits caused by buffer contention, such as buffer write wait. Usually negligible.

Lock Wait

Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock.  Locks are designed to ensure data integrity by limiting simultaneous data access.

Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared.

Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table.

Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application.

Latch Wait

Latch waits are the waits that occur when a session needs to acquire a latch that is held by another session. Latches are simple, low-level serialization mechanisms to protect shared SGA data structures and shared code segments from simultaneous session access. They are similar in purpose to locks: Latches protect internal memory structures while locks protect data structures. They are designed to be very quickly acquired and freed.

The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.

For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.

Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes. Latch waits can be eliminated by either tuning the instance or the application.

Tuning latch contention is dependent on the type of latch wait encountered.

Redo Wait

All DML activity occurring in the database is recorded in the redo log. Redo waits include the contention resulting from:

• User sessions writing redo entries to the redo log buffer.

• LGWR writing the log buffer contents into the active redo log file.

• ARCH archiving redo logs to the destination location.

Archival

Any activity which generates LGWR for archiving the redo logs to the target location.  Additionally, user sessions can also experience archival waits when the log that the LGWR will be switching into has not yet been archived and the session is issuing DMLs.

Log Buffer

Waits that occur when copying redo entries into the log buffer.   

A frequent cause of log buffer wait is if redo entries are written into the log buffer faster than LGWR can write it to the redo logs, and user sessions must therefore wait because of log write latency.

Log Switch

The session waits for a redo log switch to be completed.

Log Write

Waiting for the write (physical I/O) to a logfile to complete. LGWR experience log write waits when it periodically flushes the contents of the log buffer into the active log file.  

User sessions experience log write waits when, upon commits, it waits for the LGWR to flush its redo information to the redo log file. The user session will post the LGWR to write the log buffer to the redo log file.

Cluster Wait

Waits arising from cluster-related activity contention. For example, cache synchronization and global locks.

Global
Locks
(RAC)

Global Cache Service  (GCS) Operations tracks the locations, modes, and roles of data blocks. The GCS therefore also manages the access privileges of (and imposes waits on) various instances in relation to resources. Oracle uses the GCS for cache coherency when the current version of a data block is in one instance's buffer cache and another instance requests that block for modification.

Apart from the regular locking at the instance level, Oracle maintains another lock structure and a set of lock escalation rules to manage data blocks between instances. This lock structure is a three character representation consisting of a Role, Mode and indicator:

• Roles could be either NULL, Shared or Exclusive

• Modes could be either Local or Global

• The indicator represented by a 0 or 1 is an internal mechanism to track if the block found in the cache of an instance is either a current image or a previous image.

Cluster Coordination

Resource coordination within Real Application Clusters occurs at both an instance level and at a cluster database level.  Waits under this category refer to activities that are of a global nature, such as waits encountered while movement of blocks between instances participating in the cluster or waits encountered by cluster specific background processes.

Network/
Interconnect

Apart from the regular network activity between the client and the database servers, in a RAC environment Oracle uses a private interconnect for transfer of data between instances to satisfy user requests.  Performance of the interconnect is critical to the overall performance of the database server.

This categorization represents waits encountered with respect to the interconnect activity.

Significant network interconnect wait may indicate that excessive cache transfer because of a non-optimal application cluster affinity or a too-slow network link.

In general, the network interconnect is responsible for:

• Monitoring Health, Status, and Synchronize messages

• Transporting Distributed lock manager (DLM) messages

• Accessing remote file systems

• Moving application-specific traffic

• Providing cluster alias routing

Other Wait

Total time spent waiting for miscellaneous operations to complete. None of the underlying operations (wait events) can be classified into any other wait categories. Usually negligible.

Backup/
Recovery

When archiving is not performed fast enough, database operations can be suspended temporarily whenever the log writer is forced to wait for an inactive group to become available for reuse.

Resource
Manager

Waits experienced by sessions when exceeding thresholds/quotas permitted them by the resource manager.

Resource Manager (10g and later) can be used to provide more fine-grained control of resource allocations to Services in both RAC and single instance environments. These two facilities are integrated so that Services can be mapped to Resource Manager consumer groups.

Resource Manager waits are thus controls placed on Service workloads to limit the execution times of long running operations, the number of active sessions that are allowed to execute concurrently, the resources consumed by long running resource intensive sessions, and other resource usages.

Miscellaneous
Other Waits

Assorted independent waits. Usually negligible.

Non-[Resource] Activity

Instance activity generated by resources other than the selected Oracle wait event.