Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_mutliblock_read_count and physical IO

RE: db_file_mutliblock_read_count and physical IO

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 18 Aug 2004 11:12:12 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKOEJNFDAA.mwf@rsiz.com>


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?



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


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 Wed Aug 18 2004 - 10:34:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US