Skip to content
Oracle DBA Scripts & Database Utilities

DB_CACHE_SIZE - Size Oracle Buffer Cache for I/O Performance

DB_CACHE_SIZE

Section titled “DB_CACHE_SIZE”

Overview

Section titled “Overview”

DB_CACHE_SIZE specifies the size of the default buffer pool — the SGA region where Oracle caches data blocks read from datafiles. A larger buffer cache means more blocks remain in memory across SQL executions, reducing costly physical I/O and improving query response times. When SGA_TARGET or MEMORY_TARGET is set, Oracle auto-tunes this component through ASMM or AMM and DB_CACHE_SIZE acts as a guaranteed minimum. When auto-management is off, it directly controls the buffer cache size.

The buffer cache is typically the largest SGA component and the highest-leverage tuning target for read-intensive workloads. Even modest increases in the cache hit ratio can eliminate orders of magnitude more physical I/O.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 0 when SGA_TARGET > 0 (Oracle auto-sizes); otherwise a platform-dependent minimum Valid Range: 0 (auto) or at least DB_BLOCK_SIZE × minimum granule size Available Since: Oracle Database 9i (renamed from DB_BLOCK_BUFFERS × DB_BLOCK_SIZE) Modifiable: Yes — ALTER SYSTEM (increase is online; decrease may fail if blocks are pinned) PDB Modifiable: No — SGA parameters are CDB-level only

Configuration

Section titled “Configuration”

Viewing Current Value

Section titled “Viewing Current Value”
-- Check DB_CACHE_SIZE parameter value
SELECT name,
       value                    AS bytes,
       value / (1024 * 1024)    AS mb,
       display_value,
       isdefault,
       description
FROM   v$parameter
WHERE  name = 'db_cache_size';


-- Check actual runtime buffer cache size (ASMM may differ from parameter)
SELECT component,
       current_size / (1024 * 1024)      AS current_mb,
       min_size     / (1024 * 1024)      AS min_mb,
       max_size     / (1024 * 1024)      AS max_mb,
       user_specified_size / (1024*1024) AS floor_mb,
       last_oper_type,
       last_oper_mode
FROM   v$sga_dynamic_components
WHERE  component = 'DEFAULT buffer cache';


-- Check all buffer pools (DEFAULT, KEEP, RECYCLE, and nK caches)
SELECT component,
       current_size / (1024 * 1024) AS current_mb
FROM   v$sga_dynamic_components
WHERE  component LIKE '%buffer%'
   OR  component LIKE '%cache%'
ORDER BY current_size DESC;

Setting the Parameter

Section titled “Setting the Parameter”
-- Set a floor when using ASMM (SGA_TARGET > 0) — Oracle manages above this
ALTER SYSTEM SET db_cache_size = 2G SCOPE = BOTH;


-- Set fixed size when NOT using ASMM (manual SGA)
ALTER SYSTEM SET db_cache_size = 8G SCOPE = BOTH;


-- Increase buffer cache immediately (no restart needed)
ALTER SYSTEM SET db_cache_size = 4G SCOPE = BOTH;


-- Persist to SPFILE only (takes effect at next restart)
ALTER SYSTEM SET db_cache_size = 4G SCOPE = SPFILE;


-- Return to full ASMM control (remove the floor)
ALTER SYSTEM SET db_cache_size = 0 SCOPE = BOTH;

Tuning Guidance

Section titled “Tuning Guidance”
Section titled “Recommended Values”
EnvironmentProfileSuggested DB_CACHE_SIZE / SGA AllocationNotes
OLTP, small (< 16 GB RAM)High concurrency, small rows50–60% of SGAMany small block reads
OLTP, large (64+ GB RAM)Connection pooling70–80% of SGAMaximize cache; pool reduces session overhead
Data WarehouseFull scans, large tables30–50% of SGADirect path reads bypass cache; balance with PGA
Mixed workloadOLTP + reporting60–70% of SGAShared pool also needs headroom
Read-mostly, hot dataset fits in RAMPure cache hit scenarioDataset size + 20% bufferNear 100% hit ratio achievable

How to Size

Section titled “How to Size”

V$DB_CACHE_ADVICE is the definitive tool for buffer cache sizing. Oracle continuously simulates what the physical read count would be at different cache sizes.

-- V$DB_CACHE_ADVICE: model reads at different cache sizes
-- Run after at least 30 minutes of representative workload
SELECT size_for_estimate / (1024 * 1024)  AS cache_size_mb,
       size_factor,
       buffers_for_estimate,
       estd_physical_read_factor,
       estd_physical_reads
FROM   v$db_cache_advice
WHERE  name    = 'DEFAULT'
AND    block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
AND    advice_status = 'ON'
ORDER BY size_for_estimate;

Reading the output: estd_physical_read_factor of 1.0 represents the current cache size. Values < 1.0 mean fewer reads (better) with a larger cache. Look for the point where the factor flattens — adding more cache beyond that point gives minimal additional benefit.

-- Ensure cache advice is enabled (should be ON by default)
SELECT name, value FROM v$parameter WHERE name = 'db_cache_advice';


-- Enable if not running
ALTER SYSTEM SET db_cache_advice = ON;

Buffer Cache Hit Ratio

Section titled “Buffer Cache Hit Ratio”
-- Overall buffer cache hit ratio (logical vs physical reads)
-- Target: > 90% for OLTP; data warehouses with direct-path reads will be lower
SELECT 1 - (phy.value / (cur.value + con.value)) AS hit_ratio,
       ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) AS hit_ratio_pct,
       cur.value   AS db_block_gets,
       con.value   AS consistent_gets,
       phy.value   AS physical_reads
FROM   v$sysstat cur,
       v$sysstat con,
       v$sysstat phy
WHERE  cur.name = 'db block gets'
AND    con.name = 'consistent gets'
AND    phy.name = 'physical reads';


-- Per-segment physical reads (identify hot objects driving I/O)
SELECT owner,
       object_name,
       object_type,
       physical_reads,
       db_block_changes,
       logical_reads,
       ROUND(physical_reads / NULLIF(logical_reads, 0) * 100, 2) AS phys_read_pct
FROM   v$segment_statistics
WHERE  statistic_name = 'physical reads'
AND    physical_reads  > 0
ORDER BY physical_reads DESC
FETCH FIRST 20 ROWS ONLY;


-- Physical reads vs logical reads trend (from AWR, requires Diagnostics Pack)
SELECT snap_id,
       SUM(CASE WHEN stat_name = 'physical reads'  THEN value END) AS phys_reads,
       SUM(CASE WHEN stat_name = 'logical reads'   THEN value END) AS logical_reads
FROM   dba_hist_sysstat
WHERE  stat_name IN ('physical reads', 'logical reads')
AND    snap_id > (SELECT MAX(snap_id) - 48 FROM dba_hist_snapshot)
GROUP BY snap_id
ORDER BY snap_id;

Monitoring

Section titled “Monitoring”
-- Monitor buffer cache current state
SELECT name,
       block_size,
       current_size / (1024 * 1024)   AS current_mb,
       buffers
FROM   v$buffer_pool
ORDER BY current_size DESC;


-- Check for buffer busy waits (indicates cache contention, not size issue)
SELECT event,
       total_waits,
       time_waited_micro / 1000 AS time_waited_ms,
       average_wait
FROM   v$system_event
WHERE  event IN ('buffer busy waits', 'read by other session', 'db file sequential read', 'db file scattered read')
ORDER BY total_waits DESC;


-- Objects with highest buffer cache residency (what is actually in cache)
SELECT owner,
       segment_name,
       segment_type,
       COUNT(*) AS cached_blocks,
       COUNT(*) * s.block_size / (1024 * 1024) AS cached_mb
FROM   v$bh b
JOIN   dba_extents e
  ON   b.file# = e.file_id
 AND   b.block# BETWEEN e.block_id AND e.block_id + e.blocks - 1
JOIN   dba_segments s
  ON   e.owner = s.owner
 AND   e.segment_name = s.segment_name
WHERE  b.status != 'free'
GROUP BY owner, segment_name, segment_type, s.block_size
ORDER BY cached_blocks DESC
FETCH FIRST 20 ROWS ONLY;

Common Issues

Section titled “Common Issues”

Low buffer cache hit ratio despite large cache

Section titled “Low buffer cache hit ratio despite large cache”

A hit ratio below 90% in an OLTP environment usually has one of three causes: the cache is genuinely too small, there are large-table full scans polluting the cache, or direct-path reads are bypassing the cache (expected for data warehouses).

-- Identify full scans that may be polluting the buffer cache
SELECT sql_id,
       disk_reads,
       buffer_gets,
       rows_processed,
       ROUND(disk_reads / NULLIF(buffer_gets, 0) * 100, 2) AS disk_read_pct,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sql
WHERE  disk_reads > 10000
ORDER BY disk_reads DESC
FETCH FIRST 20 ROWS ONLY;


-- Move large lookup tables to the KEEP pool to protect them from eviction
-- First, create the KEEP pool
ALTER SYSTEM SET db_keep_cache_size = 512M SCOPE = BOTH;


-- Then assign objects to it
ALTER TABLE schema.large_lookup_table STORAGE (BUFFER_POOL KEEP);

ORA-04031 from buffer cache growth reducing shared pool

Section titled “ORA-04031 from buffer cache growth reducing shared pool”

Under ASMM, when you increase DB_CACHE_SIZE, Oracle may shrink the shared pool to compensate within SGA_TARGET. If the shared pool shrinks too much, ORA-04031 can occur.

-- Increase SGA_TARGET to give both pools room to grow
ALTER SYSTEM SET sga_target = 12G SCOPE = BOTH;


-- Or set explicit floors for both components
ALTER SYSTEM SET db_cache_size    = 8G   SCOPE = BOTH;
ALTER SYSTEM SET shared_pool_size = 1G   SCOPE = BOTH;
-- Total floors must not exceed SGA_TARGET

Buffer cache decrease fails at runtime

Section titled “Buffer cache decrease fails at runtime”

Shrinking DB_CACHE_SIZE online may fail if pinned or dirty blocks occupy the memory Oracle is trying to release.

-- Check how much of the cache is dirty or pinned
SELECT status,
       COUNT(*) AS buffer_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM   v$bh
GROUP BY status
ORDER BY buffer_count DESC;
-- Statuses: free, xcur (exclusive current), scur (shared current), cr (consistent read), read, mrec, irec

If the shrink is urgent, checkpoint the database to flush dirty buffers, then retry the resize.

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SET db_cache_size = 2G SCOPE = BOTH;
Section titled “Related Parameters”
ParameterRelationship
SGA_TARGETEnables ASMM; DB_CACHE_SIZE becomes a minimum floor when set
MEMORY_TARGETEnables AMM; DB_CACHE_SIZE still acts as a floor
DB_KEEP_CACHE_SIZESeparate pool for objects marked BUFFER_POOL KEEP
DB_RECYCLE_CACHE_SIZESeparate pool for objects marked BUFFER_POOL RECYCLE
DB_BLOCK_SIZEBlock size of the default cache; must match when creating the database
DB_nK_CACHE_SIZECaches for non-standard block sizes (2K, 4K, 8K, 16K, 32K)
DB_CACHE_ADVICEEnables/disables V$DB_CACHE_ADVICE simulation (default ON)
Section titled “Related Errors”

Version Notes

Section titled “Version Notes”
VersionNotes
9iDB_CACHE_SIZE introduced; replaces DB_BLOCK_BUFFERS
10gV$DB_CACHE_ADVICE fully operational; ASMM widely used
11gAMM (MEMORY_TARGET) makes DB_CACHE_SIZE a floor under combined pool
12cIn-Memory Column Store (INMEMORY_SIZE) competes for SGA budget alongside buffer cache
19c+Direct NFS and SmartScan (Exadata) can reduce cache effectiveness for full scans
21c+No functional change; ASMM or AMM with a floor remains best practice