A
Oracle Wait Events

This appendix describes the event name, wait time and parameters for wait events displayed by the V$SESSION_WAIT and V$SYSTEM_EVENT views.

Topics covered include:

• Displaying Wait Events

• Wait Events and Parameters

• Parameter Descriptions

• Wait Event Descriptions

Displaying Wait Events

The V$SESSION_WAIT view displays the events for which sessions have just completed waiting or are currently waiting. The V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

The V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each session. For more information on these views, see "V$SESSION_EVENT", "V$SESSION_WAIT", and "V$SYSTEM_EVENT".

Many of these wait events are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice. This may even happen during a bug fix patch release. Application developers should be aware of this and write their code to tolerate missing or extra wait events.

This appendix describes the event name, wait time, and parameters for each event.

The following SQL statement displays all Oracle events:

SELECT *
FROM V$EVENT_NAME;

Wait Events and Parameters

The following wait events are present in the Oracle server. The columns P1, P2, and P3 represent parameters for the wait event.

Table A-1 Wait Events for Oracle Parallel Server

Event Name	P1	P2	P3
BFILE check if exists
BFILE check if open
BFILE closure
BFILE get length
BFILE get name object
BFILE get path object
BFILE internal seek
BFILE open
BFILE read
Contacting SCN server or SCN lock master
DFS db file lock	file#	not used	not used
DFS lock handle	type|mode	id1	id2
DLM generic wait event
IO clients wait for LMON to join GMS group
LMON wait for LMD to inherit communication channels
Null event
PAR RECOV: Dequeue msg-Slave
PAR RECOV: Wait for reply-Query Coord
PL/SQL lock timer	duration	not used	not used
Parallel Execution Idle Wait-Slaves wait event
Replication Dequeue
SQL*Net break/reset to client	driver id	break?	not used
SQL*Net break/reset to dblink	driver id	break?	not used
SQL*Net message from client	driver id	#bytes	not used
SQL*Net message from dblink	driver id	#bytes	not used
SQL*Net message to client	driver id	#bytes	not used
SQL*Net message to dblink	driver id	#bytes	not used
SQL*Net more data from client	driver id	#bytes	not used
SQL*Net more data from dblink	driver id	#bytes	not used
SQL*Net more data to client	driver id	#bytes	not used
SQL*Net more data to dblink	driver id	#bytes	not used
Test if message present
WMON goes to sleep	not used	not used	not used
Wait for a parallel reco to abort
Wait for a undo record
Wait for credit-free buffer
Wait for credit-need buffer to send
Wait for credit-send blocked
Wait for slaves to ACK-Query Coord
Wait for slaves to join-Query Coord
Wait for stopper event to be increased
alter system set mts_dispatcher	waited	not used	not used
batched allocate scn lock request	not used	not used	not used
buffer being modified waits
buffer busy due to global cache	file#	block#	id
buffer busy waits	file#	block#	id
buffer deadlock	dba	class*10+mode	flag
buffer for checkpoint	buffer#	dba	state*10+mode
buffer latch	latch addr	chain#	not used
buffer read retry	file#	block#	not used
checkpoint completed	not used	not used	not used
checkpoint range buffer not saved	not used	not used	not used
control file parallel write	files	blocks	requests
control file sequential read	file#	block#	blocks
control file single write	file#	block#	blocks
conversion file read	file#	block#	blocks
db file parallel read	files	blocks	requests
db file parallel write	files	blocks	requests
db file scattered read	file#	block#	blocks
db file sequential read	file#	block#	blocks
db file single write	file#	block#	blocks
debugger command	not used	not used	not used
direct path write	file number	first dba	block cnt
dispatcher shutdown
dispatcher timer	sleep time	not used	not used
dupl. cluster key	dba	not used	not used
enqueue	name|mode	id1	id2
file identify	fib	file name	opcode
file open	fib	iov	0
free buffer waits	file#	block#	set-id#
free global transaction table entry	tries	not used	not used
free process state object	not used	not used	not used
global cache bg acks
global cache cr request
global cache freelist wait	lenum	not used	not used
global cache lock busy	file#	block#	lenum
global cache lock cleanup	file#	block#	lenum
global cache lock null to s	file#	block#	lenum
global cache lock null to x	file#	block#	lenum
global cache lock open null	file#	block#	class
global cache lock open s	file#	block#	lenum
global cache lock open x	file#	block#	lenum
global cache lock s to x	file#	block#	lenum
global cache multiple locks
global cache pending ast
imm op	msg ptr	not used	not used
inactive session	session#	waited	not used
inactive transaction branch	branch#	waited	not used
index block split	rootdba	level	childdba
instance recovery	undo segment#	not used	not used
instance state change	layer	value	waited
io done	msg ptr	not used	not used
kcl bg acks	count	loops	not used
kdi: Done Message Dequeue-Coord
ktpr: Done Message Dequeue-Coord
latch activity	address	number	process#
latch free	address	number	tries
library cache load lock	object address	lock address	10*mask+namespace
library cache lock	handle address	lock address	10*mode+namespace
library cache pin	handle address	pin address	10*mode+namespace
local write wait
lock manager wait for dlmd to shutdown
lock manager wait for remote message	waittime	not used	not used
log buffer space	not used	not used	not used
log file parallel write	files	blocks	requests
log file sequential read	log#	block#	blocks
log file single write	log#	block#	blocks
log file switch (archiving needed)	not used	not used	not used
log file switch (checkpoint incomplete	not used	not used	not used
log file switch (clearing log file)	not used	not used	not used
log file switch completion	not used	not used	not used
log file sync	buffer#	not used	not used
log switch/archive	thread#	not used	not used
name-service call wait
on-going SCN fetch to complete	not used	not used	not used
parallel execution create server	nservers	sleeptime	enqueue
parallel execution dequeue wait	reason	sleeptime/ senderid	passes
parallel execution qref latch	function	sleeptime	qref
parallel execution server shutdown	nalive	sleeptime	loop
parallel execution signal server	serial	error	nbusy
pending global transaction(s)	scans	not used	not used
pipe get	handle address	buffer length	timeout
pipe put	handle address	record length	timeout
pmon rdomain attach
pmon timer	duration	not used	not used
process startup	type	process#	waited
queue messages	queue id	process#	wait time
rdbms ipc message	timeout	not used	not used
rdbms ipc message block	not used	not used	not used
rdbms ipc reply	from_process	timeout	not used
redo wait	not used	not used	not used
refresh controlfile command
reliable message
retry contact SCN lock master
row cache lock	cache id	mode	request
scginq AST call	not used	not used	not used
secondary event
single-task message	not used	not used	not used
slave exit	nalive	sleeptime	loop
slave wait
smon timer	sleep time	failed	not used
sort segment request
switch logfile command	not used	not used	not used
timer in sksawat	not used	not used	not used
trace continue
trace unfreeze
trace writer I/O
trace writer flush
transaction	undo seg#| slot#	wrap#	count
unbound tx	not used	not used	not used
undo segment extension	segment#	not used	not used
undo segment recovery	segment#	tx flags	not used
undo segment tx slot	segment#	not used	not used
virtual circuit status	circuit#	status	not used
wait for DLM latch	latchtype	gets	immediate
wait for DLM
wait for DLM process allocation
wait for DLM reconfiguration to complete
wait for checking DLM domain
wait for gms de-registration
wait for gms registration
wait for influx DLM latch	latchtype	latchaddr	not used
wait for lmd and pmon to attach DLM
wait for lock db to become frozen
wait for lock db to unfreeze
wait for ownership of group-owned lock
wait for pmon to exit
wait for reconfiguration to start
wait for recovery domain attach
wait for recovery domain latch in kjpr
wait for recovery validate to complete
wait for register recovery to complete
wait for send buffers to send DLM message
wait for tickets to send DLM message
wait to run in thread run
waiting in scheduler enter
waiting in scheduler enter2
waiting in scheduler shutdown
waiting in scheduler system stop
waiting in thread check
waiting in thread check2
waiting in thread end wait
waiting in thread run (queued)
write complete waits	file#	block#	id
writes stopped by instance recovery or database suspension	by thread #	our thread#	not used

Parameter Descriptions

This section describes a number of common event parameters.

• block#

• blocks

• break?

• class

• dba

• driver id

• file#

• id1

• id2

• lenum

• mode

• name and type

• namespace

• requests

• session#

• waited

block#

This is the block number of the block for which Oracle needs to wait. The block number is relative to the start of the file. To find the object to which this block belongs, enter these SQL statements:

select name, kind      
from ext_to_obj_view    
where file#  = file#     
     and lowb  <= block#       
     and highb >= block#;

blocks

The number of blocks that is being either read from or written to the file. The block size is dependent on the file type:

• database files have a block size of DB_BLOCK_SIZE

• logfiles and controlfiles have a block size that is equivalent to the physical block size of the platform

break?

If the value for this parameter equals 0, a reset was sent to the client. A non-zero value indicates that a break was sent to the client.

class

The class of the block describes how the contents of the block are used. For example, class 1 represents data block, class 4 represents segment header.

dba

The initials "dba" represents the data block address. A dba consists of a file number and a block number.

driver id

The address of the disconnect function of the driver that is currently being used.

file#

The following query returns the name of the database file:

select *    
from v$datafile    
where file# = file#;

id1

The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).

