RE: Average physical read times
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: <daniel.fink@xxxxxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Sep 2008 13:44:11 +0200
Hallo Daniel,
You can easily get the distribution of physical read times very nicely
presented, if you use orasrp (version 3 is really
very good, it has text as well as html output):
http://www.oracledba.ru/orasrp/
11ms for a single block read is OK (not the best, but bear in mind that
everything < 4-7 ms (disk "response time" is
dominated by 'average seek time', which in turn depends on the disk's RPMs) is
coming from the cache (disk cache,
storage box cache, controller cache, database cache etc). Everything depends on
the configurations of the
software/hardware stack you have. Useful URL:
http://it.toolbox.com/blogs/database-solutions/calculate-expected-iops-per-disk-22717
http://www.oracle.com/technology/products/database/asm/pdf/back%20of%20the%20env%20by%20nitin%20oow%202007.pdf
, p.19
and 21
Furthermore performance depends on where the data physically resides. A very
nice an enlightening reading about this
topic is http://oraperf.blogspot.com/2005/06/outer-edge-of-disk.html
Most common cause for varying latencies is the disk contention. You can check
this
On AIX with "nmon" tool and watch closely for disk contention and overloaded
disks (Disk%Busy > 20-25%)
HTH. Milen
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Daniel Fink
Sent: Wednesday, September 24, 2008 6:18 PM
To: oracle-l
Subject: Average physical read times
In a recent optimization effort, I found what I think are high physical
read times. For example, a single block read was averaging 11
milliseconds with a max of over 1 second (tkprof output). For this
session, single block reads consumed over 80% of the response time, so
this is of concern.
We are running Oracle 10gr1 on AIX with an IBM storage array (shared
amongst many servers). I suspect there is some optimization that can be
done at the storage array level. If 11 milliseconds is well above
average, this will help in engaging that team in the process.
For those of you running statspack. AWR or other monitoring tools, what
are the average single block read times you are encountering on a good
system? If you are running on an IBM storage array, have you found any
specific issues/fixes?
Regards,
Daniel Fink
--
Daniel Fink
OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
