| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Wait events in Oracle | ||
|
When Oracle executes an SQL
statement, it is not constantly executing. Sometimes it has to wait
for a specific event to happen befor it can proceed.
For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding
database block is not currently in the
SGA, Oracle waits for this block to be available for
modification.
All possible wait events can be found in
v$event_name. In Oracle 10g R1, there
are some 806 different wait events.
What Oracle waits for and how long it has totally waited for these events can be monitored through the
following views:
Important events
Important events are:
buffer busy waits
If two processes try (almost) simultaneausly the same
block and the block is not resident in the
buffer cache, one process will allocate a buffer
in the buffer cache and lock it and the read the block into the buffer. The other process is locked until the
block is read. This wait is refered to as buffer busy wait.
See also this link.
db file scattered read
A process reads multiple blocks (mostly as part of
a full table scan or an index fast full scan).
It can also indicate a multiblock read when the process reads parts of a sort segement.
db file single block readdb file sequential read
In most cases, this event means that a
foreground process
reads a single block (because it
reads a block from an index or because it reads a block by rowid).
direct path readenqueue
The enqueue wait event can be queried through v$enqueue_stat.
See also enqueue types in x$ksqst
free buffer waits
See also optimal size of block buffer.
latch freelog buffer space
This wait event indicates that the size of the
log buffer is chosen too small.
log file syncSQL*Net more data from clientSQL*Net more data to dblinkwrite complete waitsWait classes
Wait events can be categorized by wait classes. These classes are exposed through v$session_wait_class.
The following wait classes exist:
AdministrativeApplicationClusterConcurrencyConfigurationCommitIdle WaitsNetworkOtherSystem I/OSchedulerUser I/OParameters
The parameters P1, P2 and P3 in v$session_wait
are dependent on the wait.
P1 refers sometimes to the datafile number.
If this number is greater than db_files, it refers to a temp file.
The name of the datafile for a number can be retrieved through v$datafiles.
|