http://www.dbdream.org/?tag=commit
在AWR报告中,log file sync等待事件显著。
2.问题原因
产生此事件的主要原因是,应用程序在插入数据的时候,采用没插入一条数据,提交一次,通常每批次数据量都较大,而且还会插入日志信息,日志也是没插入一条提交一次,这样对数据库来说,COMMIT相当频繁,而默认情况下,COMMIT会触发LGWR进程将LOG BUFFER中和事物相关的日志信息写入到REDO LOGFILE文件中,这个过程默认LGWR写一次,log file sync会发生一次,log file sync会占用大量数据库时间。
3.解决方法和案例演示
下面是插入10000条记录,每插入一条提交一次,模拟应用程序的情况。
SQL> begin 2 for i in 1..10000 loop 3 insert into t_insert values (i, 'a'||i); 4 commit; 5 end loop; 6 end; 7 /PL/SQL 过程已成功完成。已用时间: 00: 00: 01.09
插入10000条记录,用时1.09秒。
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都发生了10000次,而且redo synch time比redo write time的事件还要长,默认情况下redo synch writes只有完成,才会返回COMMIT已完成到客户端,其他事物才可以进行操作,给我们的感觉也就是数据库会有短暂的停顿,虽然这个过程是毫秒级的,但是当事物量非常多的情况下,这个时间也是很漫长的。
在看下每1000条提交一次的情况。
SQL> begin 2 for i in 1..10000 loop 3 insert into t_insert values (i, 'a'||i); 4 if mod(i, 1000) = 0 then 5 commit; 6 end if; 7 end loop; 8 commit; 9 end; 10 /PL/SQL 过程已成功完成。已用时间: 00: 00: 00.51
用时0.51秒,此时redo synch writes和redo writes都只发生了10次。
在看下插入10000条数据,值提交一次的情况。
SQL> begin 2 for i in 1..10000 loop 3 insert into t_insert values (i, 'a'||i); 4 end loop; 5 commit; 6 end; 7 /PL/SQL 过程已成功完成。已用时间: 00: 00: 00.50
这次用了0.50秒。由于上一次每1/10提交一次,而且单条数据比较小,10000次提交和1000次提交相比不明显,如果数据量比较大,这个差距还是很明显的。
如果使用数组,将10000条记录封装成一条SQL,效果非常显著。
SQL> declare 2 type t_num is table of number index by binary_integer; 3 type t_var is table of varchar2(30) index by binary_integer; 4 v_num t_num; 5 v_var t_var; 6 begin 7 for i in 1..10000 loop 8 v_num(i) := i; 9 v_var(i) := 'a'||i; 10 end loop; 11 forall i in 1..10000 12 insert into t_insert values (v_num(i), v_var(i)); 13 commit; 14 end; 15 /PL/SQL 过程已成功完成。已用时间: 00: 00: 00.04
使用数组只用了0.4秒,但这种方式应用程序可能不支持。
4.建议
由于应用程序每次插入的单条数据比较大,而且总数据条数也较多,建议批量提交(100-1000次提交一次),以减少log file sync等待事件占用的时间。
Categories:blog, oracle, 技术文章Tags:COMMIT, log file sync, ORACLE, 提交太频繁