Oracle8 Reference
Release 8.0

A58242-01

Library

Product

Contents

Index

Prev Next

A
Oracle Wait Events

Introduction

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" on page 3-85, "V$SESSION_WAIT" on page 3-88, and "V$SYSTEM_EVENT" on page 3-107.

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;

Oracle Wait Events

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  

DFS db file lock

 

file#

 

not used

 

not used

 

DFS lock handle

 

type|mode

 

id1

 

id2

 

KOLF: Register LFI close

 

lfictx

 

fileop

 

0

 

KOLF: Register LFI exists

 

lfictx

 

nameop

 

0

 

KOLF: Register LFI isopen

 

lifctx

 

nameop

 

0

 

KOLF: Register LFI length

 

lfictx

 

nameop

 

0

 

KOLF: Register LFI lfimkm

 

lfictx

 

path

 

0

 

KOLF: Register LFI lfimkpth

 

lfictx

 

pathobj

 

0

 

KOLF: Register LFI open

 

lfictx

 

fileop

 

nameop

 

KOLF: Register LFI read

 

lfictx

 

fileop

 

0

 

KOLF: Register LFI seek

 

lfictx

 

fileop

 

0

 

PL/SQL lock timer

 

duration

 

not used

 

not used

 

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

 

WMON goes to sleep

 

not used

 

not used

 

not used

 

alter system set mts_dispatcher

 

waited

 

not used

 

not used

 

batched allocate scn lock request

 

not used

 

not used

 

not used

 

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 read

 

file number

 

first dba

 

block cnt

 

direct path write

 

file number

 

first dba

 

block cnt

 

dispatcher shutdown

 

waited

 

not used

 

not used

 

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 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 ss

 

file#

 

block#

 

class

 

global cache lock open x

 

file#

 

block#

 

lenum

 

global cache locks s to x

 

file#

 

block#

 

lenum

 

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

 

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

 

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

 

on-going SCN fetch to complete

 

not used

 

not used

 

not used

 

parallel query create server

 

nservers

 

sleeptime

 

enqueue

 

parallel query dequeue wait

 

reason

 

sleeptime/
senderid

 

passes

 

parallel query qref latch

 

function

 

sleeptime

 

qref

 

parallel query server shutdown

 

nalive

 

sleeptime

 

loop

 

parallel query signal server

 

serial

 

error

 

nbusy

 

pending ast

 

lenum

 

not used

 

not used

 

pending global transaction(s)

 

scans

 

not used

 

not used

 

pipe get

 

handle address

 

buffer length

 

timeout

 

pipe put

 

handle address

 

record length

 

timeout

 

pmon timer

 

duration

 

not used

 

not used

 

process startup

 

type

 

process#

 

waited

 

queue messages

 

queue id

 

process#

 

wait time

 

queue wait

 

not used

 

not used

 

not used

 

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

 

row cache lock

 

cache id

 

mode

 

request

 

scginq AST call

 

not used

 

not used

 

not used

 

single-task message

 

not used

 

not used

 

not used

 

slave exit

 

nalive

 

sleeptime

 

loop

 

smon timer

 

sleep time

 

failed

 

not used

 

switch logfile command

 

not used

 

not used

 

not used

 

timer in sksawat

 

not used

 

not used

 

not used

 

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 influx DLM latch

 

latchtype

 

latchaddr

 

not used

 

wakeup time manager

 

not used

 

not used

 

not used

 

write complete waits

 

file#

 

block#

 

id

 

writes stopped by instance recovery

 

by thread #

 

our thread#

 

not used

 

Parameter Descriptions

This section describes a number of common event parameters.

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:

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 Events

This section describes the Oracle events.

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#" on page A-8.

 
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" on page A-9.

 

mode

 

See "mode" on page A-8.

 

id1

 

See "id1" on page A-8.

 

id2

 

See "id2" on page A-8.

 

The session needs to get the lock handle.

KOLF: Register LFI close

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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI 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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI isopen

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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI 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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI lfimkm

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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI lfimkpth

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.

session#

 

See "session#" on page A-9.

 

waited

 

See "waited" on page A-9.

 
Parameters:

KOLF: Register LFI open

The session waits on a call to open the external large object (LOB).

Wait Time: The total elapsed time for the open call to complete.

Parameters:

session#

 

See "session#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI 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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

KOLF: Register LFI 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#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

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" on page A-8.

 

