Re: Using Direct I/O Filesystem for Oracle Database

BismillahirRehmanirRahim

Hi David Miller,

Thanks for the detail reply.

During my search i found following link :

 [ first flush the buffer cache ]
sys_at_xxxxxxxxx> alter system flush buffer_cache;  

System altered.  

[ Then issue (as user oracle) ]
oracle_at_ironman:~$ truss -f -t open,ioctl -u ':directio' sqlplus user/ pass  

.... (lots of output here)  

[ Then type ]
create table a as select * from big_table;  

You will see that for every data file opened, directio is explicitly turned off:  

24399:  open("/u04/oradata/BIA/APM_DATA13.dbf", O_RDWR|O_DSYNC) = 11
24399:  -> libc:directio(0x10f, 0x0, 0x1, 0x0)
24399:  ioctl(271, 0x2000664C, 0x00000000)              = 0
 

 The 3rd parameter to the ioctl() call is 0 for directio_off, and 1 for directio_on.

Please comment on above post.

Also i have following Queries:

(*) How to confirm [any script/command/procedure] that when filesystemio_option=setall then Oracle uses Direct I/O when opening Datafiles.

Best Regards
Your DBA Colleague
Muhammad Salman Faheem  

David Miller <David.J.Miller_at_Sun.COM> wrote: Hi,

You should only need to modify filesystemio_options to setall. That will use directio where possible without requiring it on the mount options. In fact it's best to not put it in the mount options, because then activities like backups or redo log archiving does not have to use directio and can benefit from the filesystem readahead.

One change you may need to look at, however, is increasing the SGA since you no longer will have the filesystem buffering blocks, so you'll need to increase the size of the SGA so Oracle can buffer more blocks there.

You do not need to change dbwr_io_slaves or db_writer_processes and leaving them just as default may be best, unless you have a specific reason to change them.

If you are still seeing Disk I/O performance issues, make sure you have enough physical disk spindles to handle the I/O you're trying to do. directio can help get around some filesystem limitations (like the single-writer lock required by Posix) but won't make the disks spin faster or handle more I/O. So if that's your problem, you'll need to find a way to add more spindles to the Oracle objects that need more I/O.

Regards,

Dave Miller

Junior DBA wrote, On 01/08/08 23:41:
> BismillahirRehmanirRahim
>
> Hi All,
>
> We are running Oracle 10g R1 on Sun SPARC Solaris 9 Sun Fire V890 Server.
>
> By default we have following setting in the Database:
>
> disk_asynch_io = TRUE
> filesystemio_options= asynch &
> db_writer_processes=1
> dbwr_io_slaves =0
>
> To improve Disk I/O Performance We re-mount Oracle Database FileSystems
> [ containing Datafiles,logfiles & archive logs] with
> forcedirectio,noatime option.
>
> After remounting the Oracle Filesystems with forcedirectio,noatime i
> changed the filesystemio_options to setall i.e.
>
> filesystemio_options=setall
>
> (1) I need to know if i missed something or made any mistake.
>
> (2) Do i need to change dbwr_io_slaves or db_writer_processes values
>
> (3) What else i could do to improve DISK I/O performance
>
> Appreciate your valuable feedback.
>
> Best Regards,
>
> Your DBA Colleague
> Muhammad Salman Faheem
>
>
> ------------------------------------------------------------------------
> Looking for last minute shopping deals? Find them fast with Yahoo!
> Search.
>
>
       

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