Re: Average physical read times
From: LS Cheng <exriscer_at_gmail.com>
Date: Thu, 25 Sep 2008 14:04:32 +0200
Message-ID: <6e9345580809250504k59b2c4efx1a6c6fe00a00549a@mail.gmail.com>
Date: Thu, 25 Sep 2008 14:04:32 +0200
Message-ID: <6e9345580809250504k59b2c4efx1a6c6fe00a00549a@mail.gmail.com>
Hi
IMHO average single block read time should be around between 4ms and 8ms considering a 15K RPM SCSI Disk seeks time is around 4ms. Anything above that doesnt sound too good for me. Lower sounds reading cache (either filesystem or array).
Say the single block read of 11ms then for a query which needs over 10000 physical reads it will takes around 2 minutes. Query which reads 10000 physical reads isnt that unusual.
Thanks
-- LSC On Wed, Sep 24, 2008 at 6:17 PM, Daniel Fink <daniel.fink_at_optimaldba.com>wrote:Received on Thu Sep 25 2008 - 07:04:32 CDT
> 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