[20181219]测试设置参数filesystemio_options与打开数据文件的flag.txt

[20181219]测试设置参数filesystemio_options与打开数据文件的flag.txt --//测试一下设置参数filesystemio_options,oracle数据库打开数据文件以及在线日志的flag. 1.环境: SYS@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> create pfile='/tmp/@.ora' from spfile ; File created. SYS@book> @ spid        SID    SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------- --------- ------ ------- ---------- --------------------------------------------------        274          3 62448   DEDICATED 62496       21          2 alter system kill session '274,3' immediate; --//启动sqlplus的进程号是62448.下面的测试不要退出sqlplus.这样sqlplus进程号不会变. $ ps -ef |grep 6244[8] oracle   62448 62447  0 15:51 pts/2    00:00:00 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus   as sysdba oracle   62496 62448  0 15:51 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 2.测试一: --//filesystemio_options=none的情况.也是缺省设置. --//关闭重新启动数据库,为了显示方便,启动可以分2步,先启动到mount,在执行alter database open.以下测试除了filesystemio_options不同外, --//其它步骤一样. SYS@book> startup mount pfile='/tmp/book.ora'; ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes Database mounted. SYS@book> alter database open ; Database altered. SYS@book> show parameter filesystemio_options NAME                 TYPE   VALUE -------------------- ------ ------ filesystemio_options string NONE --//打开新终端,执行如下,一个小细节注意-f在-p参数前面才有效: $ strace -tt -f -p 62448 -e open 2>&1 | tee /tmp/none.txt [pid 63083] 16:12:16.425620 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 9 $ grep "ramdisk" /tmp/none.txt | egrep "system|sysaux|redo|control|users" | cut -c29- | sort | uniq -c       3 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY <unfinished ...>       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 10       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 12       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 14       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 9       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC <unfinished ...>       4 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY) = 11       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 10       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 12       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 14       2 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 9       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC <unfinished ...>       2 open("/mnt/ramdisk/book/redo01.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo01.log", O_RDWR|O_SYNC) = 11       2 open("/mnt/ramdisk/book/redo02.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDWR|O_SYNC) = 11       2 open("/mnt/ramdisk/book/redo03.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDWR|O_SYNC) = 11       2 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY) = 11       4 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 14       7 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 9       2 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY) = 11       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 12       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 14       6 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 9       5 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC <unfinished ...>       2 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY) = 11       4 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC) = 9 --//可以发现filesystemio_options=none的情况下,打开flag都包含O_SYNC标志. 3.测试二: --//设置filesystemio_options=asynch. $ strace -tt -f -p 62448 -e open 2>&1 | tee /tmp/asynch.txt $ grep "ramdisk" /tmp/asynch.txt | egrep "system|sysaux|redo|control|users" | cut -c29- | sort | uniq -c       4 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY) = 11       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 10       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 12       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 14       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC) = 9       4 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY) = 11       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 10       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 12       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 14       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC) = 9       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLY <unfinished ...>       1 open("/mnt/ramdisk/book/redo01.log", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY <unfinished ...>       1 open("/mnt/ramdisk/book/redo02.log", O_RDWR|O_SYNC <unfinished ...>       2 open("/mnt/ramdisk/book/redo03.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDWR|O_SYNC) = 11       2 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY) = 11       4 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 14       5 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC) = 9       2 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 10       3 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 11       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 14       4 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC) = 9       3 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC <unfinished ...>       2 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY) = 11       4 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC) = 11       1 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC) = 9 --//可以发现filesystemio_options=asynch的情况下,打开flag都包含O_SYNC标志. 4.测试三: --//设置filesystemio_options=directio. $ strace -tt -f -p 62448 -e open 2>&1 | tee /tmp/direct.txt $ grep "ramdisk" /tmp/direct.txt | egrep "system|sysaux|redo|control|users" | cut -c29- | sort | uniq -c       2 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY) = 11       2 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY|O_DIRECT) = 11       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 10       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 12       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 14       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       2 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY) = 11       2 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY|O_DIRECT) = 11       2 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 10       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 12       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 14       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLY|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo01.log", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY <unfinished ...>       1 open("/mnt/ramdisk/book/redo02.log", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/redo03.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDONLY|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY|O_DIRECT) = 11       4 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 14       1 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY|O_DIRECT) = 11       3 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 14       1 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY|O_DIRECT) = 11       4 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11 --//可以发现filesystemio_options=directio的情况下,打开flag都包含O_SYNC,O_DIRECT标志. 5.测试四: --//设置filesystemio_options=setall $ strace -tt -f -p 62448 -e open 2>&1 | tee /tmp/setall.txt $ grep "ramdisk" /tmp/setall.txt | egrep "system|sysaux|redo|control|users" | cut -c29- | sort | uniq -c       1 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY) = 11       2 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDONLY <unfinished ...>       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 10       2 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 12       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 14       3 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/control01.ctl", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       2 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY) = 11       2 open("/mnt/ramdisk/book/control02.ctl", O_RDONLY|O_DIRECT) = 11       2 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 10       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 12       1 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 14       3 open("/mnt/ramdisk/book/control02.ctl", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLY|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/redo01.log", O_RDONLYProcess 63595 detached       1 open("/mnt/ramdisk/book/redo01.log", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/redo02.log", O_RDONLY <unfinished ...>       1 open("/mnt/ramdisk/book/redo02.log", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/redo03.log", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDONLY|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/redo03.log", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDONLY|O_DIRECT) = 11       3 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 14       5 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/sysaux01.dbf", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/system01.dbf", O_RDONLY|O_DIRECT) = 11       3 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 14       5 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 9       2 open("/mnt/ramdisk/book/system01.dbf", O_RDWR|O_SYNC|O_DIRECT <unfinished ...>       1 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY) = 11       1 open("/mnt/ramdisk/book/users01.dbf", O_RDONLY|O_DIRECT) = 11       3 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 11       1 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC|O_DIRECT) = 9       1 open("/mnt/ramdisk/book/users01.dbf", O_RDWR|O_SYNC|O_DIRECT <unfinished ...> --//可以发现filesystemio_options=directio的情况下,打开flag都包含O_SYNC,O_DIRECT标志. --//可以看出oracle支持设置异步IO,打开flag也是设置O_SYNC. --//如果filesystemio_options=directio,setall,都设置O_DIRECT标识.只不过direct不支持异步IO.setall支持异步IO罢了. --//贴man open手册: NAME        open, creat - open and possibly create a file or device SYNOPSIS        #include <sys/types.h>        #include <sys/stat.h>        #include <fcntl.h>        int open(const char *pathname, int flags);        int open(const char *pathname, int flags, mode_t mode);        int creat(const char *pathname, mode_t mode); DESCRIPTION       O_DIRECT  Try to minimize cache effects of the I/O to and from this file.  In general this will degrade performance, but it  is useful in special situations, such as when applications do their own caching.  File I/O is done directly  to/from user  space  buffers.   The I/O is synchronous, i.e., at the completion of a read(2) or write(2), data is  guaranteed to have been transferred.  Under Linux 2.4 transfer sizes, and the alignment of user buffer and file  offset must all be multiples of the logical block size of the file system. Under Linux 2.6 alignment must fit the  block size of the device.  O_SYNC The file is opened for synchronous I/O. Any write()s on the resulting file descriptor will block the  calling process until the data has been physically written to the underlying hardware.  But see RESTRICTIONS  below. --//也就是write操作是使用同步IO,这样保证更改写操作同时更新到磁盘,避免数据库损坏信息丢失. --//不过许多存储,磁盘都有缓存,写了未必"真正"写到磁盘上,掉电等一些因为很容易导致数据库损坏,特别是繁忙的系统的在线日志. --//我发现许多人喜欢设置filesystemio_options=setall,我们系统一般使用nfs做为备份,设置这个参数恢复有问题,我的测试库. --//建立内存盘里面的(注:当前测试不是,我移动到磁盘上了).设置filesystemio_options=setall,direct.数据库无法开启,报 ORA-205 signalled during: ALTER DATABASE   MOUNT... --//链接: http://blog.itpub.net/267265/viewspace-2147530/ http://blog.itpub.net/267265/viewspace-2122164/ --//我个人认为设置filesystemio_options=asynch,这样可以充分利用os的缓存,当然也可能掩盖数据库的一些问题. --//如果看一些rac+asm的系统,因为无法使用os缓存,IO相对繁忙的系统log file sync相对靠前,Wait Avg(ms)达到10ms以上, --//基本说明存储性能很差或者规划不合理. --//当然设置filesystemio_options=setall可以空出更多的内存给sga,应用程序使用.我个人估计连接数很大消耗pga很大的一些 --//应用设置它,也许效果好一些,没有相关测试,只能是自己的猜测. -//oracle官方的解析: https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410 9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on  file system files. This parameter is platform-specific and has a default value that is best for a particular platform. FILESYTEMIO_OPTIONS can be set to one of the following values: ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.  DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.  SETALL: enable both asynchronous and direct I/O on file system files.  NONE: disable both asynchronous and direct I/O on file system files.