Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mahendra.

Asked: November 28, 2005 - 12:46 am UTC

Last updated: November 28, 2005 - 2:01 pm UTC

Version: 920

Viewed 1000+ times

You Asked

Dear Tom

If we look at the following piece of TKPROF output, Process waited for 157.66 (Seconds?) on "db file scattered read" event. Does that mean, CPU was idle but due to inefficient SQL, Process waited so long?

What are the possible causes of "db file scattered read" wait events?

It would be immense help to me if you could explain detail steps of how this event occurs internally.

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 55156 0.79 175.66
db file sequential read 1372 0.38 8.68
log buffer space 17 1.00 7.80
log file switch completion 3 0.64 0.88
log file sync 15 1.00 9.36

Thanks & Regards,
Mahendra

and Tom said...

You waited 55,156 times for multiblock IO (large reads). The average wait time for each IO was 0.0031 seconds (not back for multi-block IO).

this event occurs when you need to read data from disk, we time how long the read call took to process.


Does not mean inefficient sql necessarily, just means the data you needed for your query was not to be found in the buffer cache so we read it from disk.

Rating

  (2 ratings)

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

Comments

wangcf, November 28, 2005 - 11:01 am UTC

in the book:Oracle.Wait.Interface.A.Practical.Guide.to.Performance.Diagnostics.and.Tuning.

"db file scattered read
The db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks. The blocks read from the datafiles are scattered into the buffer cache. These blocks need not remain contiguous in the buffer cache. The event typically occurs during full table scans or index fast full scans. The initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.

Waiting on datafile I/O completion is normal in any Oracle database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for multiblock reads is significant compared to other waits, you must investigate the reason for it."

in this title,db file scattered read is 55156,longer than others,how can i do?



Tom Kyte
November 28, 2005 - 2:01 pm UTC

no, it is not "longer" than others


55,156 is the Number of TIMES you had to do an IO.


It is your most significant wait event (175.66 seconds) - the ways to reduce it includes:

a) don't do it, find another way to get the information without reading it from disk (eg: change the physical structure, rewrite the query differently)

b) make disk faster, you took an average of 0.0031 per multi block IO, that is pretty good to begin with.

c) have more of the data cached, not always likely to be possible.


Option #1 seems most likely

Perfect!

gtcol, May 05, 2009 - 7:54 am UTC

Thanks Tom, this is just perfect!
more

Connor and Chris don't just spend all day on AskTOM. 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 channel and Chris's channel 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.