RE: db_file_mutliblock_read_count and physical IO
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Aug 2004 11:54:33 -0500
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:
1. It's not just data blocks. It's index blocks, undo blocks, and so on.
It's Oracle blocks.
2. 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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bobak, Mark
Sent: Wednesday, August 18, 2004 10:12 AM
To: oracle-l@xxxxxxxxxxxxx
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@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 =
=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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
