| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_mutliblock_read_count and physical IO
... 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
Sent: Wednesday, August 18, 2004 11:12 PM
To: oracle-l_at_freelists.org
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...
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Aug 19 2004 - 01:45:22 CDT
 |
 |