Oracle中filesystemio_options 和 disk_asynch_io 参数的设置

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

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