Tuesday, May 31, 2016

Difference between scattered read and sequential read


A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. 

This is an o/s operation, most commonly used for single block reads. Single block reads are mostly commonly seen for index block access or table block access by a rowid (Eg: to access a table block after an index entry has been  seen)

This can also be a multiblock read. Then it will usually be a read from a SORT (TEMPORARY) segment as multiblock reads for table scans (and index fast full scans) usually show up as waiting on "db file scattered read"

A db file scattered read is the same type of event as "db file sequential read", except  that Oracle will read multiple data blocks. Multi-block reads are typically used on full  table scans. The name "scattered read" may seem misleading but it refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory.

if a query plan is "index range scan, table access by index rowid", it will use db file sequential reads, read index, read table block, read index, read table block - all single block IO. 

There are many blocks being read - sequentially from index to table, index to table. That is what db file sequential read 'means', sequentially from index to table. db file sequential read is the wait even for SINGLE BLOCK IO. A block at a time, block by block, from index to table. 


If We have a query on a table like 'select * from t where a=10', and we don't have an index on column a. Further, the FTS resulting from this query just returns 1 row.. is it a scattered read because all the blocks were scanned & read into the buffer to find that 1 block..?.. 

It would probably employ multiblock reads - which if done using physical IO would wait on db file scattered reads - meaning read a bunch of blocks and SCATTER them in the buffer cache.

Source: Ask Tom Site of Oracle

No comments: