Re: DB File Sequential Read Waits

"rjpfal" <ralbertson_at_comcast.net> wrote in message news:2932a99f.0312300754.5af48627_at_posting.google.com...
> srivenu_at_hotmail.com (srivenu) wrote in message
news:<1a68177.0312292314.71fc02e6_at_posting.google.com>...
> > In DB File Sequential Read Waits the thing to look for is the average
> > wait. In your case it seems a bit high at 39ms. I have seen an average
> > of upto 8 ms on some boxes.
>
> This is what I thought also. I am trying to reduce IO and improve data
> availability in the buffers to avoid the io to disk.

Hi

Improving the data availability (by which I assume you mean increasing the buffer cache hit ratio) might help but it looks to me (as with the others) like you have IO contention.

I see that you have split data and index tablespaces across separate volumes, but that the data tablespaces on u02 all experience these 40ms avg wait for db file sequential read. The figures for the other tablespaces seem much more reasonable, except that you are getting almost no IO at all against INDX. The tablespace names also are suggestive of partitioning.

So I'd look first at IO (as Jonathan points out it eats most of your time up) and specifically at u02 and how many processes are simultaneously requesting data from it. If you do use partitioning are you also querying the partitions in parallel, but the partitions are all on the same volume?

If you can trace an example session you *may* see similar behaviour to that exhibited by the system that gave my trace file in the buffer cache hit ratio thread. Here I had an avg IO wait of 25ms, but averages hide detail - extracting the waits from the trace file showed that we were waiting up to 1.5 SECONDS for a single block IO. The cause was of course IO contention. The advantage of tracing is that it can show you what statements are causing the waits and which files and blocks they wait on for how long.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK