Re: Inconsistent file IO times on AWR report

From: Harel Safra <harel.safra_at_gmail.com>
Date: Wed, 5 May 2010 08:03:01 +0300
Message-ID: <-1695319330825557120_at_unknownmsgid>

In this case you're probably seeing the effect of both the OS file caching and the SAN caching.

Harel Safra
(Sent from my phone, excuse the terseness)

ב-04/05/2010, בשעה 23:17, Neil Kodner <nkodner_at_gmail.com> כתב/ה:

Solaris 9, veritas 3.5, hitachi san. Mount points look like:

/u01 on /dev/vx/dsk/rootdg/u01
read/write/setuid/delaylog/largefiles/qio/ioerror=mwdisable/dev=3d40005 on Thu Mar 18 23:25:59 2010
/u02 on /dev/vx/dsk/rootdg/u02
read/write/setuid/delaylog/largefiles/qio/ioerror=mwdisable/dev=3d40006 on Thu Mar 18 23:26:04 2010
/u03 on /dev/vx/dsk/rootdg/u03
read/write/setuid/delaylog/largefiles/qio/ioerror=mwdisable/dev=3d40007 on Thu Mar 18 23:26:09 2010

filesystemio_options = asynch

For what its worth, 32gb server, 12gb sga, 7gb pga aggregate target, we're always maxed out on RAM and swap, and our iowait, during business hours, hovers around 25-30%.

On Tue, May 4, 2010 at 2:46 PM, Harel Safra <harel.safra_at_gmail.com> wrote:

> Even if you're using direct io it still could be caching at the storage
> level.
> What is your disk/lun configuration?
>
> Harel Safra
>
>
> On 04/05/2010 16:11, Stephens, Chris wrote:
>
> Since blocks from the heaviest hit tablespace are more likely to be in
> the os-cache, that would account for Oracle thinking blocks read from that
> tablespace are faster. I guess that assumes you aren’t using direct-io.
>
>
>
> Chris
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org><oracle-l-bounce_at_freelists.org>]
> *On Behalf Of *Neil Kodner
> *Sent:* Tuesday, May 04, 2010 7:35 AM
> *To:* oracle-l-freelists; Neil Kodner
> *Subject:* Inconsistent file IO times on AWR report
>
>
>
> I'm trying to get to the bottom of some performance issues and one of the
> things that stumps me is that my AWR reports show wildly inconsistent read
> times for my tablespaces. The heaviest-hit tablespace consistently shows an
> average read time of .59ms. The others range from 13 to 25ms in avg read
> time. I dont find anything obvious when comparing matviews_index and
> matviews_data to the rest of the tablespaces when looking in
> dba_tablespaces.
>
>
>
> Given that all of the tablespaces are on the same hardware device and mount
> points, I would expect the average read times to be somewhat similar across
> the board. Is this an invalid assumption?
>
>
>
> The tablespace and file details from awr can be found at
> http://goo.gl/CTaO
>
>
>
> I'll update the document with additional information as requested.
>
>
>
> Thanks,
>
>
>
> Neil
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which
> it is addressed and may contain information that is privileged, confidential
> and exempt from disclosure under applicable law. If the reader of this
> message is not the intended recipient or the employee or agent responsible
> for delivering this message to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>

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

Received on Wed May 05 2010 - 00:03:01 CDT