Oracle中filesystemio_options 和 disk_asynch_io 参数的设置

参数说明

参考文档Doc ID 1987437.1
filesystemio_options参数，中间是这个参数的配置值。

	Synchronous I/O	Asynchronous I/O
Buffered I/O	none	asynch
Direct I/O	directIO	setall

disk_asynch_io参数可以设置成true 或 false。这个参数是主开关，当设置成false时，所有 I/O 都是同步的，不管 filesystemio_options 参数如何设置。这个参数推荐设置成默认的true。

检查默认设置

SQL> show parameter disk_as

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io			     boolean	 TRUE
SQL> COL NAME FORMAT A50
SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE F.FILE#=I.FILE_NO
AND FILETYPE_NAME='Data File';  2    3  

NAME						   ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/yaoy11G/system01.dbf	   ASYNC_OFF
/u01/app/oracle/oradata/yaoy11G/sysaux01.dbf	   ASYNC_OFF
/u01/app/oracle/oradata/yaoy11G/undotbs01.dbf	   ASYNC_OFF
/u01/app/oracle/oradata/yaoy11G/users01.dbf	   ASYNC_OFF
/u01/app/oracle/oradata/yaoy11G/example01.dbf	   ASYNC_OFF

SQL> show parameter systemio

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options		     string	 none
SQL> alter system set filesystemio_options=asynch;
alter system set filesystemio_options=asynch
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

修改成推荐设置

Asynchronous I/O, direct I/O是推荐设置

SQL>  alter system set filesystemio_options=setall  scope=spfile;

System altered.OPTIONS:

  "asynch"    - This allows asynchronous IO to be used where supported by the OS. 
  
   "directIO"  - This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache. As of 10.2 most platforms will try to use "directio" option for NFS mounted disks (and will also check NFS attributes are sensible).

"setall"    - Enables both ASYNC and DIRECT IO.

"none"      - This disables ASYNC IO and DIRECT IO so that  Oracle uses normal synchronous writes, without  any direct io options.


SQL> startup force;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size		    2217952 bytes
Variable Size		  511707168 bytes
Database Buffers	  318767104 bytes
Redo Buffers		    2412544 bytes
Database mounted.
Database opened.
SQL> COL NAME FORMAT A50
SQL>  SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE F.FILE#=I.FILE_NO
AND FILETYPE_NAME='Data File';  2    3  

NAME						   ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/yaoy11G/system01.dbf	   ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/sysaux01.dbf	   ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/undotbs01.dbf	   ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/users01.dbf	   ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/example01.dbf	   ASYNC_ON

关于ASM

ASM 本身是异步IO，不受filesystemio_options 参数的控制，参见 Doc ID 751463.1。ASM I/O 受 DISK_ASYNCH_IO 参数的控制。

文章下方是我的微信，欢迎加我。👇