Re: Inconsistent file IO times on AWR report
Date: Mon, 24 May 2010 09:36:59 -0400
Message-ID: <AANLkTinfND71Dswo0yG6yIfWcqIVIMmKnTcFOXmkXrgJ_at_mail.gmail.com>
We were memory-starved to begin with, 32gb box with about 2000 sessions. SGA Max size is 12G. As the users connect, we typically run down to about 500 free mb by the end of the business day.
Our rationale for enabling directio was that it would free up physical RAM and let Oracle's buffer cache be the sole source for cached data.
Unfortunately, as this is the only system in our shop that uses veritas, we didn't have a non-production system to dry this out on-yes, i know, all of our other systems are running a different filesystem, ZFS, and this is the only machine that hasn't been migrated.
But still even if we stopped using the filesystem IO cache, That alone shouldn't be cause of additional physical IO operations as far as the tkprof reports, should it? Are you saying that you'd expect data to age out of our buffer cache faster since enabling directio? If it helps, this is running during a time where little to no other activity is going on.
Here are before-and-after TKPROF for two of the statements. http://dpaste.org/iqYv/
On Mon, May 24, 2010 at 9:18 AM, John Gardner <jgardner100_at_gmail.com> wrote:
>
> With directio you stop using the OS buffer cache, hence you will reduce the
> memory demand for os buffers (ie you should see more memory either free or
> being used to buffer other files.) If your instance was short of memory for
> the block buffer cache though, you will see more io as the instance has to
> recycle them more often. If you don't use directio, you're letting the os
> manage the memory between processes, other file accesses and the instance's
> io. If you do use directio then you are saying that you will reserve all the
> memory needed for the instance yourself.
>
> My past experience has been that if the instance is correctly sized, you
> get a moderate improvement on io speed. If your instance is short on memory
> then your performance really goes south by enabling directio, so definitely
> test before going to production.
>
> Regards
> John Gardner
>
> On 24/05/2010, at 9:38 PM, Neil Kodner wrote:
>
> We enabled directIO and set filesystemIO_options to setall last week. Our
> first real test was this morning and our main batch jobs ran slower. A few
> things noticed was that there was more physical IO, and by looking at the
> tkprof (and soon 10046), the cardinality numbers on the explain plans for
> nearly all statements were much higher. The same plans were used but the
> cardinality numbers on the tkprof were much, much higher.
>
> I feel that this issue relates to optimizer stats and not a direct result
> of the parameter change but am not 100% sure. We haven't changed the way we
> analyze our tables in weeks.
>
> Does enabling directIO and changing filesystemIO_options warrant either
> changing our stats gather process or re-gathering new system stats? Since
> the change, we've noticed more available physical memory, which is great,
> but have seen a handful of bad execution plans ever since the change
>
> On Wed, May 5, 2010 at 1:10 PM, Harel Safra <harel.safra_at_gmail.com> wrote:
>
>>
>> On 05/05/2010 16:52, Neil Kodner wrote:
>>
>>> 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?
>>>
>> I'm not sure about Solaris, in linux you'd use the /proc/meminfo file to
>> find out.
>>
>>
>>> Are there any drawbacks to enabling directio on our oracle data volumes?
>>> This is a pure OLTP system with some overnight batch jobs.
>>>
>> As always, it depends. You'd be loosing the FS cache and should take that
>> into consideration while sizing the SGA.
>> In addition the FS cache can cache stuff that Oracle won't cache, like
>> direct path physical reads.
>>
>>
>>> Is directio appropriate for data warehouses as well?
>>>
>> See above.
>>
>> Harel Safra
>>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 24 2010 - 08:36:59 CDT