跟事务相关的日志生成量的对比

alexzeng

下面是我做的实验:

SQL> conn / as sysdba
Connected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select name,value from v$sysstat where name like 'redo size';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 88813560

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like 'redo size';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 91160824

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like 'redo size';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 96090180

SQL> select 91160824-88813560
2 commit1 from dual;

COMMIT1
----------
2347264

SQL> select 96090180-91160824 commitN from dual;

COMMITN
----------
4929356

相同数量记录的单条提交 产生的redo log 是 成批一次性提交 的 2 倍之多

烟囱

学习了.批量修改数据得考虑将批量数据分段提交,不要逐条提交了.

rollingpig

呵呵，老贴子了

实际上，多数情况下，逐条提交和批量数据分段提交的最大问题不只是redo log 的量。更多是在 log file sync 的等待上