一、commit_write参数解释和组合
SQL> show parameter commit_write;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_write string
- IMMEDIATE: 发出COMMIT命令后，立即将相关日志信息从LOG BUFFER写出到REDO LOGFILE，也就是每次提交时都必须做一次磁盘I/O操作，默认选项。
- WAIT：日志信息从LOG BUFFER写出到REDO LOGFILE的过程中，必须等待LGWR将所有事务相关的所有日志信息都已经从LOG BUFFER写出到REDO LOGFILE以后，才会返回发出COMMIT已完成，默认选项。
- NOWAIT：发出COMMIT命令后，不管日志信息从LOG BUFFER写出到REDO LOGFILE是否完成，立即返回COMMIT完成。
- BATCH：日志需要写入时，ORACLE会对日志信息进行缓冲，Oracle会按照自己特定的规则将日志信息从LOG BUFFER批量写出到REDO LOGFILE，也就是说多个I/O操作将打包成一个批次进行处理，以提高性能。
IMMEDIATE和WAIT是传统的提交方式也是默认的提交方式，NOWAIT和BATCH可以说是10gR2的新特性，ORACLE利用此新特性解决在繁忙的系统中LGWR写是数据库瓶颈的问题，以上四个参数值可以自由组合，如下：
COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'
COMMIT_WRITE='IMMEDIATE,WAIT'
COMMIT_WRITE='IMMEDIATE,NOWAIT'
COMMIT_WRITE='BATCH,WAIT'
COMMIT_WRITE='BATCH,NOWAIT'
默认情况是:COMMIT_WRITE=‘IMMEDIATE,WAIT’
immediate|batch：此时oracle在需要进行日志写入时会根据具体是immediate还是batch来看是否需要马上写入到日志文件中
wait|nowait: 此时前台进程会根据是wait还是nowait来看是否需要等待LGWR进程写入日志完成后反馈给用户进程才会返回commit已完成
二、异步commit的场景
如果设置NOWAIT则表示启用异步COMMIT，异步COMMIT不能确保事务的相关日志信息已经全部写出到REDO LOGFILE当中，如果实例崩溃可能导致已经COMMIT的事务无法恢复
1、一般只是程序做压力测试
2、系统存在大量的LOG FILE SYNC等待需要解决（当然系统可以忍受数据库宕机后丢失部分数据）
三、commit_write参数测试
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t_commit (id number, name varchar2(30));
1、将COMMIT_WRITE参数修改为IMMEDIATE,WAIT，这也是默认值：
SQL> alter system set commit_write='immediate,wait';
SQL> begin
for i in 1..10000 loop
insert into t_commit values(i,'a'||i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.12
2、将COMMIT_WRITE参数修改为BATCH,WAIT
SQL> alter system set commit_write='batch,wait';
System altered.
Elapsed: 00:00:00.01
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 procedure successfully completed.
Elapsed: 00:00:10.51
3、将COMMIT_WRITE参数修改为IMMEDIATE,NOWAIT
SQL> alter system set commit_write='immediate,nowait';
System altered.
Elapsed: 00:00:00.01
SQL> select * from v$sysstat where statistic# in (200,202,212,220);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
200 redo writes 2 602 1948353376
202 redo write time 2 102 3094453259
212 redo synch time 2 0 4215815172
220 redo synch writes 2 3 1439995281
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 procedure successfully completed.
Elapsed: 00:00:00.79
SQL> select * from v$sysstat where statistic# in (200,202,212,220);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
200 redo writes 2 1177 1948353376
202 redo write time 2 175 3094453259
212 redo synch time 2 0 4215815172
220 redo synch writes 2 3 1439995281
immediat，nowait选项下oracle还是会每次commit都会进行日志写入，但是这个日志写入是异步的，这里redo writes为500到600，redo synch writes为0
4、将COMMIT_WRITE参数修改为BATCH,NOWAIT
SQL> alter system set commit_write='batch,nowait';
System altered.
Elapsed: 00:00:00.01
SQL> select * from v$sysstat where statistic# in (200,202,212,220);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
200 redo writes 2 1227 1948353376
202 redo write time 2 182 3094453259
212 redo synch time 2 0 4215815172
220 redo synch writes 2 3 1439995281
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 procedure successfully completed.
Elapsed: 00:00:00.76
SQL> select * from v$sysstat where statistic# in (200,202,212,220);
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
200 redo writes 2 1234 1948353376
202 redo write time 2 186 3094453259
212 redo synch time 2 0 4215815172
220 redo synch writes 2 3 1439995281
BATCH，NOWAIT选项下Oracle会将事物的日志信息打包，再由LGWR从LOG BUFFER写到REDO LOGFILE。redo waits只有7次，redo synch writes为0次，如果在多SESSION高并发的环境下效果会更好一些，batch、nowait对于日志写入的性能提成更加明显，相比immediate，nowait选项会更加高效。
三、Oracle 11g使用COMMIT_WAIT和COMMIT_LOGGING取替了COMMIT_WRITE参数,不过设置commit_write参数依然会对数据库的日志写入行为生效，虽然oracle 11g提示commit_write是个废弃的参数
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> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 1308626160 bytes
Database Buffers 251658240 bytes
Redo Buffers 7471104 bytes
Database mounted.
Database opened.
1、commit_wait参数
COMMIT_WAIT参数有WAIT、NOWAIT和FORCE_WAIT三个值可以选择
SQL> show parameter commit_wait;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_wait string
这部分也是对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.
2、commit_logging参数
COMMIT_LOGGING参数有IMMEDIATE和BATCH两个值可选
SQL> show parameter commit_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_logging string
这部分主要对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.
