There is an Oracle database parameter filesystemio_options that I found out most DBA’s are not aware of.  First, this parameter can be used if your environment is NOT based on ASM setup.

In default behaviour…In buffered I/O, the Operating System maintains its own cache of disk data. Rather than directly reading to or writing from a process buffer, data is read from the disk into the cache and copied to the process buffer or copied from the process into the cache and written from there to the disk. Moreover, read requests are processed through cached data without having to read it again from the disk and can prefetch data from the disk into the cache before processes request it, speeding reads for data.

The catch here is that Oracle database system is already has in-place buffer cache for (read,write) to disk operations, so there will be “double” operations going on from DB & OS and will cause CPU performance overhead. However, direct IO will bypass the double buffering overhead.

Add the following parameter in init.ora and re-start the database instance:

filesystemio_options=setall

To observe the performance boost, generate AWR report before setting the parameter ( period of 1 week) and compare it after setting the parameter for 1 week report generation…..and the findings:

BEFORE PARAMETER SET:

AFTER PARAMETER SET:

DB CPU, db file sequential read, direct path read wait events are drastically improved as shown in BEFORE/AFTER figures.