DB_FILE_MULTIBLOCK_READ_COUNT - Tune Oracle Full Scan I/O

DB_FILE_MULTIBLOCK_READ_COUNT

Overview

DB_FILE_MULTIBLOCK_READ_COUNT controls the maximum number of Oracle data blocks read in a single I/O operation during full table scans, full index scans, and range scans. Setting this parameter higher allows Oracle to issue fewer, larger I/O requests when scanning large objects, reducing I/O latency and improving throughput for sequential read workloads. When set to 0 (the default), Oracle automatically determines the optimal value based on the maximum I/O size supported by the operating system and the database block size.

The effective I/O size per read is: DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE. For example, with an 8KB block size and a value of 128, each multiblock read is 1MB. Oracle’s cost-based optimizer also uses this parameter as an input when estimating the cost of full scans versus index access paths — a higher value makes full scans appear cheaper relative to indexed access.

Parameter Type: Dynamic (ALTER SYSTEM / ALTER SESSION) Default Value: 0 (auto-tuned by Oracle) Valid Range: 1 to platform-dependent maximum (typically 128 or 256) Available Since: Oracle 8i Modifiable: Yes — SCOPE=BOTH (SPFILE + running instance), or per session PDB Modifiable: Yes

Configuration

Viewing Current Value

-- Check current DB_FILE_MULTIBLOCK_READ_COUNT setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'db_file_multiblock_read_count';

-- Check SPFILE value (persistent value used on next startup)
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'db_file_multiblock_read_count';

-- Determine effective I/O size in KB
SELECT p1.value AS multiblock_count,
       p2.value AS block_size_bytes,
       (TO_NUMBER(p1.value) * TO_NUMBER(p2.value)) / 1024 AS effective_io_kb
FROM v$parameter p1, v$parameter p2
WHERE p1.name = 'db_file_multiblock_read_count'
  AND p2.name = 'db_block_size';

-- Check OS-level maximum I/O size used by Oracle (auto-tuned reference)
SELECT name, value
FROM v$parameter
WHERE name IN ('db_file_multiblock_read_count', 'db_block_size');

Setting the Parameter

-- Set system-wide (takes effect immediately and persists)
ALTER SYSTEM SET db_file_multiblock_read_count = 128 SCOPE=BOTH;

-- Set for current session only (useful for batch jobs or large scans)
ALTER SESSION SET db_file_multiblock_read_count = 128;

-- Restore auto-tuning behavior
ALTER SYSTEM SET db_file_multiblock_read_count = 0 SCOPE=BOTH;

-- Set in SPFILE only (takes effect on next restart)
ALTER SYSTEM SET db_file_multiblock_read_count = 64 SCOPE=SPFILE;

Tuning Guidance

Recommended Values

Environment	Recommended Value
OLTP workload (small random reads)	8–16 (or leave at 0 for auto)
Mixed workload	32–64
Data warehouse / batch / full scans	64–128
SSD storage (low latency)	64–128 (auto usually correct)
HDD storage (high sequential throughput)	128 (maximum benefit)
Exadata / smart scan	Leave at 0 (auto-tuned by platform)

For most environments, leaving this parameter at its default of 0 (auto-tuned) is the correct choice. Manual values are most useful when you need to control optimizer cost calculations or when testing specific I/O configurations.

How to Size

The key constraint is your OS maximum I/O size. Oracle will never perform an I/O larger than what the OS supports, so setting DB_FILE_MULTIBLOCK_READ_COUNT above the OS limit is wasteful.

-- Check actual multiblock read size being used in recent SQL executions
SELECT sql_id, executions,
       physical_read_requests,
       physical_read_bytes,
       ROUND(physical_read_bytes / NULLIF(physical_read_requests, 0) / 1024, 1) AS avg_read_kb
FROM v$sql
WHERE physical_read_requests > 1000
  AND physical_read_bytes > 0
ORDER BY physical_read_bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Review full scan statistics from AWR (if licensed)
SELECT metric_name, average, maximum
FROM dba_hist_sysmetric_summary
WHERE metric_name LIKE '%Physical Read%'
  AND begin_time > SYSDATE - 7
ORDER BY metric_name;

