Oracle Wait Events – free buffer waits

This wait event will happen when all buffer gets have been suspended. This could happen when a file was read-only and is now read-write. All the existing buffers need to be invalidated since they are not linked to lock elements (needed when mounted parallel (shared)). So cache buffers are not assigned to dba's until the invalidation is finished.

This could also happen when you need a buffer from the SGA for a block in CR, READING or any of the recovery modes. Basically you will post the DBWR to make some free buffers.

This also happens after inspecting 'free buffers inspected' buffers. If no free buffer was found, Oracle waits for one second and will try to get the buffer again (depends on the context).

Wait time

1 Second.

Parameters

• file#

  This the file in which Oracle is trying to get a free block. The name of the file can be determined with the following SQL statement:

  select *
    from v$datafile
   where file# = file#;

• block#

  This is the block# in the file that Oracle is trying to read into a buffer that currently is not available. With the following SQL statement one can determine to which object the block belongs:

  select name, kind
    from ext_to_obj_view
   where file# = file#
     and lowb <= block#
     and highb >= block#;

  The DBWR is not writing enough buffers to disk. Make sure that the I/O load is evenly distributed across all disks, use O/S monitor tools or look at v$filestat:

  select name, phyrds, phywrts
     from v$filestat a, v$datafile b
    where a.file# = b.file#

  Also look for files that have full table scans:

  select name, phyrds, phyblkrd, phywrts
     from v$filestat a, v$datafile b
    where a.file# = b.file#
      and phyrds != phyblkrd

Check your application to make sure that is what you intended and that you don't miss an index. Always try to use the O/S striping software to distribute database files over as many disks as one can.

Disk Sorts are known to cause a flood of dirty buffers that will need to be written out. It is important to stripe the datafiles belonging to the TEMP tablespace over many different disks. Also during a checkpoint the SORT blocks are not checkpointed and they are thus only written in the normal write batch. If the write batch is full with SORT blocks, the other dirty blocks can't be written and foregrounds will have to wait for free buffers.