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操作将打包成一个批次进行处理,以提高性能。10g 使用参数 COMMIT_WRITE11G 使用参数 COMMIT_LOGGING 与 COMMIT_WAIT例如:10GCOMMIT_WRITE='IMMEDIATE,WAIT'COMMIT_WRITE='IMMEDIATE,NOWAIT'COMMIT_WRITE='BATCH,WAIT'COMMIT_WRITE='BATCH,NOWAIT'11GCOMMIT_LOGGING='IMMEDIATE'COMMIT_WAIT='FORCE_WAIT' -- commit_wait={nowait|wait|force_wait}1.对10G的参数进行测试SQL> alter system set COMMIT_WRITE='IMMEDIATE,WAIT' scope=spfile;系统已更改。SQL> startup force;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE 例程已经启动。Total System Global Area 1.2827E+10 bytesFixed Size 2240344 bytesVariable Size 1644167336 bytesDatabase Buffers 1.1174E+10 bytesRedo Buffers 7335936 bytes数据库装载完毕。数据库已经打开。我的环境是11.2.0.3 看来参数 COMMIT_WRITE 已经被遗弃了SQL> alter system reset commit_write scope=spfile;系统已更改。SQL> startup force;ORACLE 例程已经启动。Total System Global Area 1.2827E+10 bytesFixed Size 2240344 bytesVariable Size 1644167336 bytesDatabase Buffers 1.1174E+10 bytesRedo Buffers 7335936 bytes数据库装载完毕。数据库已经打开。我们不进行10G的测试了,11G也一样2.对11G的参数进行测试2.1SQL> alter system set COMMIT_WAIT='FORCE_WAIT' scope=both;系统已更改。SQL> alter system set COMMIT_LOGGING='IMMEDIATE' scope=both;系统已更改。进行参数设置,其实是默认的,还有可以不重启数据库.构造测试场景SQL> select group#,bytes/1024/1024,STATUS from v$log;GROUP# BYTES/1024/1024 STATUS---------- --------------- ----------------2 5 INACTIVE4 5 CURRENT两个大小为5m 的 logfile ,如果期间写 redo ,会出现切换信息beginfor i in 1 .. 1000000 loopinsert into system.wh values(100,i);end loop;commit;end;/SQL> select * from v$sysstat where name in ('redo writes','redo write time','redo synch time','redo synch writes');STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- ---------- ----------184 redo writes 2 166 1948353376186 redo write time 2 100 3094453259196 redo synch time 2 0 4215815172198 redo synch writes 2 11 14399952812.2SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;系统已更改。SQL> alter system set COMMIT_LOGGING='IMMEDIATE' scope=both;系统已更改。beginfor i in 1 .. 1000000 loopinsert into system.wh values(100,i);end loop;commit;end;/SQL> select * from v$sysstat where name in ('redo writes','redo write time','redo synch time','redo synch writes');STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- ---------- ----------184 redo writes 2 171 1948353376186 redo write time 2 102 3094453259196 redo synch time 2 0 4215815172198 redo synch writes 2 11 14399952812.3SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;系统已更改。SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;系统已更改SQL> begin2 for i in 1 .. 1000000 loop3 insert into system.wh values(100,i);4 end loop;5 commit;6 end;7 /PL/SQL 过程已成功完成。SQL> select * from v$sysstat where name in ('redo writes','redo write time','redo synch time','redo synch writes');STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- ---------- ----------184 redo writes 2 164 1948353376186 redo write time 2 102 3094453259196 redo synch time 2 0 4215815172198 redo synch writes 2 10 14399952812.4SQL> alter system set COMMIT_WAIT='WAIT' scope=both;系统已更改。SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;系统已更改SQL> begin2 for i in 1 .. 1000000 loop3 insert into system.wh values(100,i);4 end loop;5 commit;6 end;7 /PL/SQL 过程已成功完成。SQL> select * from v$sysstat where name in ('redo writes','redo write time','redo synch time','redo synch writes');STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- ---------- ----------184 redo writes 2 155 1948353376186 redo write time 2 102 3094453259196 redo synch time 2 1 4215815172198 redo synch writes 2 14 1439995281没找到合适的测试场景, 这个测试显然没有达到效果.
oracle commit满,Oracle commit 的异步提交
最新推荐文章于 2021-04-08 22:28:14 发布