Re: Inconsistent file IO times on AWR report

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Harel Safra <harel.safra@xxxxxxxxx>
  • Date: Wed, 5 May 2010 09:52:46 -0400

So lets say that we're not currently using directio - is there a way to find
out how much of our physical RAM is being used to cache filesystem data?

Are there any drawbacks to enabling directio on our oracle data volumes?
 This is a pure OLTP system with some overnight batch jobs.

Is directio appropriate for data warehouses as well?

On Wed, May 5, 2010 at 1:03 AM, Harel Safra <harel.safra@xxxxxxxxx> wrote:

> 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@xxxxxxxxx> כתב/ה:
>
> 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@xxxxxxxxx>
> harel.safra@xxxxxxxxx> 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@xxxxxxxxxxxxx>oracle-l-bounce@xxxxxxxxxxxxx 
>> [<oracle-l-bounce@xxxxxxxxxxxxx>
>> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *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>
>> 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.
>>
>>
>>
>

Other related posts: