RE: filesystemio_options setting

With parallelism the blocks are read into the PGA. Perhaps it was undersized.

Setall is going to try to use both directio and asyncio. You should verify by 
trussing the your db process that async io is working correctly.

Directio and asyncio are commonly confused. Its my understanding that the VxFS 
supports directio but not async, unless you have the ODM or QuickIO option$.

Direct i/o bypasses the filesystem cache and requires resizing of the pga and 
the sga to compensate for this.
Ansync i/o uses KAIO calls instead of pread calls to initiate asyncronous i/o. 
Even with SETALL, you are not guranteed to get async reads unless parallelism 
is invoked. Traditional single process reads will show up as "db file scattered 
reads", these won't be async. "direct path reads" are async reads. I've 
verified this with truss. With SETALL most of your writes will be async by 
default.

Some OS (such as solaris 10) mimic async i/o with a series of threaded 
syncronous reads, when the dabatase is set to use async. The db issues a KAIO 
call, the OS recoginizes this, returns a small error and then initiates the LWP 
threaded sync read calls.


Josh C.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Herring Dave - dherri
Sent: Wednesday, May 14, 2008 6:50 AM
To: Roman Podshivalov
Cc: john40855@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: filesystemio_options setting

All true, but I didn't mention (thought it wasn't to the original point) that 
nearly all objects involved in our CTAS statements have parallelism set, so no 
cache will be used, database nor filesystem, assuming DIRECTIO is set.

Maybe I'll get some time and test this thoroughly and post the results.

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: Roman Podshivalov [mailto:roman.podshivalov@xxxxxxxxx]
Sent: Wednesday, May 14, 2008 8:43 AM
To: Herring Dave - dherri
Cc: john40855@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of John Smith
Sent: Tuesday, May 13, 2008 10:19 PM
To: oracle-l@xxxxxxxxxxxxx
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


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

Other related posts: