|
No
|
Events
|
Description
|
Remarks
|
|
Contacting scn server or scn lock master
|
Wait for the message that
contacts the scn server or the
scn lockmaster.
|
Times out every kcm_lcktmout
seconds in a loop until posted.in
8.0.4 kcm_lcktmout = 1 second.
This is Parallel server specific.
|
|
Dbwr io to slave
|
When using multiple db writers
the master db writer waits on
this for slaves to complete an
IO. After the timeout it may
loop and rewait.
|
About 3 seconds.
|
|
null event
|
The session is waiting without
specifying an existing event.
|
May vary. May be treated as bug.
|
|
PL/SQL lock timer
|
This event is called through the
dbmslock.sleep procedure or
userlock.sleep procedure. this
event is most likely to come
from user written stored
procedures.
|
|
|
Wait for credit free buffer
|
Wait for credit to free a null
message
|
|
|
Wait for credit - need buffer to send
|
Waiting for the credit so we
can fill a buffer to send data.
|
The init.ora parameter
"parallel_server_sleep_time"
determines how long you will
sleep on a pq wait event. You can
specify the parameter
"parallel_server_sleep_time"
twice in your init.ora file.
The first entry will set the sleep time for if there are credits
(short wait) .
The second entry will set the
sleep time for when there are no
credits (long wait). This is hidden
in 8.0.4. The default long wait is
20 times short wait time.
|
|
Wait for credit - send blocked
|
Waiting for the credit
|
|
|
Sqlnet break reset to client
|
The server is sending a break
or reset message to the client.
The session running on the
server is waiting for a reply
from the client
|
The actual time it takes for the
break or reset message to come
back from the client.
|
|
Sql *net message from client
|
The server process (foreground
process) is waiting for a
message from the client process
to arrive.
|
The time it took for a message to
arrive from the client since the
last message was send to the
client.
|
|
Sql *net message to client
|
The server (foreground
process) is sending a message
to the client.
|
This is a normal wait condition for
an oracle shadow process. The
actual time the 'send’ takes is the
wait time
|
|
Sql * net more data from client
|
The previous operation was
also a send to the client and the
server is doing another send to
the client.
|
This is a normal wait condition for
an oracle shadow process. The
wait time waited depends on the
time it took to receive the data
(including the waiting time).
|
|
Buffer busy due to global cache
|
This wait event should only
show up in parallel server
mode.
|
We use this wait event in parallel
server mode if waiting for a buffer
but someone else is getting the
global cache lock.
|
|
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 an
incompatible mode.
|
Parameters:
 p1 - file no. This is the file id.
To get the file name, use the
query
select * from v$datafile
where file# = <fileno>
p2 - block no. This is the block
number of the block that
oracle needs to wait for. The
block number is relative to the
start of the file. To find the
object that this block belongs
to enter:
select segment_name ,
segment_type ,owner ,
tablespace_name
from sys.dba_extents
where file_id = <file#>
and <block#>
between block_id and
block_id + blocks -1
p3 - id. This gives the reason
code.The different Ids are
given below:
id reason
0 a block is being read
1003 block is being read,
probably with undo information
for rollback
1007 trying to get a new block
1010 trying to get a buffer in
share mode, but a modification has
started on the buffer that has not
yet been completed.
1012 a modification is
happening on a scur or xcur
buffer, but has not yet completed
1013 block is being read by
another session, so we have to
wait until the read is completed.
This may also occur after a buffer
cache assumed deadlock. The
kernel can't get a buffer in a
certain amount of time and
assumes a deadlock. There for it
will read the cr version of the
block.
1014 block is being read by
another session, so we have to
wait until the read is completed.
1016 the session wants the block
in scur or xcur mode. if we are in
discrete tx mode, wait for the first
time and the second time escalate
the block as a deadlock.
To reduce buffer busy waits on:
Data blocks: change pctfree
and/or pctused. Check for 'right-
hand-indexes' (indexes that get
inserted into at the same point by
many processes). Up initrans.
Reduce number of rows per block.
Segment header: Use freelists or
increase of number of freelists.
Use freelist groups (even in single
instance this can make a
difference).
Freelist blocks: add more
freelists. In case of parallel server
make sure that each instance has
its own freelist group(s).
Undo header: Add more rollback
segments (when running in
exclusive mode). Set transactions
per rollback segment to 1
(exclusive mode).
Undo block: Consider making
rollback segments larger in
exclusive mode or increase
number of pcm locks in parallel
server mode
|
|
Control file parallel write
|
This happens while oracle is
writing physical blocks to all
controlfiles. eg:
Oracle starts a controlfile
transaction (to make sure
that the controlfiles are up
to date if oracle would
crash before committing the
controlfile transaction).
When oracle7 commits a
transaction to a controlfile.
When changing a generic
entry in the controlfile,
write the new value to all
controlfiles.
|
|
|
Control file sequential read
|
Reading from the controlfile
and waiting for the I/O to
complete. This happens in
many cases. E.g.:
When making a backup of the
controlfiles.
The shared info (between
instances) from the controlfile.
Reading other blocks from the
controlfiles.
Reading the header block.
|
Parameters:
P1 - file no. This identifies the
controlfile that oracle7 is reading
from and with the following sql
statement one can determine the
name of the controlfile:
select * from x$kcccf
where indx = <file#>
P2 - block number in the
controlfile from where we are
starting to read. The block size is
the as the physical block size of
the port (normally 512 bytes, some
Unix ports have 1 Kilo bytes or 2
kilobytes).
P3 - no. of blocks to read.
|
|
Control file single write
|
This write is done to write the
shared info of the controlfile to
disk. This is atomic operation
protected by an instance (cf) if
oracle is started shared, so that only one instance can do this
for a parallel server database.
|
If the wait time is too long (more
than average i/o speed), check if
the controlfiles are not a disk that
is too busy. This could really
impact parallel server performance
as some of the synchronization
between instances is done through
the controlfiles.
|
|
Db file parallel read
|
We start the reads
asynchronously then wait for
all of them to complete.
|
parameters:
P1 filesnumber of files
being requested
P2 total number of blocks
being requested
P3 requestsnumber of actual
AIO requests
|
|
Db file parallel write
|
This will show up in the
DBWR.
It tells that the DBWR is doing
a parallel write to 'files' and
'blocks' are written. 'Requests’
show the real number of I/Os
that are being done. When the
last i/o has gone to disk it will
end the wait.
Wait for completion of multi-
file, single block i/o.
|
Parameters:
P1 - no. Of files
P2 - no. Of blocks to write.
P3 - total no. Of I/O requests
(same as p2 ).
If the waiting is too long, it could
indicate that there is some kind of
i/o bottleneck on the system.
Compare the wait time with other
i/o times on the system. Also to
speed the wait time it may be a
good idea to put the database files
on different devices, so that the
write will happen in parallel to
these devices. The number of
devices should divide the total
wait time. Also the internal write
batch could be too big, try
reducing the parameter
db_block_write_batch.
|
|
Db file scattered read
|
Same type of event as "db file
sequential read", except that
oracle will read multiple data
blocks. Multi-block reads are
typically used on full table
scans. The name "scattered
read" may seem misleading but
it refers to the fact that multiple
blocks are read into db block
buffers that are 'scattered'
throughout memory.
|
Parameters:
P1 - file no. This is the file id. To
get the file name, use the query
select * from v$datafile where
file# = <fileno>
P2 - block no. This is the block
number of the block that oracle
needs to wait for. The block
number is relative to the start of
the file. To find the object that this
block belongs to enter:
select segment_name ,
segment_type ,owner ,
tablespace_name
from sys.dba_extents
where file_id = <file#>
and <block#>
between block_id and block_id + blocks -1
P3 - no of blocks Oracle is trying
to read.
This event is normally an indicator
that a full table scan is happening
on the object identified before.
also the number of blocks that can
be read is dependent on the
init.ora parameter
db_file_multiblock_read_count
|
|
Db file sequential read
|
This event shows a wait for a
foreground process while doing
a sequential read from the
database. also used for
rebuilding the controlfile,
dumping datafile headers and
generally getting the database
file headers.
|
Parameters:
P1 - file no. This is the file id. To
get the file name, use the query
select * from v$datafile where
file# = <fileno>
P2 - block no. This is the block
number of the block that oracle
needs to wait for. The block
number is relative to the start of
the file. To find the object that this
block belongs to enter:
select segment_name ,
segment_type ,owner ,
tablespace_name
from sys.dba_extents
where file_id = <file#>
and <block#>
between block_id and block_id + blocks -1
P3 - no of blocks Oracle is trying
to read.
Table scans (direct reads) can certainly give an incorrect result.
|
|
Db file single write
|
This event is used to wait for
the writing of the file headers.
|
Parameters:
P1 - file no. This is the file id. To
get the file name, use the query
select * from v$datafile where
file# = <fileno>
P2 - block no. This is the block
number of the block that oracle
needs to wait for. The block
number is relative to the start of
the file. To find the object that this
block belongs to enter:
select segment_name ,
segment_type ,owner ,
tablespace_name
from sys.dba_extents
where file_id = <file#>
and <block#>
between block_id and block_id
+ blocks -1
P3 – no of blocks Oracle is trying
to read.
|
|
Direct path read
|
Time taken from wanting to
access the block to a read
completing. If async read ahead
is being used the time waited
may be less than the actual I/O
time. There is no timeout.
|
Parameters as above.
Direct I/O allows read ahead so
the io for a block may be queued
before we actually try to use the
block. Once wecome to look at the
block we need to wait for the read
to complete if it has not yet done
so.
|
|
Direct path write
|
Time we wait for a direct write
to complete. There is no
timeout.
|
Parameters as above.
Direct io allows us to
asynchronously queue writes. If
we need to know an outstanding
write is complete then we wait on
this wait event. This can happen
because we are out of free slots
and just need an empty buffer (we
wait on the oldest I/O) or because
we need to ensure all writes are
flushed.
|
|
Enqueue
|
Oracle is waiting for a local
(vs. global) enqueue (vs. latch)
the wait is dependent on the
name of the enqueue.
|
Parameters:
P1 - name | mode. Name the
name of enqueue is always two
characters. these enqueues are
used internally by oracle to
synchronize access to internal
resources.
The following Sql statement
shows the name of the enqueue
that the sessions are trying to get:
select chr(bitand(p1,-
16777216)/16777215)||
chr(bitand(p1, 16711680)/65535)
"lock",
bitand(p1, 65535) "mode"
from v$session_wait
where event = 'enqueue';
The mode is the requested mode
by the session. The sql for the
"name" (above) includes the
"mode".
P2: the first identifier of the
enqueue.
P3 - the second identifier of the
enqueue.
|
|
File identity
|
Used when we ask the OS to
perform some file 'identify'
operation.
|
|
|
File open
|
Used when we ask the OS to
open a file
|
|
|
Free buffer waits
|
This will happen if:
All buffer gets have been
suspended. This could
happen when a file was
read-only and is now read-
write. All the existing
buffers need to be
invalidated since they are
not linked to lock elements
(needed when mounted
parallel (shared)). So cache
buffers are not assigned to
DBA's until the invalidation
is finished.
This could also happen
when you need a buffer
from the SGA for a block
in cr, reading or any of the
recovery modes. Basically
you will post the DBWR to
make some free buffers.
This also happens after
inspecting 'free buffers
inspected ‘buffers’. If no
free buffer was found,
oracle waits for one second
and will try to get the
buffer again (depends on
the context).
With MLS, getting an undo
sequence
|
Parameters:
P1 - file no. This is the file id. To
get the file name, use the query
select * from v$datafile where
file# = <fileno>
P2 - block no. This is the block
number of the block that oracle
needs to wait for. The block
number is relative to the start of
the file. To find the object that this
block belongs to enter:
select segment_name ,
segment_type ,owner ,
tablespace_name
from sys.dba_extents
where file_id = <file#>
and <block#>
between block_id and block_id
+ blocks -1
The DBWR is not writing enough
buffers to disk. Make sure that the
i/o load is evenly distributed
across all disks, use o/s monitor
tools or look at v$filestat. Also
look for files that have full table
scans. Disk sorts are known to
cause a flood of dirty buffers that
Will need to written out. It is
important to stripe the datafiles
belonging to the temp tablespace
to over many different disks.
|
|
Latch free
|
The process is waiting for a
latch that is currently busy
(held by another process).
|
The wait time increases
exponential and doesn't include
spinning on the latch (active
waiting). The maximum wait time
also depends on the number of
latches that the oracle process is
holding. There is an exponential
back off up to 2 seconds
Parameters:
P1 - latch address
P2 - latch no.
P3 - backoff count
|
|
Log buffer space
|
Waiting for space in the log
buffer because we are writing
stuff 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.
|
|
Log file parallel write
|
Writing redo records to the
redo log files from the log
buffer.
|
Check the average i/o time in
v$system_event for this event.
When the i/o time is too high
check to see if the disk that
contains the log files is too busy.
Consider moving the log files in
RAW disks or use strips. Do not
use RAID 5.
|
|
Log file sequential read
|
Waiting for the read from this
logfile to return. This used to
read redo records from the log
file.
|
|
|
log file single write
|
Waiting for the write to this
logfile to complete. This is
used to write the header of the
logfile. This done during
adding a log file member and
when the writing sequence
numbers are being advanced.
|
|
|
Log file switch (archiving needed)
|
Waiting for a log switch
because the log we 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
speedup archiving consider adding
more archive processes or putting
the archive files on striped disks.
Also ensure parameter
log_archive_start is true
|
|
Log file switch (checkpoint incomplete)
|
Waiting for a log switch
because we cannot wrap into
the next log because the
checkpoint for that log has not
completed.
|
Increase Redo Log file size.
|
|
Log file switch completion
|
Waiting for log switch because
current log is full and LGWR
needs to complete writing to
current log and open the new
log or some other request to
switch log files.
|
|
|
Log file sync
|
When a user session commits
(or rolls back), the sessions
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 that it will
post the user session.
|
Speed up LGWR
|
|
Process startup
|
Wait for a multi-threaded
server (shared server),
dispatcher or other background
process to start.
|
If a lot of waits /timeouts show on
this event, it could mean that the
minimum number of processes
(either parallel query slaves,
dispatchers and shared servers), is
set to low. Increase the
corresponding init.ora parameters
to set a higher minimum.
|
|
RDBMS IPC message
|
This is basically the main
waiting event for the event
based background process like
dbwr, LGWR and the LCK
processes. These event based
background processes basically
wait for a message from the
foreground to arrive and act
based on that message.
|
This is a normal wait condition for
background processes.
|
|
RDBMS IPC reply
|
This event is used to wait for a
reply from one of the
background processes.
|
All cross instance calls (even in
single instance mode) cause this
wait event.
|
|
Virtual circuit status
|
Wait for virtual circuit manager
|
|