TOP Interview Question : Explain Brief Top 5 Wait Events ?

Analyzing Wait Events :

Wait events are statistics that a server process or thread increments when it waits for an operation to complete in order to continue its processing. For example, a SQL statement may be modifying data, but the server process may have to wait for a data block to be read from disk because it’s not available in the SGA. Although there’s a large number of wait events, some the most common wait events are the following:

• Buffer busy waits: These occur when multiple processes attempt to concurrently access the same buffers in the buffer cache.

• Free buffer waits: These waits occur when a server process posts the database writer process to write out dirty buffers in order to make free buffers available.

• Db file scattered read: These are waits incurred by an user process for a physical I/O to return when reading buffers into the SGA buffer cache. The scattered reads are multiblock reads and can occur because of a full table scan or a fast full scan of an index.

• Db file sequential read: These are waits incurred by an user process for a physical I/O to return when reading buffers into the SGA buffer cache. The reads are single block reads and are usually because of indexed reads.

• Enqueue waits: These are waits on Oracle locking mechanisms that control access to internal database resources and that occur when a session waits on a lock held by another session. You actually won’t see a wait event named enqueue (or enq) because the enqueue wait event types are always held for a specific purpose, such as enq: TX – contention, enq:TX – index contention, and enq:TX – row lock contention.

• Log buffer space: These are waits caused by server processes waiting for free space in the log buffer.

• Log file sync: These are waits by server processes that are performing a commit (or rollback) for the LGWR to complete its write to the redo log file.

Analyzing Oracle wait events is the starting point when troubleshooting a slow-running query. When a query is running slow, it usually means that there are excessive waits of one type or another. Some of the waits may be because of excessive I/O due to missing indexes. Other waits may be caused by a latch or a locking event.

Every Oracle wait event belongs to a specific wait event class. Oracle groups wait events into classes, such as Administrative, Application, Cluster, Commit, Concurrency, Configuration, Scheduler, System I/O, and User I/O, to facilitate the analysis of wait events. Here are the characteristics of typical waits in some of these wait classes:

• Application: Waits resulting from application code issues such as lock waits because of rowl evel locking

• Commit: Waits for confirmation of a redo log write after committing a transaction.

• Network: Waits caused by delays in sending data over the network

• User I/O: Waits for reading blocks from disk Two key wait classes are the Application and User I/O wait classes. The Application wait class contains waits because of row and table locks caused by an application. The User I/O class includes the db file scattered read, db file sequential read, direct path read, and direct path write events. The System I/O class includes redo log–related wait events among other waits. The Commit class contains just the log file sync wait information.

There’s also an “idle” class of wait events such as SQL*Net message from client, for example, that merely indicate an inactive session. You can ignore the idle waits.

Classes of wait events help you quickly find out what type of activity is affecting database performance. For example, the Administrative wait class may show a high number of waits because you’re rebuilding an index. Concurrency waits point to waits for internal database resources such as latches. If the Cluster wait class shows the most wait events, then your RAC instances may be experiencing contention for global cache resources (gc cr block busy event). Note that the System I/O wait class includes waits for background process I/O such as the database writer (DBWR) wait event db file parallel write.

The Application wait class contains waits that result from user application code—most of your enqueue waits fall in this wait class. The only wait event in the Commit class is the log file sync event, which we examine in detail later in this chapter. The Configuration class waits include waits such as those caused by log files that are sized too small.

The following SQL statement displays an alphabetical list of all Oracle wait events and the wait class to which they belong:

SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;