当IO遇到瓶颈的时候，我们首先会去检查AIO功能是否生效，在默认创建库的时候，使用raw方式的数据文件aio功能是开启了的，使用文件系统的数据文件，aio功能是没有开启的，在linux平台文件系统方式下，很多人在创建数据库的时候都没有手动开启aio，才会导致一些IO相关的等待事件的出现，下面就是测试LINUX下AIO功能是否开启。
OS :RHEL 4.8 DB:10.2.0.5.10/9.2.0.8
1,查询AIO软件是否安装
[oracle10g@rhel4 ~]$ rpm -qa|grep aio libaio-0.3.105-2 libaio-devel-0.3.105-2 libaio-0.3.105-2 libaio-devel-0.3.105-2
2.查看系统是否启用AIO
[oracle10g@rhel4 ~]$ cat /proc/slabinfo | grep kio kioctx 21 30 384 10 1 : tunables 54 27 8 : slabdata 3 3 0 kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
kiocb前2个值都是0，但是并不一定表示没有启用AIO功能，如果ORACLE数据文件使用ASM，可能启用了AIO,如果是文件系统肯定没有启用AIO
3.查看ORACLE是否支持AIO
[oracle10g@rhel4 ~]$ /usr/bin/ldd $ORACLE_HOME/bin/oracle | grep libaio
libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000003447a00000)
此处说明ORACLE软件已经加载AIO功能。10GR2开始默认已经加载了，ORACLE 9I中默认是没有开启的，关于ORACLE 9I开启AIO功能，见最后部分
4.查询ORACLE相当参数是否配置
www.htz.pw >select name from v$dbfile; NAME -------------------------------------------------------------------------------- /datafile/orcl10g/orcl10g/users01.dbf /datafile/orcl10g/orcl10g/sysaux01.dbf /datafile/orcl10g/orcl10g/undotbs01.dbf /datafile/orcl10g/orcl10g/system01.dbf 此处使用的文件系统，文件系统的AIO由参数filesystemio_options控制，而不受disk_asynch_io控制，filesystemio_options为none表示不启用， 为asynch表示启用异步IO，为directIO表示启用直接IO，为setall时表示启用直接IO与AIO，建议更改为SETALL www.htz.pw >show parameter disk NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskgroups string asm_diskstring string disk_asynch_io boolean TRUE 如果数据文件使用的是RAW或者ASM中，AIO功能由disk_asynch_io来控制，因为ASM直接绕过文件系统层。 www.htz.pw >show parameter filesystem NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ filesystemio_options string NONE 此处为NONE代表没有启用AIO，再加上数据文件使用文件系统，所以整个环境没有启用AIO功能，
6，strace dbwr进程查看是否启用aio功能
6.1 没用启用aio的dbwr进程的trace内容
12:29:42.435268 pwrite(19, "&242 ) 200 t016 24206# "..., 8192, 335872) = 8192 12:29:42.438044 times(NULL) = 429715112 12:29:42.438065 times(NULL) = 429715112 12:29:42.438081 pwrite(19, "&242 211 200 00016 142545 "..., 8192, 1122304) = 8192 12:29:42.438371 times(NULL) = 429715112 12:29:42.438392 times(NULL) = 429715112 12:29:42.438407 pwrite(19, "2242 216 200 376/16 2424210 t 4 >1 334 ##"..., 8192, 1163264) = 8192 12:29:42.438692 times(NULL) = 429715112 12:29:42.438713 times(NULL) = 429715112 12:29:42.438729 pwrite(19, "2242 t5200 t016 2420423 3 6 S1 301##"..., 8192, 11436032) = 8192 12:29:42.438986 times(NULL) = 429715112 12:29:42.439004 times(NULL) = 429715112 12:29:42.439019 pwrite(21, "6242 34 1t016 26302215 1 t l311 R#t "..., 8192, 229376) = 8192 12:29:42.439260 times(NULL) = 429715112 12:29:42.439278 times(NULL) = 429715112 12:29:42.439293 pwrite(21, "6242 $ 1t016 26'' 2 31 m311 07016 "..., 8192, 294912) = 8192
6.2 启用aio后的dbwr进程的trace内容
修改成setall后
www.htz.pw >alter system set filesystemio_options=setall scope=spfile;
System altered.
www.htz.pw >startup force;
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 2084552 bytes
Variable Size 125829432 bytes
Database Buffers 306184192 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
12:32:47.648352 semtimedop(622592, 0x7fbfffdff0, 1, {3, 0}) = 0
12:32:50.227300 times(NULL) = 429733888
12:32:50.227385 semctl(622592, 10, SETVAL, 0x1) = 0
12:32:50.227536 getrusage(RUSAGE_SELF, {ru_utime={0, 6998}, ru_stime={0, 12998}, ...}) = 0
12:32:50.227573 getrusage(RUSAGE_SELF, {ru_utime={0, 6998}, ru_stime={0, 12998}, ...}) = 0
12:32:50.227606 times(NULL) = 429733888
12:32:50.227631 semtimedop(622592, 0x7fbfffdff0, 1, {0, 420000000}) = 0
12:32:50.229953 times(NULL) = 429733888
12:32:50.229991 io_submit(182927040512, 1, {...}) = 1
12:32:50.230131 io_getevents(182927040512, 1, 1024, {...}{600, 0}) = 1
这里我们看到了两个函数，io_submit,io_getevents这两个函数就是跟aio相关的
7.关于os上aio参数的配置
[root@11rac1 ~]# echo "fs.aio-max-nr=1048576">>/etc/sysctl.conf;sysctl -p;echo "***********";sysctl -a|grep fs.aio-max net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 2 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 net.ipv4.tcp_syncookies = 1 kernel.msgmnb = 65536 kernel.msgmax = 65536 kernel.shmmax = 68719476736 kernel.shmall = 4294967296 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 11036870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 fs.aio-max-nr = 1048576 fs.aio-max-nr = 1048576 *********** fs.aio-max-nr = 1048576
ORACLE建议把fs.aio_max_nr的值配置成1048576，不然有可能会遇到一些错误。
8.oracle软件加裁aio的功能
8.1 9i开启aio
[oracle9i@rhel4 lib]$ cd $ORACLE_HOME/rdbms/lib [oracle9i@rhel4 lib]$ make -f ins_rdbms.mk async_on 如果是关闭使用下面这条命令 [oracle9i@rhel4 lib]$ make -f ins_rdbms.mk async_off rm -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o cp /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaio.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o - Linking Oracle rm -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle gcc -o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle -L/u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ -L/u01/app/oracle/product/9.2.0/db_1//lib/ -L/u01/app/oracle/product/9.2.0/db_1//lib/stubs/ -Wl,-E `test -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o && echo /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o` /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/opimai.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ssoraed.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ttcsoi.o /u01/app/oracle/product/9.2.0/db_1//lib/nautab.o /u01/app/oracle/product/9.2.0/db_1//lib/naeet.o /u01/app/oracle/product/9.2.0/db_1//lib/naect.o /u01/app/oracle/product/9.2.0/db_1//lib/naedhs.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/config.o -lserver9 -lodm9 -lskgxp9 -lskgxn9 -lclient9 -lvsn9 -lwtcserver9 -lcommon9 -lgeneric9 /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/defopt.o -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap9" ; fi` -lslax9 -lpls9 -lplp9 -lserver9 -lclient9 -lvsn9 -lwtcserver9 -lcommon9 -lgeneric9 -lknlopt -lslax9 -lpls9 -lplp9 -ljox9 -lserver9 -locijdbcst9 -lwwg9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lmm -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -ltrace9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `if /usr/bin/ar tv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo9"; fi` -lctxc9 -lctx9 -lzx9 -lgx9 -lctx9 -lzx9 -lgx9 -lordimt9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 -lsnls9 -lunls9 -lxsd9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/9.2.0/db_1//lib:/lib:/usr/lib -lm `cat /u01/app/oracle/product/9.2.0/db_1//lib/sysliblist` -ldl -lm `test -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o && echo -laio` mv -f /u01/app/oracle/product/9.2.0/db_1//bin/oracle /u01/app/oracle/product/9.2.0/db_1//bin/oracleO mv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle /u01/app/oracle/product/9.2.0/db_1//bin/oracle chmod 6751 /u01/app/oracle/product/9.2.0/db_1//bin/oracle [oracle9i@rhel4 lib]$ make -f ins_rdbms.mk ioracle - Linking Oracle rm -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle gcc -o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle -L/u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ -L/u01/app/oracle/product/9.2.0/db_1//lib/ -L/u01/app/oracle/product/9.2.0/db_1//lib/stubs/ -Wl,-E `test -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o && echo /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o` /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/opimai.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ssoraed.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/ttcsoi.o /u01/app/oracle/product/9.2.0/db_1//lib/nautab.o /u01/app/oracle/product/9.2.0/db_1//lib/naeet.o /u01/app/oracle/product/9.2.0/db_1//lib/naect.o /u01/app/oracle/product/9.2.0/db_1//lib/naedhs.o /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/config.o -lserver9 -lodm9 -lskgxp9 -lskgxn9 -lclient9 -lvsn9 -lwtcserver9 -lcommon9 -lgeneric9 /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/defopt.o -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap9" ; fi` -lslax9 -lpls9 -lplp9 -lserver9 -lclient9 -lvsn9 -lwtcserver9 -lcommon9 -lgeneric9 -lknlopt -lslax9 -lpls9 -lplp9 -ljox9 -lserver9 -locijdbcst9 -lwwg9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lmm -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -ltrace9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `if /usr/bin/ar tv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo9"; fi` -lctxc9 -lctx9 -lzx9 -lgx9 -lctx9 -lzx9 -lgx9 -lordimt9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 -lsnls9 -lunls9 -lxsd9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `cat /u01/app/oracle/product/9.2.0/db_1//lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/9.2.0/db_1//lib:/lib:/usr/lib -lm `cat /u01/app/oracle/product/9.2.0/db_1//lib/sysliblist` -ldl -lm `test -f /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/skgaioi.o && echo -laio` mv -f /u01/app/oracle/product/9.2.0/db_1//bin/oracle /u01/app/oracle/product/9.2.0/db_1//bin/oracleO mv /u01/app/oracle/product/9.2.0/db_1//rdbms/lib/oracle /u01/app/oracle/product/9.2.0/db_1//bin/oracle chmod 6751 /u01/app/oracle/product/9.2.0/db_1//bin/oracle [oracle9i@rhel4 lib]$ which ldd /usr/bin/ldd [oracle9i@rhel4 lib]$ ldd $ORACLE_HOME/bin/oracle|grep aio libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000003447a00000)
8.2 10G加载aio
[oracle10g@rhel4 lib]$ cd $ORACLE_HOME/rdbms/lib [oracle10g@rhel4 lib]$ ln -s /usr/lib/libaio.so.1 skgaio.o [oracle10g@rhel4 lib]$ make PL_ORALIBS=-laio -f ins_rdbms.mk async_on rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o cp /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o chmod 755 /u01/app/oracle/product/10.2.0/db_1/bin - Linking Oracle rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/oracle gcc -o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/oracle -L/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ -L/u01/app/oracle/product/10.2.0/db_1/lib/ -L/u01/app/oracle/product/10.2.0/db_1/lib/stubs/ -Wl,-E `test -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sskgpsmti.o && echo /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sskgpsmti.o` /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/opimai.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ttcsoi.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/defopt.o -Wl,--whole-archive -lperfsrv10 -Wl,--no-whole-archive /u01/app/oracle/product/10.2.0/db_1/lib/nautab.o /u01/app/oracle/product/10.2.0/db_1/lib/naeet.o /u01/app/oracle/product/10.2.0/db_1/lib/naect.o /u01/app/oracle/product/10.2.0/db_1/lib/naedhs.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/config.o -lserver10 -lodm10 -lnnet10 -lskgxp10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lhasgen10 -lcore10 -lskgxn2 -locr10 -locrb10 -locrutl10 -lhasgen10 -lcore10 -lskgxn2 -lclient10 -lvsn10 -lcommon10 -lgeneric10 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap10" ; fi` -lslax10 -lpls10 -lplp10 -lserver10 -lclient10 -lvsn10 -lcommon10 -lgeneric10 `if [ -f /u01/app/oracle/product/10.2.0/db_1/lib/libavserver10.a ] ; then echo "-lavserver10" ; else echo "-lavstub10"; fi` `if [ -f /u01/app/oracle/product/10.2.0/db_1/lib/libavclient10.a ] ; then echo "-lavclient10" ; fi` -lknlopt -lslax10 -lpls10 -lplp10 -ljox10 -lserver10 -lclsra10 -ldbcfg10 -locijdbcst10 -lwwg `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lmm -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `if /usr/bin/ar tv /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo10"; fi` -lctxc10 -lctx10 -lzx10 -lgx10 -lctx10 -lzx10 -lgx10 -lordimt10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lsnls10 -lunls10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -laio `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/10.2.0/db_1/lib -lm `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/10.2.0/db_1/lib /u01/app/oracle/product/10.2.0/db_1/lib//libcore10.a(lcd.o)(.text+0xb71): In function `lcdprm': : warning: the `gets' function is dangerous and should not be used. mv -f /u01/app/oracle/product/10.2.0/db_1/bin/oracle /u01/app/oracle/product/10.2.0/db_1/bin/oracleO mv /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/oracle /u01/app/oracle/product/10.2.0/db_1/bin/oracle chmod 6751 /u01/app/oracle/product/10.2.0/db_1/bin/oracle 如果不做LN这一步会报下面的错误 [oracle10g@rhel4 lib]$ make PL_ORALIBS=-laio -f ins_rdbms.mk async_on rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o cp /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o cp: cannot stat `/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o': No such file or directory make: *** [do_async_on] Error 1
LINUX平台ORACLE AIO功能测试：等您坐沙发呢！