| 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
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
ryan.gaffuri_at_comcast.net
Sent: Wednesday, August 18, 2004 10:25 AM
To: oracle-l_at_freelists.org
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?
 |
 |