Re: DB File Sequential Read Waits

"rjpfal" <ralbertson_at_comcast.net> wrote in message news:2932a99f.0312310612.57228367_at_posting.google.com...

> The space taken up on /u08 are old archive logs that had come across
> from the primary before I activated my current test environment. It
> was 1 of 2 standby's we had setup.

No worries then.

> > The other thing is: if it's separate volumes on the EMC box
> > for each f/s, then how come the device name above is the same for all
> > f/s? Is this a result of Veritas? I don't recall seeing this when I
> > used EMCs. I wasn't using Veritas.
>
> Here I am unforunately relying on the admins but yes it would be a
> result of VERITAS volume manager I beleive. The admins have provided a
> breakout of the individual 8G hyper volumes to be and how they are
> spread across many physical disk to account for the space required for
> my data and indexes.

Hmm, have a look yourself, just in case. Don't automatically trust all the admins say... Another thing I'd check: I can't remember from your prior posts if you have any control files on the f/s where data and index are stored. If so, move them out of there. Same if there is one on your undo or temp f/s. Control files do I/O at the strangest of times and they can interfere with other I/O going on.

The I/O times you get are consistent with single disk physical I/O I've seen in EMC hardware. When the cache is not in action. Ie, when the I/O is of a random nature that does not benefit from any intelligent cacheing the disk farm hardware might want to do. That links in with my points below.

>
> Yes this seems to be my bottleneck and with striped volumes I thought
> it would have been much less. However same test run againt production
> produced an average wait of 34ms so it is not that far off. Production
> is concatenated volumes.
> snip
> I have been reviewing explain plans on all the SQL and everything is
> low cost, few rows being returned. i.e. indexes being used.

Well, here is possibly why having data striped is not gonna help. If your queries are of random nature and the access is very few blocks, mostly via index, then what you are doing for all intents and purposes is direct access reads. In which case the nature of striping in speeding up consecutive block access is not gonna make much difference.

I'd say this explains why you don't see that much diff between your production and test numbers: given similar RANDOM access query workloads, you wouldn't really gain much from striping.

Jonathan might want to confirm this or not, but I reckon striping does help mostly when you have to access consecutive (adjacent) blocks in a single read request. Which is the case in FTS, or in data range scans (if the data is clustered), or in index range scans.

Single index access will gain little benefit from striping. You'd have to simulate a very large user load to really see a benefit, I reckon.

You gain most from striping when you're doing FTS access, or in sequential write access like the one performed in the redo logs.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam