James's Blog

Oracle: Wait Events

1. DB File Scattered Read
- This generally indicates waits related to full table scans. As full table scans
are pulled into memory, they rarely fll into contiguous buffers but instead are
scattered throughout the buffer cache.
- A large number here indicates that your table may have missing or suppressed index
check to ensure that full table scan are necessary when you see these waits.
try to cache small tables to avoid reading them in over and over again, since
full table scan is put at cold end of LRU list.

2. DB File Sequential Read
- This event generally indicates a single block read(an index read, for example).
A large number of waits here could indicate poor joining orders of tables or
unselective indexing.
- You should correlate this wait statistic with other known issues within the
statspack report, such as inefficient SQL. check to ensure that index scans are
necessary and join orders for multiple table joins.

3. Free Buffer
- This indicates your system is waiting for a buffer in memory because none is
available. Waits in this category may indicate that you need to increase the
DB_BUFFER_CACHE. It could also indicate that unselective SQL is causing data
to flood the buffer cache with index blocks.
- This normally indicate that there's a substantial amount of DML being done and
that the DBWR is not writing quickly enough.
To address this, you may want to consider accelerating incremental checkpointing
using more DBWR processes or increasing the number of physical disks.

4. Buffer Busy

5. Latch Free

6. Enqueue
- An enqueue is a lock that protects a shared resource.

7. Log Buffer space

posted by James Pei @ 6:17 PM