break?

 

See "break?" on page A-7.

 

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" on page A-8.

 

break?

 

See "break?" on page A-7.

 
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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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" on page A-8.

 

#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#" on page A-8.

 

block#

 

See "block#" on page A-7.

 

id

 

The buffer busy wait event is called from different places in the session. Each place in the kernel points to different reason, as described in

 

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 only yields the CPU and will usually be placed at the end of the CPU run queue.

Parameters:

class

 

See "class" on page A-7.

 

mode

 

See "mode" on page A-8.

 

flag

 

The flag points to the internal flags used by the session to get this block.

 

dba

 

See "dba" on page A-7.

 

buffer for checkpoint

The buffer could not be checkpointed, because some process is modifying it. This means that after the wait, the DBWR will scan the whole buffer cache again. This could happen during a database close or after a user does a local checkpoint. During this situation the database cannot be closed.

Wait Time: 1 second

Parameters:

dba

 
See "dba" on page A-7.
 

state

 

State refers to the status of the buffer contents.

 

mode

 

See "mode" on page A-8.

 

buffer#

 

This is the index of the block in the buffer cache (V$BH).

 

buffer latch

The session waits on the buffer hash chain latch. Primarily used in the dump routines.

Wait Time: 1 second

Parameters:

latch addr

 

The virtual address in the SGA where this latch is located. Use the following command to find the name of this latch:

select *     
from v$latch a, v$latchname b     
where addr = latch addr    
and a.latch# = b.latch#;
 

chain#

 

The index into array of buffer hash chains. When the chain is 0xfffffff, the foreground waits on the LRU latch.

 

buffer read retry

This event occurs only if the instance is mounted in shared mode (Parallel Server). During the read of the buffer, the contents changed. This means that either:

The block will be re-read (this may fail up to 3 times), then corruption is assumed and the corrupt block is dumped in the trace file.

Wait Time: The wait time is the elapsed time of the read.

Parameters::

file#

 

See "file#" on page A-8.

 

block#

 

See "block#" on page A-7.

 

checkpoint completed

A session waits for a checkpoint to complete. This could happen, for example, during a close database or a local checkpoint.

Wait Time: 5 seconds

Parameters: none

checkpoint range buffer not saved

During a range checkpoint operation a buffer was found that was not saved or written. Either:

Wait Time: 10 milliseconds

Parameters: none

control file parallel write

This event occurs while the session is writing physical blocks to all controlfiles. This happens when:

Wait Time: The wait time is the time it takes to finish all writes to all controlfiles.

Parameters:

files

 

The number of controlfiles to which the session is writing.

 

blocks

 

The number of blocks that the session is writing to the controlfile.

 

requests

 

The number of I/O requests which the session wants to write.

 

control file sequential read

Reading from the controlfile. This happens in many cases. For example, while:

Wait Time: The wait time is the elapsed time of the read.

Parameters:

file#

 

The controlfile from which the session is reading.

 

block#

 

Block number in the controlfile from where the session starts to read. The block size is the physical block size of the port (usually 512 bytes, some UNIX ports have 1 or 2 Kilobytes).

 

blocks

 

The number of blocks that the session is trying to read.

 

control file single write

This wait is signaled while the controlfile's shared information is written to disk. This is an atomic operation protected by an enqueue (CF), so that only one session at a time can write to the entire database.

Wait Time: The wait time is the elapsed time of the write.

Parameters:

file#

 

This identifies the controlfile to which the session is currently writing.

 

block#

 

Block number in the controlfile where the write begins. The block size is the as the physical block size of the port (usually 512 bytes, some UNIX ports have 1 or 2 Kilobytes).

 

blocks

 

The number of blocks that the session is trying to read.

 

conversion file read

This event occurs during a the creation of a Version 7 controlfile as part of converting a database to Version 7 from Version 6.

Wait Time: The wait time is the elapsed time of the read.

Parameters:

file#

 

The controlfile to which the session is currently writing.

 

block#

 

Block number in the controlfile where the write begins. The block size is the as the physical block size of the port (usually 512 bytes, some UNIX ports have 1 or 2 Kilobytes).

 

blocks

 

The number of blocks that the session is trying to read.

 

db file parallel read

This happens during recovery. Database blocks that need to be changed as part of recovery are read in parallel from the database.

Wait Time: Wait until all of the I/Os are completed.

Parameters:

files

 

