Oracle Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of the wait classes.

· Administrative: Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

· Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

· Cluster: Waits related to Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')

· Commit: This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

· Concurrency: Waits for internal database resources (for example, latches)

· Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

· Idle: Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')

· Network: Waits related to network messaging (for example, 'SQL*Net more data to dblink')

· Other: Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

· Scheduler: Resource Manager related waits (for example, 'resmgr: become active')

· System I/O: Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

· User I/O: Waits for user I/O (for example 'db file sequential read')

Note:

1. Information about wait events is displayed in three dynamic performance views:

· V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.

· V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

· V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each session.

2. Wait event classes in Oracle 10gR2 and its total events:

SELECT wait_class, count(*) FROM V$EVENT_NAME

GROUP BY wait_class ORDER BY 1;

WAIT_CLASS	COUNT(*)
Administrative	46
Application	12
Cluster	47
Commit	1
Concurrency	24
Configuration	23
Idle	62
Network	26
Other	591
Scheduler	2
System I/O	24
User I/O	17

3. For a complete listing of wait events, in alphabetical order, you can issue the following SQL statement:

SELECT name FROM V$EVENT_NAME ORDER BY name;