Re: filesystemio_options setting

Dave,

If you set filesystemio_option to SETALL, DIRECTIO is not guaranteed because your underlaying filesystem might not support it. VxFS for example. In case of Veritas - ODM or Quick IO products have to be used to really enable direct IO. Many other filesystems have to be mounted with special options to enable direct IO. But this is completely different subject 8-)

Back to the original - once you really enabled direct IO - filesystem cache will be bypassed and many Oracle blocks read from the FS won't be cached in the memory. To mitigate that you have to grow the SGA, or buffer cache more specifically, to reserve the space for those blocks, that were previously cached in the FS cache, but not in the SGA.

BTW same stands true for databases migrated from filesystem to ASM. In many cases buffer cache/SGA needs to be expanded. This situation was discussed on multiple OOW sessions back in the days when ASM was introduced.

--romas

On 5/14/08, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
>
> We ran into a related problem on RHEL 4 and 10.2.0.2. After changing from
> ASYNCH to SETALL, after some amount of time CTAS statements were dragging
> along at 50 MB/s. After changing back to ASYNCH the same statements ran at
> 400 MB/s. Changing back again to SETALL caused those same statements, after
> running for x amount of time, to gradually drop again to 50 MB/s.
>
> I've never had time to fully investigate this, so I can't say for sure that
> SETALL was the culprit but it sure seemed that way after a few, basic
> tests. This behavior is opposite of what I would have expected, as it
> seemed that the addition of DIRECTIO seemed to slowly fill up something,
> killing throughput. Yet DIRECTIO should be bypassing filesystem cache so it
> shouldn't be affected by any memory-related setting. Trying to explain this
> now has me REALLY curious as to what was happening. Great, more work!
>
> Dave
>
>
> ___________________________________
> Dave Herring, DBA | A c x i o m M I C S / C S O
> 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of John Smith
> Sent: Tuesday, May 13, 2008 10:19 PM
> To: oracle-l_at_freelists.org
> Subject: filesystemio_options setting
>
> Hi. We are running oracle 10.2.0.3 on our test evironment in suse 9.
> Emc symmetrix storage. I was going over the settings and discovered that
> filesystemio_options was not set (no value given). I changed it to setall
> per the Oracle documentation for Linux. Shortly after that, our queries
> suddenly started running longer, and the culprit appears to be disk IO.
>
> I understood that this setting could not cause a problem, and all the
> Oracle documentation I could find said to set this value to setall in
> 10.2.0.3. I unset the value and the problem went away. Can someone shed
> some light on this?
> *************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be
> legally privileged.
>
> If the reader of this message is not 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 resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank you.
> *************************************************************************
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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