free buffer waits - Diagnose Oracle Buffer Cache Issues

free buffer waits

Overview

The free buffer waits wait event occurs when a session needs to read a block into the buffer cache but cannot find a free (clean) buffer to use. The buffer cache is full of dirty buffers — blocks that have been modified but not yet written to the data files by DBWR (the Database Writer background process). The session must wait for DBWR to write some dirty buffers to disk, freeing them for reuse.

To understand this event, it helps to know how Oracle manages the buffer cache. Oracle maintains a list of buffers ordered by recency of use (the LRU — Least Recently Used — chain). When a session needs a new buffer, it looks for a free buffer at the cold end of the LRU. If the cold end contains only dirty buffers, Oracle signals DBWR to write them, then the session waits on free buffer waits until DBWR completes those writes and the buffers become available.

This is fundamentally a write-throughput bottleneck: DBWR cannot write dirty buffers to the data files as fast as the workload is dirtying them. The root cause can be on either side of the equation — DBWR is too slow (I/O bottleneck, insufficient writer processes) or the buffer cache is too small (buffers are reused so rapidly that DBWR can never keep up).

free buffer waits is closely related to db file parallel write (the wait DBWR itself experiences while writing to disk). When your top waits show both free buffer waits (foreground sessions) and db file parallel write (DBWR), you have confirmed an I/O throughput constraint on the write path.

When This Wait Is a Problem

Acceptable: Near-zero occurrences. Like log buffer space, free buffer waits should appear rarely. An occasional spike during a large bulk DML operation that generates many dirty buffers is forgivable.

Investigate when:

• free buffer waits appears in the top 5 wait events in AWR
• Average wait time exceeds 10ms
• Total wait time accounts for more than 1% of DB time
• V$SYSSTAT shows “free buffer requested” increasing rapidly while “dirty buffers inspected” is also high
• V$BUFFER_POOL_STATISTICS shows a low hit ratio alongside high physical writes

Critical threshold: If you see hundreds of sessions simultaneously waiting for free buffers, the database is essentially stalled on write I/O. Transaction throughput will be severely degraded until DBWR catches up.

Diagnostic Queries

1. Confirm Free Buffer Waits Are the Active Issue

-- Check current session waits
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(max_wait / 100, 2) AS max_wait_secs
FROM
    v$system_event
WHERE
    event IN (
        'free buffer waits',
        'db file parallel write',
        'checkpoint completed',
        'db file sequential read',
        'db file scattered read',
        'write complete waits'
    )
ORDER BY
    total_wait_secs DESC;

2. V$BUFFER_POOL_STATISTICS — Cache Sizing and Hit Ratio

-- Buffer pool statistics: hit ratio, dirty buffer counts, write activity
SELECT
    name AS pool_name,
    block_size,
    buffers,
    ROUND(buffers * block_size / 1024 / 1024, 2) AS size_mb,
    free_buffer_wait,
    write_complete_wait,
    buffer_busy_wait,
    free_buffer_inspected,
    dirty_buffers_inspected,
    db_block_gets,
    consistent_gets,
    physical_reads,
    physical_writes,
    ROUND(
        100 * (1 - physical_reads / NULLIF(db_block_gets + consistent_gets, 0)),
        2
    ) AS cache_hit_ratio_pct
FROM
    v$buffer_pool_statistics
WHERE
    buffers > 0
ORDER BY
    name;

3. DBWR Performance Statistics

-- DBWR write statistics from V$SYSSTAT
SELECT
    name,
    value,
    CASE name
        WHEN 'DBWR timeouts'          THEN 'Times DBWR woke up with nothing to write'
        WHEN 'DBWR make free requests' THEN 'Times foreground asked DBWR to free buffers'
        WHEN 'DBWR free buffers found' THEN 'Buffers freed per make-free request'
        WHEN 'DBWR lru scans'         THEN 'LRU scans performed by DBWR'
        WHEN 'DBWR checkpoints'       THEN 'Number of checkpoints processed'
        WHEN 'DBWR buffers scanned'   THEN 'Total buffers scanned during LRU scan'
        WHEN 'physical writes'        THEN 'Total physical write I/Os'
        WHEN 'physical writes direct' THEN 'Direct path writes (bypass buffer cache)'
        WHEN 'dirty buffers inspected' THEN 'Dirty buffers found on LRU during scans'
        WHEN 'free buffer requested'  THEN 'Requests for a free buffer'
        ELSE NULL
    END AS description
FROM
    v$sysstat
WHERE
    name IN (
        'DBWR timeouts',
        'DBWR make free requests',
        'DBWR free buffers found',
        'DBWR lru scans',
        'DBWR checkpoints',
        'DBWR buffers scanned',
        'physical writes',
        'physical writes direct',
        'dirty buffers inspected',
        'free buffer requested'
    )
ORDER BY
    name;

4. Check Current DBWR Process Count and Configuration

-- Number of DBWR processes running
SELECT
    name,
    value
FROM
    v$parameter
WHERE
    name IN (
        'db_writer_processes',
        'dbwr_io_slaves',
        'db_cache_size',
        'db_keep_cache_size',
        'db_recycle_cache_size',
        'sga_target',
        'memory_target',
        'db_file_multiblock_read_count',
        'fast_start_mttr_target'
    )
ORDER BY
    name;

5. Data File I/O Performance — Find Bottleneck Files

-- Data file write statistics — identify hot files with high write latency
SELECT
    df.name AS file_name,
    ts.name AS tablespace_name,
    fs.phyrds AS physical_reads,
    fs.phywrts AS physical_writes,
    fs.readtim AS read_time_cs,
    fs.writetim AS write_time_cs,
    CASE
        WHEN fs.phyrds > 0
        THEN ROUND(fs.readtim / fs.phyrds * 10, 2)
        ELSE 0
    END AS avg_read_ms,
    CASE
        WHEN fs.phywrts > 0
        THEN ROUND(fs.writetim / fs.phywrts * 10, 2)
        ELSE 0
    END AS avg_write_ms
FROM
    v$filestat fs
    JOIN v$datafile df ON fs.file# = df.file#
    JOIN v$tablespace ts ON df.ts# = ts.ts#
WHERE
    fs.phywrts > 0
ORDER BY
    fs.writetim DESC;

6. ASH — Free Buffer Waits Historical Pattern

-- When did free buffer waits occur? Correlate with batch job schedule
SELECT
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS sample_minute,
    COUNT(*) AS ash_samples,
    COUNT(DISTINCT session_id) AS sessions_affected
FROM
    v$active_session_history
WHERE
    event = 'free buffer waits'
    AND sample_time > SYSDATE - 1
GROUP BY
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')
ORDER BY
    sample_minute DESC;

7. Buffer Cache Advisor — Right-Size the Cache

-- DB Cache Advisor: estimated physical reads at different cache sizes
SELECT
    size_for_estimate AS cache_size_mb,
    buffers_for_estimate,
    estd_physical_reads,
    estd_physical_read_factor,
    ROUND(100 * (1 - estd_physical_read_factor), 2) AS estimated_improvement_pct
FROM
    v$db_cache_advice
WHERE
    name = 'DEFAULT'
    AND block_size = (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size')
ORDER BY
    size_for_estimate;

Root Causes

1. DBWR Cannot Write Fast Enough (I/O Throughput Bottleneck)

The most frequent cause. The storage subsystem hosting the data files cannot sustain the write throughput required by the workload. This may manifest as high average write times in V$FILESTAT (above 20ms is a warning; above 50ms is critical). Causes include: spinning disk with saturated I/O queues, shared SAN LUNs with insufficient allocated IOPS, misconfigured storage controllers, or missing asynchronous I/O configuration.

2. Insufficient DB_WRITER_PROCESSES

By default, Oracle starts one DBWR process (DBW0). A single process may not provide enough write parallelism on systems with many CPU cores and high-throughput workloads. Oracle allows up to 36 DBWR processes (DBW0–DBW9 and DBWa–DBWz). As a rule of thumb, use one DBWR process per 2–4 CPU cores, up to a maximum of 36.

3. Buffer Cache Too Small for the Working Set

When DB_CACHE_SIZE (or SGA_TARGET) is insufficient to hold the working set of active data blocks, the LRU replacement rate is very high. Buffers are dirtied and aged out faster than DBWR can write them, creating a perpetual shortfall of free buffers. The Buffer Cache Advisor (V$DB_CACHE_ADVICE) is the authoritative tool for right-sizing.

4. Excessive Checkpoint Pressure

Frequent checkpoints (triggered by FAST_START_MTTR_TARGET, frequent log switches, or explicit ALTER SYSTEM CHECKPOINT) force DBWR to write all dirty buffers that haven’t been written since the last checkpoint. If log switches are too frequent, DBWR is in a constant state of checkpoint-driven writes, leaving no bandwidth for normal LRU-driven writes.

5. Async I/O Disabled

When asynchronous I/O is disabled (DISK_ASYNCH_IO = FALSE), DBWR must submit each write sequentially and wait for it to complete before submitting the next. This can dramatically reduce DBWR’s effective write throughput. Enabling async I/O allows DBWR to submit many writes in parallel and collect completions asynchronously.

6. Large Batch DML Generating Massive Dirty Buffer Volumes

A single batch job that updates or inserts tens of millions of rows will dirty a proportional number of buffers in rapid succession. If the batch generates dirty buffers faster than DBWR can write them, the buffer cache fills with dirty blocks and free buffer waits ensue.

Resolution Steps

Step 1: Increase DB_WRITER_PROCESSES

-- Check current DBWR process count
SHOW PARAMETER db_writer_processes;

-- Increase for multi-core systems with high write workloads
-- Rule of thumb: 1 DBWR per 2-4 CPU cores, max 36
ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

-- Note: Requires database restart to take effect
-- After restart, verify with:
SELECT name, value FROM v$parameter WHERE name = 'db_writer_processes';

Step 2: Enable Asynchronous I/O

-- Check current async I/O settings
SHOW PARAMETER disk_asynch_io;
SHOW PARAMETER filesystemio_options;

-- Enable disk async I/O (requires restart)
ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;

-- For file system databases, also set:
ALTER SYSTEM SET filesystemio_options = SETALL SCOPE=SPFILE;
-- SETALL enables both async I/O and direct I/O

Step 3: Increase the Buffer Cache

-- Use V$DB_CACHE_ADVICE to determine optimal size first (see query above)
-- Then increase:

-- If using manual memory management:
ALTER SYSTEM SET db_cache_size = 8G SCOPE=BOTH;

-- If using ASMM (recommended):
ALTER SYSTEM SET sga_target = 24G SCOPE=BOTH;

-- If using AMM:
ALTER SYSTEM SET memory_target = 32G SCOPE=BOTH;

Step 4: Tune Checkpoint Frequency

-- Reduce checkpoint pressure by tuning FAST_START_MTTR_TARGET
-- Larger value = less frequent incremental checkpoints
-- Balance with recovery time objective (RTO)
ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;  -- 5 minutes max MTTR

-- Also ensure redo log files are large enough (see log-buffer-space guide)
-- to reduce log switch frequency, which triggers full checkpoints

Step 5: Identify and Throttle the High-Write Batch Job

-- Find the session generating the most dirty buffers
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ss.value AS block_changes
FROM
    v$session s
    JOIN v$sesstat ss ON s.sid = ss.sid
    JOIN v$statname sn ON ss.statistic# = sn.statistic#
WHERE
    sn.name = 'db block changes'
    AND ss.value > 10000
    AND s.username IS NOT NULL
ORDER BY
    ss.value DESC
FETCH FIRST 20 ROWS ONLY;

-- Consider adding SLEEP calls in the batch or running during off-peak
-- Or use direct-path inserts to bypass buffer cache entirely:
INSERT /*+ APPEND */ INTO target_table SELECT * FROM source_table;

Prevention & Tuning

Right-size the buffer cache using the Cache Advisor: Run V$DB_CACHE_ADVICE weekly and adjust DB_CACHE_SIZE or SGA_TARGET when the estimated improvement from a larger cache exceeds 10%.

Scale DBWR with CPU count: On 16-core or larger servers, a single DBWR process is almost always insufficient. Set DB_WRITER_PROCESSES to at least CPU_COUNT / 4 rounded up.

Place data files on storage with adequate write IOPS: Validate storage performance before production go-live using OS-level tools (iostat, fio) and confirm average write latency below 5ms for OLTP workloads.

Use DBWR I/O Slaves for older storage without native async I/O support:

-- For legacy storage without native async I/O
ALTER SYSTEM SET dbwr_io_slaves = 4 SCOPE=SPFILE;
-- Note: Conflicts with DB_WRITER_PROCESSES > 1. Use one or the other.

Monitor dirty buffer ratios proactively:

-- Alert if dirty_buffers_inspected / free_buffer_requested ratio is high
-- A high ratio means DBWR is struggling to keep free buffers available
SELECT
    name,
    value
FROM v$sysstat
WHERE name IN ('dirty buffers inspected', 'free buffer requested', 'DBWR make free requests')
ORDER BY name;

Related Wait Events

• db file parallel write — The wait DBWR itself experiences while writing dirty buffers to data files; co-occurs with free buffer waits
• write complete waits — A session needs a buffer that DBWR is currently writing; must wait for the write to complete
• checkpoint completed — A session needs to recycle a redo log but the checkpoint hasn’t advanced far enough yet
• buffer busy waits — Contention on a specific block class (not related to free buffer availability)
• read by other session — Another session is reading a block into the cache; related but caused by read I/O, not write I/O