This indicates the number of files to which the session is reading.

 

blocks

 

This indicates the total number of blocks to be read.

 

requests

 

This indicates the total number of I/O requests, which will be the same as blocks.

 

db file parallel write

This event occurs in the DBWR. It indicates that the DBWR is performing a parallel write to files and blocks. The parameter requests indicates the real number of I/Os that are being performed. When the last I/O has gone to disk, the wait ends.

Wait Time: Wait until all of the I/Os are completed.

Parameters:

files

 

This indicates the number of files to which the session is writing.

 

blocks

 

This indicates the total number of blocks to be written.

 

requests

 

This indicates the total number of I/O requests, which will be the same as blocks.

 

db file scattered read

Similar to db file sequential read, except that the session is reading multiple data blocks.

Wait Time: The wait time is the actual time it takes to do all of the I/Os.

Parameters:

file#

 

See "file#" on page A-8.

 

block#

 

See "block#" on page A-7.

 

blocks

 

The number of blocks that the session is trying to read from the file# starting at block#.

 

db file sequential read

The session waits while a sequential read from the database is performed. This event is also used for rebuilding the controlfile, dumping datafile headers, and getting the database file headers.

Wait Time: The wait time is the actual time it takes to do the I/O.

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

blocks

 

This is the number of blocks that the session is trying to read (should be 1).

 

db file single write

This event is used to wait for the writing of the file headers.

Wait Time: The wait time is the actual time it takes to do the I/O.

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

blocks

 

This is the number of blocks that the session is trying to write in file# starting at block#.

 

direct path read

During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Wait Time: 10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds. The session waits in a tight loop until all outstanding I/Os have completed.

Parameters:

descriptor address

 

This is a pointer to the I/O context of outstanding direct I/Os on which the session is currently waiting.

 

first dba

 

The dba of the oldest I/O in the context referenced by the descriptor address.

 

block cnt

 

Number of valid buffers in the context referenced by the descriptor address.

 

direct path write

During Direct Path operations the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct write no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Wait Time: 10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds. The session waits in a tight loop until all outstanding I/Os have completed.

Parameters:

descriptor address

 

This is a pointer to the I/O context of outstanding direct I/Os on which the session is currently waiting.

 

first dba

 

The dba of the oldest I/O in the context referenced by the descriptor address.

 

block cnt

 

Number of valid buffers in the context referenced by the descriptor address.

 

dispatcher shutdown

During shutdown immediate or normal, the shutdown process must wait for all the dispatchers to shutdown. As each dispatcher is signaled, the session that causes the shutdown is waits on this event until the requested dispatcher is no longer alive.

Wait Time: 1 second

Parameter:

waited

 

Indicates the cumulative wait time. After 5 minutes, the session writes to the alert and trace files to indicate that there might be a problem.

 

dispatcher timer

This basically means that the dispatcher is idle and waiting for some work to arrive.

Wait Time: 60 seconds

Parameter:

sleep time

 

The intended sleep time. The dispatcher will return to work sooner if it is posted by either data arriving on the network or by a post from a shared server process to send data back to the client.

 

duplicate cluster key

It is possible for a race condition to occur when creating a new cluster key. If it is found that another process has put the cluster key into the data/index block, then the session waits and retries. The retry should then find a valid cluster key.

Wait Time: 0.01 seconds

Parameter:

dba

 

The dba of the block into which the session is trying to insert a cluster key.

 

enqueue

The session is waiting for a local enqueue. The wait is dependent on the name of the enqueue (see Appendix B, "Enqueue and Lock Names").

Wait Time: Depends on the enqueue name.

Parameters:

name

 

See "name and type" on page A-9.

 

mode

 

See "mode" on page A-8.

 

file identify

The time it takes to identify a file so that it can be opened later.

file open

The time it takes to open the file.

free buffer waits

This will happen if:

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-8.

 

block#

 

See "block#" on page A-7.

 

free global transaction table entry

The session is waiting for a free slot in the global transaction table (used by the Distributed Database Option). It will wait for 1 second and try again.

Wait Time: 1 second

Parameter:

tries

 

The number of times the session tried to find a free slot in the global transaction table.

 

free process state object

Used during the creation of a process. The session will scan the process table and look for a free process slot. If none can be found, PMON is posted to check if all the processes currently in the process table are still alive. If there are dead processes, PMON will clean them and make the process slot available to new processes. The waiting process will then rescan the process table to find the new slot.