id2

The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).

lenum

The relative index number into V$LOCK_ELEMENT.

mode

The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request. This parameter has one of the following values:

Table A-2 Lock Mode Values

Mode Value	Description
1	Null mode
2	Sub-Share
3	Sub-Exclusive
4	Share
5	Share/Sub-Exclusive
6	Exclusive

Use the following SQL statement to retrieve the name of the lock and the mode of the lock request:

select chr(bitand(p1,-16777216)/16777215)||
       chr(bitand(p1, 16711680)/65535) "Lock",
       bitand(p1, 65536) "Mode"
from v$session_wait
where event = 'DFS enqueue lock acquisition';

name and type

The name or "type" of the enqueue or global lock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.

select chr(bitand(p1,-16777216)/16777215)||
      chr(bitand(p1,16711680)/65535) "Lock"
from v$session_wait
where event = 'DFS enqueue lock acquisition';

namespace

The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view.

requests

The number of I/Os that are "requested". This differs from the number of blocks in that one request could potentially contain multiple blocks.

session#

The number of the inactive session. Use the following SQL statement to find more information about the session:

select *      
from v$session     
where sid = session#;

waited

This is the total amount of time the session has waited for this session to die.

Wait Event Descriptions

This section describes the Oracle events.

BFILE check if exists

The session waits to check if an external large object (LOB) exists.

Wait Time: The total elapsed time for the exists call.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE check if open

The session waits to check if an external large object (LOB) has already been opened.

Wait Time: The total elapsed time for the isopen call.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE closure

The session waits for an external large object (LOB) to close.

Wait Time: The total elapsed time for the close call.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE get length

The session waits on a call to check the size of an external large object (LOB).

Wait Time: The total elapsed time for the call to check the LOB size.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE get name object

The session waits on a call to find or generate the external name of a external large object.

Wait Time: The total elapse time for make external file name to complete.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE get path object

The session is waiting on a call to find or generate the external path name of an external large object (LOB).

Wait Time: The total elapsed time for make external path to complete.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE internal seek

The session waits for a positioning call within the external large object (LOB) to complete.

Wait Time: The total elapse time for the seek to complete.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE open

The session waits to check if an external large object (LOB) has already been opened.

Wait Time: The total elapsed time for the isopen call.

Parameters:

session#	See "session#".
waited	See "waited".

BFILE read

The session waits for a read from a external large object (LOB) to complete.

Wait Time: The total elapse time for the read to complete.

Parameters:

session#	See "session#".
waited	See "waited".

DFS db file lock

This event occurs only for the DBWR in the Parallel Server. Each DBWR of every instance holds a global lock on each file in shared mode. The instance that is trying to offline the file will escalate the global lock from shared to exclusive. This signals the other instances to synchronize their SGAs with the controlfile before the file can be taken offline. The name of this lock is DF (see Appendix B, "Enqueue and Lock Names" for more information).

Wait Time: 1 second in loop. The DBWR is waiting in a loop (sleep, check) for the other instances to downgrade to NULL mode. During this time, the DBWR cannot perform other tasks such as writing buffers.

file	See "file#".

Parameter:

DFS lock handle

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.

Parameters:

name	See "name and type".
mode	See "mode".
id1	See "id1".
id2	See "id2".

The session needs to get the lock handle.

