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

最新推荐文章于 2026-01-07 10:43:04 发布

原创最新推荐文章于 2026-01-07 10:43:04 发布 · 2.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #parameters #session #oracle #insert #access

博客对比了Oracle中插入相同数量记录时单条提交和成批提交产生的日志量，发现成批提交通常远小于单条提交。还对比了设置transaction_auditing为false/true前后的日志生成量差异，指出该参数设置会影响大量短小事务的日志生成情况。

1：插入相同数量记录的单条提交和成皮提交之间的对比
我们可以看出分别是 25112152 - 8024588 = 17087564
8024588 - 13384 = 8011204
对比相差达到9M

SQL> show parameters transaction_auditing

NAME TYPE VALUE
------------------------------------ ------- --------------------
transaction_auditing boolean TRUE
SQL>

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

NAME VALUE
---------------------------------------------------------------- ----------
redo size 13384

SQL> begin
2 for i in 1..25835 loop
3 insert into rainy.t select * from dba_objects where rownum = 1;
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

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

NAME VALUE
---------------------------------------------------------------- ----------
redo size 8024588

SQL> begin
2 for i in 1..25835 loop
3 insert into rainy.t select * from dba_objects where rownum = 1;
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 25112152

2 : 关于设置 transaction_auditing = false/true 前后的对比
13892572 - 12332 = 13880240

对比 1 中相同状况下的结果 17087564 ，可以看出体制生成两相差了大约3M

SQL> show parameters transaction_auditing

NAME TYPE VALUE
------------------------------------ ------- --------------------
transaction_auditing boolean FALSE
SQL>

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

NAME VALUE
---------------------------------------------------------------- ----------
redo size 12332

SQL> begin
2 for i in 1..25835 loop
3 insert into rainy.t select * from dba_objects where rownum = 1;
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 13892572

结论：
成批提交和但条提交所产生的日志量差异巨大，成批提交通常远小于单条提交

关于transaction_auditing ，我们先看oracle的解释
If TRANSACTION_AUDITING is true, Oracle generates a special redo record that contains the user logon name, username, the session ID, some operating system information, and client information. For each successive transaction, Oracle generates a record that contains only the session ID. These subsequent records link back to the first record, which also contains the session ID.

These records might be useful if you are using a redo log analysis tool. You can access the records by dumping the redo log.

If TRANSACTION_AUDITING is false, no redo record will be generated

这个意思是，如果设置为true，则oracle将对事务进行审计，也就是日志里面为事务产生一几乎固定大小的信息，包括用户名、session is、一些操作信息、client信等（通过dump logfile可以看到），这些信息在使用logmnr等工具分析的时候可以有用的。如果设置该参数为false，则在大量的短小事务的情况下将产生不可忽略的日志生成量的差异