Oracle problems

free buffer waits: The Database Writer Behind, and the Fix

3 November 2026

free buffer waits means a session has a block to bring into the buffer cache and cannot find a buffer it may reuse: every buffer it inspected at the cold end of the list was dirty, pinned or being written, and the database writer has not yet cleaned one. Oracle's reference says that when no free buffer is found, Oracle waits for one second and then tries again. The event appears when a batch reads far more than the cache holds while the online sessions keep changing blocks, when the datafile storage writes slowly, or when one writer process is not enough for the host. It travels with write complete waits and sometimes with log file switch (checkpoint incomplete), which are the same writer behind in two other places. Two readings of five statistics say which it is.

What the wait means

The reference lists three reasons. Two of them are the daily ones: "The session moved some dirty buffers to the dirty queue and now this dirty queue is full. The dirty queue must written first. The session will wait on this event and try again to find a free buffer", and "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". The third, all buffer gets suspended because a file went from read-only to read/write, is rare and short. The wait time is 1 second, and the parameters are file# and block#: the block the session wanted to read, not the block it could not free. Every second of this wait is a second of a read that has not started, so the event sits in the Configuration wait class and the statement looks like slow I/O to the user.

Who waits, and how often

SELECT s.sql_id, s.event, COUNT(*) AS sessions,
       SUM(s.seconds_in_wait) AS seconds
FROM   v$session s
WHERE  s.event IN ('free buffer waits', 'write complete waits')
GROUP  BY s.sql_id, s.event
ORDER  BY sessions DESC;

Then the statistics Oracle keeps since startup. Take them twice, a minute apart, and work with the difference:

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('free buffer requested', 'free buffer inspected',
                'dirty buffers inspected', 'pinned buffers inspected',
                'physical reads cache', 'physical writes from cache',
                'DBWR checkpoint buffers written',
                'summed dirty queue length')
ORDER  BY name;

The reference defines the pair that tells the story. free buffer requested is the "Number of times a reusable buffer or a free buffer was requested to create or load a block", and free buffer inspected is the "Number of buffers skipped over from the end of an LRU queue to find a reusable buffer". Inspected near zero while requested climbs is a healthy cache: the first buffer at the cold end was free. Inspected climbing as fast as requested, with dirty buffers inspected most of it, means the cold end is full of dirty buffers and the writer is behind. physical reads cache in the same minute is the flood that needed all those buffers.

Is it the writer or the storage

SELECT event, total_waits,
       ROUND(time_waited_micro / 1e6)                 AS seconds,
       ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,1) AS avg_ms
FROM   v$system_event
WHERE  event IN ('db file parallel write', 'free buffer waits',
                 'write complete waits',
                 'log file switch (checkpoint incomplete)')
ORDER  BY seconds DESC;

db file parallel write is the database writer's own wait, the time its batches of blocks take to reach the datafiles. A few milliseconds on average is a healthy write path. Tens of milliseconds while the sessions wait for free buffers says the storage is the limit, and neither a bigger cache nor a second writer process will change it. Then the writer's configuration:

SELECT name, value
FROM   v$parameter
WHERE  name IN ('db_writer_processes', 'cpu_count', 'disk_asynch_io',
                'filesystemio_options', 'db_cache_size', 'sga_target',
                'fast_start_mttr_target')
ORDER  BY name;

The reference sets the default of DB_WRITER_PROCESSES at "1 or CPU_COUNT / 8, whichever is greater", so a 16-thread host has two writers and a 64-thread host eight. A write path that is fast per write, under a writer that cannot submit enough writes, is the case for more of them and for asynchronous I/O, which lets one writer keep many writes in flight.

The reader that floods the cache

SELECT sql_id, executions, disk_reads, buffer_gets,
       ROUND(disk_reads / NULLIF(executions, 0)) AS reads_per_exec
FROM   v$sql
WHERE  last_active_time > SYSDATE - 1/24
ORDER  BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

Free buffer waits rarely appear on a quiet cache. The usual picture is a batch, a report or an index build reading hundreds of thousands of blocks through the cache while the online sessions keep changing blocks, so the writer has to clean what the batch pushes out before the batch can read on. A full scan that Oracle runs as a direct path read does not take a buffer at all; the scans that hurt here are the index range scans and the cached full scans of medium tables, the ones disk_reads counts against a statement that runs every minute.

The fix, in order

1. Measure the write path first. db file parallel write averaging tens of milliseconds is a storage problem: the datafiles on the same disks as a backup or a snapshot in progress, a replicated volume, a saturated SAN queue. Fix the path and the event goes with it; nothing inside the database compensates for a slow write.
2. Cut the flood. The statement at the top of the disk_reads list reads too much: a missing index, a range scan over a year when a day was asked for, a plan that changed. A scan that must read everything runs faster, and takes no buffers, as a parallel query, which reads direct path, and a load runs as a direct-path insert, which writes its blocks straight to the datafiles.
3. Let the writer work ahead. FAST_START_MTTR_TARGET makes the writer clean dirty buffers continuously instead of in bursts at log switches, so the cold end of the list holds clean buffers when the readers arrive. A target of a few minutes costs some write bandwidth all day and buys a cache that is never full of dirty blocks.
4. More writers, and asynchronous I/O. On a host with many CPU threads and a write path that is fast per write, raise DB_WRITER_PROCESSES, which takes a restart, and make sure asynchronous I/O is on, FILESYSTEMIO_OPTIONS = SETALL on a file system, so each writer keeps many writes in flight instead of one.
5. Then the cache size, last. A bigger buffer cache helps only when the reads repeat. V$DB_CACHE_ADVICE, which needs no pack, predicts the physical reads at each size: a batch that reads every block once gains nothing from twice the cache, and a cache that is simply too small for the working set shows there as a steep curve.
6. Move the batch. If the flood is a nightly job, run it when the online sessions are not dirtying blocks, or split it so the writer keeps up. The statistics above, taken each hour, say when the writer falls behind, and a job moved an hour away from the backup often ends the wait without any other change.

See it coming

Radar Engine, the detection layer of Scops, reads the waiting sessions every 5 seconds and keeps a standing watch on the four events that mean a writer is behind, so the first minute of free buffer waits during a batch is a finding with the statement that floods the cache and the writer's own latency, before the online sessions queue:

The finding names the database, the event, the statement behind most of the waiting sessions and the writer's average write time, so the first question, storage or flood, is answered in the mail. The Activity chart draws the Configuration class by statement on any edition, without the Diagnostics Pack.

Questions

What does free buffer waits mean?
A session wants to read a block into the buffer cache and every buffer it inspected at the cold end of the list was dirty, pinned or being written, so there was none to reuse. Oracle waits one second and looks again. The database writer is behind the readers: the storage writes slowly, there are too few writer processes, or a batch is pushing blocks through the cache faster than they can be cleaned.

Is free buffer waits a buffer cache size problem?
Rarely first. It is a write problem: dirty buffers inspected climbing as fast as free buffer requested says the cold end is full of dirty blocks, and db file parallel write says how long each write takes. A bigger cache helps only when the reads repeat, which V$DB_CACHE_ADVICE shows without any pack license.

How do I know whether DBWR is slow?
db file parallel write in V$SYSTEM_EVENT is the writer's own wait; its average over a few milliseconds while sessions wait for free buffers is slow storage. The pair free buffer requested and free buffer inspected in V$SYSSTAT says how far the writer is behind, and DB_WRITER_PROCESSES, 1 or CPU_COUNT / 8 by default, says how many writers there are.

What is the difference between free buffer waits and write complete waits?
Free buffer waits is a session that cannot find a buffer to read into. Write complete waits is a session that wants to change a buffer the writer is in the middle of writing to disk, and must wait for that write to finish. Both mean the writer is behind, and Scops watches both with the same detector.

Related problems

• log file switch (checkpoint incomplete)
• log buffer space
• db file scattered read
• direct path read
• buffer busy waits
• Oracle wait events, by class
• All Oracle problems

Sources

• Oracle: Descriptions of wait events, Statistics descriptions, DB_WRITER_PROCESSES, FAST_START_MTTR_TARGET, V$DB_CACHE_ADVICE, V$SYSTEM_EVENT
• Scops docs: Detector reference