Re: Multi-Block read count

Svend Jensen <Master_at_OracleCare.Com> wrote in message news:<3C3E0203.6070501_at_OracleCare.Com>...
> Norman Dunbar wrote:
>
> > Morning all,
> >
> > my first question of 2002 ...
> >
> > Quote from Guy Harrison in Oracle SQL High-Performance Tuning :
> >
> > "On Windows NT/Win2k the maximum number of blocks that can be read
> > cannot exceed 128K for 32 bit versions,
> > On most versions of Unix when the I/O is through a filesystem, no more
> > than 8K can be read in one physical I/O,
> > On Unix when the datafiles are locvated on raw devices, the maximum I/O
> > rate varies from 64K (earlier versions of Solaris) to 1MB (latest
> > version of HP-UX."
> >
> > So the question I have is, how do I find out what the maximum number of
> > Oracle blocks that I can read in one single OS I/O read is ?
> >
> > Regards,
> > Norman.
> >
> > ------------------------------------------------------------------------
> > -----
> > Norman Dunbar EMail: Norman.Dunbar_at_LFS.co.uk
> > Database/Unix administrator Phone: 0113 289 6265
> > Fax: 0113 289 3146
> > Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
> > ------------------------------------------------------------------------
> > -----
> >
> >
>
> The trick with show parameter is nice, but I cant believe the figures I
> get. At work on a IBM e-server 250 Win2K Oracle 8.1.7EE with hardware
> 4M raid controler and seven mirrors striped, I've set dfmrc to 32384,
> set event 10046, level 12 and ran a large full scans. Acording to the
> trace file 'scattered read' p3 (blocks) never got any higher than 128.
> At 8k block that makes 1M.
> Tried the show parameter at my home system, win2K, Oracle 8.1.7EE and I
> got 256. Havent tried the trace yet.
> But same OS, oracle version and different results - better on ide disk
> than 160scsi. Hard to believe.

Idly wondering if a bunch of 128's through a 4M raid controller might be pushed faster than a 256 through an IDE.

But with the mystery of Win2K, nothing would surprise me.

And with a system doing a bunch of things besides benchmarking a scan of one table, it may not make a difference even if the ide can scan bigger chunks. It may be very easy to design a benchmark that works against a piece of hardware specialized for grabbing a bunch of different things off an array of disks in the quickest order. Parallelizing a bunch of slow things can be faster than having a serial fast thing.

This is still all very interesting. Perhaps it might be even more so if in these heterogenous tests the time to scan the table is posted. And the time to update the table.

>
> A way to make sure, restart database, set high dfmrc, run some large
> full scans and then check v$filestat against some disk io OS monitor.
> If the figures are close to one another, then the requested block reads
> from oracle to the os-layer is OK. If it is in the nabourhood of 1:n,
> then one oracle request for x blocks is turned into n times x OS disk io's.
>
> I tried that on Sun solaris 6 some time ago. Solaris read only 7 blocks
> of 8k in one io cycle. With default setup, no fiddling.
>
>
> Somebody got a tool for win2k to measure OS disk io's like iostat?
>
> /Svend Jensen

jg

--