commit究竟发生了什么，其实很少的事情需要做

把SCN号写入日志文件
LGWN把剩余的redo写入日志文件
所有locks被释放掉
进行块清理。块清理是指将，将保存在这个block中的事务信息清理掉

[@more@]

1.从理解LOGW什么时候将redo log buffer中把redo写入redo log file理解commit;

当redo log buffer 三分之一满的时候写
当commit的时候写
当发生日志切换的时候写
当产生的redo满1M的时候写
当DBWN要写的时候，先写redo
每3秒写

也就是说，redo是以一种相对连续的方式写入日志文件的。所以不管一个事务产生了多少redo，其实commit的时候做的工作都差不多，因为大部门的redo已经写入日志文件了

2.commit的时候发生了什么

把SCN号写入日志文件
LGWN把剩余的redo写入日志文件
所有locks被释放掉
进行块清理。块清理是指将，将保存在这个block中的事务信息清理掉。

所以实际上每次commit的时候做的工作都很少，最大的工作就是将redo写入日志文件了，但是大部门redo已经写入到日志文件了；但是这也不代表，每次修改一个块的时候，都要去commit，这样会增加对日志文件的竞争（日志文件是个共享结构），同时也增加了对latches的竞争（保护对共享结构的访问）。所以应该根据逻辑事务的大小来决定什么时候commit。

3.下面一个例子，产生不同大小的redo，来说明提交的时候，做的工作时间上差不多

s1：创建一个大表

[sql] view plain copy print ?

scott@WISON>create table t as select * from all_objects;
Table created.
scott@WISON>

scott@WISON>create table t as select * from all_objects;

Table created.

scott@WISON>

s2：创建一个需要插入10行数据的表t_10，设置自动追踪

[sql] view plain copy print ?

scott@WISON>create table t_10 as select * from t where 1=0;
Table created.
scott@WISON>set autotrace on statistics;
scott@WISON>set timing on
scott@WISON>insert into t_10 select * from t where rownum <=10;
10 rows created.
Elapsed: 00:00:00.06 插入时间
Statistics
----------------------------------------------------------
451 recursive calls
56 db block gets
337 consistent gets
1 physical reads
7284 redo size
919 bytes sent via SQL*Net to client
1018 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
scott@WISON>commit;
Commit complete.
Elapsed: 00:00:00.00

scott@WISON>create table t_10 as select * from t where 1=0;

Table created.

scott@WISON>set autotrace on statistics;
scott@WISON>set timing on
scott@WISON>insert into t_10 select * from t where rownum <=10;

10 rows created.

<strong>Elapsed: 00:00:00.06 插入时间
</strong>
Statistics
----------------------------------------------------------
        451  recursive calls
         56  db block gets
        337  consistent gets
          1  physical reads
      <strong> 7284  redo size</strong>
        919  bytes sent via SQL*Net to client
       1018  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         10  rows processed

scott@WISON>commit;

Commit complete.

Elapsed: 00:00:00.00

s3:插入1000行

[sql] view plain copy print ?

scott@WISON>insert into t_10 select * from t where rownum <=1000;
1000 rows created.
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
39 recursive calls
143 db block gets
180 consistent gets
7 physical reads
104880 redo size
919 bytes sent via SQL*Net to client
1020 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
scott@WISON>commit;
Commit complete.
Elapsed: 00:00:00.12
scott@WISON>

scott@WISON>insert into t_10 select * from t where rownum <=1000;

1000 rows created.

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
         39  recursive calls
        143  db block gets
        180  consistent gets
          7  physical reads
     <strong>104880  redo size
</strong>        919  bytes sent via SQL*Net to client
       1020  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

scott@WISON>commit;

Commit complete.

<strong>Elapsed: 00:00:00.12</strong>
scott@WISON>

插入10000行

[sql] view plain copy print ?

scott@WISON>insert into t_10 select * from t where rownum <=10000;
10000 rows created.
Elapsed: 00:00:00.10
Statistics
----------------------------------------------------------
487 recursive calls
1808 db block gets
596 consistent gets
121 physical reads
1102708 redo size
919 bytes sent via SQL*Net to client
1021 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@WISON>commit;
Commit complete.
Elapsed: 00:00:00.01
scott@WISON>

scott@WISON>insert into t_10 select * from t where rownum <=10000;

10000 rows created.

Elapsed: 00:00:00.10

Statistics
----------------------------------------------------------
        487  recursive calls
       1808  db block gets
        596  consistent gets
        121  physical reads
   <strong> 1102708  redo size
</strong>        919  bytes sent via SQL*Net to client
       1021  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

scott@WISON>commit;

Commit complete.

<strong>Elapsed: 00:00:00.01</strong>
scott@WISON>

发现，插入10行，1000行，10000行，commit的时间都很短。

ITPub博客

oracle commit的时候究竟发生了什么

最新文章