当前位置：Oracle研究中心 > 运维DBA > Linux >
【学习笔记】开启LINUX平台ORACLE AIO功能详细步骤
时间:2016-10-18 21:41 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
运维DBA反映当前生产环境Oracle数据库IO遇到瓶颈.需要开启Oracle AIO功能。结合案例详细讲解如何开启ORACLE AIO功能详细步骤。
当IO遇到瓶颈的时候.我们首先会去检查AIO功能是否生效.在默认创建库的时候.使用raw方式的数据文件aio功能是开启了的.使用文件系统的数据文件.aio功能是没有开启的.在linux平台文件系统方式下.很多人在创建数据库的时候都没有手动开启aio.才会导致一些IO相关的等待事件的出现.下面就是测试LINUX下AIO功能是否开启。
OS :RHEL 4.8 DB:10.2.0.5.10/9.2.0.8
[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
[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
[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功能.见最后部分
oracleplus.net>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
oracleplus.net>show parameter disk
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string
asm_diskstring string
disk_asynch_io boolean TRUE
如果数据文件使用的是RAW或者ASM中.AIO功能由disk_asynch_io来控制.因为ASM直接绕过文件系统层。
oracleplus.net>show parameter filesystem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string NONE
此处为NONE代表没有启用AIO.再加上数据文件使用文件系统.所以整个环境没有启用AIO功能.
6.1 没用启用aio的dbwr进程的trace内容
12:29:42.435268 pwrite(19, "&242)200t01624206#"..., 8192, 335872) = 8192
12:29:42.438044 times(NULL) = 429715112
12:29:42.438065 times(NULL) = 429715112
12:29:42.438081 pwrite(19, "&24221120000016142545"..., 8192, 1122304) = 8192
12:29:42.438371 times(NULL) = 429715112
12:29:42.438392 times(NULL) = 429715112
12:29:42.438407 pwrite(19, "2242216200376/162424210t4>1334##"..., 8192, 1163264) = 8192
12:29:42.438692 times(NULL) = 429715112
12:29:42.438713 times(NULL) = 429715112
12:29:42.438729 pwrite(19, "2242t5200t016242042336S1301##"..., 8192, 11436032) = 8192
12:29:42.438986 times(NULL) = 429715112
12:29:42.439004 times(NULL) = 429715112
12:29:42.439019 pwrite(21, "6242341t016263022151tl311R#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$1t01626''231m31107016"..., 8192, 294912) = 8192
6.2 启用aio后的dbwr进程的trace内容
修改成setall后
oracleplus.net>alter system set filesystemio_options=setall scope=spfile;
System altered.
oracleplus.net>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相关的
[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.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/produOracleoracleplus.netct/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
本文固定链接: http://www.htz.pw/2013/05/01/linux%e5%b9%b3%e5%8f%b0oracle-aio%e5%8a%9f%e8%83%bd%e6%b5%8b%e8%af%95.html | 认真就输
Oracle研究中心
当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相当参数是否配置
oracleplus.net>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
oracleplus.net>show parameter disk
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string
asm_diskstring string
disk_asynch_io boolean TRUE
如果数据文件使用的是RAW或者ASM中.AIO功能由disk_asynch_io来控制.因为ASM直接绕过文件系统层。
oracleplus.net>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)200t01624206#"..., 8192, 335872) = 8192
12:29:42.438044 times(NULL) = 429715112
12:29:42.438065 times(NULL) = 429715112
12:29:42.438081 pwrite(19, "&24221120000016142545"..., 8192, 1122304) = 8192
12:29:42.438371 times(NULL) = 429715112
12:29:42.438392 times(NULL) = 429715112
12:29:42.438407 pwrite(19, "2242216200376/162424210t4>1334##"..., 8192, 1163264) = 8192
12:29:42.438692 times(NULL) = 429715112
12:29:42.438713 times(NULL) = 429715112
12:29:42.438729 pwrite(19, "2242t5200t016242042336S1301##"..., 8192, 11436032) = 8192
12:29:42.438986 times(NULL) = 429715112
12:29:42.439004 times(NULL) = 429715112
12:29:42.439019 pwrite(21, "6242341t016263022151tl311R#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$1t01626''231m31107016"..., 8192, 294912) = 8192
6.2 启用aio后的dbwr进程的trace内容
修改成setall后
oracleplus.net>alter system set filesystemio_options=setall scope=spfile;
System altered.
oracleplus.net>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/produOracleoracleplus.netct/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
本文固定链接: http://www.htz.pw/2013/05/01/linux%e5%b9%b3%e5%8f%b0oracle-aio%e5%8a%9f%e8%83%bd%e6%b5%8b%e8%af%95.html | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】开启LINUX平台ORACLE AIO功能详细步骤
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/999.html
