RE: db_file_mutliblock_read_count and physical IO

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 18 Aug 2004 11:54:33 -0500
Message-ID: <025a01c48544$0ce5beb0$6501a8c0@CVMLAP02>

I think it's important to realize that even the Oracle Reference manual = is
subtly wrong in two ways.

WRONG: The total number of data blocks read from disk.

RIGHT: The total number of Oracle blocks obtained via OS read calls.

The errors:

It's not just data blocks. It's index blocks, undo blocks, and so on. It's Oracle blocks.
The phrase "read from disk" is incorrect. It's "obtained via OS read calls." There's no way for the Oracle DBMS (just an app atop an OS) to = know whether the OS read call is actually reading from disk, or from the OS buffer cache, or a controller cache, etc. /All/ Oracle knows is that the blocks were obtained via OS read calls. There's no way for the Oracle = kernel to know whether the blocks obtain were really obtained from a physical device or not.

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 Bobak, Mark
Sent: Wednesday, August 18, 2004 10:12 AM To: oracle-l_at_freelists.org
Subject: RE: db_file_mutliblock_read_count and physical IO

Ryan,

The problem is that the statistic you're looking at (physical reads) =3D does not mean what you think it means. Look at the definition of the =
=3D

'physical reads' statistic in the Oracle Reference manual (under =3D Statistics Descriptions). It is:
"Total number of data blocks read from disk. This number equals the =3D value of "physical reads direct" plus all reads into buffer cache."

So, it's the total number of data blocks read from disk. It's NOT the =
=3D

number of db file scattered read (i.e. readv()) calls. So, by varying =
=3D

db_file_multiblock_read_count, you'll vary the number of readv() calls =
=3D

and the number of blocks read per call, but you will NOT affect the =3D total number of blocks read. Note that reducing the number of readv() =
=3D

calls reduces the number of context switches and is a good thing.

Hope that helps,

-Mark

PS For more info on read() and readv(), and setting =3D db_file_multiblock_read_count, go to http://www.hotsos.com and check out =
=3D

"Why are Oracle's read events named backwards?" and "Predicting =3D multi-block read call sizes", both by Jeff Holt.

-----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 =
=3D

db_file_multiblock_read_count.
How do I metric this? I look at my total physical IOs when I did a =3D tkprof report and my total number of physical IOs remained the same when =
=3D

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 =3D blocks per IO.'
That assumption proved false. Can someone explain why?=3D20

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:47:24 CDT