当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

有关COMMIT太频繁问题解决方案

1.问题描述
在之前测试人员生成的ADDM报告,当导入数据的时候,数据库存在大量的提交和回退事件。

查找结果 1: 提交和回退
受影响的是 .51 个活动会话, 占总活动的 58.55%。
-------------------------------
在执行 COMMIT 和 ROLLBACK 操作时, 等待 "日志文件同步" 事件消耗了大量数据库时间。
   建议案 1: 应用程序分析
   估计的收益为 .51 个活动会话, 占总活动的 58.55%。
   --------------------------------
   操作
      研究应用程序逻辑, 了解通过增加事务处理的大小来减少 COMMIT 操作数量的可能性。
   原理
      应用程序每分钟执行 3364 个事务处理, 每个事务处理的平均重做日志大小为 2369 字节。
   建议案 2: 主机配置
   估计的收益为 .51 个活动会话, 占总活动的 58.55%。
   --------------------------------
   操作
      研究改善对联机重做日志文件的 I/O 性能的可能性。
   原理
      对联机重做日志文件执行写入的平均大小为 2 K, 每次写入的平均时间为 8 毫秒。
   原理
      重做日志文件上的总 I/O 吞吐量的读取为每秒 0 K, 写入为每秒 153 K。
   原理
      重做日志 I/O 吞吐量由以下部分构成: RMAN 和恢复占 0%, 日志写进程占 100%,
      归档程序占 0%, 流 AQ 占 0%,
      所有其他活动占 0%。
   导致查找结果的故障现象:
   ------------
      等待类 "提交" 消耗了大量数据库时间。
      受影响的是 .51 个活动会话, 占总活动的 58.55%。

在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等待事件占用的时间。

本文固定链接: http://www.dbdream.com.cn/2012/01/%e6%9c%89%e5%85%b3commit%e5%a4%aa%e9%a2%91%e7%b9%81%e9%97%ae%e9%a2%98%e8%a7%a3%e5%86%b3%e6%96%b9%e6%a1%88/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年01月04日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 有关COMMIT太频繁问题解决方案 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , ,

有关COMMIT太频繁问题解决方案:目前有2 条留言

  1. 板凳
    dony:

    有一点不太明白,请指点:你插入10000行数据提交一次的情况,为什么后面总结用时,0.5s,每1/10提交一次呢?

    2013-01-03 21:56 [回复]
  2. 沙发
    streamsong:

    dony :
    十分感谢这位兄弟的意见,是我没写清楚,0.5s和1/10是上次1000条提交一次的,是和本次10000条提交一次做对比。

    2013-01-08 21:40 [回复]

发表评论

快捷键:Ctrl+Enter