free buffer waits

The free buffer waits event has three parameters: file#, block#, and set ID. In Oracle Database 10 g , 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 Oracle9 i 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:

 select * 
 from   v$sysstat 
 where  name in ('free buffer requested', 'free buffer inspected'); 
 
 STATISTIC# NAME                      CLASS          VALUE 
 ---------- ------------------------- ----- -------------- 
 75 free buffer requested         8  3,311,443,932 
 79 free buffer inspected         8    108,685,547 
 
 select * 
 from   v$system_event 
 where event = 'free buffer waits'; 
 
 EVENT                TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT 
 -------------------- ----------- -------------- ----------- ------------ 
 free buffer waits          30369          15795     2187602   72.0340479

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

Inefficient SQL Statements

Poorly written SQL statements are by far the main culprits. Look for statements that perform a lot of physical reads (DISK_READS) in the V$SQL view. The statements may be performing full table scans, index fast full scans, or accessing tables via unselective indexes. Tune the statements to lower the physical read demands. Use the most effective join method and access path . Also, if appropriate for the application, make more use of direct reads and writes , such as direct loads, direct inserts , and parallel queries. Direct read and write operations bypass the SGA.

Insufficient DBWR Processes

As the LGWR process is responsible for making room in the log buffer, the DBWR process is responsible for making free buffers on LRU lists. There is a relationship between the DBWR process and the buffer cache working set. Remember, a working set is comprised of the LRU and LRUW lists. (We discussed the buffer cache working set in the ‚“ cache buffers lru chain Latches ‚½ section in Chapter 6.) If there is only one DBWR process, it has to service all the working sets. When there are multiple DBWR processes, Oracle divides them among the working sets. Naturally, a higher number of DBWR processes can service the working sets more efficiently and yield a higher throughput. The following snapshot from the X$KCBWDS (kernel cache buffer working set descriptors) view shows eight working sets and two DBWR processes:

 select set_id, dbwr_num 
 from   x$kcbwds 
 order by set_id; 
 
 SET_ID   DBWR_NUM 
 ---------- ---------- 
 1          0 
 2          0 
 3          0 
 4          0 
 5          1 
 6          1 
 7          1 
 8          1

Some of you are using asynchronous I/O and may have been told to use only one DBWR process. You should know that is only the general guideline, and you can still use multiple DBWR processes with asynchronous I/O to combat the free buffer waits symptom. Depending on your CPU_COUNT, you can increase the number of DBWR processes with the DB_WRITER_PROCESSES parameter. However, this is not an excuse for not optimizing SQL statements with high buffer gets, if they exist.

Note ‚

Quite often DBAs think they need more DBWR processes when the real problem is that they oversized the buffer cache. Just because the database is a few terabytes in size does not mean that it needs a gigabyte-size SGA. Rarely is there a need for a gigabyte-size buffer cache. Instead of increasing the number of DBWR processes, try reducing the buffer cache size .

In addition to using multiple DBWR processes, you can also reduce the number of free buffer waits occurrences by increasing the number of DBWR checkpoints. In Oracle9 i Database, this can be achieved by shortening the mean time to recovery (MTTR) through the FAST_START_MTTR_TARGET parameter. A shorter MTTR causes the DBWR process to be more aggressive and consequently a better supply of clean buffers. However, a more aggressive DBWR process increases the chances of processes waiting on the write complete waits event if they need to modify the blocks that are in transit to disk. You have to find a balance based on the application behavior. We will show you how to handle the write complete waits event in the next section.

Slow I/O Subsystem

The DBWR performance is vastly influenced by the quality and speed of the I/O subsystem. If the average wait time of the db file parallel write wait event is high, it can negatively impact foreground processes causing them to wait on the free buffer waits event. We showed you how to handle the db file parallel write wait event in Chapter 5.

Delayed Block Cleanouts

After you have loaded a table, you should perform a full table scan operation on the table before turning it over to the application. This is because the first process that reads the data blocks may be penalized to perform delayed block cleanouts, which can cause free buffer waits . So, when you bear the penalty before turning the table over to the application, you spare the application from the misleading one-time free buffer waits attack. You can see this behavior in Oracle9 i Database with the following simple test:

Set the buffer cache to about 20,000 blocks.
Set the FAST_START_MTTR_TARGET to a low number, say 5 seconds. This will cause the DBWR process to aggressively write out dirty buffers so that delayed block cleanouts must be performed when the blocks are queried at a later time.
Insert a large amount of rows (say, about 1.5 million) into a table with no indexes and commit the transaction.
Shutdown and restart the database.
Count the number of rows in the table. The Oracle process will perform a full table scan operation on the table. Query the V$SESSION_EVENT for this particular session. You should see many free buffer waits events due to delayed block cleanouts.

Repeat steps 4 and 5. However, this time you should not see free buffer waits events because the old ITLs have been cleaned out.

Note ‚

Delayed block cleanout and commit cleanout ‚ a transaction may affect and dirty many data blocks in the buffer cache. The DBWR process writes both committed and uncommitted blocks to the data files at various intervals. When a transaction commits, the Oracle process performs commit cleanouts to blocks that have not been written out by the DBWR process. Blocks that have been written out will be cleaned by the next process that reads them. This is known as delayed block cleanout. For more information see Metalink note #40689.1.

Small Buffer Cache

Lastly, processes may experience free buffer waits contention if the buffer cache is simply too small to handle the free buffer demands. We purposely make this our last point because nowadays it is uncommon to find an undersized buffer cache. DBAs tend to oversize the buffer cache because database servers are equipped with a ‚“boatload ‚½ of memory. Many DBAs are still under the impression that they need to allocate 50 percent of the available memory for SGA. Before you entertain the idea of increasing the buffer cache size, you should first increase the number of DBWR processes and see if that will reduce the number of free buffer waits events. Then you may increase the buffer cache size.