This is part II in series of Oracle Wait Events That Everyone Should Know
control file sequential read
The process is waiting for blocks to be read from a control file. This happens generally
- making a backup of the controlfiles
- sharing information (between instances) from the controlfile
- reading other blocks from the controlfiles
- reading the header block
If this is major waiting event, it means control file location need to changed to faster disk location
control file parallel write
The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.
log buffer space
The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.
This can also happen, if the I/O to disk where redo logs are located is slow
There should be no log buffer space waits as such in the database.Consider making the log buffer bigger if it is small or consider moving log files to faster disks such as striped disks.
Select event, total_waits, total_timeouts, time_waited, average_wait
where event = ‘log buffer space’;
Select sid, event, seconds_in_wait, state
where event = ‘log buffer space’;
Select name, value
where name in (‘redo log space requests’);
The pct_buff_alloc_retries should be zero or less than 0.01 (< 1%). If it is greater consider making the log buffer bigger. If it is greater consider moving the log files to faster disks such as striped disks.
Select v1.value as redo_buff_alloc_retries, v2.value as redo_entries,
trunc(v1.value/v2.value,4) as pct_buff_alloc_retries
from v$sysstat v1, v$sysstat v2
where v1.name = ‘redo buffer allocation retries’
and v2.name = ‘redo entries’;
log file sequential read
The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.
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.
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.)
A slow LGWR process can introduce log file sync waits which makes the user to experience wait times during commit or rollback. The log file parallel write and log file sync wait events are interrelated and must be dealt simultaneously.
We must try to allocate the redo logs to high performance disk(Solid state disk). Also we should try to reduce the load on LGWR by reducing commits in the applications.
The manual hotbackup piece can also introduce stress in the system by generating lot of redo stuff,So avoid that during peak time
Sometimes LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve for CPU too. This will lead to slower response from LGWR, increasing ‘log file sync’ waits. After all, these system calls and I/O calls must use CPU. In this case, ‘log file sync’ is a secondary symptom and resolving root cause for high CPU usage will reduce ‘log file sync’ waits.
Due to memory starvation issues, LGWR can also be paged out. This can lead to slower response from LGWR too.