Sequential Read and Scatter Read

A db file sequential read is a single-block read and db file scatter read is a multi block read.

db file sequential read - A single-block read (i.e., index fetch by ROWID) 
db file scatter read       - A multi block read (a full-table scan, OPQ, sorting)

The db file sequential read event signifies that the user process is reading the buffers into SGA(Database buffer cache) and is waiting for physical I/O to return or complete. It reads the blocks in to contiguous memory space and these single block reads or I/Os uses indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.

Starting with Oracle 10g R2, Oracle recommends to not to set db_file_multiblock_read_count parameters that allowing oracle to empirically determine the optimal setting.


The db file sequential read has 3 parameters.

1. file#,
2. first block#,
3. block count.

In 10g, Wait event falls under User I/O wait class.
Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level. solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.

e.g.

Top 5 Timed Events

                                                           % Total

Event                        Waits         Time (s)       Ela Time

--------------------------- ------------ -----------      --------

db file sequential read       2,598          7,146          48.54

db file scattered read       25,519          3,246          22.04

library cache load lock         673          1,363           9.26

CPU time                                     1,154           7.83

log file parallel write      19,157            837           5.68

From the above e.g. reads and a write constitute the majority of the total database time. In this case, We need to increase database buffer cache (db_cache_size) or tune the SQL or Invest amount in having faster disk (SSD) I/O sub system.

Script to measure disk I/O cost of db file sequential read.

col c1 heading 'Average Waits|forFull| Scan Read I/O'        format 9999.999

col c2 heading 'Average Waits|for Index|Read I/O'            format 9999.999

col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99

col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99

col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

 

 

select

   a.average_wait                                  c1,

   b.average_wait                                  c2,

   a.total_waits /(a.total_waits + b.total_waits)  c3,

   b.total_waits /(a.total_waits + b.total_waits)  c4,

   (b.average_wait / a.average_wait)*100           c5

from

  v$system_event  a,

  v$system_event  b

where

   a.event = 'db file scattered read'

and

   b.event = 'db file sequential read';

Scattered reads and full-table scans

Full table scans are not necessarily a detriment to performance and is a fastest way to access the table rows. The CBO choices to perform FTS depends on OPQ, db_block_size and clustering_factor, estimated % of rows determined by the query and other factors. Once of CBO chooses to perform FTS, The speed of performing FTS (SOFTS) depends on internal and external factors.

1. The number of CPUs on the system
2. The setting for Oracle Parallel Query (parallel hints, alter table)
3. Table partitioning
4. The speed of the disk I/O subsystem

With all the above factors it may be impossible to find best setting for optimizer_index_cost_adj parameter. In the real world the decision to perform FTS depends on below factors.

1. Free blocks available in database buffer cache.
2. Free space in temp as if query having order by clause
3. Current demands on CPU.

Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.