Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jatin.

Asked: April 30, 2010 - 3:43 pm UTC

Last updated: June 25, 2011 - 11:18 am UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hi Tom

Thanks for taking the question, we rarely get a chance to see the link active :)

My question is regarding the doubt I have regarding scattered read and sequential read definition. I understand from text that all index reads are sequential and all FTS are scattered reads. Howvever, when I interpret with examples I come up with some doubt like:

1. If I have a query on a table like 'select * from t where a=10' say, and I have an index on column a. Further, the result set returns like 100 rows all from different blocks.. 100 different blocks .. will it be a sequential read? I think it should not be as there are several blocks being read into the buffer from disk..?..

2. What will be the answer to 1 if it is just 1 block containing all the 100 rows..?..

3. If I have a query on a table like 'select * from t where a=10' say, and I donot 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..?..

and Tom said...

most full table scans are scattered reads, but there can also be some sequential reads thrown in.


1) if the query plan was "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.


2) same, if the plan is index range scan, table access by index rowid. It would go a block at a time for the IO's from the buffer cache.

3) 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.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle read events

Mark Bobak, May 03, 2010 - 10:27 pm UTC

On a related note, here's a quick little paper that
talks about why serial and scattered reads are named
the way they are.

http://www.hotsos.com/e-library/abstract.php?id=16

Another Doubt..

Jatin Singh, May 04, 2010 - 12:51 pm UTC

thanks ..

1. Is there a cost comparison as to which one of two (sequential or scattered) is more expensive. Can there be a case where we find all blocks (say 1000) during a FTS in the cache except for 1 or 2 blocks - & as the multiblock read count is say 16, oracle thinks it is cheaper to do a sequential read (a block at a time) rather than reading/waiting for scattered read (as it is expensive?)?

2. What is that rare case where I can see sequential read in a FTS? I asked a related question in one of your followups where I see a delete doing a FTS but I am seeing lots of sequential read waits.
Tom Kyte
May 06, 2010 - 1:36 pm UTC

1) we use db file scattered reads when we know we are going to read more than one block. we use db file sequential reads when we know we are going to read only one block.

I'm not really sure how to "compare" them other than we use them in different places, under entirely different circumstances.


If we were decided whether to use sequential versus scattered reads given the same circumstances - it would make sense - however, since the code is like:

if (blocks to read = 1) 
then 
   read that single block
   add time to the wait event db file sequential read
elsif (blocks to read > 1 ) 
then
   read those blocks
   add time to the wait even db file scattered read
end if;



I don't see the relevance of comparing them, in fact I don't know how to compare them.


2) for that delete we addressed that - a delete will full scan the table - but still has to maintain the indexes! In order to delete a row, we modify the block in the table and we have to index range scan all of the indexes on that table one after the other in order to remove the key entries as well.


but for a normal full table scan - what if the max IO size was 32 blocks. And what if you have 33 blocks to read?

Or what if you have blocks 2, 4, 6, 8, ... (all of the even ones) in the buffer cache and start full scanning? We'll have to read the odd blocks in from disk, a block at a time.


Thanks

Jatin, May 05, 2010 - 10:43 am UTC

Thanks, I have the answer from that hotsos link and one of your followups explaining in great details.

thanks again.

sonu, June 24, 2011 - 3:32 pm UTC

Hi Tom,

Oracle use three types of read, secquetial, shattered, parallel. i understood about first two from this articals and others links. can you expalin on parallel read. what are the difference? or if you can point me any link then also fine. thanks
Tom Kyte
June 25, 2011 - 11:18 am UTC

there are three types of read WAIT events - yes:

a) db file sequential read (single block IO). this reads into the buffer cache, you do a logical IO after this read to get the block out.

b) db file scattered read (multi-block IO, we scatter the blocks in the buffer cache). this reads into the buffer cache, you do logical IO's after this read to get the blocks out.

c) direct path read (usually, but not always, done with parallel queries - can be done with serial queries these days as well). this read bypasses the buffer cache, we read from disk directly into your pga avoiding the overhead of the buffer cache.

there are two types of reads:

a) single block IO
b) multi block IO



More to Explore

Performance

Get all the information about database performance in the Database Performance guide.