Re: Inconsistent file IO times on AWR report
Date: Mon, 24 May 2010 07:38:02 -0400
Message-ID: <AANLkTild5MTCMjzgq8Apw_tSj_PqoVK7Fj1PaHBkuOcr_at_mail.gmail.com>
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 - 06:38:02 CDT