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. Time is reported in's of a second for Oracle8i releases and below, and's of a second for Oracle9i and above.
Most people confuse these events with each other as they think of how data is read from disk. In general, this event is indicative of disk contention on index reads. Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads. 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.
Rating 23 ratings Is this answer out of date? You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account. More to Explore Performance Get all the information about database performance in the Database Performance guide.
Questions Difference between "db file scatterd read" and "db file sequential read". Question and Answer. Regards Ravi. Excellent clarification for the confusing names! I was just wondering, when doing a full table scan, how will blocks that are buffered and potentially changed influence the multi-block read operation? Particularly, say that multi-block read count is 16, blocks 1 through 16 should be read, but block 10 is buffered - will it perhaps cause two multi-block reads, or several single-block reads?
Will that be reflected in 'db file scattered read' or 'db file sequential read'? How can the sequential read be reduced?
Rich, October 09, - pm UTC. November 30, - pm UTC. I see no contention here -- what do you mean? If you mean "how can we reduce reads" -- you do that by tuning your physical structures storing stuff so it answers your questions efficiently or tuning your SQL or not being afraid of full scans -- in short, by reducing IO. A reader, May 24, - pm UTC.
May 25, - am UTC. Scattered and Sequential read is too high.. Moiz, November 18, - am UTC. Hi tom, for last 3 to 4 days , my db file scattered read and sequential read is increasing very badly I give the detail for it..
November 18, - am UTC. May 31, - am UTC. Tom, Yes it is happening, below is for your information for above query:- bash All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9. June 02, - pm UTC. This question is vague, but the answer we are expecting is the one which you would give to beginers in Oracle. June 20, - am UTC. If it requires less resources and one of the resources it was using was IO -- you will reduce that. Look at your cache size, is it in fact sized right for you.
Although the descriptions were useful, the information on how to fix the problem was not. Why db file sequential read is index block read? Hi tom, Why event 'db file sequential read' is index block read? October 26, - pm UTC. It might be, but it does not have to be. Why need db file sequential read wait event? A reader, October 26, - pm UTC. Hi Tom, Thanks, Why oracle need single block read? If yes then how can it be possible? Note: It will be helpful if you can show us with an example.
February 06, - pm UTC. Translate into your own language. Saturday, April 30, What is db file sequential read and db file scattered read wait event and how to deal with it. The db file sequential read wait event has three parameters: file , first block , and block count. Keep the following key thoughts in mind when dealing with the db file sequential read wait event.
The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk. Significant db file sequential read wait time is most likely an application issue. Common Causes, Diagnosis, and Actions The db file sequential read wait event is initiated by SQL statements both user and recursive that perform single-block read operations against indexes, rollback or undo segments, and tables when accessed via rowid , control files and data file headers.
This wait event normally appears as one of the top five wait events, according to systemwide waits. It may not even be a bad thing if a session spends a lot of time on this event.
In contrast, it is definitely bad if a session spends a lot of time on events like enqueue or latch free. This is where this single-block read subject becomes complicated.
0コメント