Interpretating ela= values for "db file sequential read" in trace files

Hi all,

I'm in the process of trying to chase down a disk i/o problem with our Oracle 9 instance on Solaris 8, and I've been looking at the 10046 trace files for the relevant sessions. One thing that we've discovered is that our critical processes are running much faster when we are setting DISK_ASYNCH_IO is set to FALSE, at least according to the trace files.

Specifically, we have times when some of our select statements have
"db file sequential read" times exceeding 4 seconds (with asynch i/o
turned on), but the highest we ever get when it is turned off is 40 milliseconds.

What I'm wonder is: could this be an illusion? I'm reading Millsap's
"Optimizing Oracle Performance", and one thing I'm unsure about: how
can the wait times reported for "db file sequential read" be cumulative if you are running with asynch_io turned on? In other words, if you had a statement that made 100 dfsr requests and did them all at once, it would then proceed to wait as each one came back. If the average time to service a request was 20 ms, then you'd probably see one that waited 20, one that waited 40, one that waited 60 & so on, up to one that waited 2000 ms.

But if you were running with asynch turned off, you'd just see 100 requests each taking 20. This would make the asynch reporting seem slower, when in reality they took the same amount of time (or the asynch might be even faster).

Is there something about the reporting of disk i/o that I'm not understanding when it comes to these files?

-Steve Received on Fri Nov 19 2004 - 18:02:10 CST