再上一篇:9.3提交和回滚处理
上一篇:9.4分析 redo
主页
下一篇:9.6小结
再下一篇:10.1表类型
文章列表

9.5分析 undo

Oracle 9i 10g编程艺术:深入数据库体系结构

我们已经讨论了许多有关undo段的主题,介绍了恢复时如何使用undo段,undo段与重做日志如何 交互,以及undo段如何用于数据的一致性、非阻塞读等。在这一节中,我们将分析有关undo段的一些常 被问到的问题。
我们主要讨论讨厌的ORA-01555:anapshot too old错误,因为这个问题所引发的困惑比其他任何数 据库主题带来的困惑都多。不过,在此之前,下一节先分析另一个与undo相关的问题:哪些类型的DML操 作会生成最多和最少的undo(根据前面临时表的有关例子,可能你自己已经能回答这个问题了)。

9.5.1什么操作会生成最多和最少的undo?

这是一个常常问到的问题,不过很容易回答。如果存在索引(或者实际上表就是索引组织表),这将 显著地影响生成的undo量,因为索引是一种复杂的数据结构,可能会生成相当多的undo信息。
也就是说,一般来讲,INSERT生成的undo最少,因为Oracle为此需记录的只是要“删除”的一个 rowid(行ID)。UPDATE一般排名第二(在大多数情况下)。对于UPDATE,只需记录修改的字节。你可能只 更新(UPDATE)了整个数据行中很少的一部分,这种情况最常见。因此,必须在undo中记录行的一小部分 。 前面的许多例子都与这条经验相左,不过这是因为那些列更新的行很大(有固定大小),而且它们更新了整 个行。更常见的是 UPDATE一行,并修改整行中的一小部分。一般来讲,DELETE生成的undo最多。对于DELETE, Oracle必须把整行的前映像记录到undo段中。在redo生成方面,前面的临时表例子展示了这样一个事实 : DELETE生成的redo最多,而且由于临时表的DML操作只会把undo记入日志,这实际上也表明DELETE会 生成最多的undo。INSERT只生成需要建立日志的很少的undo。UPDATE生成的undo量等于所修改数据的前 映像大小,DELETE会生成整个数据集写至undo段。

前面已经提到,必须把索引执行的工作也考虑在内。你会发现,与加索引列的更新相比,对一个未加 索引的列进行更新不仅执行得更快,生成的undo也会好得多。例如,下面创建一个有两列的表,这两列包 含相同的数据,但是其中一列加了索引:
ops$tkyte@ORA10G> create table t
2 as
3 select object_name unindexed,
4 object_name indexed
5 from all_objects
6 /
Table created.
ops$tkyte@ORA10G> create index t_idx on t(indexed);

Index created.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats(user,'T'); PL/SQL procedure successfully completed.
下面更新这个表,首先,更新未加索引的列,然后更新加索引的列。我们需要一个新的V$查询来测
量各种情况下生成的undo量。以下查询可以完成这个工作。它先从V$MYSTAT得到我们的会话ID(SID), 在使用这个会话ID在V$SESSION视图中找到相应的会话记录,并获取事务地址(TADDR)。然后使用 TADDR 拉出(查出)我们的V$TRANSACTION记录(如果有),选择USED_UBLK列,即已用undo块的个数。由于我 们目前不在一个事务中,这个查询现在应该返回0行:
ops$tkyte@ORA10G> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /
no rows selected

然后在每个UPDATE之后再使用这个查询,不过在正文中不再重复这个查询,下面只会显示查询的结
果。

现在我们准备好执行更新,并测试各个更新使用的undo块数:
ops$tkyte@ORA10G> update t set unindexed = lower(unindexed);
48771 rows updated.
ops$tkyte@ORA10G> select used_ublk

...
10 / USED_UBLK
----------
401
ops$tkyte@ORA10G> commit; Commit complete.
这个UPDATE使用了401个块存储其undo。提交会“解放”这些块,或者将其释放,所以如果再次对
V$TRANSACTION运行这个查询,它还会显示no rows selected。更新同样的数据时,不过这一次是加索引 的列,会观察到下面的结果:
ops$tkyte@ORA10G> update t set indexed = lower(indexed);
48771 rows updated.
ops$tkyte@ORA10G> select used_ublk
...
10 / USED_UBLK
----------
1938

可以看到,在这个例子中,更新加索引的列会生成几乎5倍的undo。这是因为索引结构本身所固有
的复杂性,而且我们更新了这个表中的每一行,移动了这个结构中的每一个索引键值。

9.5.2ORA-01555:snapshot too old错误

在上一章中,我们简要分析了ORA-01555错误,并了解了导致这个错的一个原因:提交得太过频繁。 这一节我们将更详细地分析ORA-01555错误的起因和解决方案。ORA-01555是最让人讨厌的错误之一。这 是许多神话、谬误和不当推测的基础。
注意 ORA-01555与数据破坏或数据丢失毫无关系。在这方面,这是一个“安全”的错误;惟一的影
响是:接收到这个错误的查询无法继续处理。
这个错误实际上很直接,其实只有两个原因,但是其中之一有一个特例,而且这种特例情况发生得如 此频繁,所以我要说存在3个原因:
undo段太小,不足以在系统上执行工作。
你的程序跨COMMIT获取(实际上这是前一点的一个变体)。我们在上一章讨论了这种情况。 块清除。
前两点与Oracle的读一致性模型直接相关。从第7章可以了解到,查询的结果是预定的,这说明在 Oracle去获取第一行之前,结果就已经定好了。Oracle使用undo段来回滚自查询开始以来有修改的块, 从而提供数据库的一致时间点“快照”。例如执行以下语句:
update t set x = 5 where x = 2;
insert into t select * from t where x = 2;
delete from t where x = 2;
select * from t where x = 2;

执行每条语句时都会看到T的一个读一致视图以及X=2的行集,而不论数据库中还有哪些并发的活动 。
注意 其他语句也可以看到T的读一致视图,这里所示的4条语句只是这样的一个例子。它们不作为 数据库中单独的事务来运行,因为第一个更新(如果作为单独的事务)可能导致后面3条语句看 不到记录。这几条语句纯粹是为了说明之用,没有实际意义。
所有“读”这个表的语句都利用了这种读一致性。在上面所示的例子中,UPDATE读这个表,找到X=2 的行(然后UPDATE这些行)。INSERT也要读表,找到X=2的行,然后INSERT,等等。由于两个语句都使用 了undo段,都是为了回滚失败的事务并提供读一致性,这就导致了ORA-01555错误。
前面列的第三项也会导致ORA-01555,而且这一点更阴险,因为它可能在只有一个会话的数据库中发 生,而且这个会话并没有修改出现ORA-01555错误时所查询的表!看上去好像不太可能,既然表肯定不会 被修改,为什么还需要这个表的undo数据呢?稍后将会解释。
在充分说明这三种情况之前,我想先与你分享 ORA-01555错误的几种解决方案,一般来说可以采用下 面的方法:
适当地设置参数UNDO_RETENTION(要大于执行运行时间最长的事务所需的时间)。可以用 V$UNDOSTAT来确定长时间运行的查询的持续时间。另外,要确保磁盘上已经预留了足够的空间, 使undo段能根据所请求的UNDO_RETENTION增大。
使用手动undo管理时加大或增加更多的回滚段。这样在长时间运行的查询执行期间,覆盖
undo数据的可能性就能降低。这种方法可以解决上述的所有3个问题。
减少查询的运行时间(调优)。如果可能的话,这绝对是一个好办法,所以应该首先尝试这 种方法。这样就能降低对undo段的需求,不需求太大的undo段。这种方法可以解决上述的所 有3个问题。
收集相关对象的统计信息。这有助于避免前面所列的第三点。由于大批量的 UPDATE 或
INSERT会导致块清除(block cleanout),所以需要在大批量UPDATE或大量加载之后以某种方 式收集统计信息。
我们还会详细讨论这些方案,因为这些都是必须掌握的重要内容。在真正开始介绍这些解决方案之前 , 最好先来看看具体情况是怎样的。

1. undo 段确实太小

一种场景是:你的系统中事务很小。正因如此,只需要分配非常少的undo段空间。假如,假设存在 以下情况:
每个事务平均生成8KB的undo。
平均每秒完成其中5个事务(每秒生成40KB的undo,每分钟生成2,400KB的undo)。 有一个生成1MB undo的事务平均每分钟出现一次。总的说来,每分钟会生成大约3.5MB的
undo。
你为系统配置了15MB的undo。
处理事务时,相对于这个数据库的undo需求,这完全够了。undo段会回绕,平均每3~4分钟左右 会重用一次undo段空间。如果要根据执行修改的事务确定undo段的大小,那你做得没错。
不过,在同样的环境中,可能有一些报告需求。其中一些查询需要运行相当长的时间,可能是 5分钟 。 这就有问题了。如果这些查询需要执行5分钟,而且它们需要查询开始时的一个数据视图,你就极有可能 遭遇ORA-01555错误。由于你的undo段会在这个查询执行期间回绕,要知道查询开始以来生成的一些undo 信息已经没有了,这些信息已经被覆盖。如果你命中了一个块,而这个块几乎在查询开始的同时被修改, 这个块的undo信息就会因为undo段回绕而丢掉,你将收到一个ORA-01555错误。
以下是一个小例子。假设我们有一个表,其中有块1、2、3、…、1,000,000。表9-4显示了可能出 现的事件序列。
表9-4长时间运行的查询时间表 时间(分:秒) 动作

0:00 查询开始

0:01 另一个会话更新(UPDATE)块 1,000,000。将块 1,000,000的 undo信息记录到某个 undo 段

0:01 这个 UPDATE 会话提交(COMMIT)。它生成的 undo 数据还在 undo 段中,但是倘若我们需要空间,选 择

允许覆盖这个信息

1:00 我们的查询还在运行。现在更新到块 200,000

1:01 进行了大量活动。现在已经生成了稍大于 14MB的 undo

3:00 查询还在兢兢业业地工作着。现在处理到块 600,000 左右

4:00 undo段开始回绕,并重用查询开始时(0:00)活动的空间。具体地讲,我们已经重用了原先 0:01 时刻 UPDATE

块 1,000,000时所用的 undo段空间

5:00 查询终于到了块 1,000,000。它发现自查询开始以来这个块已经修改过。它找到 undo段,试图发现 对应这

一块的 undo 来得到一个一致读。此时,它发现所需要的信息已经不存在了。这就产生了 ORA-

01555错误,

查询失败

具体就是这样的。如果如此设置undo段大小,使得很有可能在执行查询期间重用这些undo段,而且 查询要访问被修改的数据,那就也有可能不断地遭遇ORA-01555错误。此时必须把UNDO_RETENTION参数设 置得高一些,让Oracle负责确定要保留多少undo段的大小,让它们更大一些(或者有更多的undo段 )。 你要配置足够的undo,在长时间运行的查询期间应当能够维持。在前面的例子中,只是针对修改数据的事 务来确定系统undo段的大小,而忘记了还有考虑系统的其他组件。
对于Oracle9i和以上版本,管理系统中的undo有两种方法:
自动undo管理(Automatic undo management):采用这种方法,通过UNDO_RETENTION参 数告诉Oracle要把undo保留多长时间。Oracle会根据并发工作负载来确定要创建多少个undo 段,以及每个 undo 段应该多大。数据库甚至在运行时可以在各个undo段之间重新分配区段, 以满足DBA设置的UNDO_RETENTION目标。这是undo管理的推荐方法。
手动undo管理(Manual undo management):采用这种方法的话,要由DBA来完成工作。 DBA要根据估计或观察到的工作负载,确定要手动地创建多少个undo段。DBA根据事务量(生 成多少undo)和长时间运行查询的长度来确定这些undo段应该多大。
在手动undo管理的情况下,DBA要确定有多少个undo段,以及各个undo段有多大,这就产生了一 个容易混淆的问题。有人说:“那好,我们已经配置了XMB的undo,但是它们可以增长。我们把MAXEXTENTS 设置为500,而且每个区段是1MB,所以undo可以相当大。”问题是,倘若手动地管理undo段,undo段从 来不会因为查询而扩大;只有INSERT、UPDATE和DELETE才会让undo段增长。事实上,如果执行一个长时 间运行的查询,Oracle不会因此扩大手动回滚段(即手动管理的回滚段)来保留数据,以备以后可能需要 用到这些数据。只有当执行一个长时间运行的UPDATE事务时才会扩大手动回滚段。在前面的例子中,即使 手动回滚段有增长的潜力,但它们并不会真正增长。对于这样一个系统,你需要有更大的手动回滚段(尽 管它们已经很大了)。你要永久地为回滚段分配空间,而不是只给它们自行增长的机会。
对于这个问题,惟一的解决方案只能是适当地设置手动回滚段的大小,从而每 6~10分钟才回绕,或 者让查询执行时间不能超过2~3分钟。在这种情况下,DBA要让永久分配的undo量再扩大2~3倍。第二 种建议也同样适用(也相当有效)。只要能让查询运行得更快,就应该尽力为之。如果自查询开始以来生成 的undo从未被覆盖,就可以避免ORA-01555。
在自动undo管理的情况下,从ORA-01555角度看,问题则要容易得多。无需自行确定undo空间有多 大并完成预分配,DBA只有告诉数据库运行时间至少在这段时间内保留undo。如果已经分配了足够的空间 可以扩展,Oracle就会扩展undo段,而不是回绕,从而满足UNDO_RETENTION保持时间的要求。这与手动 管理的undo截然相反,手动管理是会回绕,并尽可能块地重用undo空间。这是由于这个原因(即自动undo
管理支持UNDO_RETENTION参数),所以我强烈建议尽可能采用自动undo管理。这个参数可以大大降低遭遇 ORA-01555错误的可能性(只要进行适当地设置!)。
使用手动undo管理时,还要记住重要的一点,遇到ORA-01555错误的可能性是由系统中最小的回滚 段指示的(而非最大的回滚段,也并非平均大小的回滚段)。增加一个“大”回滚段不能解决这个问题。处 理查询时只会让最小的回滚段回绕,这个查询就有可能遇到ORA-01555错误。使用遗留的回滚段时我主张 回滚段大小要相等,以上就是原因所在。如果回滚段的大小都相等,那么每个回滚段即是最小的,也是最 大的。这也是我为什么避免使用“最优大小”回滚段的原因。如果你收缩一个此前被扩大的回滚段,就要 丢掉以后可能还需要的大量undo。倘若这么做,会丢掉最老的回滚数据,从而力图使风险最小,但是风险 还是存在。我喜欢尽可能在非高峰期间手动地收缩回滚段。
在这方面我有些过于深入了,有介入 DBA角色之嫌,所以下面讨论另一个话题。重要的是,你要知道 出现这种情况下的ORA-01555错 误是因为系统没有根据工作负载适当地确定大小。解决方案只有一个,那 就是针对工作负载正确地设置大小。这不是你的过错,但是既然遇到了,那就是你的问题 了。这与查询期 间临时空间耗尽的情况是一样的。对此可以为系统分配足够的临时空间;或者重写查询,使得所用的查询 计划不需要临时空间。
为了演示这种效果,可以建立一个有些人为的小测试。我们将创建一个非常小的undo表空间,并有 一个生成许多小事务的会话,实际上这能确保这个undo 表空间回绕,多次重用所分配的空间,而不论 UNDO_RETENTION设置为多大,因为我们不允许undo表空间增长。使用这个undo段的会话将修改一个表T。 它使用T的一个全表扫描,自顶向下地读表。在另一个会话中,我们将执行一个查询,它通过一个索引读 表T。采用这种方式,这个查询会稍微有些随机地读表:先读第1行,然后是第1,000行,接下来是第500 行,再后面是第20,001行,如此等等。这样一来,我们可能会非常随机地访问块,并在查询的处理期间多 次访问块。这种情况下得到ORA-01555错误的机率几乎是100%。所以,在一个会话中首先执行以下命令:
ops$tkyte@ORA10G> create undo tablespace undo_small
2 datafile size 2m
3 autoextend off
4 /
Tablespace created.
ops$tkyte@ORA10G> alter system set undo_tablespace = undo_small; System altered.

现在,我们将建立表 T 来查询和修改。注意我们在这个表中随机地对数据排序。CREATE TABLE AS

SELECT力图按查询获取的顺序将行放在块中。我们的目的只是把行弄乱,使它们不至于认为地有某种顺序 , 从而得到随机的分布:
ops$tkyte@ORA10G> create table t
2 as

3 select *
4 from all_objects
5 order by dbms_random.random; Table created.
ops$tkyte@ORA10G> alter table t add constraint t_pk primary key(object_id)
2 /
Table altered.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true ); PL/SQL procedure successfully completed.
现在可以执行修改了:
ops$tkyte@ORA10G> begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid =
x.rid;
5 commit;
6 end loop;
7 end;
8 /

在运行这个修改的同时,我们在另一个会话中运行一个查询。这个查询要读表T,并处理每个记录。

获取下一个记录之前处理每个记录所花的时间大约为1/100秒(使用DBMS_LOCK.SLEEP(0.01)来模拟)。在 查询中使用了FIRST_ROWS提示,使之使用前面创建的索引,从而通过索引(按OBJECT_ID排序)来读出表 中的行。由于数据是随机地插入到表中的,我们可能会相当随机地查询表中的块。这个查询只运行几秒就 会失败:
ops$tkyte@ORA10G> declare

2 cursor c is
3 select /*+ first_rows */ object_name
4 from t
5 order by object_id;
6
7 l_object_name t.object_name%type;
8 l_rowcnt number := 0;
9 begin
10 open c;
11 loop
12 fetch c into l_object_name;
13 exit when c%notfound;
14 dbms_lock.sleep( 0.01 );
15 l_rowcnt := l_rowcnt+1;
16 end loop;
17 close c;
18 exception
19 when others then
20 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
21 raise;
22 end;
23 /
rows fetched = 253 declare

*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 23 with name "_SYSSMU23$" too small
ORA-06512: at line 21
可以看到,在遭遇ORA-01555:snapshot too old错误而失败之前,它只处理了253个记录。要修正
这个错误,我们要保证做到两点:
数据库中UNDO_RETENTION要设置得足够长,以保证这个读进程完成。这样数据库就能扩大
undo表空间来保留足够的undo,使我们能够完成工作。
undo表空间可以增长,或者为之手动分配更多的磁盘空间。 对于这个例子,我认为这个长时间运行的进程需要大约600秒才能完成。我的UNDO_RETENTION设置

为900(单位是秒,所以undo保持大约15分钟)。我修改了undo表空间的数据文件,使之一次扩大1MB, 直到最大达到2GB:
ops$tkyte@ORA10G> column file_name new_val F
ops$tkyte@ORA10G> select file_name
2 from dba_data_files
3 where tablespace_name = 'UNDO_SMALL';
FILE_NAME
------------------------------
/home/ora10g/oradata/ora10g/OR A10G/datafile/o1_mf_undo_sma_1
729wn1h_.dbf
ops$tkyte@ORA10G> alter database
2 datafile '&F'
3 autoextend on

4 next 1m
5 maxsize 2048m;
old 2: datafile '&F'
new 2: datafile '/home/ora10g/.../o1_mf_undo_sma_1729wn1h_.dbf' Database altered.
再次并发地运行这些进程时,两个进程都能顺利完成。这一次undo表空间的数据文件扩大了,因为
在此允许undo表空间扩大,而且根据我设置的undo保持时间可知:
ops$tkyte@ORA10G> select bytes/1024/1024
2 from dba_data_files
3 where tablespace_name = 'UNDO_SMALL';
BYTES/1024/1024
---------------
11

因此,这里没有收到错误,我们成功地完成了工作,而且undo扩大得足够大,可以满足我们的需要。
在这个例子中,之所以会得到错误只是因为我们通过索引来读表T,而且在全表上执行随机读。如果不是 这样,而是执行全表扫描,在这个特例中很可能不会遇到ORA-01555错误。原因是SELECT和UPDATE都要 对T 执行全表扫描,而SELECT扫描很可能在UPDATE之前进行(SELECT只需要读,而UPDATE不仅要读还 有更新,因此可能更慢一些)。如果执行随机读,SELECT就更有可能要读已修改的块(即块中的多行已经 被UPDATE修改而且已经提交)。这就展示了ORA-01555的“阴险”,这个错误的出现取决于并发会话如何访 问和管理底层表。

2. 延迟的块清除

块清除是导致ORA-01555错误错误的原因,尽管很难完全杜绝,不过好在毕竟并不多见,因为可能出 现块清除的情况不常发生(至少在Oracle8i及 以上版本中是这样)。我们已经讨论过块清除机制,不过这 里可以做一个总结:在块清除过程中,如果一个块已被修改,下一个会话访问这个块时,可能必须查看最 后 一个修改这个块的事务是否还是活动的。一旦确定该事务不再活动,就会完成块清除,这样另一个会话访 问这个块时就不必再历经同样的过程。要完成块清除,Oracle会从块首部确定前一个事务所用的undo段, 然后确定从undo首部能不能看出这个块是否已经提交。可以用以下两种方式完成这种确认。一种方式是 Oracle可以确定这个事务很久以前就已经提交,它在undo段事务表中的事务槽已经被覆盖。另一种情况 是COMMIT SCN还在undo段的事务表中,这说明事务只是稍早前刚提交,其事务槽尚未被覆盖。
要从一个延迟的块清除收到ORA-01555错误,以下条件都必须满足:
首先做了一个修改并COMMIT,块没有自动清理(即没有自动完成“提交清除”,例如修改了
太多的块,在SGA块缓冲区缓存的10%中放不下)。
其他会话没有接触这些块,而且在我们这个“倒霉”的查询(稍后显示)命中这些块之前, 任何会话都不会接触它们。
开始一个长时间运行的查询。这个查询最后会读其中的一些块。这个查询从SCN t1 开始, 这就是读一致SCN,必须将数据回滚到这一点来得到读一致性。开始查询时,上述修改事务的事 务条目还在undo段的事务表中。
查询期间,系统中执行了多个提交。执行事务没有接触执行已修改的块(如果确实接触到, 也就不存在问题了)。
由于出现了大量的COMMIT,undo段中的事务表要回绕并重用事务槽。最重要的是,将循环 地重用原来修改事务的事务条目。另外,系统重用了undo段的区段,以避免对undo段首部块 本身的一致读。
此外,由于提交太多,undo段中记录的最低SCN现在超过了t1(高于查询的读一致SCN)。 如果查询到达某个块,而这个块在查询开始之前已经修改并提交,就会遇到麻烦。正常情况下,会回
到块所指的undo段,找到修改了这个块的事务的状态(换句话说,它会找到事务的 COMMIT SCN)。如果这
个COMMIT SCN小于t1,查询就可以使用这个块。如果该事务的COMMIT SCN大于t1,查询就必须回滚这个 块。不过,问题是,在这种特殊的情况下,查询无法确定块的COMMIT SCN是大于还是小于 t1。相应地, 不清楚查询能否使用这个块映像。这就导致了ORA-01555错误。
为 了真正看到这种情况,我们将在一个表中创建多个需要清理的块。然后在这个表上打开一个游标, 并允许对另外某个表完成许多小事务(不是那个刚更新并打开了游 标的表)。最后尝试为该游标获取数据。 现在,我们认为游标需要的数据每问题,应该能看到所有数据,因为我们是在打开游标之前完成并提交了 表修改。倘若此时 得到ORA-01555错误,就说明存在前面所述的问题。要建立这个例子,我们将使用:
2MB UNDO_SMALL undo 表空间(还是这个undo表空间)。
4MB的缓冲区缓存,足以放下大约500个块。这样我们就可以将一些脏块刷新输出到磁盘来 观察这种现象。

首先创建要查询的大表:
ops$tkyte@ORA10G> create table big
2 as
3 select a.*, rpad('*',1000,'*') data
4 from all_objects a; Table created.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'BIG' );

PL/SQL procedure successfully completed.
由于使用了这么大的数据字段,每个块中大约有6~7行,所以这个表中有大量的块。接下来,我们
创建将由多个小事务修改的小表:
ops$tkyte@ORA10G> create table small ( x int, y char(500) );
Table created.
ops$tkyte@ORA10G> insert into small select rownum, 'x' from all_users;
38 rows created. ops$tkyte@ORA10G> commit; Commit complete.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'SMALL' ); PL/SQL procedure successfully completed.

下面把那个大表“弄脏”。由于undo表空间非常小,所以希望尽可能多地更新这个大表的块,同时生

成尽可能少的undo。为此,将使用一个有意思的UPDATE 语句来执行该任务。实质上讲,下面的子查询要 找出每个块上的“第一个”行rowid。这个子查询会返回每一个数据库块的一个rowid(标识了这个块是的 一行)。我们将更新这一行,设置一个VARCHAR2(1)字段。这样我们就能更新表中的所有块(在这个例子中 , 块数大约比8,000稍多一点),缓冲区缓存中将会充斥着必须写出的脏块(现在只有500个块的空间)。仍 然必须保证只能使用那个小undo表空间。为做到这一点,而且不超过undo表空间的容量,下面构造一个 UPDATE语句,它只更新每个块上的“第一行”。ROW_NUMBER()内置分析函数是这个操作中使用的一个工具; 它把数字1 指派给表中的数据库块的“第1 行”,在这个块上只会更新这一行:
ops$tkyte@ORA10G> alter system set undo_tablespace = undo_small;
System altered.
ops$tkyte@ORA10G> update big
2 set temporary = temporary
3 where rowid in
4 (

5 select r
6 from (
7 select rowid r, row_number() over
dbms_rowid.rowid_block_number(rowid) order by rowid) rn
8 from big
9 )
10 where rn = 1
11 )
12 /
8045 rows updated.
ops$tkyte@ORA10G> commit; Commit complete.
(partition by
现在我们知道磁盘上有大量脏块。我们已经写出了一些,但是没有足够的空间把它们都放下。接下来
打开一个游标,但是尚未获取任何数据行。要记住,打开游标时,结果集是预定的,所以即使 Oracle并没 有具体处理一行数据,打开结果集这个动作本身就确定了“一致时间点”,即结果集必须相对于那个时间点 一致。现在要获取刚刚更新并提交的数据,而且我们知道没有别人修改这个数据,现在应该能获取这些数 据行而不需要如何undo。但是此时就会“冒出”延迟块清除。修改这些块的事务太新了,所以Oracle必 须验证在我们开始之前这个事务是否已经提交,如果这个信息(也存储在undo表空间中)被覆盖,查询就 会失败。以下打开了游标:
ops$tkyte@ORA10G> variable x refcursor
ops$tkyte@ORA10G> exec open :x for select * from big; PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> !./run.sh


run.sh是一个shell脚本。其中使用一个命令启动9个SQL*Plus会话:
$ORACLE_HOME/bin/sqlplus / @test2 1 &

这里为每个SQL*Plus会话传递一个不同的数字(这里是数字1,还有2、3等)。每个会话运行的脚 本test2.sql如下:
begin
for i in 1 .. 1000
loop
update small set y = i where x= &1;
commit;
end loop;
end;
/
exit
这样一来,就有了 9个会话分别在一个循环中启动多个事务。run.sh脚本等待这9个SQL*Plus会话
完成其工作,然后返回我们的会话,也就是打开了游标的会话。在视图大约时,我们观察到下面的结果:
ops$tkyte@ORA10G> print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 23 with name "_SYSSMU23$" too small
no rows selected

前面已经说过,以上是一种很少见的情况。它需要许多条件,所有这些条件必须同时存在才会出现这
种情况。首先要有需要清理的块,而这种块在 Oracle8i及以前的版本中很少见。收集统计信息的DBMS_STATS 调用就能消除这种块。尽管大批量的更新和大量加载是造成块清除最常见的理由,但是利用DBMS_STATS调 用的话,这些操作就不再成为问题,因为在这种操作之后总要对表执行分析。大多数事务只会接触很少的 块,而不到块缓冲区缓存的10%;因此,它们不会生成需要清理的块。万一你发现遭遇了这个问题,即选 择(SELECT)一个表时(没有应用其他DML操作)出现了ORA-01555错误,能你可以试试以下解决方案:
首先,保证使用的事务“大小适当”。确保没有不必要地过于频繁地提交。
使用DBMS_STATS扫描相关的对象,加载之后完成这些对象的清理。由于块清除是极大量的 UPDATE或INSERT造成的,所以很有必要这样做。
允许undo表空间扩大,为之留出扩展的空间,并增加undo保持时间。这样在长时间运行 查询期间,undo段事务表中的事务槽被覆盖的可能性就会降低。针对导致ORA-01555错误的另 一个原因(undo段太小),也同样可以采用这个解决方案(这两个原因有紧密的关系;块清除问
题就是因为处理查询期间遇到了undo段重用,而undo段大小正是重用undo段的一个根本原因 )。 实际上,如果把undo表空间设置为一次自动扩展1MB,而且undo保持时间为900秒,再运行前 面的例子,对表BIG的查询就能成功地完成了。
减少查询的运行时间(调优)。如果可能的话,这总是件好事,所以应该首先尝试这样做。