-- Check optimizer's view of the parameter (affects CBO costing)
SELECT name, value
FROM v$parameter
WHERE name IN ('db_file_multiblock_read_count',
               'db_block_size',
               'optimizer_mode');

Sizing Formula

Maximum useful value = OS_MAX_IO_SIZE / DB_BLOCK_SIZE

Example (Linux, 1MB max I/O, 8KB blocks):
  1048576 / 8192 = 128

Example (Linux, 1MB max I/O, 16KB blocks):
  1048576 / 16384 = 64

Monitoring

-- Monitor physical read I/O patterns from V$FILESTAT
SELECT f.file#, f.name,
       s.phyrds,
       s.readtim,
       ROUND(s.phyblkrd / NULLIF(s.phyrds, 0), 1) AS avg_blocks_per_read
FROM v$datafile f
JOIN v$filestat s ON f.file# = s.file#
WHERE s.phyrds > 0
ORDER BY s.phyblkrd DESC;

-- Check if full scans are dominating physical reads
SELECT name, value
FROM v$sysstat
WHERE name IN (
  'physical reads',
  'physical reads direct',
  'table scans (long tables)',
  'table scans (short tables)',
  'table fetch by rowid'
)
ORDER BY name;

Common Issues

Issue 1: Optimizer Prefers Full Scans After Increasing This Value

Raising DB_FILE_MULTIBLOCK_READ_COUNT lowers the CBO cost estimate for full table scans. If this causes unwanted plan changes (e.g., indexed queries switching to full scans), the optimizer is recalculating access path costs.

Resolution: Set the parameter at session level for batch jobs only, rather than system-wide. Alternatively, use optimizer hints or SQL plan baselines to stabilize critical query plans.

-- Check if a specific query is switching from index to full scan
SELECT sql_id, plan_hash_value, executions,
       optimizer_cost, full_plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%your_table%'
ORDER BY last_active_time DESC;

-- Pin a plan using SQL Plan Management
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id');

Issue 2: Value Exceeds OS Maximum I/O Size

Setting DB_FILE_MULTIBLOCK_READ_COUNT above what the OS supports does not cause errors, but Oracle silently caps the actual I/O size. The excess configuration has no effect.

Resolution: Determine the OS maximum I/O size and set the parameter to the corresponding block multiple.

-- Verify effective block read size in V$PARAMETER (Oracle may report auto-calculated value)
SELECT name, value, isdefault
FROM v$parameter
WHERE name = 'db_file_multiblock_read_count';

Issue 3: Per-Session Overrides Not Persisting for Batch Jobs

ALTER SESSION settings are lost when a session ends. Batch jobs launched from connection pools may not retain customized values.

Resolution: Include the ALTER SESSION command at the beginning of the batch script or procedure, or use a logon trigger for specific users.

-- Logon trigger for batch user
CREATE OR REPLACE TRIGGER set_batch_params
AFTER LOGON ON batch_user.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = 128';
END;
/

Related Parameters

• DB_BLOCK_SIZE — Database block size; determines the actual I/O size in bytes when combined with this parameter
• DISK_ASYNCH_IO — Enables asynchronous I/O; works alongside multiblock reads to reduce I/O wait times
• FILESYSTEMIO_OPTIONS — Configures direct and asynchronous I/O at the filesystem level
• DB_WRITER_PROCESSES — Controls write-side I/O parallelism; relevant to overall I/O subsystem sizing

Related Errors

• ORA-01652: Unable to Extend Temp Segment — Large full scans generating sort overflow; consider session-level tuning of this parameter for sort-heavy workloads
• ORA-01555: Snapshot Too Old — Long-running full scans consuming undo; increasing multiblock read size can reduce scan duration and undo consumption

Version Notes

Version	Notes
Oracle 8i	Parameter introduced; manual sizing required
Oracle 10g	Auto-tuning introduced; default changed to 0
Oracle 11g	Auto-tuning improved; Oracle calculates based on OS max I/O and block size
Oracle 12c+	PDB-level override supported; auto-tuning remains default recommendation
Oracle 19c+	No functional changes; auto-tuning (value 0) recommended for all workloads
Oracle 21c / 23ai	Behavior unchanged; parameter honored and auto-tuned as in prior releases