The following are the most common causes of wait events:
DB File Scattered Read: This wait event is usually caused by large full table scans. This is normal for DSS systems but is critical for OLTP systems. The DBA should consider the caching of small tables to eliminate file reads. Also, in the OLTP environment, consideration should be given to tuning SQL statements as well.
DB File Sequential Read: A high number of waits for this event indicates possible problems with join operations of SQLs or invocation of non-selective indexes. This wait is caused by a large number of single block reads.
Buffer Busy: This wait is caused by concurrent access to buffers in the buffer cache. This statistic should be correlated with the Buffer Waits section of the AWR report.
Free Buffer: This wait event indicates that Oracle waited many times for a free buffer in the buffer cache. This could be caused by the small size of buffer cache, or a large number of reads which populated the buffer cache with unnecessary data. In this case, the SQLs and buffer contents should be examined. Also, slow work by the Database Writer (DBWR) process could cause such wait event.
Log Buffer Space: This wait event shows that the Log Writer (LGWR) process is not fast enough to free log cache for new blocks. This could be caused by slow log switches, slow disks serving redo logs, or a small size of the redo log buffer.
Latch Free: This wait event is often caused by not using bind variables in SQL statements. This fact is indicated by the library cache latch in the Latches section of the AWR report. There are other latches that can cause this wait event to be high: redo allocation latch , cache buffers LRU chain, cache buffers chain, etc.
The wait events for background processes are separated in the Background Wait Events section. In most cases, Oracle background processes place very little overhead on the system. However, it makes sense to monitor the database’s wait events activity in order to see how they operate.
