COMMIT_WAIT/COMMIT_WRITE参数学习
上周五听eygle谈到COMMIT_WRITE参数，今天正好有时间，学习下。
一直以来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条数据，没插入一条记录提交一次，记录操作的时间。
SQL> begin 2 for i in 1..10000 loop 3 insert into t_commit values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 已用时间: 00: 00: 05.67
在COMMIT_WRITE参数修改为IMMEDIATE,WAIT的情况下，向数据库中插入10000条数据需要5.67秒，查看REDO的相关信息。
SQL> select * from v$sysstat where statistic# in (90,91,138,140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 10017 1439995281
91 redo synch time 8 2671 4215815172
138 redo writes 2 10027 1948353376
140 redo write time 2 2131 3094453259
可见，在这种模式下，redo synch writes一万次，redo writes一万次，这就有可能产生 log file sync等待事件，严重影响性能，这也是程序不要修改一条数据就提交一次，建议批量提交的原因。
再测试下COMMIT_WRITE参数的值为IMMEDIATE,NOWAIT模式下，插入10000条记录，也是每插入一条记录就提交一次，记录时间。
SQL> alter system set commit_write='immediate,nowait'; 系统已更改。 SQL> begin 2 for i in 1..10000 loop 3 insert into t_commit values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 已用时间: 00: 00: 01.14
在COMMIT_WRITE参数的值为IMMEDIATE,NOWAIT的情况下，同样插入10000条记录，只用1.14秒，看下REDO的相关信息。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 10017 1439995281
91 redo synch time 8 2671 4215815172
138 redo writes 2 16805 1948353376
140 redo write time 2 4003 3094453259
可以看到，这次redo synch writes并没有增长，redo writes只增长了6千多次，也就是LOWR只写了6千多次就已经将LOG BUFFER的相关信息写到了REDO LOGFILE，而不是写10000次，还有redo synch time没有发生，而redo write time几乎没有变化，还是2000多毫秒，这种模式需要注意的是，一旦提交完成，而LOWR还没有将所有的REDO BUFFER里面相关的信息写到REDO LOGFILE的时候数据库崩溃，已提交的数据可能会找不回来，这也是这种模式需要慎用的原因。
再测试下COMMIT_WRITE参数的值为BATCH，WAIT模式下，同样插入10000条记录，需要的时间。
SQL> alter system set commit_write='batch,wait'; 系统已更改。 SQL> begin 2 for i in 1..10000 loop 3 insert into t_commit values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 已用时间: 00: 00: 06.20
这次用了6.20秒，竟然比IMMEDIATE,WAIT模式下用了5.67秒的时间还要长，这是由于在COMMIT以后, 客户端需要等待LGWR完成的通知，由于BATCH模式会将其他SESSION的日志信息一起打包批量从LOG BUFFER写入REDO LOGFILE，所以它会查找和等待其他事务的日志信息，再看下BATCH，WAIT模式下LGWR写REDO的相关信息。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 20023 1439995281
91 redo synch time 8 6084 4215815172
138 redo writes 2 26813 1948353376
140 redo write time 2 6167 3094453259
对照IMMEDIATE,NOWAIT模式的信息可以发现，redo synch writes写了10000次，redo writes也写了10000次，redo write time一直很稳定，还是2000毫秒左右，但是redo synch time却用了3400毫秒左右，从这个实验也可以看出redo synch对性能的影响，这家伙用的时间比redo write用的时间都多。
看下BATCH，NOWAIT模式的效果怎么样。
SQL> alter system set commit_write='batch,nowait'; 系统已更改。 SQL> begin 2 for i in 1..10000 loop 3 insert into t_commit values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 已用时间: 00: 00: 01.01
单SESSION情况下，BATCH，NOWAIT模式和IMMEDIATE，NOWAIT用时差不多，看下LGWR写REDO的相关信息。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 20024 1439995281
91 redo synch time 8 6084 4215815172
138 redo writes 2 26835 1948353376
140 redo write time 2 6246 3094453259
看看，这家伙够狠吧，redo synch writes可以说没有发生，redo synch time当然也就没有，redo writes只写了只写了20次左右，redo write time只有几十毫秒，BATCH，NOWAIT将事物的日志信息打包，再由LGWR从LOG BUFFER写到REDO LOGFILE，也就是LOGWR只写了20次左右就写完了提交10000次的日志信息，而且这个时间由以前的2000毫秒降到了几十毫秒，这只是在单SESSION环境下，如果在多SESSION，高并发的环境下，效果会更好一些，可见ORACLE对高并发应用LGWR写瓶颈的改善还是灰常灰常牛X的，但同样值得关注的还是高性能带来的高风险，还好COMMIT_WRITE是动态参数，可以随意调整而不需要重启数据库，这也是我在考虑我们的应用是否使用COMMIT_WRITE参数的原因。
还是看下BATCH模式对高并发环境LGWR写REDO的信息，为了演示更直观，本实验使用BATCH,WAIT模式，而且重启了数据库，清空v$sysstat视图的信息，本实验一共开了5个SESSION，每个SESSION都执行上述实验的SQL，都插入10000条记录，看下LGWR写REDO的信息。
SQL> alter system set commit_write='batch,nowait'; 系统已更改。
5个SESSION都同时执行下面的SQL，每个SESSION都插入10000条记录。
SQL> begin 2 for i in 1..10000 loop 3 insert into t_commit values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。
然后查看LGWR写REDO的信息。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 50010 1439995281
91 redo synch time 8 52530 4215815172
138 redo writes 2 19336 1948353376
140 redo write time 2 8747 3094453259
5个SESSION都插入10000条数据，redo synch writes5万次， redo synch time高达52530毫秒，但redo writes并不是5万次，而是19336次，这是由于BATCH将其他SESSION的日志信息打包处理了。
如果是BATCH，NOWAIT模式，这个结果让我很震惊，也让我想起了2011年的经典台词，至于你信吗？反正我信了！下面是重启数据库后，BATCH，NOWAIT模式下5个SESSION同时插入10000条数据的LGWR写REDO日志的信息。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------- ------ ---------- ----------
90 redo synch writes 8 6 1439995281
91 redo synch time 8 2 4215815172
138 redo writes 2 68 1948353376
140 redo write time 2 489 3094453259
redo synch writes只有6次，还可能不是由于插入数据产生的，可能是ORACLE内部的调用引起的，redo writes只有68次，就写完了所有的日志信息，而且只用了489毫秒！震撼吗？
COMMIT_WRITE是10gR2引入的新特性，11g版本的数据库已经不在使用COMMIT_WRITE参数，11g使用COMMIT_WAIT和COMMIT_LOGGING取替了COMMIT_WRITE参数，如果在11g的数据库中设置了COMMIT_WRITE参数，重启后，将会报ORA-32004错误。
SQL> alter system set commit_write=immediate,nowait; 系统已更改。 SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE 例程已经启动。 Total System Global Area 313860096 bytes Fixed Size 1374304 bytes Variable Size 239077280 bytes Database Buffers 67108864 bytes Redo Buffers 6299648 bytes 数据库装载完毕。 数据库已经打开。
ORACLE会说这是个老掉牙的参数了，我现在已经不用了，你设置这个参数就相当于在参数文件里写了一条垃圾，我根本不用它，但是这个错误不影响数据库的启动和运行。
被COMMIT_LOGGING和COMMIT_WAIT取代的COMMIT_WRITE参数在11g有所增强，COMMIT_LOGGING参数有IMMEDIATE和BATCH两个值可选，COMMIT_WAIT参数有WAIT、NOWAIT和FORCE_WAIT三个值可以选择，ORACLE的馆方文档说COMMIT_LOGGING和COMMIT_WAIT没有默认值，是说SQLPLUS里看不到默认值，实际上ORACLE的COMMIT机制默认是IMMEDIATE和FORCE_WAIT。
SQL> show parameter commit_ NAME TYPE VALUE ------------------------ ----------------- -------- commit_logging string commit_point_strength integer 1 commit_wait string commit_write string
关于11g关于这方面的性能测试不再演示，摘录下11g官方文档有关参数的介绍：
这部分主要对COMMIT_LOGGING参数的简单介绍，主要说COMMIT_LOGGING是个新引进的参数，有IMMEDIATE和BATCH两个值可以选择，可以在会话级和系统级进行设置，RAC的每个节点都可以单独设置，还说如果在COMMIT_WAIT参数设置FORCE_WAIT之后修改COMMIT_LOGGING参数，那么FORCE_WAIT将失效。
COMMIT_WAIT is an advanced parameter used to control when the redo for a commit is flushed to the redo logs. Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly. If the parameter is set to FORCE_WAIT, the default behavior (immediate flushing of the redo log buffer with wait) is used. If this is a system setting, the session level and transaction level (COMMIT_WRITE) options will be ignored. If this is a session level setting, the transaction level options will be ignored. If COMMIT_WAIT is altered after it has been set to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.
一样，这部分也是对COMMIT_WAIT参数的简单介绍，主要说COMMIT_WAIT参数有WAIT、NOWAIT和FORCE_WAIT三个值可以选择，可以在会话级和系统级做设置，RAC的每个节点都可以单独设置，还说了下NOWAIT可能会由于虚假提交丢失数据。
COMMIT_WRITE is an advanced parameter used to control how redo for transaction commits is written to the redo logs. The IMMEDIATE and BATCH options control how redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo for a commit is flushed to the redo logs. Note: The COMMIT_WRITE parameter is deprecated. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.
这部分是对COMMIT_WRITE参数的简单介绍，主要是说COMMIT_WRITE是个过度参数，在11g已经被COMMIT_LOGGING参数和COMMIT_WAIT参数所取代。
COMMIT_WAIT/COMMIT_WRITE参数学习：等您坐沙发呢！
发表评论
