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.
> >
> >
>
> 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.
>
> 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

Svend, the 56K IO you found for Solaris is exactly what Ahmed Alomari said was the largest IO Solaris performs in his book Oracle & UNIX Performance Tuning from Prentice Hall. I bring this up only because I though it was a pretty good book and it spent a lot of time discussing IO options. But with version 9 it may well be a little dated.

Considering how hard it is to get this information from your System Administrator, the Hardware vendor, or Oracle I think that if you have an OLTP that because the size of the MBRC affects the CBO that it is best to choose 64K for this setting. Larger settings cause the CBO to underweigh indexes in favor of full table scans; this comment was based on information in the student manual for Oracle Performance and Tuning for version 8. Obviously a bigger setting would be in order for a warehouse.

I find it amazing how complex it can be to determine the right value for a simple setting with Oracle.

• Mark D Powell --