RE: db_file_mutliblock_read_count and physical IO
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Aug 2004 11:58:40 -0500
In a sit-down I had with Steve Adams once, he told me that many (if not =
all)
Unix device drivers don't do multi-block fetches even on multi-block I/O
calls. He said that there's actually a rotational latency between =
adjacent
blocks in a multi-block OS read call (just not a context switch, which =
is
why an n-block read is still better than n 1-block reads). If this is =
true,
it would matter less that the Oracle kernel "reads around" blocks that =
are
already in the buffer cache.
But, since I/O processing is the root cause of far fewer Oracle =
performance
problems tan most people think, it doesn't matter that much anyway.
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...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Wednesday, August 18, 2004 10:12 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: db_file_mutliblock_read_count and physical IO
An exhaustive list of all the reasons for exceptions is likely on the =
order
of infinite.
If you can get 128 blocks in a single IO, you'll only do so when there =
are
128 continguous blocks you need, none of which are currently in Oracle
cache. I'm moderately sure that Oracle will still break up the IO =
request if
it already has one of the block you need and that Oracle has no =
evaluation
that it might be faster to take the single drink of 128 and ignore the
one(s) it doesn't need as far as LIOs go. I could imagine keeping a =
dynamic
histogram of delay and service time by number of blocks for each file =
and
scoring logical IO needs currently partially satisfied by Oracle's cache
with some sort of honeycomb scoring, but I do not believe they (or any =
other
relational systems) currently do that. That's a bit abstract. Example: =
Say
you need 127 of 128 blocks, but one near the middle you already have. It
*might* be faster in physical IO to read the 128 rather than reading 53,
skipping one and reading 74, but I don't think Oracle considers that in
servicing your block requirements. Again, I'm not certain, cause they =
keep
sneaking features in when I'm not paying attention.
Also, I've no idea what your query was that you traced. Suggested
experiment, non ASM: create a virgin table with rows designed not to =
chain
in a single extent larger than 128 blocks. Fill it up. Warm boot the
database. Select * from it under trace with your multiblock read set to =
read
a certain number of blocks, varying it from less than 128 up to 128. =
Warm
boot the database for each test.
Let us know how it works out.
mwf
PS: After the first go, all this will likely be cached somewhere in your =
OS
to disk i/o space. So if you're talking about actual physical i/o to the
disk farm, well, power the entire thing off including the disk array in
between each go. Even that might not be good enough if your disk array =
has
sneaky warm up the cache from recently used data stuff. What is possible
kinda makes yer head spin.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
ryan.gaffuri@xxxxxxxxxxx
Sent: Wednesday, August 18, 2004 10:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: db_file_mutliblock_read_count and physical IO
I did a 10046 trace and verified that I can get up to 128 blocks/IO with
db_file_multiblock_read_count.
How do I metric this? I look at my total physical IOs when I did a =
tkprof
report and my total number of physical IOs remained the same when I had =
the
value set to 8 as when I had it set to 128?
Before I did this test, my assumption was:
'Oracle would do less total Physical IOs since I am retrieving more =
blocks
per IO.'
That assumption proved false. Can someone explain why?
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
