Direct and Asynchronous I/O:
Most of the performance tuning issues can be related to I/O in any database. Oracle provides only two main parameters to control I/O behaviour these are filesystemio_options and disk_asynch_io
There is a major disscussion of Oracle performance on various platforms based on typical storage. I/O operations in UNIX and Linux systems typically go through the file system cache. Although this doesn't represent a problem in itself, this extra processing does require resources. Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations. Operations against raw devices automatically bypass the file system cache.
When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. With asynchronous I/O, processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass
some of the performance bottlenecks associated with I/O operations.
Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS parameter, whose possible
Posible values are listed below.
ASYNCH - Enabled asynchronous I/O where possible.
DIRECTIO- Enabled direct I/O where possible.
SETALL- Enabled both direct I/O and asynchronous I/O where possible.
NONE - Disabled both direct I/O and asynchronous I/O.
The following example shows how the parameter is set.
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
SQL>
FAQ:
1) FILESYSTEMIO_OPTIONS=setall always safe?
async I/O library for database data and log writes. Oracle performs writes in batches or lists, which are submitted to the operating system
using the listio() functionality. The listio() asynchronous I/O facility was added specifically to optimize database performance.
you should enable the parameter FILESYSTEMIO_OPTIONS=setall for system performance.
2) Why "setall" ?
For optimal disk performance, Oracle should always use direct I/O to its data files, bypassing any caching at the OS layer. Direct I/O must be enabled both in Oracle and in the operating system.
Oracle controls direct I/O with a parameter named filesystemio_options. According to the Oracle documentation the filesystemio_options parameter must be set to "setall" (the preferred method, according to the Oracle documentation) or "directio" in order for Oracle to read data blocks
directly from disk:
Using direct I/O allows you to enhance I/O by bypassing the redundant OS block buffers, reading the data block directly into the Oracle SGA. Using direct I/O also allow you to create multiple blocksized tablespaces to improve I/O performance:
In overall, Oracle recommends to set parameter filesystemio_options to value 'setall' but it is not always good practise especially when SGA is small. setting it to setall lets your Oracle DB perform I/O operations without going to file system cache and it saves overhead of double caching but if SGA is smaller and DB host machine has large free memory then it is not good to set this parameter to value setall. In this case you should increase DB_CACHE_SIZE and only then set filesystemio_options to setall.
3) What will be best practice for IBM - AIX?
IBM has published an excellent case study with Oracle Database on AIX, illustrating how to optimizer AIX direct I/O for Oracle 10g and 11g databases:
Click here to see the Document
4) What other parameters look into?
Other parameters to affect write (as well as read) is dbwriter_processes. When asynchronous I/O operations are slower in operating system in comparison to synchronous I/O then turn off asynchronous I/O by setting disk_asynch_io to false and set multiple db writer processes by increasing dbwriter_processes values from 1 to 2,3 or 4 suitable value to your system. Alternate is incrase dbwr_io_slaves from 0 to 2,3,4 suitable value.
You would be keen to disable asynchronous I/O when you see high average_wait on event db_file_parallel_wait. Other reason for turning it off will be synchronous I/O is more reliable.
SQL> select event,average_wait from v$system_event where event like 'db file parallel write';
EVENT AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file parallel write 28.2 [ centi seconds]
This is not a very good ASYNCH I/O. Try Synchronous I/O
Note 1: Asynchronous I/O operations are more prone to block corruptions than synchronous operations so many DBAs disable it and follow practice as mentioned in above paragraph. So if you do not have standby databases and oracle 11g then which autoamatically recovers corrupted block on primary then you would not want asynchronous I/O
Note 2: For 11g R2 for tuning purpose, the “db file async I/O submit” should be treated as “db file parallel write” in previous releases.
5) What will be for Linux?
As per Burleson site, the following setup may use full.
Linux - Linux systems support direct I/O on a per-filehandle basis (which is much more flexible). Also with 10g and beyond, this feature is already working which means that is does not require any patch. For Oracle, the DBA will need to download- Abstract: DIRECT IO SUPPORT OVER NFS . To enable direct I/O support:, check these settings:
- Set the filesystemio_options parameter in the parameter file to DIRECTIO (filesystemio_options = DIRECTIO)
- If the asynchronous I/O option is in use, the filesystemio_options parameter in the parameter file should be set to SETALL.
Most of the performance tuning issues can be related to I/O in any database. Oracle provides only two main parameters to control I/O behaviour these are filesystemio_options and disk_asynch_io
There is a major disscussion of Oracle performance on various platforms based on typical storage. I/O operations in UNIX and Linux systems typically go through the file system cache. Although this doesn't represent a problem in itself, this extra processing does require resources. Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations. Operations against raw devices automatically bypass the file system cache.
When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. With asynchronous I/O, processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass
some of the performance bottlenecks associated with I/O operations.
Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS parameter, whose possible
Posible values are listed below.
ASYNCH - Enabled asynchronous I/O where possible.
DIRECTIO- Enabled direct I/O where possible.
SETALL- Enabled both direct I/O and asynchronous I/O where possible.
NONE - Disabled both direct I/O and asynchronous I/O.
The following example shows how the parameter is set.
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
SQL>
FAQ:
1) FILESYSTEMIO_OPTIONS=setall always safe?
async I/O library for database data and log writes. Oracle performs writes in batches or lists, which are submitted to the operating system
using the listio() functionality. The listio() asynchronous I/O facility was added specifically to optimize database performance.
you should enable the parameter FILESYSTEMIO_OPTIONS=setall for system performance.
2) Why "setall" ?
For optimal disk performance, Oracle should always use direct I/O to its data files, bypassing any caching at the OS layer. Direct I/O must be enabled both in Oracle and in the operating system.
Oracle controls direct I/O with a parameter named filesystemio_options. According to the Oracle documentation the filesystemio_options parameter must be set to "setall" (the preferred method, according to the Oracle documentation) or "directio" in order for Oracle to read data blocks
directly from disk:
Using direct I/O allows you to enhance I/O by bypassing the redundant OS block buffers, reading the data block directly into the Oracle SGA. Using direct I/O also allow you to create multiple blocksized tablespaces to improve I/O performance:
In overall, Oracle recommends to set parameter filesystemio_options to value 'setall' but it is not always good practise especially when SGA is small. setting it to setall lets your Oracle DB perform I/O operations without going to file system cache and it saves overhead of double caching but if SGA is smaller and DB host machine has large free memory then it is not good to set this parameter to value setall. In this case you should increase DB_CACHE_SIZE and only then set filesystemio_options to setall.
3) What will be best practice for IBM - AIX?
IBM has published an excellent case study with Oracle Database on AIX, illustrating how to optimizer AIX direct I/O for Oracle 10g and 11g databases:
Click here to see the Document
4) What other parameters look into?
Other parameters to affect write (as well as read) is dbwriter_processes. When asynchronous I/O operations are slower in operating system in comparison to synchronous I/O then turn off asynchronous I/O by setting disk_asynch_io to false and set multiple db writer processes by increasing dbwriter_processes values from 1 to 2,3 or 4 suitable value to your system. Alternate is incrase dbwr_io_slaves from 0 to 2,3,4 suitable value.
You would be keen to disable asynchronous I/O when you see high average_wait on event db_file_parallel_wait. Other reason for turning it off will be synchronous I/O is more reliable.
SQL> select event,average_wait from v$system_event where event like 'db file parallel write';
EVENT AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file parallel write 28.2 [ centi seconds]
This is not a very good ASYNCH I/O. Try Synchronous I/O
Note 1: Asynchronous I/O operations are more prone to block corruptions than synchronous operations so many DBAs disable it and follow practice as mentioned in above paragraph. So if you do not have standby databases and oracle 11g then which autoamatically recovers corrupted block on primary then you would not want asynchronous I/O
Note 2: For 11g R2 for tuning purpose, the “db file async I/O submit” should be treated as “db file parallel write” in previous releases.
5) What will be for Linux?
As per Burleson site, the following setup may use full.
Linux - Linux systems support direct I/O on a per-filehandle basis (which is much more flexible). Also with 10g and beyond, this feature is already working which means that is does not require any patch. For Oracle, the DBA will need to download
- Set the filesystemio_options parameter in the parameter file to DIRECTIO (filesystemio_options = DIRECTIO)
- If the asynchronous I/O option is in use, the filesystemio_options parameter in the parameter file should be set to SETALL.
