Most common Oracle wait event descriptions



Last updated on July 29th, 2016 at 03:04 am

Wait Event
Description
buffer busy waits
The session wants to access a data
block that is either 1) currently not in memory, but another process has
already issued an I/O request to read the block into memory, or 2) in memory
but in an incompatible mode (current versus consistent, for example).
control file parallel write
The session has issued multiple
I/O requests in parallel to write blocks to all control files, and is waiting
for all of the writes to complete.
control file sequential read
The session is waiting for blocks
to be read from a control file.
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.
db file parallel write
The process, typically DBWR, has
issued multiple I/O requests in parallel to write dirty blocks from the
buffer cache to disk and is waiting for all requests to complete.
db file scattered read
The session has issued an I/O
request to read a series of contiguous blocks from a data file into the
buffer cache and is waiting for the operation to complete. This typically
happens during a full table scan or fast full index scan.
db file sequential read
The session has issued an I/O
request to read one block from a data file into the buffer cache and is
waiting for the operation to complete. This typically happens during an index
lookup or a fetch from a table by ROWID when the required data block is not
already in memory.
direct path read,
direct path write
The session has issued
asynchronous I/O requests that bypass the buffer cache and is waiting for
them to complete. These wait events often involve temporary segments.
Enqueue
The session is waiting on an Enqueue (a lock you can see in v$lock). This commonly occurs when one user is
trying to update a row in a table that is currently being updated by another
user.
free buffer waits
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.
latch free
The session is waiting for a latch
held by another session. (This event does not apply to processes that are
spinning while waiting for a latch; when a process is spinning, it is not
waiting.)
library cache load lock
The session is waiting for the
opportunity to load an object or a piece of an object into the library cache.
(Only one process can load an object or a piece of an object at a time.)
library cache pin
The session wants to pin an object
in memory in the library cache for examination, ensuring no other processes
can update the object at the same time. This happens when you are compiling
or parsing a PL/SQL object or a view.
log buffer space
The session is waiting for space
in the log buffer. (Space becomes available only after LGWR has written the
current contents of the log buffer to disk.) This typically happens when
applications generate redo faster than LGWR can write it to disk.
log file parallel write
The session is waiting for blocks
to be written to all online redo log members in one group. LGWR is typically
the only process to see this wait event. It will wait until all blocks have
been written to all members.
log file sequential read
The session is waiting for blocks
to be read from the online redo log into memory. This primarily occurs at instance
startup and when the ARCH process archives filled online redo logs.
log file switch completion
The session is waiting for a log
file switch to complete, typically so more redo can be generated.
log file sync
The session is waiting for LGWR to
finish flushing the log buffer to disk. This occurs when a user commits a
transaction. (A transaction is not considered committed until all of the redo
to recover the transaction has been successfully written to disk.)
undo segment extension
The session is waiting for an undo
segment to be extended or shrunk.
write complete waits
The session is waiting for a
requested buffer to be written to disk; the buffer cannot be used while it is
being written.

Important Information on wait event

The v$session_wait view displays information about wait events for which active sessions are currently waiting. The following is the description of this view, and it contains some very useful columns, especially the P1 and P2 references to the objects associated with the wait events.

desc v$session_wait

Name Null? Type
————————— ——– ————
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

Using v$session_wait, it is easy to interpret each wait event parameter using the corresponding descriptive text columns for that parameter. Also, wait class columns were added so that various wait events could be grouped into the related areas of processing such as network, application, idle, concurrency, etc.
These column was also added to v$session table from 10g onwards. So you can just use v$session to find all the details

Each wait event contains other parameters that provide additional information about the event.
How to find the information about wait event and its parameter

The meaning of each wait event corresponds know by querying the V$EVENT_NAME p1, p2, p3 of
col name format a25;
col p1 format a10;
col p2 format a10;
col p3 format a10;
SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3
FROM V$EVENT_NAME
WHERE NAME = ‘&event_name’;

Let say for example we took

The event_name input values: db file scattered read
Original value of 3: WHERE NAME = ‘& event_name A’
The new value 3: WHERE NAME = ‘db file scattered read’

The name P1 P2 P3
————————————————– ——–
db file scattered read file # block # blocks

file #: data file number
Block #: starting block number
blocks: to read the the the number of of the data block

Now let us see how the above information can help us capture various things
Suppose  a particular session waits for a buffer busy waits event, the database object causing this wait event can easily be determined:

select username, event, p1, p2 from  v$session_wait  where sid = 4563;

The output of this query for a particular session with SID 4563 might look like this:

USERNAME    EVENT            SID P1 P2
———- —————– — — —
APPS         buffer busy waits 4563  11  545

Columns P1 and P2 allow the DBA to determine file and block numbers that caused this wait event. The query below retrieves the object name that owns data block 155, the value of P2 above:

SQL> select segment_name,segment_type
from dba_extents
where file_id = 11
and 45 between block_id and block_id + blocks – 1;

The ability to analyze and correct Oracle Database  wait events is critical in any tuning project. The majority of activity in a database involves reading data, so this type of tuning can have a huge, positive impact on performance.

Note: When doing wait analysis, it is critical to remember that all Oracle databases experience wait events, and that the presence of waits does not always indicate a problem. In fact, all well-tuned databases have some bottleneck.

we can use 10046 event to trace wait event of the session also


Leave a Reply