file system io options

a place to share experience of Oracle, JAVA and Linux and my favorite topic performance tuning

Translate

Search This Blog

I/O tuning in Oracle: filesystemio_options and disk_asynch_io

Part1: This is part one of series I/O tuning.

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

filesystemio_options alllows you to specify synchronous and asynchronous read/write and direct and indirect read write. By default it is none it means operating systen’s default I/O mode is selected which is synchronous read/write and indirect read write that is file system cached read write operations. But since disk_asynch_io parameter defaults to true Oracle supports asynchronous read write by default.

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.

Five years ago I was setting up Oracle database to handle large I/O of new Dataware house project. I set filesystemio_options to setall. This speeded up load operations aproximately by 5% whose caching in file system was not useful as these were insert statements and not repeated. At the same time increased SGA to quite high to cache I/O as much as possible so that repetitive select queries can benefit from SGA as in this case when filesystemio_options was set to setall then file system cache was not available.

So summary is be prudent when setting filesystemio_options to setall to enable direct read/write and asynchronous operations.

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. O ther 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.

FILESYSTEMIO_OPTIONS specifies I/O operations for file system files.

Varies by database version and operating system.

Modifiable in a PDB

Oracle Database Performance Tuning Guide for information about setting this parameter before running I/O calibration

Определение

Представляет дополнительные параметры для создания объекта FileStream. Represents advanced options for creating a FileStream object.

Это перечисление имеет атрибут FlagsAttribute, который разрешает побитовое сочетание значений его элементов.

Указывает, что файл может использоваться для асинхронного чтения и записи. Indicates that a file can be used for asynchronous reading and writing.

Указывает, что файл автоматически удаляется, если он не будет больше использоваться. Indicates that a file is automatically deleted when it is no longer in use.

Указывает, что файл является зашифрованным и может быть расшифрован только с использованием той же учетной записи пользователя, которая применялась для шифрования. Indicates that a file is encrypted and can be decrypted only by using the same user account used for encryption.

Указывает, что при создании объекта FileStream не должны использоваться дополнительные параметры. Indicates that no additional options should be used when creating a FileStream object.

Указывается, что доступ к файлу осуществляется произвольно. Indicates that the file is accessed randomly. Система может использовать это в качестве подсказки для оптимизации кэширования файла. The system can use this as a hint to optimize file caching.

Указывает, что доступ к файлу осуществляется последовательно от начала к концу. Indicates that the file is to be accessed sequentially from beginning to end. Система может использовать это в качестве подсказки для оптимизации кэширования файла. The system can use this as a hint to optimize file caching. Если в приложении указатель позиции в файле перемещается для произвольного доступа, оптимального кэширования можно не достигнуть, однако правильная работа гарантируется. If an application moves the file pointer for random access, optimum caching may not occur; however, correct operation is still guaranteed. Указание этого флага в некоторых случаях может повысить производительность. Specifying this flag can increase performance in some cases.

Указывает, что запись в системе должна выполняться через любой промежуточный кэш и переходить напрямую на диск. Indicates that the system should write through any intermediate cache and go directly to disk.

Примеры

В следующем примере показано, как использовать асинхронное значение при создании файлового потока. The following example shows how to use the Asynchronous value when creating a file stream.