Wait Events (Oracle)
You can use this procedure to display wait events.
- Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Activity
Alternatively, use transaction code ST04.
- Choose Detail analysis menu → Wait events.
This screen formats the data in the Oracle system tables V$System_Event and V$Session_Event. To display the current value, parameter init<SID>.ora timed_statistics (TIMED_STATISTICS (Oracle)) must be set to true.
This screen displays the wait situation of the Oracle processes. There is a difference between cases where the Oracle process is waiting because there is currently nothing for it to do ('idle waits'), and where the Oracle process wants to run but first has to wait for a resource that is not yet available ('busy waits'). 'Total waits time' describes the sum of 'idle wait time' and 'busy waits time'.
'Idle wait' situations are:
- 'SQL*Net message from client' (the process is waiting for an SQL statement from the client, for example, the R/3 work process),
- 'rdbms ipc message' (the process is waiting for a statement from the RDBMS),
- 'dispatcher timer', 'virtual circuit status', 'pmon timer', 'smon timer', 'WMON goes to sleep', 'Null event'.
'Busy wait' situations are:
- Wait situation for physical I/O: 'db file sequential read', 'db file parallel write', 'log file sequential read', etc.:
- 'enqueue': wait situations due to exclusive database locks that can be examined using the 'Exclusive Lockwaits' screen.
- 'buffer busy waits': wait situations in the Oracle buffers: you can find details on this on the screen under 'Buffer busy waits'.
- 'log file switch (archiving needed)': problems with the log switch or with checkpointing. Look in the database message log for entries such as 'Cannot allocate log, archival required' or 'All online logs needed archiving'.
- 'SQL*Net more data to client' and 'SQL*Net more data from client': The Oracle process is waiting because data cannot be transferred quickly enough from, for example, the client. This wait situation indicates problems with the SQL*Net-Installation or with the network.
See also: