Databases Reference
In-Depth Information
There's more...
Other options for the
FILESYSTEMIO_OPTIONS
parameter include:
F
NONE
: this disables both asynchronous I/O and direct I/O
F
ASYNCH
: this enables asynchronous operations
F
DIRECTIO
: this enables only the direct I/O option, thus bypassing the O/S cache
On Windows systems the OS cache is never used, so every request will bypass the Operating
System cache and go directly to the disk.
On Windows systems, starting from Oracle 10
g
, asynchronous I/O is supported for all files on
Windows Server 2003 x64 bit upward.
On *nix systems we need to pay attention to certain operations that don't use database buffer
cache. Using direct I/O option may lead to a performance dip. Those operations include I/O on
the temporary tablespace, using
NOCACHE
LOBs
, and parallel query slaves reading data.
Asynchronous I/O is important for
DBWn
and
LGWR
processes, because it allows
DBWn
to use the available hardware bandwidth completely. The
LGWR
writes to multiple log
file members and can overlap multiple writes due to rapidly subsequent committed
transactions occurring simultaneously.
Using asynchronous I/O on redo log files and temp files eliminates some contention related
to the file system read/write locks, resulting in increased performance. Using asynchronous
I/O on data files instead doesn't affect performance but scalability, that is, the database
can handle more requests at a time. The
DBWn
processes, responsible for writing to the data
files, work asynchronously, so the performance of user processes is not affected by the use
of asynchronous I/O. However, using asynchronous I/O allows
DBWn
processes to use all the
available bandwidth, and use it in a more efficient way, so higher workloads can be managed
by the system when asynchronous I/O is enabled.
On platforms that don't support asynchronous I/O, we can enable multiple database writer
slave processes. A single
DBWR
process will use multiple slave processes to write data on disks,
simulating something similar to asynchronous I/O. Please note that multiple
DBWn
processes
and multiple
DBWR
slaves cannot be run together, and the last option takes precedence.
To enable multiple database writer slave processes, you need to
set the initialization parameter
DBWR_IO_SLAVES
to a non-zero
value, setting the number of slave processes to use.
See also
F
Refer to the
Tuning the buffer cache
recipe in
Chapter 9
,
Tuning Memory