Re: DB File Sequential Read Waits

"rjpfal" <ralbertson_at_comcast.net> wrote in message news:2932a99f.0312311154.7117ef9b_at_posting.google.com...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bsurb2$hbs$3$8302bc10_at_news.demon.co.uk>...

> >
> > You won't gain "directly" from striping. if all you do is
> > single block access. But in cases like this, the purpose of
> > striping is not to speed up the individual read, but to
> > scatter the reads as evenly as possible across the
> > available and avoid queues. It's a statistical game,
> > of course, and you may get unlucky, but it does tend
> > to avoid hot spots.

Got it. Then in this case perheaps striping the data only over a larger number of drives or even using more than one f/s (spread partitions) might improve matters, rather than trying to stripe the ENTIRE database?

> The statspack also said 213 reads per second - which is about
> 35 per second per disc - which is poor but not a total surprise.

That's about par for max requests on a single non-cached disk: around 40 requests per second (or roughly 25ms/request).

Anything above that is usually hard to get due to delays along the chain of f/s layer <=> OS I/O handlers <=> hardware controller <=> cabling <=> disk.
Particularly when there is a disk farm involved: they have long internal access paths for random single block access.

> I know by shutting down the instance it is not totally realistic but
> given the radomness of the data I wanted to see what it does when
> forced to do disk IO.

Note that you may also be by-passing any cache on the disk farm. In fact given your average single disk access times, I'd say you are.

> What I am seeing is alot of buffers read for equal number of rows and
> in some cases more buffers than rows.

The buffers read = rows is OK if you re-started the db and your test access is quite random. If you were accessing data already in cache, then you'd get less physical I/O and if the rows accessed were in the same block then the buffers accessed would drop dramatically. The more buffers than rows would be a consequence I think, of the index access.

> Could the global partition be the issue and when all customer tickets
> are retrieved it is hitting more than 1 partition which all reside
> under /u02?

I'd say so.

> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.13 0.12 2 197 0
> 0

Really don't like this. It's that parsing thing again...

> Also out of curiosity I ran same test but against all 8 CPU's rather
> than a dedicated 2. What is of interest is that the total elapsed time
> was greater when using all 8 rather than 2:

Dunno about this one.

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