free buffer waits

This will happen if:

  • 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 data block addresses until the invalidation is finished.

  • The session moved some dirty buffers to the dirty queue and now this dirty queue is full. The dirty queue needs to be written first. The session will wait on this event and try again to find a free buffer

  • This also happens after inspecting ‘free buffer inspected’ buffers. If no free buffer is found, Oracle waits for one second, and then tries to get the buffer again (depends on the context). For more information, see free buffer inspected.

  • 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.

Wait time   1 Second.

The free buffer waits event has three parameters: file#, block#, and set ID. In Oracle Database 10g, this wait event falls under the Configuration wait class. Keep the following key thoughts in mind when dealing with the free buffer waits event.

Before a block is read into the buffer cache, an Oracle process must find and get a free buffer for the block. Sessions wait on the free buffer waits event when they are unable to find a free buffer on the LRU list or when all buffer gets are suspended.

The DBWR process is responsible for making clean buffers on the LRU lists.

Common Causes, Diagnosis, and Actions

A foreground process needing a free buffer scans the LRU list up to a predetermined threshold, usually a percentage of the LRU list. In Oracle9i Database, the threshold is 40 percent. This value is described in the X$KVIT (kernel performance information transitory instance parameters) view as “Max percentage of LRU list foreground can scan for free.” If a free buffer is not found when the threshold is met, the foreground process posts the DBWR process to make available clean buffers. While the DBWR process is at work, the Oracle session waits on the free buffer waits event.

Oracle keeps a count of every free buffer request. The statistic name in the V$SYSSTAT view is free buffer requested. Oracle also keeps a count of every free buffer request that fails. This is given by the TOTAL_WAITS statistic of the free buffer waits event. Free buffer requests are technically buffer gets, if you will, and free buffer requests that fail can be considered as buffer misses. Yet another V$SYSSTAT statistic free buffer inspected tells you how many buffers Oracle processes have to look at to get the requested free buffers. If the free buffer inspected value is significantly higher than the free buffer requested, that means processes are scanning further up the LRU list to get a usable buffer. The following queries list the systemwide free buffer requested, free buffer inspected, and free buffer waits statistics:

Fig 7-2

Fig 7-3

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.

If a session spends a lot of time on the free buffer waits event, it is usually due to one or a combination of the following five reasons:

  • Inefficient SQL statements
  • Not enough DBWR processes
  • Slow I/O subsystem
  • Delayed block cleanouts
  • Small buffer cache
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s