Re: Using Direct I/O Filesystem for Oracle Database

From: David Miller <David.J.Miller_at_Sun.COM>
Date: Tue, 15 Jan 2008 13:25:00 -0600
Message-id: <478D088C.3040903@Sun.COM>

Hi Muhammad Salman Faheem,

You should be getting directio with the setup you have using just "setall". You can verify this by doing "truss -f" on the startup call and looking at the calls mentioned on the website, i.e. the open, directio and ioctl. That's really the only way to do it. There currently is no Solaris utility to display the status of a file (that I know of).

If you do truss, you'll want to carefully match the file descriptor number returned by open() with the file descriptor passed to directio() or ioctl() to be sure the calls are happening on the appropriate files.

There were some versions of Oracle and versions of Solaris where "setall" was not sufficient, but they were older versions. With 10g R1 and Solaris 9 9/05, that shouldn't be the case, so you should be fine.

But as other posters have mentioned, just using directio may or may not affect your overall performance. You'll need to monitor the application to see how it responds.

And as I mentioned, if you do use directio, you may need to increase the data buffers in the SGA since you will not be buffering data in the filesystem cache any more and so may need to cache them in the SGA. Regards,

Dave Miller

Junior DBA wrote, On 01/14/08 23:54:
> BismillahirRehmanirRahim
> Hi David Miller,
> The output i pasted in my last email was taken from someone posted ,
> and i gave the link to that post. so i just wanted to know the
> authencity of the claimed the poster made that "Oracle still dont
> use direct I/O with filesystemoption_io=setall."
> Our Filesystem is ufs, we are running Oracle 10g R1 on Sun Sparc V890
> Solaris 2.9.
> After doing little research on Direct I/O i found out that
> filesystemoption_io=setall is only required thing to enable oracle to
> use Direct I/O for datafile Access. [ you also suggested that ]
> And there is no need to mount Datafiles ufs filesystem with
> forcedirectio option.
> Infact our Vendor Recommended to mount Oracle Datafiles ufs Filesystems
> with
> forcedirectio, whereas i decided to handle direct I/O at Oracle Level.
> In my understanding its more efficient and appropriate to use
> filesystemoption_io=setall than to mount whole filesystem with
> forcedirectio, as it will do direct I/O on per datafile basis instead of
> per filesystem basis [ as in case of forcedirectio mount option.]
> Moreover with filesystemio_options=setall , the backups and other
> command [ e.g. cp , mv , compress ] would still use buffered I/O and
> wont get slower.
> I need experts comments on using filesystemio_options instead of
> forcedirectio mount option.
> also i need to know how can ensure that Oracle is using Direct I/O with
> filesystemio_options=setall ,
> and how can i compare the I/O performance with
> filesystemio_options=setall and with forcedirectio mount option ? which
> is recommended .
> the output of /etc/release is pasted below.
> Thanks for your support & time.
> Your DBA Colleague
> Muhammad Salman Faheem
> [oracle_at_sun:ORCL oracle]$ more /etc/release
> Solaris 9 9/05 s9s_u8wos_05 SPARC
> Copyright 2005 Sun Microsystems, Inc. All Rights Reserved.
> Use is subject to license terms.
> Assembled 04 August 2005

Received on Tue Jan 15 2008 - 13:25:00 CST

Original text of this message