《Expert one on one Oracle》- 重做回滚- 笔记-3

临时表以及重做/回滚

Oracle不会为临时表的数据块产生重做日志，所以临时表上的操作都是不可“恢复”的。当对临时标的数据块进行更改的时候，更改的纪录不会在重做日志中记录。然而，临时表会产生回滚，回滚信息会被重做日志纪录。所以，临时表会产生重做日志。临时表之所以会产生回滚信息，这是为了支持事务中回滚。例如：临时表和普通表一样也有约束（constrain），所以当不满足约束的数据更新或者插入的临时表的时候，必须能够回滚。

插入临时表产生的回滚数据非常少，查询临时表不产生回滚数据。但是更新和删除临时表数据将会产生较多的回滚信息。

SQL> create table perm
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 /
Table created.

SQL>
SQL> create global temporary table temp
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 on commit preserve rows
6 /
Table created.

SQL> create or replace procedure do_sql( p_sql in varchar2 )
2 as
3 l_start_redo number;
4 l_redo number;
5 begin
6 select value into l_start_redo from redo_size;
7
8 execute immediate p_sql;
9 commit;
10
11 select value-l_start_redo into l_redo from redo_size;
12
13 dbms_output.put_line
14 ( to_char(l_redo,'9,999,999') ' bytes of redo generated for "'
15 substr( replace( p_sql, chr(10), ' '), 1, 25 ) '"...' );
16 end;
17 /

Procedure created.

SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> begin
2 do_sql( 'insert into perm (x,y,z)
3 select 1,1,1
4 from all_objects
5 where rownum <= 500' );
6
7 do_sql( 'insert into temp (x,y,z)
8 select 1,1,1
9 from all_objects
10 where rownum <= 500' );
11
12 do_sql( 'update perm set x = 2' );
13 do_sql( 'update temp set x = 2' );
14
15 do_sql( 'delete from perm' );
16 do_sql( 'delete from temp' );
17 end;
18 /
3,263,576 bytes of redo generated for "insert into perm (x,y,z) "...
58,428 bytes of redo generated for "insert into temp (x,y,z) "...
2,160,600 bytes of redo generated for "update perm set x = 2"...
1,082,848 bytes of redo generated for "update temp set x = 2"...
3,353,108 bytes of redo generated for "delete from perm"...
3,253,220 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

通过如上可以看到，插入非临时表产生大量的重做日志，插入临时表则只有回滚段的“重做”日志产生，所以非常小。

更新非临时表产生大概两倍于更新临时表的“重做”日志。对于临时表，“镜像”前的更新被保存，而“镜像”后的更新无需保存。

删除在临时表和非临时表上产生的重做日志大小基本相同。这是因为，删除操作产生大量的回滚数据，而产生的更改块的重做日志非常小。

对于临时表，总结起来有如下规则：
a. 插入操作产生相当少的回滚/重做活动。
b. 删除操作产生的重做数量基本等于非临时表。
c. 更新操作产生的重做数量是更新非临时表的一半（如果字段的所有的值为空，以2000bytes的值更新这个字段，那么产生的重做类似于插入操作。反之，如果字段几乎所有的值有2000bytes，全部以NULL更新，那么产生的重做类似于DELETE）。

拥有如上知识后，在临时表实际的应用中，尽量只使用INSERT和SELECT操作。避免删除临时表，而是使用TRUNCATE命令或者让临时表在提交之后自行清空表。

分析表

可以通过LogMiner能够将重做日志载入系统的动态表（V$），从而能够方便的查询日志。可以看到执行DML语句之后，列值的变化；可以看到重做和回滚事务的SQL；可以当删除一个表的时候Oracle在OBJ$表上删除操作。这些都有利于DBA进行分析，找到恢复所需要的数据和时间点。
回滚

什么操作产生最少/最多UNDO

INSERT操作产生很少的UNDO，因为ORACLE只需要记录需要删除的ROW ID。UPDATE操作通常列第二位，因为ORACLE需要纪录更改的BYTES，而UPDATE通常只更新整行的部分字段。DELETE操作一般意义来说，产生最多的UNDO。因为需要纪录删除前整行的数据。

SET TRANSACTION

通过这个语句可以指定事务运行所使用的回滚段。一般不用使用这种方式，而是将MAXEXTENTS设置足够大，然ORACLE自动区扩展回滚段。如果需要，设置参数使得回滚段在扩展到一定大小之后，再自动缩减到一定大小。

使用打得回滚段的一个主要问题就是——不能阻止其他事务使用这个回滚段。所以最好的方式是让系统挑选回滚段，并让其能够自动扩展。同时，有些工具并不支持指定回滚段。IMP不支持，SQLLDR不支持，SNAPSHOT的刷新支持。

如果确实存在有一次性的大量的数据需要使用大的回滚段，也可以创建一个，然后进行处理，然后再置为OFFLINE，然后再删除。对于这种情况，将标分为几个部分，然后采用多个任务同时更新，这样各自的任务能够使用各自的回滚段，也许更有效。

ORA-01555：回滚段太旧

如下三个原因会造成这个错误：
a. 回滚段太小
b. 程序跨COMMITS取数
c. 块清除

前面两个都和ORACLE持续读相关。ORACLE使用回滚段来回滚那些查询开始后已经更改的块。通过这种方式，ORACLE提供一个连续的数据库的快照。通过两个Session来验证：

步骤1：
Session 1:创建表，插入数据，更新一条数据不提交
SQL> create table t1 ( a number );
Table created.

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> update t1 set a = 10 where a = 1;
1 row updated.

步骤2：
Session 2:插入数据
SQL> insert into t1 select * from t1 where a = 1;
1 row created.

步骤3：
Session 1:提交更新结果，查询表数据
SQL> commit;

Commit complete.

步骤4：
Session 2: 提交插入，查询数据表
SQL> commit;
Commit complete.

SQL> select * from t1;--由于一致性读，步骤2仍然能够插入数据
A
----------
10
1

处理这种ORA-01555的方法有：
a. 分析表，进行块清除。
b. 分配更大的回滚段。
c. 优化查询SQL。

回滚段太小造成ORA-01555的错误

重现错误:
a. Session A 发起一个查询。
b. Session B 发起一个更新并且提交。相关回滚信息记录在回滚段，同时因为已经提交，回滚段信息在需要的时候可能被覆盖。假定第1000个数据块被修改了。
c. Session A 继续运行，查询到第800个数据块。由于其他的一些操作，占用了大量的回滚段，回滚段的空间不够，特别地，1000个数据块更改的回滚信息被覆盖。
d. Session A 查询到1000个数据块的时候，发现数据块已经被更改，尝试查找回滚信息来保证一致性读，发现回滚信息已经被覆盖，从而引发了了ORA-01555错误。
即使将会滚段的MAXEXTENT设置足够大，但是由于查询语句并不自动扩展（更新语句能够自动扩展），所以通常还是在查询的时候会出现ORA-01555的错误。所以，不要让其自动扩展，而是通过手工调节。

对于9i的数据库，因为是自动管理回滚段，所以需要将其更改为手动管理之后，重新启动之后，才能运行如下的例子。
alter system set undo_management= manual scope=spfile;

步骤1：
Session1:创建很小的回滚段，创建测试表，执行更新语句
SQL> create rollback segment rbs_small--创建一个很小的回滚段
2 storage
3 ( initial 8k next 8k
4 minextents 2 maxextents 3 )
5 --tablespace rbs_test
6 /
Rollback segment created.

SQL> alter rollback segment rbs_small online;--设置为online
Rollback segment altered.

SQL> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.

SQL> create index t_idx on t(object_id)
2 /
Index created.

SQL> begin
2 for x in ( select rowid rid from t )--执行更新语句，更新过程全表扫描
3 loop
4 commit;
5 set transaction use rollback segment rbs_small;--设置事务使用刚才建立回滚段
6 update t
7 set object_name = lower(object_name)
8 where rowid = x.rid;
9 end loop;
10 commit;
11 end;
12 /

步骤2：
Session2：执行查询语句（要确保步骤1的更新还未完成的时候运行）
SQL> select object_name from t where object_id > 0 order by object_id;
…….
ERROR: 出现ORA-01555错误
ORA-01555: snapshot too old: rollback segment number 11 with name "RBS_SMALL"
too small

由于步骤2中的查询是通过索引，所以其访问数据块是顺序是随机的，查询访问那些步骤1中被更新的快的几率会增大，从而可能造成ORA-01555的错误。如果是步骤2中使用全表扫描查询（通过RULE指定），那么由于步骤2的查询比步骤1的更新所花费的时间更少，所以经常是查询完成再去更新，从而产生ORA-01555错误的几率将会非常小。

交叉提交造成ORA-01555的错误

如下的例子中，将会每100行更新之后就提交一次。就有可能，查询会重新访问已经提交更新的块。而由于回滚段的信息已经被覆盖，所以也会造成ORA-01555错误。

SQL> declare
2 l_cnt number default 0;
3 begin
4 for x in ( select rowid rid, t.* from t where object_id > 0 )
5 loop
6 if ( mod(l_cnt,100) = 0 )
7 then
8 commit;
9 set transaction use rollback segment rbs_small;
10 end if;
11 update t
12 set object_name = lower(object_name)
13 where rowid = x.rid;
14 l_cnt := l_cnt + 1;
15 end loop;
16 commit;
17 end;
18 /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 11 with name "RBS_SMALL"
too small
ORA-06512: at line 4

块延迟清除造成ORA-01555的错误

造成错误的原因是非常严格的，所以现实情况中由这种原因而造成的ORA-01555非常少，需要满足如下条件：
a. 一个更新被提交，部分块没有被自动清除事务标示。
b. 在某个SQL访问这些块前，这些块不被其他的SESSION访问。
c. 这个SQL开始运行，其SCN为T1。这个SQL最终会访问到上述没有被清除事务标示的块。
d. 系统中又有许多其它的事务提交，但这些事务并不访问上述的块。
e. 由于其他事务的提交，最初的更改数据事务的事务实体被循环重用。
f. 最终，回滚段中最小的SCN号已经大于T1。这个时候，SQL访问这些块的时候，不能判断COMMIT SCN是大于T1（如果大于T1，回滚）还是小于T1（如果小于T1，SQL将会使用这个块）。

参考：回滚段探究