Oracle Database - Wait Event

1 - About

A session can hang for a lot of reason that are called wait event. A lock can give also the same impression

From SQL Developer, in the Monitoring Session Tools:

3 - Classes/Group/Type 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 Cluster 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 IO (for example, DBWR wait for 'db file parallel write')
  • User I/O: Waits for user IO (for example db file sequential read and db file scattered read, all events with the term read.)

4 - API / View

Information about wait events is displayed in this views:

  • the v$session
  • and in three following dynamic performance views:
    • V$SESSION_WAIT,
    • V$SESSION_EVENT,
    • and V$SYSTEM_EVENT

If the configuration parameter TIMED_STATISTICS is set to true, you can now how long each resource was waited for.

4.1 - V$SESSION_WAIT

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

Because V$SESSION_WAIT is a current state view, it also contains a finer-granularity of information than V$SESSION_EVENT or V$SYSTEM_EVENT. It includes additional identifying data for the current event in three parameter columns: P1, P2, and P3.

For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read, but it does not show which file and block number. However, V$SESSION_WAIT shows:

  • the file number in P1,
  • the block number read in P2,
  • and the number of blocks read in P3

(P1 and P2 let you determine for which segments the wait event is occurring).

4.2 - Others

  • 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.

5 - Wait Event

db/oracle/wait.txt ยท Last modified: 2017/10/27 15:54 by gerardnico