Friday, 29 August 2014

Wait Event: free buffer waits

Wait Event: free buffer waits

Possible Causes:
  • This means we are waiting for a free buffer but there are none available in the cache because there are too many dirty buffers in the cache
  • Either the buffer cache is too small or the DBWR is slow in writing modified buffers to disk
  • DBWR is unable to keep up to the write requests
  • Checkpoints happening too fast – maybe due to high database activity and under-sized online redo log files
  • Large sorts and full table scans are filling the cache with modified blocks faster than the DBWR is able to write to disk
  • If the  number of dirty buffers that need to be written to disk is larger than the number that DBWR can write per batch, then these waits can be observed
Actions:
  • Reduce checkpoint frequency  - increase the size of the online redo log files
  • Examine the size of the buffer cache – consider increasing the size of the buffer cache in the SGA
  • Set disk_asynch_io = true set
  • If not using asynchronous I/O
  • increase the number of db writer
  • processes or dbwr slaves
  • Ensure hot spots do not exist by spreading datafiles over disks and disk controllers
  • Pre-sorting or reorganizing data can help
Ref:

Understanding and Tuning Buffer Cache and DBWR Note# 62172.1
How to Identify a Hot Block within the database Buffer Cache. Note# 163424.1
gavinsoorma.com



No comments: