What settings are available for the parameter?
Posted: Tue Feb 11, 2025 4:27 am
This parameter is used to configure properties that determine the technologies used to access the database files. There are many misunderstandings that ultimately lead to suboptimal database IO performance.
The configuration options have basically remained the same since Oracle 7, see Oracle 23ai documentation .
possible values
NONE : Async and Direct I/O disabled
SETALL : Async and Direct I/O enabled
DIRECTIO : Async I/O disabled, Direct I/O enabled
ASYNCH : Async I/O enabled, Direct I/O disabled
The default value for FILESYSTEMIO_OPTIONSdepends on the operating system:
AIX: ASYNCH, however both IBM and many storage vendors recommend using SETALL.
Linux, Windows: NONE – and this default is a performance trap!
What does Async or Direct I/O actually mean?
Async I/O means that a process sends an I/O request to the storage and then iraq telegram data carries on with its activity. As soon as the requested block has arrived from the storage, the process is informed and can carry on working accordingly. This is particularly interesting when a process is processing several blocks "simultaneously", for example when the DBWR wants to write several blocks to the disk or when a statement has to read in many blocks during a full table scan. If async I/O is deactivated, this is called sync I/O. In this case, the process waits for the I/O operation to be completed. This limits the number of blocks that can be processed per unit of time. Performance is extremely dependent on storage latency, because with each I/O operation you have to wait the average I/O latency before you can continue processing.
The configuration options have basically remained the same since Oracle 7, see Oracle 23ai documentation .
possible values
NONE : Async and Direct I/O disabled
SETALL : Async and Direct I/O enabled
DIRECTIO : Async I/O disabled, Direct I/O enabled
ASYNCH : Async I/O enabled, Direct I/O disabled
The default value for FILESYSTEMIO_OPTIONSdepends on the operating system:
AIX: ASYNCH, however both IBM and many storage vendors recommend using SETALL.
Linux, Windows: NONE – and this default is a performance trap!
What does Async or Direct I/O actually mean?
Async I/O means that a process sends an I/O request to the storage and then iraq telegram data carries on with its activity. As soon as the requested block has arrived from the storage, the process is informed and can carry on working accordingly. This is particularly interesting when a process is processing several blocks "simultaneously", for example when the DBWR wants to write several blocks to the disk or when a statement has to read in many blocks during a full table scan. If async I/O is deactivated, this is called sync I/O. In this case, the process waits for the I/O operation to be completed. This limits the number of blocks that can be processed per unit of time. Performance is extremely dependent on storage latency, because with each I/O operation you have to wait the average I/O latency before you can continue processing.