Databases Reference
In-Depth Information
How it works...
The Oracle database may use synchronous or asynchronous I/O calls. With synchronous I/O,
when an I/O request is submitted to the Operating System, the write process will block until
the operation is completed.
Using asynchronous I/O, while the I/O request is still executing, the calling process continues
its work without blocking. This is the reason why asynchronous I/O can lead to performance
gain in processing writes to Oracle database files.
There is an important downside to keep in mind while using asynchronous
writes—the blocks may not get written immediately to the file system, and
this behavior may lead to missing data, or corruption, in case of a failure.
In step 2, we verify if the asynchronous I/O option is enabled in our database instance. In
step 3, we set the value to
SETALL
, enabling both asynchronous and direct I/O on system
files. Using direct I/O allows the process to bypass the Operating System cache. The Oracle
database already uses the database buffer cache, so we can access database files directly,
without consuming resources required by the OS cache.
In steps 4 and 5, we restart the database to set the new parameters, and, in step 6, we verify
the new value for the
FILESYSTEMIO_OPTIONS
parameter.
In the following screenshot, we can see the output for the previous operations:
