最近有个系统遇到log file sync这个等待事件,找了一些资料,测试了一些方法,下边把异步提交找到的资料发出来。
一直以来ORACLE对于COMMIT默认采取同步写事务LOG的方式,也就是说,一旦发出COMMIT命令,那么必须等待LGWR将事务相关的所有日志信息都已经从LOG BUFFER写出到REDO LOGFILE以后,才会返回发出COMMIT已完成。从10gR2开始,ORACLE推出了一个新的参数COMMIT_WRITE,可以通过调整此参数实现ORACLE在COMMIT时是采用同步或异步日志,该参数有以下值:
1.IMMEDIATE: 发出COMMIT命令后,立即将相关日志信息从LOG BUFFER写出到REDO LOGFILE,也就是每次提交时都必须做一次磁盘I/O操作,默认选项。
2.WAIT:在日志信息从LOG BUFFER写出到REDO LOGFILE的过程中,必须等待LGWR将所有事务相关的所有日志信息都已经从LOG BUFFER写出到REDO LOGFILE以后,才会返回发出COMMIT已完成,默认选项。
3.NOWAIT:发出COMMIT命令后,不管日志信息从LOG BUFFER写出到REDO LOGFILE是否完成,立即返回COMMIT完成。
4.BATCH:ORACLE会对日志信息进行缓冲,ORACLE会按照自己特定的规则将日志信息从LOG BUFFER批量写出到REDO LOGFILE,也就是说,多个I/O操作将打包成一个批次进行处理,以提高性能。
IMMEDIATE和WAIT是传统的提交方式也是默认的提交方式,NOWAIT和BATCH可以说是10gR2的新特性,ORACLE利用此新特性解决在繁忙的系统中LGWR写是数据库瓶颈的问题,当然,同步COMMIT也可以BATCH,异步COMMIT也可以将日志信息立即写出,以上四个参数值可以自由组合,如下:
COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'
默认情况是:
COMMIT_WRITE=’IMMEDIATE,WAIT’
所以COMMIT_WRITE有下面几种组合:
COMMIT_WRITE='IMMEDIATE,WAIT'
COMMIT_WRITE='IMMEDIATE,NOWAIT'
COMMIT_WRITE='BATCH,WAIT'
COMMIT_WRITE='BATCH,NOWAIT'
由于异步COMMIT不能确保事务的相关日志信息已经全部写出到REDO LOGFILE当中,一旦实例崩溃,可能导致已经COMMIT的事务无法恢复,所以一定看程序是否适合使用异步COMMIT,COMMIT_WRITE是动态参数,可以在程序做适合开启异步COMMIT的时候再使用,使用后应在程序不需要使用异步COMMIT的时候尽快关闭异步COMMIT。比如我们的系统,在批量导入条目数据和图片数据的时候,可以开启异步COMMIT特性,在导入数据完成后,关闭异步COMMIT特性,这样可以解决程序在加载数据的时候,大量的LOG FILE SYNC等待事件,即使此时数据库崩溃,可以删除这个批次的数据,重新导入一次。(当然,数据库崩溃的可能不是很大,要不还要我们DBA干嘛)。
下面是我在本机10gR2版本数据库做的简单演示:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
创建测试表T_COMMIT。
SQL> create table t_commit (id number, name varchar2(30));
表已创建。
将COMMIT_WRITE参数修改为IMMEDIATE,WAIT,这也是默认值。
SQL> alter system set commit_write='immediate,wait';
系统已更改。
向T_COMMIT表中插入10000条数据,没插入一条记录提交一次,记录操作的时间。
S