Re: Inconsistent file IO times on AWR report
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Harel Safra <harel.safra@xxxxxxxxx>
- Date: Mon, 24 May 2010 07:38:02 -0400
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@xxxxxxxxx> 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
>
Other related posts:
