RE: db_file_mutliblock_read_count and physical IO
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Aug 2004 02:49:08 -0400
... and you said Steve was referencing UNIX, while Mladen referenced Linux.
Since Linux was from the ground up scratch in order to be sure it was not
someone else's intellectual property, certainly any overlap is coincidental.
(Wink.)
... and even off the shelf JBOD from office supply comes with 4 to 16 MB of
cache on the physical disk now, so they actually masquerade as ide or ata or
scsi specs they think will look sane to operating systems and make use of
the cache (as Mladen alluded to in the submitted code) on the hardware side
of the equation in proprietary fashion likely to be based on heuristics to
publish the minimum average seek time and the maximum MTBF for the device.
... and the OP got the info to rely on p3 of the read to see how many Oracle
blocks Oracle would be requesting, while the tkprof and other aggregates of
blocks read were as Cary described (not the number of OS read calls) so that
number will not correlate with the average p3 requested (which I think was
his primary mystery solved. He also noted this is for general understanding,
not specific tuning.)
Now on the hardware side, I certainly *hope* they understand their own
internal geometry so they do either constant or skip sector read and write
operations when they fill or drain drive cache. That is, the rotational
latency is built into the microcode on the hardware side such that
"contiguous blocks" are continguous in the sense that it's the next one (or
multiblock set) you can read on the same lap. Maybe not. The last device
driver I personally wrote was 8.5 inch floppies (fixed sectors with strobe
holes). If you read or wrote alternate sectors that was just right so you
could read or write a track in just over 2 laps. If you tried to write
sectors 1, 2, and 3 in a row, that would be a little over 2 laps right
there. Sigh. Even SSD can masquerade as ATA, PCI, or SCSI now, and there is
neither seek nor rotation, unless you want to count the address math time as
the equivalent sum.
Finally, I certainly agree that the vast preponderance of performance
disasters in the field are rooted in bad design, bad code, and bad plans.
Then the question becomes whether it is effort (ie. money) well spent to
make the bad run as fast as it can while it is awaiting revision. The answer
depends on the situation, but even when the answer is yes the solution is
logically a stop-gap and certainly is subject to that rule about not being
able to speed up a process by optimizing i/o more than the time it is
spending doing i/o (unless, I suppose, you get i/o so fast it causes a time
warp, but I think that's science fiction, right?)
If the disk farm set up is reasonably sane, that is probably good enough
most of the time. Only when the design is good, the code is good, the plans
are good, and you've got a huge bulk of data to process does optimization of
physical i/o move to center stage. I do see it though. Those extra context
switches might add up and I just love to give Oracle lurkers something to
think about: Hmm, might it actually speed things up to multi-block bigger
and ignore a few blocks we already have? Can we dynamically sample to figure
that out (hey if they can tune it automatically, it's gotta go in there.)
cool thread. hope I dint ruin it.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap
Sent: Wednesday, August 18, 2004 11:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: db_file_mutliblock_read_count and physical IO
Very nice, Mladen, thank you.
> Steve's explanation is almost correct. Almost, but not completely.
I reserve the right to have mis-communicated what Steve had told me. So,
rather than saying that Steve is wrong, let's say more formally that =
"Cary's
recollection of Steve's explanation is almost correct."
:)
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New =
Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
