本文深入探讨了SQL中undo生成、commit与rollback的操作机制，包括undo生成的具体情况、commit和rollback的操作流程及其对数据库性能的影响。通过实验展示了undo生成的数量与事务大小的关系，以及commit与rollback操作的性能对比。此外，文章还详细解释了块清除的概念和其对数据库性能的优化作用。
1、查看undo生成的具体情况
(1)创建一个空表。
(2)对它做一个全表扫描，观察读表所执行的I/O数量。
(3)在表中填入许多行（但没有提交）。
(4)回滚这个工作，并撤销。
(5)再次进行全表扫描，观察所执行的I/O数量。
u1@ORCL> alter session set deferred_segment_creation = false; --取消延迟段创建。
会话已更改。
u1@ORCL> create table t
2 as
3 select *
4 from all_objects
5 where 1=0;
表已创建。
u1@ORCL> select * from t;
未选定行
u1@ORCL> set autotrace traceonly statistics
u1@ORCL> select * from t; --取第二次结果。
未选定行
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3
consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
u1@ORCL> set autotrace off
u1@ORCL> insert into t select * from all_objects;
已创建71071行。
u1@ORCL> rollback;
回退已完成。
u1@ORCL> select * from t;
未选定行
u1@ORCL> set autotrace traceonly statistics
u1@ORCL> select * from t; --取第二次结果。
未选定行
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1073
consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
前面的insert导致将一些块增加到表的高水位线之下，这些快没有因为回滚而消失，他们还在那里，而且已经格式化，只不过现在为空。全表扫描必须读取这些块，看看其中是否包含行。说明回滚只是一种逻辑上“将数据库恢复原状态”的操作。数据库并不会完全恢复原状，只是逻辑上相同而已。
2、commit做什么
commit的响应时间相当“平”，而不论事务大小。在数据库中执行commit之前，困难的工作都已经做了。
已经在SGA中生成了undo块。
已经在SGA中生成了已经修改数据块。
已经在SGA中生成了对应前两项的缓存redo。
取决于前三项的大小，以及这些工作花费的时间，前面的某个数据（或某些数据）可能已经刷新输出到磁盘。
已经得到了所需的全部锁。
执行commit时，余下的工作只是：
为事务生成一个SCN。
LGWR将所有余下的缓存重做日志条目写至磁盘，并把SCN记录到在线重做日志文件中。这一步就是真正的commit。如果出现了这一步，事务条目会从v$transaction中“删除”，这说明我们已经提交。
v$lock中记录着我们的会话持有的锁，这些锁都将被释放，而排队等待这些锁的每一个人都会被唤醒，可以继续完成他们的工作。
如果事务修改的某些块还在缓冲区中，则会以一种快速的模式访问并“清理”。块清除是指清除存储在数据库块首部的与锁相关的信息。市值上讲，我们在清除块上的事务信息，这样下一个访问这个块的人就不用再这么做了。我们采用一种无需生成redo的方式来完成块清除，这样可以省去以后的大量工作。
u1@ORCL> declare
2 l_redo number;
3 l_cpu number;
4 l_ela number;
5 begin
6 dbms_output.put_line
7 ( '-' || ' Rows' || ' Redo' ||
8 ' CPU' || ' Elapsed' );
9 for i in 1 .. 6
10 loop
11 l_redo := get_stat_val( 'redo size' );
12 insert into t select * from big_table where rownum <= power(10,i);
13 l_cpu := dbms_utility.get_cpu_time;
14 l_ela := dbms_utility.get_time;
15 commit work write wait;
16 --rollback;
17 dbms_output.put_line
18 ( '-' ||
19 to_char( power( 10, i ), '9,999,999') ||
20 to_char( (get_stat_val('redo size')-l_redo), '999,999,999' ) ||
to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999' ) ||
to_char( (dbms_utility.get_time-l_ela), '999,999' ) );
23 end loop;
24 end;
25 /
- Rows Redo CPU Elapsed
- 10 2,816 0 0
- 100 10,056 0 0
- 1,000 113,288 1 0
- 10,000 1,137,992 0 1
- 100,000 12,078,944 0 14
- 1,000,000 121,545,988 0 6
PL/SQL 过程已成功完成。
可以看到随着生成不同数量的redo（从2816字节到121M），却几乎测不出commit时间的差异。
3、rollback做什么
rollback时，要做以下工作。
撤销已做的所有修改。其完成方式如下：从undo段读回数据，然后实际上逆向执行前面所做的操作，并将undo条目标记为已用。如果先前插入了一行，rollback会将其删除。如果更新了一行，回滚就会取消更新。如果删除一行，回滚将把它再次插入。
会话持有的所有锁都将释放，如果有人在排队等待我们持有的锁，就会被唤醒。
u1@ORCL> declare
2 l_redo number;
3 l_cpu number;
4 l_ela number;
5 begin
6 dbms_output.put_line
7 ( '-' || ' Rows' || ' Redo' ||
8 ' CPU' || ' Elapsed' );
9 for i in 1 .. 6
10 loop
l_redo := get_stat_val( 'redo size' );
insert into t select * from big_table where rownum <= power(10,i);
l_cpu := dbms_utility.get_cpu_time;
l_ela := dbms_utility.get_time;
--commit work write wait;
16 rollback;
17 dbms_output.put_line
18 ( '-' ||
19 to_char( power( 10, i ), '9,999,999') ||
20 to_char( (get_stat_val('redo size')-l_redo), '999,999,999' ) ||
21 to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999' ) ||
22 to_char( (dbms_utility.get_time-l_ela), '999,999' ) );
23 end loop;
24 end;
25 /
- Rows Redo CPU Elapsed
- 10 3,036 0 0
- 100 10,632 0 1
- 1,000 120,576 1 1
- 10,000 1,212,156 2 2
- 100,000 12,847,336 3 6
- 1,000,000 129,366,416 99 1,075
PL/SQL 过程已成功完成。
CPU占用时间与运行时间的差别绝对是所修改数据量的一个函数。
与此不同，commit只是将重做日志缓冲区中剩余的数据刷新输出到磁盘。与rollback相比，commit完成的工作非常少。回滚操作的开销很大，因为你花了大量时间做的工作，还要花大量的时间撤销这些工作。
4、块清除
块清除，即删除所修改数据块上与“锁定”有关的信息。数据锁实际上是数据的属性，存储在块首部。这就带来一个副作用，下一次访问这个块时，可能必须清理这个块，换句话说，要将这些事务信息删除。这个动作会生成redo，并导致块变脏（原本并不脏，因为数据本身没有修改），这说明一个简单的select也可能生成redo，而且可能导致完成下一个检查点时将大量的块写至磁盘。不过，在大多数正常的情况下，这是不会发生的。如果系统中主要是小型或中小型事务（OLTP），或者数据仓库会执行直接路径加载或使用DBMS_STATS在加载操作后分析表，你会发现块通常已经得到“清理”。
commit时处理的步骤之一是：如果块还在SGA中，就要再次访问这些块，如果可以访问（没有别人在修改这些块），则对这些块完成清理。这个活动称为提交清除，即清除已修改块上的事务信息。最理想的是，commit可以完成块清除，这样后面的select就不必再清理了。只有块的update才回真正清楚残余的事务信息，由于update已经在生成reodo，所以注意不到这个清除工作。
如果缓冲区缓存设置为可以缓存3000个块，oracle会为我们维护最多300个块（3000的10%）。commit作用时，oracle会处理这些包含20个块指针的列表，如果块仍可用，它会执行一个很快的清理。所以，只要我们修改的块数没有超过缓存中总块数的10%，而且块仍在缓存中并且是可用的，oracle就会在提交时清理这些块。否则，它只会将其忽略（也就是说不清理）。如果oracle不对块完成这种延迟清除，那么commit的处理就会与事务本身一样长。提交必须重新访问每一个块。
5、什么操作会生成最多和最少的undo
一般来讲，insert生成的undo最少，因为只需记录要“删除”的一个rowid。update一般排在第二，你可能只更新了整个数据行中很少的一部分，必须在undo中记录行的一小部分。一般来讲，delete生成的undo最多。对于delete，必须把整行的前映象记录到undo段中。与加索引列的更新相比，对一个未加索引的列进行更新不仅执行得更快，生成的undo也会少得多。
扫一扫
1328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