Wait Time: 1 second

Parameters: none

global cache freelist wait

All releasable locks are used and a new one has been requested. To make a lock element available, a lock element is pinged.

Wait Time: The duration of the lock get operation to ping the lock element.

Parameter:

lenum

 

See "lenum" on page A-8.

 

global cache lock busy

The session waits to convert a buffer up from Shared Current to Exclusive Current status.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 

global cache lock cleanup

PMON is waiting for an LCK process to cleanup the lock context after a foreground process died while doing a global cache lock operation.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 

global cache lock null to s

The session waits for a lock convert from NULL to SHARED mode on the block identified by file# and block#.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

class

 

See "class" on page A-7.

 

global cache lock null to x

The session waits for a lock convert from NULL to EXCLUSIVE mode on the block identified by file# and block#.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 

global cache lock open null

The session waits for a lock get in NULL mode on the block identified by file# and block#.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

class

 

See "class: on page A-6.

 

global cache lock open s

The session waits for a lock get in SHARED mode on the block identified by file# and block#.

Wait Time: 1 second

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

class

 

See "class: on page A-7.

 
Parameters:

global cache lock open ss

The session waits for a lock get in SUB SHARED mode on the block identified by file# and block#.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 

global cache lock open x

The session waits for a lock get in EXCLUSIVE mode on the block identified by file# and block#.

Wait Time: 1 second

Parameters:

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 

global cache lock s to x

The session waits for a lock convert from SHARED to EXCLUSIVE mode on the block identified by file# and block#.

Wait Time: 1 second

file#

 

See "file#" on page A-7.

 

block#

 

See "block#" on page A-6.

 

lenum

 

See "lenum" on page A-8.

 
Parameters:

inactive session

This event is used for two purposes:

Wait Time: 1 second

Parameters:

session#

 

See "session#" on page A-9.

 

waited

 

See "waited" on page A-9.

 

inactive transaction branch

The session waits for a transaction branch that is currently used by another session.

Wait Time: 1 second

Parameters:

branch#

 

The serial number of the transaction for which the session is waiting.

 

waited

 

See "waited" on page A-9.

 

index block split

While trying to find an index key in an index block, Oracle noticed that the index block was being split. Oracle will wait for the split to finish and try to find the key again.

Wait Time: The session will yield the CPU, so there is no actual waiting time.

Parameters:

rootdba

 

The root of the index.

 

level

 

This is the level of the block that the session is trying to split in the index. The leaf blocks are level 0. If the level is > 0, it is a branch block. (The root block can be considered a special branch block).

 

childdba

 

The block that the session is trying to split.

 

instance recovery

The session waits for SMON to finish the instance, transaction recovery, or sort segment cleanup.

Wait Time: The wait time can vary and depends on the amount of recovery needed.

Parameter:

undo segment#

 

If the value is 0, SMON is probably performing instance recovery. If P1 > 0, use this query to find the undo segment:

select *       
from v$rollstat      
where usn = undo segment#;
 

instance state change

The session waits for SMON to enable or disable cache or transaction recovery. This usually happens during ALTER DATABASE OPEN or CLOSE.

Wait Time: Wait time depends on the amount of time the action takes (that is, the amount of recovery needed).

layer

 

This value can be 1 or 2. If 1, it means that the transaction layer wants transaction recovery to be performed. If 2, it means that cache recovery will be performed.

 

value

 

This value can be 0 (disable) or 1 (enable).

 

waited

 

The number of seconds waited so far.

 
Parameters:

io done

The session waits for an I/O to complete or it waits for a slave process to become available to submit the I/O request. This event occurs on platforms that do not support asynchronous I/O.

Wait Time: 50 milliseconds

Parameter:

msg ptr

 

A pointer to the I/O request.

 

kcl bg acks

The session waits for the background LCK process(es) to finish what they are doing. For example:

Wait Time: 10 seconds

Parameters:

count

 

The number of LCK processes that have finished.

 

loops

 

The number times the process had to wait for the LCK processes to finish what they were doing.

 

latch activity

This event is used as part of the process of determining whether a latch needs to be cleaned.

Wait Time: 0.05 to 0.1 seconds

Parameters:

address

 

The address of the latch that is being checked.

 

number

 

The latch number of the latch that has activity. To find more information on the latch, use this SQL command:

select *
from v$latchname
where latch# = number;
 

