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

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 ?

1. scott@WISON>create table t as select * from all_objects;
3. Table created.
5. 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 ?

1. scott@WISON>create table t_10 as select * from t where 1=0;
3. Table created.
5. scott@WISON>set autotrace on statistics;
6. scott@WISON>set timing on
7. scott@WISON>insert into t_10 select * from t where rownum <=10;
9. 10 rows created.
11. Elapsed: 00:00:00.06 插入时间
13. Statistics
14. ----------------------------------------------------------
15. 451 recursive calls
16. 56 db block gets
17. 337 consistent gets
18. 1 physical reads
19. 7284 redo size
20. 919 bytes sent via SQL*Net to client
21. 1018 bytes received via SQL*Net from client
22. 4 SQL*Net roundtrips to/from client
23. 2 sorts (memory)
24. 0 sorts (disk)
25. 10 rows processed
27. scott@WISON>commit;
29. Commit complete.
31. 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. 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.00s3:插入1000行

[sql] view plain copy print ?

1. scott@WISON>insert into t_10 select * from t where rownum <=1000;
3. 1000 rows created.
5. Elapsed: 00:00:00.02
7. Statistics
8. ----------------------------------------------------------
9. 39 recursive calls
10. 143 db block gets
11. 180 consistent gets
12. 7 physical reads
13. 104880 redo size
14. 919 bytes sent via SQL*Net to client
15. 1020 bytes received via SQL*Net from client
16. 4 SQL*Net roundtrips to/from client
17. 1 sorts (memory)
18. 0 sorts (disk)
19. 1000 rows processed
21. scott@WISON>commit;
23. Commit complete.
25. Elapsed: 00:00:00.12
26. 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 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> 插入10000行

[sql] view plain copy print ?

1. scott@WISON>insert into t_10 select * from t where rownum <=10000;
3. 10000 rows created.
5. Elapsed: 00:00:00.10
7. Statistics
8. ----------------------------------------------------------
9. 487 recursive calls
10. 1808 db block gets
11. 596 consistent gets
12. 121 physical reads
13. 1102708 redo size
14. 919 bytes sent via SQL*Net to client
15. 1021 bytes received via SQL*Net from client
16. 4 SQL*Net roundtrips to/from client
17. 1 sorts (memory)
18. 0 sorts (disk)
19. 10000 rows processed
21. scott@WISON>commit;
23. Commit complete.
25. Elapsed: 00:00:00.01
26. 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 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> 发现，插入10行，1000行，10000行，commit的时间都很短。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-1057513/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7916042/viewspace-1057513/