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 before 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:
v$session_event
v$session_wait
v$system_event
1.Buffer Busy waits/Cache Buffers Chains Latch waits:
This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".
The two main cases where this can occur are:
1.Another session is reading the block into the buffer
2.Another session holds the buffer in an incompatible mode to our request.
2."Read By Other Session" wait event.
When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.
3.Log File Sync waits:
Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.
4. Db file sequential read
Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory. (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).
5.Db file scattered read
The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.
6.Direct path read
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.
7.Db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.
8.Log file sync :
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction.
(A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)
9.Enqueue:
The process is waiting on an enqueue (a lock you can see in v$lock).
This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.
10.Log file parallel write :
The process is waiting for blocks to be written to all online redo log members in one group.
LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.
When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before 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:
v$session_event
v$session_wait
v$system_event
1.Buffer Busy waits/Cache Buffers Chains Latch waits:
This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".
The two main cases where this can occur are:
1.Another session is reading the block into the buffer
2.Another session holds the buffer in an incompatible mode to our request.
2."Read By Other Session" wait event.
When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.
3.Log File Sync waits:
Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.
4. Db file sequential read
Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory. (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).
5.Db file scattered read
The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.
6.Direct path read
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.
7.Db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.
8.Log file sync :
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction.
(A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)
9.Enqueue:
The process is waiting on an enqueue (a lock you can see in v$lock).
This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.
10.Log file parallel write :
The process is waiting for blocks to be written to all online redo log members in one group.
LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.
No comments:
Post a Comment