The db file sequential read wait event represents time spent waiting for single-block I/O operations. Unlike db file scattered read, which fetches multiple blocks into non-contiguous SGA buffers, sequential reads target one block at a time—typically into contiguous memory locations within the process workspace. It commonly arises when accessing index leaf blocks or retrieving table rows via rowid. Although predominantly single-block, multi-block sequential reads can occur in certain scenarios, such as reading datafile headers (indicated when paraemter P2 equals 1).

When an Oracle server process cannot locate a required block in the SGA, it issues a physical I/O request and waits for completion. Index lookups, undo segment access, rowid-based table fetches, control file reads, and datafile header retrievals all generate this wait type. Its presence alone does not imply a fault; moderate occurrences are normal. However, excessive time spent here may highlight inefficiencies.

Parameters

Three parameters identify the I/O operation:

• P1: Absolute file number
• P2: Starting block number within the file
• P3: Number of blocks requested (usually 1)

Querying the event definition confirms these mappings:

SELECT name, parameter1, parameter2, parameter3
FROM   v$event_name
WHERE  name = 'db file sequential read';

In classification, starting with Oracle 10g, this event belongs to the User I/O wait class:

SELECT name, wait_class
FROM   v$event_name
WHERE  name = 'db file sequential read';

Common Causes and Interpretation

High frequency of this wait often signals suboptimal join order in multi-table queries, where the driving table selection is inefficient. It may also indicate indiscriminate index usage—indexes are not universally beneficial. For large scans, full table access can outperform index traversal. In well-tuned systems, some level of db file sequential read is expected due to frequent index accesses.

Excessive waits may stem from fragmented storage layouts or partial caching of adjacent blocks, especially on heavily updated tables. Periodic reorganization or reclaiming unused space can mitigate such patterns.

Starting with Oracle 9iR2, segment-level statistics aid in pinpointing hot segments responsible for physical reads. Query V$SEGMENT_STATISTICS to identify indexes or tables with high physical read counts:

SELECT statistic#, name, sampling
FROM   v$segstat_name;

These statistics help decide whether restructuring partitions, rebuilding indexes, or altering storage parameters reduces I/O overhead.

For cost-based optimizer (CBO) environments, keeping object statistics current ensures optimal execution plans and avoids unnecessary I/O due to outdated cardinality estimates.

Diagnosing Through AWR Reports

In an Automatic Workload Repository (AWR) report, examine total and average wait times for db file sequential read. Correlate with buffer cache hit ratio—low hit ratios (below ~95%) generally lead to more physical reads and longer waits. Also review foreground wait events and per-SQL user I/O times to spot tuning opportunities.

Mitigation Strategies

• Increase the size of the buffer cache to reduce physical reads.
• Refactor SQL statements to minimize unnecessary block accesses.
• Reassess index usage; replace with full scans where appropriate.
• Address storage fragmentation through reorganization.
• Ensure timely collection of optimizer statistics.