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_WRITE
11G 使用参数 COMMIT_LOGGING 与 COMMIT_WAIT
例如:
10G
COMMIT_WRITE='IMMEDIATE,WAIT'
COMMIT_WRITE='IMMEDIATE,NOWAIT'
COMMIT_WRITE='BATCH,WAIT'
COMMIT_WRITE='BATCH,NOWAIT'
11G
COMMIT_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 instance
ORACLE 例程已经启动。
Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo 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 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo Buffers 7335936 bytes
数据库装载完毕。
数据库已经打开。
我们不进行10G的测试了,11G也一样
2.对11G的参数进行测试
2.1
SQL> 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 INACTIVE
4 5 CURRENT
两个大小为5m 的 logfile ,如果期间写 redo ,会出现切换信息
begin
for i in 1 .. 1000000 loop
insert 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 1948353376
186 redo write time 2 100 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 11 1439995281
2.2
SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='IMMEDIATE' scope=both;
系统已更改。
begin
for i in 1 .. 1000000 loop
insert 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 11 1439995281
2.3
SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;
系统已更改
SQL> begin
2 for i in 1 .. 1000000 loop
3 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 10 1439995281
2.4
SQL> alter system set COMMIT_WAIT='WAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;
系统已更改
SQL> begin
2 for i in 1 .. 1000000 loop
3 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 1 4215815172
198 redo synch writes 2 14 1439995281
没找到合适的测试场景, 这个测试显然没有达到效果.
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_WRITE
11G 使用参数 COMMIT_LOGGING 与 COMMIT_WAIT
例如:
10G
COMMIT_WRITE='IMMEDIATE,WAIT'
COMMIT_WRITE='IMMEDIATE,NOWAIT'
COMMIT_WRITE='BATCH,WAIT'
COMMIT_WRITE='BATCH,NOWAIT'
11G
COMMIT_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 instance
ORACLE 例程已经启动。
Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo 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 bytes
Fixed Size 2240344 bytes
Variable Size 1644167336 bytes
Database Buffers 1.1174E+10 bytes
Redo Buffers 7335936 bytes
数据库装载完毕。
数据库已经打开。
我们不进行10G的测试了,11G也一样
2.对11G的参数进行测试
2.1
SQL> 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 INACTIVE
4 5 CURRENT
两个大小为5m 的 logfile ,如果期间写 redo ,会出现切换信息
begin
for i in 1 .. 1000000 loop
insert 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 1948353376
186 redo write time 2 100 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 11 1439995281
2.2
SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='IMMEDIATE' scope=both;
系统已更改。
begin
for i in 1 .. 1000000 loop
insert 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 11 1439995281
2.3
SQL> alter system set COMMIT_WAIT='NOWAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;
系统已更改
SQL> begin
2 for i in 1 .. 1000000 loop
3 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 0 4215815172
198 redo synch writes 2 10 1439995281
2.4
SQL> alter system set COMMIT_WAIT='WAIT' scope=both;
系统已更改。
SQL> alter system set COMMIT_LOGGING='BATCH' scope=both;
系统已更改
SQL> begin
2 for i in 1 .. 1000000 loop
3 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 1948353376
186 redo write time 2 102 3094453259
196 redo synch time 2 1 4215815172
198 redo synch writes 2 14 1439995281
没找到合适的测试场景, 这个测试显然没有达到效果.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7569309/viewspace-2135321/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7569309/viewspace-2135321/