Re: Using Direct I/O Filesystem for Oracle Database
- From: Junior DBA <juniordba2004@xxxxxxxxx>
- To: David.J.Miller@xxxxxxx
- Date: Thu, 10 Jan 2008 12:17:38 -0800 (PST)
BismillahirRehmanirRahim
Hi David Miller,
Thanks for the detail reply.
During my search i found following link :
----------------------------------------------------------------------------------------------------------
http://orafaq.com/usenet/comp.databases.oracle.server/2007/07/12/0854.raw
[ first flush the buffer cache ]
sys@xxxxxxxxx> alter system flush buffer_cache;
System altered.
[ Then issue (as user oracle) ]
oracle@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@xxxxxxx> 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.
>
>
---------------------------------
Never miss a thing. Make Yahoo your homepage.
Other related posts: