RE: db_file_mutliblock_read_count and physical IO

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 18 Aug 2004 11:12:12 -0400

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

Other related posts: