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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 18 Aug 2004 11:58:40 -0500
Message-ID: <025d01c48544$9fed5ce0$6501a8c0@CVMLAP02>


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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Wednesday, August 18, 2004 10:12 AM To: oracle-l_at_freelists.org
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_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


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 - 12:29:33 CDT

Original text of this message

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