RE: Average physical read times

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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/

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 25 2008 - 06:44:11 CDT