PL/SQL lock timer

This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure. This event will most likely originate from procedures written by a user.

Wait Time: The wait time is in hundredths of seconds and is dependent on the user context.

Parameter:

duration

The duration that the user specified in the DBMS_LOCK.SLEEP or USER_LOCK.SLEEP procedures.

SQL*Net break/reset to client

The server sends a break or reset message to the client. The session running on the server waits for a reply from the client.

Wait Time: The actual time it takes for the break or reset message to return from the client.

Parameters:

driver id	See "driver id".
break?	See "break?".

SQL*Net break/reset to dblink

Same as SQL*Net break/reset to client, but in this case, the break/reset message is sent to another server process over a database link.

Wait Time: The actual time it takes for the break or reset message to return from the other server process.

driver id	See "driver id".
break?	See "break?".

Parameters:

SQL*Net message from client

The server process (foreground process) waits for a message from the client process to arrive.

Wait Time: The time it took for a message to arrive from the client since the last message was sent to the client.

driver id	See "driver id".
#bytes	The number of bytes received by the server (foreground process) from the client.

Parameters:

SQL*Net message from dblink

The session waits while the server process (foreground process) receives messages over a database link from another server process.

Wait Time: The time it took for a message to arrive from another server (foreground process) since a message was sent to the other foreground process.

Parameters:

driver id	See "driver id".
#bytes	The number of bytes received by the server (foreground process) from another foreground process over a database link.

SQL*Net message to client

The server (foreground process) is sending a message to the client.

Wait Time: The actual time the send takes.

Parameters:

driver id	See "driver id".
#bytes	The number of bytes sent by the server process to the client.

SQL*Net message to dblink

The server process (foreground process) is sending a message over a database link to another server process.

Wait Time: The actual time the send takes.

Parameters:

driver id	See "driver id".
#bytes	The number of bytes sent by the server process to another server process over a database link.

SQL*Net more data from client

The server is performing another send to the client. The previous operation was also a send to the client.

Wait Time: The time waited depends on the time it took to receive the data (including the waiting time).

Parameters:

driver id	See "driver id".
#bytes	The number of bytes received from the client.

SQL*Net more data from dblink

The foreground process is expecting more data from a data base link.

Wait Time: The total time it takes to read the data from the database link (including the waiting time for the data to arrive).

driver id	See "driver id".
#bytes	The number of bytes received.

Parameters:

SQL*Net more data to client

The server process is sending more data/messages to the client. The previous operation to the client was also a send.

Wait Time: The actual time it took for the send to complete.

Parameters:

driver id	See "driver id".
#bytes	The number of bytes that are being sent to the client.

SQL*Net more data to dblink

The event indicates that the server is sending data over a database link again. The previous operation over this database link was also a send.

Wait Time: The actual time it takes to send the data to the other server.

Parameters:

driver id	See "driver id".
#bytes	The number of bytes that are sent over the database link to the other server process.

WMON goes to sleep

WMON is the UNIX-specific Wait Monitor, that can be used to reduce the number of system calls related to setting timers for posting or waiting in Oracle. You need to set an initialization parameter that enables the WMON process.

Wait Time: Depends on the next timeout.

Parameters: none

alter system set mts_dispatchers

A session has issued an alter system set mts_dispatchers=<string> and it waiting for the dispatchers to get started.

Wait Time: The session will wait 1/100 of a second and check to see if the new dispatchers have started else the session will wait again.

Parameters:

waited

The number of times that the session has waited 1/100 of second.

batched allocate scn lock request

A session is waiting on another process to allocate an System Change Number (SCN). If the foreground timed out waiting on a process to get the SCN, the foreground will get the SCN.

Wait Time: The wait time is 1 second on the assumption that an SCN allocation should normally need much less than that.

Parameters: none

buffer busy waits

Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).

Wait Time: Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds.

Parameters:

file#	See "file#".
block#	See "block#".
id	The buffer busy wait event is called from different places in the session.

buffer deadlock

Oracle does not really wait on this event; the foreground only yields the CPU. Thus, the chances of catching this event are very low. This is not an application induced deadlock, but an assumed deadlock by the cache layer. The cache layer cannot get a buffer in a certain mode within a certain amount of time.

Wait Time: 0 seconds. The foreground process o