Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Sal L" <member46676_at_dbforums.com> wrote in message
news:3549915.1067753041_at_dbforums.com...
>
> As Jonathan Lewis wrote regarding Symmetrix:
> "rate of 10 ms per read is pretty reasonable
> for random, non-cached I/O, unless you happen
> to have the newest fastest discs on the market"
>
>
> Well, but VC got the following numbers on his home PC with IDE disk:
> call count cpu elapsed disk query current
> Fetch 2 0.27 0.54 375 376 0
>
>
>
> Based upon method of Jonathan Lewis the rate of IDE disk is 1.44 ms
per
> read.
>
> Who can explain me why IDE disk in 7 times faster than Symmetrix ?
>
Good question - aand one which highlights the importance
of careful testing and even more careful examination of the
results. I don't have the eariler posts any more, so can't
guarantee that the following comments are correct; but they
should give some indication of the background thinking.
a) 254 physical reads, 750-ish logical I/Os - the stats from
the original post look like indexed access, where the index
gets two buffer visits, and the table gets a physical read.
Assume the poster is using a Symmetrix because they
have a very large data set (I think 30M rows may have been
mentioned). The output is consistent with the idea of extremely
scattered single block random reads - and I based my comments
on that assumption
b) VC - 2 fetches, no rows, 375 disk reads, 376 logical I/Os.
When reads = logicals, then I tend to assumed multiblock
reads (table scan or index fast full scan). In this case, the
number of reads might well be done 8 blocks at a time, so
the read request count is actually 375/8 = 47 rather than
375 - which gives a read request time of about 11.4 ms,
which is back to 'reasonable'. This assumes, of course,
that an Oracle multi-block read is 8 blocks, and that the
Oracle 8 block read-request turns into some form of real
disk request that reads a contiguous stream of sectors
from the disk.
Option (B) to explain the VC figures is to ask if VC built
a test case with a similar number of rows (30M?) with a
similar row size, index type, and data scatter. It is
possible that the PC test case left all the data in the file
system buffer immediately after creation, even when it
wasn't in the Oracle buffer. Even if it wasn't buffered, it
could be that the nature of the data, setup, and hardware
resulted in the hardware doing (say) a 64K read-ahead on
the first read, so that 7 out of every 8 blocks that Oracle
wanted had been pre-read with only a small amount of
lost time on the 1st of each 8.
> And what is magic value of 10 ms for disk (may be this one is
defined by
> disk seek time) ?
Yes - typical manufacturers claims tend to be given
as something around 100 - 120 I/Os per second (possibly
a little higher, I haven't checked closely for some time).
A "real" read time requires:
time for the heads to move across the disc to the
right track, and stabilise there.
time for the disc to rotate so that the correct starting
sector is under the heads.
time for the disc to rotate far enough for all the required
sectors to be read
The frst two are typically a small number of milliseconds
each, the third can vary qiute a bit depending on whether
you are doing a single (Oracle) block read, or a 'lucky'
multi-block read.
>
Received on Sun Nov 02 2003 - 08:59:56 CST