process#

 

If this is 0, it is the first phase of the in-flux tests.

 

latch free

The process waits for a latch that is currently busy (held by another process).

Wait Time: The wait time increases exponentially and does not include spinning on the latch (active waiting). The maximum wait time also depends on the number of latches that the process is holding. There is an incremental wait of up to 2 seconds.

Parameters:

address

 

The address of the latch for which the process is waiting.

 

number

 

The latch number that indexes in the V$LATCHNAME view.To find more information on the latch, use this SQL command:

select *
from v$latchname
where latch# =
number;
 

tries

 

A count of the number of times the process tried to get the latch (slow with spinning) and the process has to sleep.

 

library cache load lock

The session tries to find the load lock for the database object so that it can load the object. The load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the load lock is busy the session will wait on this event until the lock becomes available.

Wait Time: 3 seconds (1 second for PMON)

Parameters:

object address

 

Address of the object being loaded.

 

lock address

 

Address of load lock being used.

 

mask

 

Indicates which data pieces of the object that needs to be loaded.

 

library cache lock

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

This lock is also obtained to locate an object in the library cache.

Wait Time: 3 seconds (1 second for PMON)

Parameters:

handle address

 

Address of the object being loaded.

 

lock address

 

Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.

 

mode

 

Indicates the data pieces of the object which need to be loaded.

 

namespace

 

See "namespace" on page A-9.

 

library cache pin

This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.

Wait Time: 3 seconds (1 second for PMON)

Parameters:

handle address

 

Address of the object being loaded.

 

pin address

 

Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is basically a State Object.

 

mode

 

Indicates which data pieces of the object that needs to be loaded.

 

namespace

 

See "namespace" on page A-9.

 

lock manager wait for remote message

The lock manager waits for a message from a remote lock manager in the same configuration.

Wait Time: The elapsed time of the wait

Parameter:

waittime

 

The elapsed time of the actual wait.

 

log buffer space

Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out. Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks.

Wait Time: Usually 1 second, but 5 seconds if it is waiting for a Switch Logfile to complete.

Parameters: none

log file parallel write

Writing redo records to the redo log files from the log buffer.

Wait Time: Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk.

Parameters:

files

 

Number of files to be written.

 

blocks

 

Number of blocks to be written.

 

requests

 

Number of I/O requests.

 

log file sequential read

Waiting for the read from this logfile to return. This is used to read redo records from the log file.

Wait Time: Time it takes to complete the physical I/O (read).

log#

 

The relative sequence number of the logfiles within a log group (used only when dumping the logfiles).

 

block#

 

See "block#" on page A-7.

 

blocks

 

The number of blocks to read.

 
Parameters:

log file single write

Waiting for the write to this logfile to complete. This event is used while updating the header of the logfile. It is signaled when adding a log file member and when incrementing sequence numbers.

Wait Time: Time it takes for the physical I/O (write) to complete.

Parameters:

log#

 

This is the number of the group/log to which the session is currently writing.

 

block#

 

See "block#" on page A-7.

 

blocks

 

The number of blocks to write.

 

log file switch (archiving needed)

Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. Check the alert file to make sure that archiving has not stopped due to a failed archive write. To speed archiving, consider adding more archive processes or putting the archive files on striped disks.

Wait Time: 1 second

Parameters: none

log file switch (checkpoint incomplete)

Waiting for a log switch because the session cannot wrap into the next log. Wrapping cannot be performed because the checkpoint for that log has not completed.

Wait Time: 1 second

Parameters: none

log file switch (clearing log file)

Waiting for a log switch because the log is being cleared due to a CLEAR LOGFILE command or implicit clear logfile executed by recovery.

Wait Time: 1 second

Parameters: none

log file switch completion

Waiting for a log switch to complete.

Wait Time: 1 second

Parameters: none

log file sync

When a user session commits, the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.

Wait Time: The wait time includes the writing of the log buffer and the post.

Parameter:

buffer#

 

The number of the physical buffer in the redo log buffer that needs to be sync'ed

 

log switch/archive

Used as part of the ALTER SYSTEM ARCHIVE LOG CHANGE scn command. The session waits for the current log from all open threads to be archived.

Wait Time: Wait for up to 10 seconds.

Parameter:

thread#

 

The thread number of the thread that is currently archiving its current log.

 

on-going SCN fetch to complete

Another session is fetching the SCN (System Change Number). This session waits for the other session finish fetching the SCN.

Wait Time: 1 second

Parameters: none

parallel query create server

Used when creating or starting a Parallel Query Slave.

Wait Time: The time it takes to start all of the requested Parallel Query Slaves.

Parameters:

nservers

 

The number of Parallel Query Slaves that are being started.

 

sleeptime

 

Time it takes to get the processes started. The process should be started within sleeptime.

 

enqueue

 

The number of blocks to read.

 

parallel query dequeue wait

The process is waiting for a message during a parallel execute.

Wait Time: The wait time depends on how quickly the message arrives. Wait times can vary, but it will normally be a short period of time.

Parameters:

reason

 

The reason for dequeuing.

 

sleeptime

 

The amount of time that the session slept.

 

loop

 

The total number of times that the session has slept.

 

parallel query qref latch

Each Parallel Query Process has a parallel query qref latch, which needs to be acquired before the queue buffers can be manipulated.

Wait Time: Wait up to 1 second.

function

 

Indicates the type of wait that the session is doing.

 

sleeptime

 

The amount of time that the session waits (in hundredths of a second).

 

qref

 

The address of the process queue for which the session is waits.

 
Parameters:

parallel query server shutdown

During normal or immediate shutdown the Parallel Query Slaves are posted to shutdown cleanly. If any Parallel Query Slaves are still alive after 10 seconds, they are killed.

Wait Time: Wait up to 0.5 seconds.

Parameters:

nalive

 

The number of Parallel Query Slaves that are still running.

 

sleeptime

 

The total sleeptime since the session started to wait on this event.

 

loop

 

The number of times the session waited for this event.

 

parallel query signal server

This event occurs only in Exclusive mode. The Query Coordinator is signalling the Query Slaves that an error has occurred.

Wait Time: 0.5 seconds

Parameters:

serial

 

The serial number of the slave process queue.

 

error

 

The error that has occurred.

 

nbusy

 

The number of slave processes that are still busy.

 

pending ast

The session is waiting for an outstanding AST to be delivered before the lock element can be used.

Wait Time: The session yields the CPU.

Parameter:

lenum

 

See "lenum" on page A-8.

 

pending global transaction(s)

This event should happen only during testing. The session waits for pending transactions to clear.

Wait Time: 30 seconds

Parameter:

scans

 

Number of times the session has scanned the PENDING_TRANS$ table.

 

pipe get

The session waits for a message to be received on the pipe or for the pipe timer to expire.

Wait Time: There is a 5 second wake up (check) and the pipe timer set by the user.

Parameters:

handle address

 

The library cache object handle for this pipe.

 

buffer length

 

The length of the buffer.

 

timeout

 

The pipe timer set by the user.

 

pipe put

The session waits for the pipe send timer to expire or for space to be made available in the pipe.

Wait Time: There is the 5 second wakeup (check) and the user-supplied timeout value.

handle address

 

The library cache object handle for this pipe.

 

record length

 

The length of the record or buffer that has been put into the pipe.

 

timeout

 

The pipe timer set by the user.

 
Parameters:

pmon timer

This is the main wait event for PMON. When PMON is idle, it is waiting on this event.

Wait Time: Up to 3 seconds, if not posted before.

Parameter:

duration

 

The actual amount of time that the PMON is trying to sleep.

 

process startup

Wait for a Multi-Threaded Server (Shared Server), Dispatcher, or other background process to start.

Wait Time: Wait up to 1 second for a background process to start. If timed out, then re-wait until 5 minutes have passed and signal an error. If the process has started, the event will acknowledge this.

Parameters:

type

 

The process type that was started.

 

process#

 

The process number of the process being started.

 

waited

 

Cumulative time waited for the process to start.

 

queue messages

The session is waiting on an empty OLTP queue (Advanced Queue) for a message to arrive so that the session can dequeue that message.

Wait Time: The amount of time that the session wants to wait is determined by the parameter wait time.

Parameters:

queue id

 

The ID of the OLTP queue for which this session is waiting.

 

process#

 

The process number of the process in which this session runs.

 

wait time

 

The intended wait time for this session.

 

queue wait

The direct loader uses a queue of slots for managing buffers. These slots are used for read/write operations. If a new slot is requested, but none are available, then this wait event is signaled. This wait will only occur if the underlying operating system is using asynchronous I/O.

Wait Time: Wait up to 1 second for slots to be freed in the circular direct loader I/O buffer queue.

Parameters: none

rdbms ipc message

The background processes (LGWR, DBWR, LCK0) use this event to indicate that they are idle and are waiting for the foreground processes to send them an IPC message to do some work.

Wait Time: Up to 3 seconds. The parameter timeout shows the true sleep time.

Parameter:

timeout

 

The amount of time that the session waits for an IPC message.

 

rdbms ipc message block

This event indicates that all message blocks are in use and that the session had to wait for a message block to become available.

Wait Time: Wait up to 60 seconds.

Parameters: none

rdbms ipc reply

This event is used to wait for a reply from one of the background processes.

Wait Time: The wait time is specified by the user and is indicated by the parameter timeout.

Parameters:

from_process

 

The background process for which the session is waiting. The wait is for a reply to an IPC message sent by the session.

 

timeout

 

The amount of time in seconds that this process will wait for a reply.

 

redo wait

Defined but not used by the code.

row cache lock

The session is trying to get a data dictionary lock.

Wait Time: Wait up to 60 seconds.

Parameters:

cache id

 

The CACHE# column value in the V$ROWCACHE view.

 

mode

 

See "mode" on page A-8.

 

request

 

The pipe timer set by the user.

 

scginq AST call

Called by the session to find the highest lock mode that is held on a resource.

Wait Time: Wait up to 0.2 seconds, but the wait will continue until the NULL mode Acquisition AST has fired.

Parameters: none

single-task message

When running single task, this event indicates that the session waits for the client side of the executable.

Wait Time: Total elapsed time that this session spent in the user application.

Parameters: none

smon timer

This is the main idle event for SMON. SMON will be waiting on this event most of the time until it times out or is posted by another process.

Wait Time: 5 minutes (300 seconds)

Parameters:

sleeptime

 

The amount of time that SMON tries to wait on this event in seconds.

 

failed

 

The number of times SMON was posted when there some kind of error.

 

switch logfile command

The session waits on the user command SWITCH LOGFILE to complete.

Wait Time: 5 seconds

Parameters: none

timer in sksawat

The session waits for the Archiver (ARCH) asynchronous I/O to complete.

Wait Time: 0.01 seconds

Parameters: none

transaction

Wait for a blocking transaction to be rolled back. Continue waiting until the transaction has been rolled back.

Wait Time: 1 second

Parameters:

undo seg#

 

The rollback segment ID.

 

slot#

 

The slot ID inside the rollback segment.

 

wrap#

 

The sequence number that is incremented for each transaction.

 

count

 

The number of times that the session has waited on this transaction.

 

unbound tx

The session waits to see if there are any transactions that have been started but do not have a Rollback Segment associated with them.

Wait Time: 1 second

Parameters: none

undo segment extension

The undo segment is being extended or shrunk. The session must wait until the operation on the undo segment has finished.

Wait Time: 0.01 seconds

Parameter:

segment#

 

The ID of the rollback segment that is being extended or shrunk.

 

undo segment recovery

PMON is rolling back a dead transaction. The wait continues until rollback finishes.

Wait Time: 3 seconds

Parameters:

segment#

 

The ID of the rollback segment that contains the transaction that is being rolled back.

 

tx flags

 

The transaction flags (options) set for the transaction that is being rolled back.

 

undo segment tx slot

Wait for a transaction slot to become available within the selected rollback segment. Continue waiting until the slot is available.

Wait Time: 1 second

Parameters:

segment#

 

The ID of the rollback segment that contains the transaction that is being rolled back.

 

virtual circuit status

The session waits for a virtual circuit to return a message type indicated by status.

Wait Time: 30 seconds

Parameters:

circuit#

 

Indicates the virtual circuit# being waited on.

 

status

 

Indicates what the session is waiting for.

 

write complete waits

The session waits for a buffer to be written. The write is caused by normal aging or by a cross-instance call.

Wait Time: 1 second

Parameters:

file#

 

The rollback segment id that contains the transaction that is being rolled back.

 

block#

 

The transaction flags (options) set for the transaction that is being rolled back.

 

id

 

Identifies the reason for waiting.

 

writes stopped by instance recovery

The session is blocked until the instance that started Instance Recovery is finished.

Wait Time: 5 seconds

Parameters:

bythread#

 

The rollback segment id that contains the transaction that is being rolled back.

 

ourthread#

 

The current instance thread number.

 




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index