频繁提交带来的代价小议

记得之前写过一篇，说我当时刚刚接触oracle的时候，一次面试被问到如何处理大量insert语句的提交频率？我当时的回答是模糊的，说尽量分散提交，针对这个错误的回答，之前的文章已经阐述过了。
今天就针对这个问题带来的代价或者影响做个测试。

<!--more-->

频繁的insert&commit不仅仅带来语句维护的难度、失败后工作恢复难度大等等问题，下面我们观察从等待事件上带来的数据。
一般来说每次commit会带来一次的log file sync的等待事件，所以我们就从这个事件和另一个user commits事件来观察。
[oracle@crmdb2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 27 13:52:53 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table test_commit(id number);

Table created.

SQL> desc test_commit;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

SQL> select * from v$mystat where rownum=1;

SID STATISTIC# VALUE
---------- ---------- ----------
1565 0 1
SQL> set linesize 1000;

SQL> Select event,total_waits,time_waited_micro From v$session_event Where Sid=1565 And event='log file sync';

EVENT TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
log file sync 1 434

SQL> Select a.SID,b.NAME,a.VALUE From v$sesstat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And a.SID=1565 And b.name='user commits';

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
1565 user commits 1

上面是测试开始之前的log file sync和user commits指标。
测试1、插入5条记录，5次分别提交。
SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> Select event,total_waits,time_waited_micro From v$session_event Where Sid=1565 And event='log file sync';

EVENT TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
log file sync 6 6422

SQL> Select a.SID,b.NAME,a.VALUE From v$sesstat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And a.SID=1565 And b.name='user commits';

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
1565 user commits 6

SQL>
可以看到log file sync和user commits对应的等待次数、等待时间（毫秒）和次数都增加了。这个在紧张的业务应用中会带来致命的性能问题。所以说每次insert之后紧接着的commit是错误的处理方式，替代的方案应该是全部insert结尾处进行commit，不用担心大量insert之后的commit带来的时间影响问题，任何、无论多大的insert操作，最终的commit完成时间都是大致相同的-瞬间完成，因为在insert过程中，log file是实时写入的，只要log file完成的记录，commit是瞬间完成的。
测试2、5次insert、1次commit。
SQL> insert into test_commit values(1);

1 row created.

SQL> insert into test_commit values(1);

1 row created.

SQL> insert into test_commit values(1);

1 row created.

SQL> insert into test_commit values(1);

1 row created.

SQL> insert into test_commit values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> Select event,total_waits,time_waited_micro From v$session_event Where Sid=1565 And event='log file sync';

EVENT TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
log file sync 7 7233

SQL> Select a.SID,b.NAME,a.VALUE From v$sesstat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And a.SID=1565 And b.name='user commits';

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
1565 user commits 7

SQL>
可以看到，相关的指标只增加了1。另一个可行的处理办法是放到PL/SQL语句块中组织提交处理。
测试3、PL/SQL语句块中组织提交
SQL> declare
2 begin
3 for i in 1..5 loop
4 insert into test_commit values(1);
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> Select event,total_waits,time_waited_micro From v$session_event Where Sid=1565 And event='log file sync';

EVENT TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
log file sync 8 8188

SQL> Select a.SID,b.NAME,a.VALUE From v$sesstat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And a.SID=1565 And b.name='user commits';

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
1565 user commits 12

SQL>
上面我们执行了5次insert、5次commit，可以看到user commits指标虽然增加了5（因为5次commit），但是log file sync指标只是增加了1。

小结
关键词：v$session_event log file sync v$sesstat v$statname user commits
-The End-