Oracle 9i & 10g编程艺术-深入数据库体系结构——第9章：redo与undo

第9章                      redo与undo

这一章将介绍Oracle数据库中最重要的两部分数据：redo与undo。redo（重做信息）是Oracle在在线（或归档）重做日志文件中记录的信息，万一出现失败时可以利用这些数据来“重放”（或重做）事务。undo（撤销信息）是Oracle在undo段中记录的信息，用于取消或回滚事务。

在这一章中，我们讨论的内容很多，包括redo和undo（回滚信息）如何生成，以及事务、恢复等方面如何应用redo和undo。首先我们给出一个高层概述，说明undo和redo分别是什么，它们如何协作。然后向下细化，更深入地介绍各个主题，并讨论作为开发人员需要了解哪些内容。

这一章主要面向开发人员，在此没有涵盖应由DBA完全负责确定和调整的问题。例如，如何找到RECOVERY_PARALLELISM或FAST_START_MTTR_TARGET参数的最优设置？这个问题要由DBA确定，本章就没有涉及。但是，redo和undo则是DBA和开发人员都关心的主题，它们是DBA和开发人员之间的桥梁。不论是DBA还是开发人员，都需要对redo和undo的作用有很好的基本了解，知道它们如何工作，并且知道如何避免与redo和undo的使用有关的潜在问题。如果掌握了redo和undo的相关知识，这还有助于DBA和开发人员更好地理解数据库一般如何操作。

在这一章中，我将针对Oracle的这些机制提供伪代码，并从概念上解释到底会发生什么。这里不会详尽地介绍所有内部细节，如会用哪些数据字节更新哪些文件等详细内容并不会谈到。具体发生的情况可能比我们介绍的更复杂，但是不管怎样，如果能很好地理解这些机制的工作流程，将很有意义，这有助于理解你的动作会带来怎样的影响。

9.1   什么是redo？

重做日志文件（redo log file）对Oracle数据库来说至关重要。它们是数据库的事务日志。Oracle维护着两类重做日志文件：在线（online）重做日志文件和归档（archived）重做日志文件。这两类重做日志文件都用于恢复；其主要目的是，万一实例失败或介质失败，它们就能派上用场。

如果数据库所在主机掉电，导致实例失败，Oracle会使用在线重做日志将系统恰好恢复到掉电之前的那个时间点。如果磁盘驱动器出现故障（这是一个介质失败），Oracle会使用归档重做日志以及在线重做日志将该驱动器上的数据备份恢复到适当的时间点。另外，如果你“无意地”截除了一个表，或者删除了某些重要的信息，然后提交了这个操作，那么可以恢复受影响数据的一个备份，并使用在线和归档重做日志文件把它恢复到这个“意外”发生前的时间点。

归档重做日志文件实际上就是已填满的“旧”在线重做日志文件的副本。系统将日志文件填满时，ARCH进程会在另一个位置建立在线重做日志文件的一个副本，也可以在本地和远程位置上建立多个另外的副本。如果由于磁盘驱动器损坏或者其他物理故障而导致失败，就会用这些归档重做日志文件来执行介质恢复。Oracle拿到这些归档重做日志文件，并把它们应用于数据文件的备份，使这些数据文件能“赶上”数据库的其余部分。归档重做日志文件是数据库的事务历史。

注意      随着Oracle 10g 的到来，我们现在还有了一种闪回技术（flashback）。利用闪回技术，可以执行闪回查询（也就是说，查询过去某个时间点的数据），取消数据库表的删除，将表置回到以前某个时间的状态，等等。因此，现在使用备份和归档重做日志文件来完成传统恢复的情况越来越少。不过，执行恢复是DBA最重要的任务，而且DBA在数据库恢复方面绝对不能犯错误。

每个Oracle数据库都至少有两个在线重做日志组，每个组中至少有一个成员（重做日志文件）。这些在线重做日志组以循环方式使用。Oracle会写组1中的日志文件，等写到组1中文件的最后时，将切换到日志文件组2，开始写这个组中的文件。等到把日志文件组2写满时，会再次切换回日志文件组1（假设只有两个重做日志文件组；如果有3个重做日志文件组，Oracle当然会继续写第3个组）。

数据库之所以成为数据库（而不是文件系统等其他事物），是因为它有自己独有的一些特征，重做日志或事务日志就是其中重要的特性之一。重做日志可能是数据库中最重要的恢复结构，不过，如果没有其他部分（如undo段、分布式事务恢复等），但靠重做日志什么也做不了。重做日志是数据库区别于传统文件系统的一个主要因素。Oracle正写到一半的时候有可能发生掉电，利用在线重做日志，我们就能有效地从这个掉电失败中恢复。归档重做日志则允许我们从介质失败中恢复，如硬盘损坏，或者由于人为错误而导致数据丢失。如果没有重做日志，数据库提供id保护就比文件系统多不了多少。

9.2   什么是undo？

从概念上讲，undo正好与redo相对。你对数据执行修改时，数据库会生成undo信息，这样万一你执行的事务或语句由于某种原因失败了，或者如果你用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据放回到修改前的样子。redo用于在失败时重放事务（即恢复事务），undo则用于取消一条语句或一组语句的作用。与redo不同，undo在数据库内部存储在一组特殊的段中，这称为undo段（undo segment）。

注意      “回滚段”（rollback segment）和“undo段“（undo segment）一般认为是同义词。使用手动undo管理时，DBA会创建”回滚段“。使用自动undo管理时，系统将根据需要自动地创建和销毁”undo段“。对于这里的讨论来说，这些词的意图和作用都一样。

通常对undo有一个误解，认为undo用于数据库物理地恢复到执行语句或事务之前的样子，但实际上并非如此。数据库只是逻辑地恢复到原来的样子，所有修改都被逻辑地取消，但是数据结构以及数据库块本身在回滚后可能大不相同。原因在于：在所有多用户系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要功能之一就是协调对数据的并发访问。也许我们的事务在修改一些块，而一般来讲往往会有许多其他的事务也在修改这些块。因此，不能简单地将一个块放回到我们的事务开始前的样子，这样会撤销其他人（其他事务）的工作！

例如，假设我们的事务执行了一个INSERT语句，这条语句导致分配一个新区段（也就是说，导致表的空间增大）。通过执行这个INSET，我们将得到一个新的块，格式化这个块以便使用，并在其中放上一些数据。此时，可能出现另外某个事务，它也向这个块中插入数据。如果要回滚我们的事务，显然不能取消对这个块的格式化和空间分配。因此，Oracle回滚时，它实际上会做与先前逻辑上相反的工作。对于每个INSERT，Oracle会完成一个DELETE。对于每个DELETE，Oracle会执行一个INSERT。对于每个UPDATE，Oracle则会执行一个“反UPDATE“，或者执行另一个UPDATE将修改前的行放回去。

注意      这种undo生成对于直接路径操作（direct path operation）不适用，直接路径操作能够绕过表上的undo生成。稍后将更详细地讨论这些问题。

怎么才能看到undo生成（undo generation）具体是怎样的呢？也许最容易的方法就是遵循以下步骤：

（1）         创建一个空表。

（2）         对它做一个全部扫描，观察读表所执行的I/O数量。

（3）         在表中填入许多行（但没有提交）。

（4）         回滚这个工作，并撤销。

（5）         再次进行全表扫描，观察所执行的I/O数量。

首先，我们创建一个空表：

ops$tkyte@ORA 10G > create table t 2 as 3 select * 4 from all_objects 5 where 1=0; Table created.

然后查询这个表，这里在SQL*Plus中启用了AUTOTRACE，以便能测试I/O。

­­注意      在这个例子中，每次（即每个用例）都会做两次全表扫描。我们的目标只是测试每个用例中第二次完成的I/O。这样可以避免统计在解析和优化期间优化器可能完成的额外I/O。

最初，这个查询需要3个I/O来完成这个表的全表扫描：

ops$tkyte@ORA 10G > select * from t; no rows selected   ops$tkyte@ORA 10G > set autotrace traceonly statistics ops$tkyte@ORA 10G > select * from t; no rows selected   Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets ... ops$tkyte@ORA 10G > set autotrace off

接下来，向表中增加大量数据。这会使它“扩大“，不过随后再将其回滚：

ops$tkyte@ORA 10G > insert into t select * from all_objects; 48350 rows created.   ops$tkyte@ORA 10G > rollback; Rollback complete.

现在，如果再次查询这个表，会发现这一次读表所需的I/O比先前多得多：

ops$tkyte@ORA 10G > select * from t; no rows selected   ops$tkyte@ORA 10G > set autotrace traceonly statistics ops$tkyte@ORA 10G > select * from t; no rows selected   Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 689 consistent gets ... ops$tkyte@ORA 10G > set autotrace off

前面的INSERT导致将一些块增加到表的高水位线（high-water mark，HWM）之下，这些块没有因为回滚而消失，它们还在那里，而且已经格式化，只不过现在为空。全表扫描必须读取这些块，看看其中是否包含行。这说明，回滚只是一个“将数据库还原“的逻辑操作。数据库并非真的还原成原来的样子，只是逻辑上相同而已。

9.2.1           redo和undo如何协作？

在这一节中，我们来看看在不同场景中redo和undo如何协作。例如，我们会讨论处理INSERT时关于redo和undo生成会发生什么情况，另外如果在不同时间点出现失败，Oracle将如何使用执行信息。

有意思的是，尽管undo信息存储在undo表空间或undo段中，但也会受到redo的保护。换句话说，会把undo数据当成是表数据或索引数据一样，对undo的修改会生成一些redo，这些redo将计入日志。为什么会这样呢？稍后在讨论系统崩溃时发生的情况时将会解释它，到时你会明白了。将undo数据增加到undo段中，并像其他部分的数据一样在缓冲区缓存中得到缓存。

INSERT-UPDATE-DELETE示例场景

作为一个例子，我们将分析对于下面这组语句可能发生什么情况：

insert into t (x,y) values (1,1); update t set x = x+1 where x = 1; delete from t where x = 2;

我们会沿着不同的路径完成这个事务，从而得到以下问题的答案：

q         如果系统在处理这些语句的不同时间点上失败，会发生什么情况？

q         如果在某个时间点上ROLLBACK，会发生什么情况？

q         如果成功并COMMIT，会发生什么情况？

1.      INSERT

对于第一条INSERT INTO T语句，redo和undo都会生成。所生成的undo信息足以使INSERT“消失“。INSERT INTO T生成的redo信息则足以让这个插入”再次发生“。

插入发生后，系统状态如图9-1所示。

图9-1     INSERT之后的系统状态

这里缓存了一些已修改的undo块、索引块和表数据块。这些块得到重做日志缓冲区中相应条目的“保护“。

l          假想场景：系统现在崩溃

即使系统现在崩溃也没有关系。SGA会被清空，但是我们并不需要SGA里的任何内容。重启动时就好像这个事务从来没有发生过一样。没有将任何已修改的块刷新输出到磁盘，也没有任何redo刷新输出到磁盘。我们不需要这些undo或redo信息来实现实例失败恢复。

l          假想场景：缓冲区缓存现在已满

在这种情况下，DBWR必须留出空间，要把已修改的块从缓存刷新输出。如果是这样，DBWR首先要求LGWR将保护这些数据库块的redo条目刷新输出。DBWR将任何有修改的块写至磁盘之前，LGWR必须先刷新输出与这些块相关的redo信息。这是有道理的——如果我们要刷新输出表T中已修改的块，但没有刷新输出与undo块关联的redo条目，倘若系统失败了，此时就会有一个已修改的表T块，而没有与之相关的redo信息。在写出这些块之前需要先刷新输出重做日志缓存区，这样就能重做（重做）所有必要的修改，将SGA放回到现在的状态，从而能发生回滚。

从第二个场景还可以预见到一些情况。这里描述的条件是“如果刷新输出了表T的块，但没有刷新输出undo块的相应redo，而且此时系统失败了“，这个条件开始变得有些复杂。随着增加更多用户、更多的对象，再加上并发处理等因素，条件还会更复杂。

此时的情况如图9-1所示。我们生成了一些已修改的表和索引块。这些块有一些与之关联的undo段块，这3类块都会生成redo来保护自己。如果还记得第4章中对重做日志缓冲区的讨论，应该知道，它会在以下情况刷新输出：每3秒一次；缓冲区1/3满时或者包含了1MB的缓冲数据；或者是只要发生提交就会刷新输出。重做日志缓冲区还有可能会在处理期间的某一点上刷新输出。在这种情况下，其状态如图9-2所示。

图9-2     重做日志缓冲区刷新输出后的系统状态

2.      UPDATE

UPDATE所带来的工作与INSERT大体一样。不过UPDATE生成的undo量更大；由于存在更新，所以需要保存一些“前“映像。系统状态如图9-3所示。

图9-3     UPDATE后的系统状态

块缓冲区缓存中会有更多新的undo段块。为了撤销这个更新，如果必要，已修改的数据库表和索引块也会放在缓存中。我们还生成了更多的重做日志缓存区条目。下面假设前面的插入语句生成了一些重做日志，其中有些重做日志已经刷新输出到磁盘上，有些还放在缓存中。

l          假想场景：系统现在崩溃

启动时，Oracle会读取重做日志，发现针对这个事务的一些重做日志条目。给定系统的当前状态，利用重做日志文件中对应插入的redo条目，并利用仍在缓冲区中对应插入的redo信息，Oracle会“前滚”插入。最后到与图9-1类似的状态。现在有一些undo块（用以撤销插入）、已修改的表块（刚插入后的状态），以及已修改的索引块（刚插入后的状态）。由于系统正在进行崩溃恢复，而且我们的会话还不再连接（这是当然），Oracle发现这个事务从未提交，因此会将其回滚。它取刚刚在缓冲区缓存中前滚得到的undo，并将这些undo应用到数据和索引块，使数据和索引块“恢复”为插入发生前的样子。现在一切都回到从前。磁盘上的块可能会反映前面的INSERT，也可能不反映（这取决于在崩溃前是否已经将块刷新输出）。如果磁盘上的块确实反映了插入，而实际上现在插入已经被撤销，当从缓冲区缓存刷新输出块时，数据文件就会反映出插入已撤销。如果磁盘上的块本来就没有反映前面的插入，就不用去管它——这些块以后肯定会被覆盖。

这个场景涵盖了崩溃恢复的基本细节。系统将其作为一个两步的过程来完成。首先前滚，把系统放到失败点上，然后回滚尚未提交的所有工作。这个动作会再次同步数据文件。它会重放已经进行的工作，并撤销尚未完成的所有工作。

l          假想场景：应用回滚事务

此时，Oracle会发现这个事务的undo信息可能在缓存的undo段块中（基本上是这样），也可能已经刷新输出到磁盘上（对于非常大的事务，就往往是这种情况）。它会把undo信息应用到缓冲区缓存中的数据和索引块上，或者倘若数据和索引块已经不在缓存中，则要从磁盘将数据和索引块读入缓存，再对其应用undo。这些块会恢复为其原来的行值，并刷新输出到数据文件。

这个场景比系统崩溃更常见。需要指出，有一点很有用：回滚过程中从不涉及重做日志。只有恢复和归档时会当前重做日志。这对于调优是一个很重要的概念：重做日志是用来写的（而不是用于读）。Oracle不会在正常的处理中读取重做日志。只要你有足够的设备，使得ARCH读文件时，LGWR能写到另一个不同的设备，那么就不存在重做日志竞争。许多其他的数据库（非Oracle）都把日志文件处理为“事务日志”。这些数据库没有把redo和undo分开。对于这些系统，回滚可能是灾难性的，回滚进程必须读取日志，而日志写入器正在试图写这个日志。这就向系统中最薄弱的环节引入了竞争。Oracle的目标是：可以顺序地写日志，而且在写日志时别人不会读日志。

3.      DELETE

同样，DELETE会生成undo，块将被修改，并把redo发送到重做日志缓冲区。这与前面没有太大的不同。实际上，它与UPDATE如此类似，所以我们不再啰嗦，直接来介绍COMMIT。

4.      COMMIT

我们已经看到了多种失败场景和不同的路径，现在终于到COMMIT了。在此，Oracle会把重做日志缓冲区刷新输出到磁盘，系统状态如图9-4所示。

图9-4     COMMIT后的系统状态

已修改的块放在缓冲区缓存中；可能有一些块已经刷新输出到磁盘上。重做这个事务所需的全部redo都安全地存放在磁盘上，现在修改已经是永久的了。如果从数据文件直接读取数据，可能会看到块还是事务发生前的样子，因为很有可能DBWR还没有（从缓冲区缓存）写出这些块。这没有关系，如果出现失败，可以利用重做日志文件来得到最新的块。undo信息会一直存在，除非undo段回绕重用这些undo块。如果某些对象受到影响，Oracle会使用这个undo信息为需要这些对象的会话提供对象的一致读。

9.3   提交和回滚处理

有一点很重要，我们要知道重做日志文件对开发人员有什么影响。下面介绍编写代码的不同方法会对重做日志的利用有怎样的影响。在本章前面已经了解了redo的原理，接下来介绍一些更特定的问题。作为开发人员，你能检测到许多这样的场景，但是它们要由DBA来修正，因为这些场景会影响整个数据库实例。我们先来介绍COMMIT期间会发生什么，然后讨论有关在线重做日志的一些经常被问到的问题。

9.3.1             COMMIT做什么？

作为一名开发人员，你应该深入了解COMMIT期间会做些什么。在这一节中，我们将分析Oracle中处理COMMIT语句期间发生的情况。COMMIT通常是一个非常快的操作，而不论事务大小如何。

你可能认为，一个事务越大（换句话说，它影响的数据越多），COMMIT需要的时间就越长。不是这样的。不论事务有多大，COMMIT的响应时间一般都很“平”（flat，可以理解为无高低变化）。这是因为COMMIT并没有太多的工作去做，不过它所做的确实至关重要。

这一点很重要，之所以要了解并掌握这个事实，原因之一是：这样你就能心无芥蒂地让事务有足够的大小。在上一章曾经讨论过，许多开发人员会人为地限制事务的大小，分别提交太多的行，而不是一个逻辑工作单元完成后才提交。这样做主要是出于一种错误的信念，即认为可以节省稀有的系统资源，而实际上这只是增加了资源的使用。如果一行的COMMIT需要X个时间单位，1,000次COMMIT也同样需要X个时间单位，倘若采用以下方式执行工作，即每行提交一次共执行1,000次COMMIT，就会需要1000*X各时间单位才能完成。如果只在必要时才提交（即逻辑工作单元结束时），不仅能提高性能，还能减少对共享资源的竞争（日志文件、各种内部闩等）。通过一个简单的例子就能展示出过多的提交要花费更长的时间。这里将使用一个Java应用，不过对于大多数其他客户程序来说，结果可能都是类似的，只有PL/SQL除外（在这个例子后面，我们将讨论为什么会这样）。首先，下面是我们要插入的示例表：

scott@ORA 10G > desc test Name                          Null?                Type -----------------               --------               ------------ ID                                                            NUMBER CODE                                                     VARCHAR2(20) DESCR                                                  VARCHAR2(20) INSERT_USER                                    VARCHAR2(30) INSERT_DATE                                     DATE

Java程序要接受两个输入：要插入（INSERT）的行数（iters），以及两次提交之间插入的行数（commitCnt）。它先连接到数据库，将autocommit（自动提交）设置为off（所有Java代码都应该这么做），然后将doInserts()方法共调用3次：

q         第一次调用只是“热身”（确保所有类都已经加载）。

q         第二次调用指定了要插入（INSERT）的行数，并指定一次提交多少行（即每N行提交一次）。

q         最后一次调用将要插入的行数和一次提交的行数设置为相同的值（也就是说，所有行都插入之后才提交）。

然后关闭连接，并退出。其main方法如下：

import java.sql.*; import oracle.jdbc.OracleDriver; import java.util.Date; public class perftest {          public static void main (String arr[]) throws Exception          {                   DriverManager.registerDriver(new oracle.jdbc.OracleDriver());                   Connection con = DriverManager.getConnection                            ("jdbc:oracle:thin:@localhost.localdomain:1521:ora 10g ",                            "scott", "tiger");                   Integer iters = new Integer(arr[0]);                   Integer commitCnt = new Integer(arr[1]);                   on.setAutoCommit(false);                   doInserts( con, 1, 1 );                   doInserts( con, iters.intValue(), commitCnt.intValue() );                   doInserts( con, iters.intValue(), iters.intValue() );                   con.commit();                   con.close(); }

现在，doInserts()方法相当简单。首先准备（解析）一条INSERT语句，以便多次反复绑定/执行这个INSERT：

static void doInserts(Connection con, int count, int commitCount )          throws Exception          {                   PreparedStatement ps =                            con.prepareStatement                            ("insert into test " +                            "(id, code, descr, insert_user, insert_date)"                            + " values (?,?,?, user, sysdate)");

然后根据要插入的行数循环，反复绑定和执行这个INSERT。另外，它会检查一个行计数器，查看是否需要COMMIT，或者是否已经不在循环范围内。还要注意，我们分别在循环之前和循环之后获取了当前时间，从而监视并报告耗用的时间：

int rowcnt = 0;                   int committed = 0;                   long start = new Date().getTime();                   for (int i = 0; i < count; i++ )                   {                            ps.setInt(1,i);                            ps.setString(2,"PS - code" + i);                            ps.setString(3,"PS - desc" + i);                            ps.executeUpdate();                            rowcnt++;                            if ( rowcnt == commitCount )                            {                                     con.commit();                                     rowcnt = 0;                                     committed++;                            }                   }                   con.commit();                   long end = new Date().getTime();                   System.out.println                            ("pstatement " + count + " times in " +                            (end - start) + " milli seconds committed = "+committed);          } }

下面根据不同的输入发放运行这个代码：

$ java perftest 10000 1 pstatement 1 times in 4 milli seconds committed = 1 pstatement 10000 times in 11510 milli seconds committed = 10000 pstatement 10000 times in 2708 milli seconds committed = 1   $ java perftest 10000 10 pstatement 1 times in 4 milli seconds committed = 1 pstatement 10000 times in 3876 milli seconds committed = 1000 pstatement 10000 times in 2703 milli seconds committed = 1   $ java perftest 10000 100 pstatement 1 times in 4 milli seconds committed = 1 pstatement 10000 times in 3105 milli seconds committed = 100 pstatement 10000 times in 2694 milli seconds committed = 1

可以看到，提交得越多，花费的时间就越长（你的具体数据可能与这里报告的不同）。这只是单用户的情况，如果有多个用户在做同样的工作，所有这些用户都过于频繁地提交，那么得到的数字将飞速增长。

在其他类似的情况下，我们也不止一次地听到过同样的“故事”。例如，我们已经知道，如果不使用绑定变量，而且频繁地完成硬解析，这会严重地降低并发性，原因是存在库缓存竞争和过量的CPU占用。即使转而使用绑定变量，如果过于频繁地软解析，也会带来大量的开销（导致过多软解析的原因可能是：执意地关闭游标，尽管稍后就会重用这些游标）。必须在必要时才完成操作，COMMIT就是这样的一种操作。最好根据业务需求来确定事务的大小，而不是错误地为了减少数据库上的资源使用而“压缩”事务。

在这个例子中，COMMIT的开销存在两个因素：

q         显然会增加与数据库的往返通信。如果每个记录都提交，生成的往返通信量就会大得多。

q         每次提交时，必须等待redo写至磁盘。这会导致“等待”。在这种情况下，等待称为“日志文件同步”（log file sync）。

q         只需对这个Java应用稍做修改就可以观察到后面这一条。我们将做两件事情：

q         增加一个DBMS_MONITOR调用，启用对等待事件的SQL跟踪。在Oracle9i中，则要使用alter session set events ‘10046 trace name context forever, level 12’，因为DBMS_MONITOR是Oracle 10g 中新增的。

q         把con.commit()调用改为一条完成提交的SQL语句调用。如果使用内置的JDBC commit()调用，这不会向跟踪文件发出SQL COMMIT语句，而TKPROF（用于格式化跟踪文件的工具）也不会报告完成COMMIT所花费的时间。

因此，我们将doInserts()方法修改如下：

doInserts( con, 1, 1 );        Statement stmt = con.createStatement ();        stmt.execute        ( "begin dbms_monitor.session_trace_enable(waits=>TRUE); end;" );          doInserts( con, iters.intValue(), iters.intValue() );

对于main方法，要增加以下代码：

PreparedStatement commit =               con.prepareStatement               ("begin /* commit size = " + commitCount + " */ commit; end;" );          int rowcnt = 0;          int committed = 0;          ...        if ( rowcnt == commitCount )          {               commit.executeUpdate();                   rowcnt = 0;                   committed++;

如果运行这个应用来插入10,000行，每行提交一次，TKPROF报告显示的结果如下：

begin /* commit size = 1 */ commit; end; .... Elapsed times include waiting on following events: Event waited on                               Times                Max. Wait                 Total Waited ----------------------------------------       Waited              ----------                      ------------ SQL*Net message to client           10000               0.00                           0.01 SQL*Net message from client       10000               0.00                           0.04 log file sync                                       8288                 0.06                           2.00

如果还是插入10,000行，但是只在插入了全部10,000行时才提交，就会得到如下的结果：

begin /* commit size = 10000 */ commit; end; .... Elapsed times include waiting on following events: Event waited on                              Times                 Max. Wait                 Total Waited ----------------------------------------      Waited                ----------                      ------------ log file sync                                      1                          0.00                           0.00 SQL*Net message to client           1                          0.00                           0.00 SQL*Net message from client      1                          0.00                           0.00

如果在每个INSERT之后都提交，几乎每次都要等待。尽管每次只等待很短的时间，但是由于经常要等待，这些时间就会累积起来。运行时间中整整2秒都用于等待COMMIT完成，换句话说，等待LGWR将redo写至磁盘。与之形成鲜明对比，如果只提交一次，就不会等待很长时间（实际上，这个时间实在太短了，以至于简直无法度量）。这说明，COMMIT是一个很快的操作；我们希望响应时间或多或少是“平”的，而不是所完成工作量的一个函数。

那么，为什么COMMIT的响应时间相当“平”，而不论事务大小呢？在数据库中执行COMMIT之前，困难的工作都已经做了。我们已经修改了数据库中的数据，所以99.9%的工作都已经完成。例如，已经发生了以下操作：

q         已经在SGA中生成了undo块。

q         已经在SGA中生成了已修改数据块。

q         已经在SGA中生成了对于前两项的缓存redo。

q         取决于前三项的大小，以及这些工作花费的时间，前面的每个数据（或某些数据）可能已经刷新输出到磁盘。

q         已经得到了所需的全部锁。

执行COMMIT时，余下的工作只是：

q         为事务生成一个SCN。如果你还不熟悉SCN，起码要知道，SCN是Oracle使用的一种简单的计时机制，用于保证事务的顺序，并支持失败恢复。SCN还用于保证数据库中的读一致性和检查点。可以把SCN看作一个钟摆，每次有人COMMIT时，SCN都会增1.

q         LGWR将所有余下的缓存重做日志条目写到磁盘，并把SCN记录到在线重做日志文件中。这一步就是真正的COMMIT。如果出现了这一步，即已经提交。事务条目会从V$TRANSACTION中“删除”，这说明我们已经提交。

q         V$LOCK中记录这我们的会话持有的锁，这些所都将被释放，而排队等待这些锁的每一个人都会被唤醒，可以继续完成他们的工作。

q         如果事务修改的某些块还在缓冲区缓存中，则会以一种快速的模式访问并“清理”。块清除（Block cleanout）是指清除存储在数据库块首部的与锁相关的信息。实质上讲，我们在清除块上的事务信息，这样下一个访问这个块的人就不用再这么做了。我们采用一种无需生成重做日志信息的方式来完成块清除，这样可以省去以后的大量工作（在下面的“块清除”一节中将更全面地讨论这个问题）。

可以看到，处理COMMIT所要做的工作很少。其中耗时最长的操作要算LGWR执行的活动（一般是这样），因为这些磁盘写是物理磁盘I/O。不过，这里LGWR花费的时间并不会太多，之所以能大幅减少这个操作的时间，原因是LGWR一直在以连续的方式刷新输出重做日志缓冲区的内容。在你工作期间，LGWR并非缓存这你做的所有工作；实际上，随着你的工作的进行，LGWR会在后台增量式地刷新输出重做日志缓冲区的内容。这样做是为了避免COMMIT等待很长时间来一次性刷新输出所有的redo。

因此，即使我们有一个长时间运行的事务，但在提交之前，它生成的许多缓存重做日志已经刷新输出到磁盘了（而不是全部等到提交时才刷新输出）。这也有不好的一面，COMMIT时，我们必须等待，直到尚未写出的所有缓存redo都已经安全写到磁盘上才行。也就是说，对LGWR的调用是一个同步（synchronous）调用。尽管LGWR本身可以使用异步I/O并行地写至日志文件，但是我们的事务会一直等待LGWR完成所有写操作，并收到数据都已在磁盘上的确认才会返回。

前面我提高过，由于某种原因，我们用的是一个Java程序而不是PL/SQL，这个原因就是PL/SQL提供了提交时优化（commit-time optimization）。我说过，LGWR是一个同步调用，我们要等待它完成所有写操作。在Oracle 10g Release 1及以前版本中，除PL/SQL以外的所有编程语言都是如此。PL/SQL引擎不同，要认识到直到PL/SQL例程完成之前，客户并不知道这个PL/SQL例程中是否发生了COMMIT，所以PL/SQL引擎完成的是异步提交。它不会等待LGWR完成；相反，PL/SQL引擎会从COMMIT调用立即返回。不过，等到PL/SQL例程完成，我们从数据库返回客户时，PL/SQL例程则要等待LGWR完成所有尚未完成的COMMIT。因此，如果在PL/SQL中提交了100次，然后返回客户，会发现由于存在这种优化，你只会等待LGWR一次，而不是100次。这是不是说可以在PL/SQL中频繁地提交呢？这是一个很好或者不错的主意吗？不是，绝对不是，在PL/SQ;中频繁地提交与在其他语言中这样做同样糟糕。指导原则是，应该在逻辑工作单元完成时才提交，而不要在此之前草率地提交。

注意      如果你在执行分布式事务或者以最大可能性模式执行Data Guard，PL/SQL中的这种提交时优化可能会被挂起。因为此时存在两个参与者，PL/SQL必须等待提交确实完成后才能继续。

为了说明COMMIT是一个“响应时间很平”的操作，下面将生成不同大小的redo，并测试插入（INSERT）和提交（COMMIT）的时间。为此，还是在SQL*Plus中使用AUTOTRACE。首先创建一个大表（要把其中的测试数据插入到另一个表中），再创建一个空表：

ops$tkyte@ORA 10G > @big_table 100000 ops$tkyte@ORA 10G > create table t as select * from big_table where 1=0; Table created.

然后在SQL*Plus中运行以下命令：

ops$tkyte@ORA 10G > set timing on ops$tkyte@ORA 10G > set autotrace on statistics; ops$tkyte@ORA 10G > insert into t select * from big_table where rownum <= 10; ops$tkyte@ORA 10G > commit;

在此监视AUTOTRACE提供的redo size（redo大小）统计，并通过set timing on监视计时信息。我执行了这个测试，并尝试插入不同数目的行（行数从10到100,000，每次增加一个数量级）。表9-1显示了我的观察结果。

表9-1     随事务大小得到的提交时间*

   插入行数         插入时间（秒）      redo大小（字节）                提交时间（秒）

               10                   0.05                                    116                                       0.06

             100                   0.08                                 3,594                                       0.04

          1,000                   0.07                            372,924                                       0.06

       10,000                   0.25                         3,744,620                                       0.06

     100,000                   1.94                      37,843,108                                       0.07

*这个测试在一个单用户主机上完成，这个主机有一个8MB的日志缓冲区和两个512MB的在线重做日志文件。

可以看到，使用一个精确度为百分之一秒的计数器度量时，随着生成不同数量的redo（从116字节到37MB），却几乎测不出COMMIT时间的差异。在我们处理和生成重做日志时，LGWR也没有闲着，它在后台不断地将缓存的重做信息刷新输出到磁盘上。所以，我们生成37MB的重做日志信息时，LGWR一直在忙着，可能每1MB左右刷新输出一次。等到COMMIT时，剩下的重做日志信息（即尚未写出到磁盘的redo）已经不多了，可能与创建10行数据生成的重做日志信息相差无几。不论生成了多少redo，结果应该是类似的（但可能不完全一样）。

9.3.2             ROLLBACK做什么？

把COMMIT改为ROLLBACK，可能会得到完全不同的结果。回滚时间绝对是所修改数据量的一个函数。修改上一节中的脚本，要求完成一个ROLLBACK（只需把COMMIT改成ROLLBACK），计时信息将完全不同（见表9-2）。

表9-2     随事务大小得到的回滚时间

   插入行数                        回滚时间（秒）                                 提交时间（秒）

               10                                       0.04                                                               0.06

             100                                       0.05                                                               0.04

          1,000                                       0.06                                                               0.06

       10,000                                       0.22                                                               0.06

     100,000                                        1.6                                                                0.07

这是可以想见的，因为ROLLBACK必须物理地撤销我们所做的工作。类似于COMMIT，必须完成一系列操作。在到达ROLLBACK之前，数据库已经做了大量的工作。再复习一遍，可能已经发生的操作如下：

q         已经在SGA中生成了undo块。

q         已经在SGA中生成了已修改数据块。

q         已经在SGA中生成了对于前两项的缓存redo。

q         取决于前三项的大小，以及这些工作花费的时间，前面的每个数据（或某些数据）可能已经刷新输出到磁盘。

q         已经得到了所需的全部锁。

ROLLBACK时，要做以下工作：

q         撤销已做的所有修改。其完成方式如下：从undo段读回数据，然后实际上逆向执行前面所做的操作，并将undo条目标记为已用。如果先前插入了一行，ROLLBACK会将其删除。如果更新了一行，回滚就会取消更新。如果删除了一行，回滚将把它再次插入。

q         会话持有的所有锁都将释放，如果有人在排队等待我们持有的锁，就会被唤醒。

与此不同，COMMIT只是将重做日志缓冲区中剩余的数据刷新到磁盘。与ROLLBACK相比，COMMIT完成的工作非常少。这里的关键是，除非不得已，否则不会希望回滚。回滚操作的开销很大，因为你花了大量的时间做工作，还要花大量的时间撤销这些工作。除非你有把握肯定会COMMIT你的工作，否则干脆什么也别做。听上去这好像是一个常识，这是当然的了，既然不想COMMIT，又何苦去做所有这些工作！不过，我经常看到这样一些情况：开发人员使用一个“真正”的表作为临时表，在其中填入数据，得到这个表的报告，如何回滚，并删除表中的临时数据。下一节我会讨论真正的临时表，以及如何避免这个问题。

1.4   分析redo

作为一名开发人员，应该能够测量你的操作生成了多少redo，这往往很重要。生成的redo越多，你的操作花费的时间就越长，整个系统也会越慢。你不光在影响你自己的会话，还会影响每一个会话。redo管理是数据库中的一个串行点。任何Oracle实例都只有一个LGWR，最终所有事务都会归于LGWR，要求这个进程管理它们的redo，并BOMMIT其事务，LGWR要做的越多，系统就会越慢。通过查看一个操作会生成多少redo，并对一个问题的多种解决方法进行测试，可以从中找出最佳的方法。

9.4.1             测量redo

要查看生成的redo量相当简单，这在本章前面已经见过。我使用了SQL*Plus的内置特性AUTOTRACE。不过AUTOTRACE只能用于简单的DML，对其他操作就力所不能及了，例如，它无法查看一个存储过程调用做了什么。为此，我们需要访问两个动态性能视图：

q         V$MYSTAT，其中有会话的提交信息。

q         V$STATNAME，这个视图能告诉我们V$MYSTAT中的每一行表示什么（所查看的统计名）。

因为我经常要做这种测量，所以使用了两个脚本，分别为mystat和mystat2。mystat.sql脚本把我感兴趣的统计初始值（如redo大小）保存在一个SQL*Plus变量中：

set verify off column value new_val V define S="&1"   set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' /

mystat2.sql脚本只是打印出该统计的初始值和结束值之差：

set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' /

下面，可以测量一个给定事务会生成多少redo。我们只需这样做：

@mystat "redo size" ...process... @mystat2

例如：

ops$tkyte@ORA 10G > @mystat "redo size" NAME                                                   VALUE ------------------------------                       ---------- redo size                                              496   ops$tkyte@ORA 10G > insert into t select * from big_table; 100000 rows created.   ops$tkyte@ORA 10G > @mystat2 NAME                                    V                                     DIFF ------------------------------       ----------                          ---------------- redo size                               37678732                     37,678,236

如上所示，这个INSERT生成了大约37MB的redo。你可能想与一个直接路径INSERT生成的redo做个比较，如下：

注意      这一节的例子在一个NOARCHIVELOG模式的数据库上执行。如果你的数据库采用ARCHIVELOG模式，要想观察到这种显著的差异，必须把表置为NOLOGGING。稍后的“SQL中设置NOLOGGING”一节中会更详细地分析NOLOGGING属性。不过，在一个“实际”的系统上，对于所有“非日志”（nonlogged）操作，一定要与你的DBA协调好。

ops$tkyte@ORA 10G > @mystat "redo size" NAME                                                   VALUE ------------------------------                       ---------- redo size                                              37678732   ops$tkyte@ORA 10G > insert /*+ APPEND */ into t select * from big_table; 100000 rows created.   ops$tkyte@ORA 10G > @mystat2 ops$tkyte@ORA 10G > set echo off NAME                                    V                                     DIFF ------------------------------       ----------                          ---------------- redo size                               37714328                     35,596

以上方法使用了V$MYSTAT视图，这个方法对于查看各个选项的副作用通常很有用。mystat.sql     脚本适用于有一两个操作的小测试，但是如果我们想完成很大的一系列测试呢？在此可以用到一个很小的测试工具。在下一节中，我们将建立和使用这个测试工具，并利用一个表来记录我们的结果，从而分析BEFORE触发器生成的redo。

9.4.2             redo生成和BEFORE/AFTER触发器

经常有人问我：“除了可以在BEFORE触发器中修改一行的值外，BEFORE和AFTER触发器之间还有没有其他的区别？”嗯，对于这个问题，答案是当然有。BEFORE触发器要额外的redo信息，即使它根本没有修改行中的任何值。实际上，这是一个很有意思的案例研究，使用上一节介绍的技术，我们会发现：

q         BEFORE或AFTER触发器不影响DELETE生成的redo。

q         在Oracle9i Release 2 及以前版本中，BEFORE或AFTER触发器会使INSERT生成同样数量的额外redo。在Oracle 10g 中，则不会生成任何额外的redo。

q         在Oracle9i Release 2及以前的所有版本中，UPDATE生成的redo只受BEFORE触发器的影响。AFTER触发器不会增加任何额外的redo。不过，在Oracle 10g 中，情况又有所变化。具体表现为：

n          总的来讲，如果一个表没有触发器，对其更新期间生成的redo量总是比Oracle9i及以前版本中要少。看来这是Oracle着力解决的一个关键问题：对于触发器的表，要减少这种表更新所生成的redo量。

n          在Oracle 10g 中，如果表有一个BEFORE触发器，则其更新期间生成的redo量比9i中更大。

n          如果表有AFTER触发器，则更新所生成的redo量与9i中一样。

为了完成这个测试，我们要使用一个表T，定义如下：

create table t ( x int, y char(N), z date );

但是，创建时N的大小是可变的。在这个例子中，将使用N=30、100、500、1,000和2,000来得到不同宽度的行。针对不同大小的Y列运行测试，再来分析结果。我使用了一个很小的日志表来保存多次运行的结果：

create table log ( what varchar2(15), -- will be no trigger, after or before op varchar2(10), -- will be insert/update or delete rowsize int, -- will be the size of Y redo_size int, -- will be the redo generated rowcnt int ) -- will be the count of rows affected

这里使用以下DO_WORK存储过程来生成事务，并记录所生成的redo。子过程REPORT是一个本地过程（只在DO_WORK过程中可见），它只是在屏幕上报告发生了什么，并把结果保存到LOG表中：

ops$tkyte@ORA 10G > create or replace procedure do_work( p_what in varchar2 ) 2   as 3          l_redo_size number; 4          l_cnt number := 200; 5 6          procedure report( l_op in varchar2 ) 7          is 8          begin 9                   select v$mystat.value-l_redo_size 10                           into l_redo_size 11                  from v$mystat, v$statname 12                  where v$mystat.statistic# = v$statname.statistic# 13                           and v$statname.name = 'redo size'; 14 15                  dbms_output.put_line(l_op || ' redo size = ' || l_redo_size || 16                           ' rows = ' || l_cnt || ' ' || 17                           o_char(l_redo_size/l_cnt,'99,999.9') || 18                           ' bytes/row' ); 19                  insert into log 20                           select p_what, l_op, data_length, l_redo_size, l_cnt 21                            from user_tab_columns 22                           where table_name = 'T' 23                           and column_name = 'Y'; 24         end;

本地过程SET_REDO_SET会查询V$MYSTAT和V$STATNAME，来获取到目前为止会话已生成的当前redo量。它将过程中的变量L_REDO_SIZE设置为这个值：

25         procedure set_redo_size 26         as 27         begin 28                  select v$mystat.value 29                           into l_redo_size 30                  from v$mystat, v$statname 31                  where v$mystat.statistic# = v$statname.statistic# 32                           and v$statname.name = 'redo size'; 33         end;

接下来是主例程。它收集当前的redo大小，运行一个INSERT/UPDATE/DELETE，然后把该操作生成的redo保存到LOG表中：

34         begin 35                  set_redo_size; 36                  insert into t 37                           select object_id, object_name, created 38                           from all_objects 39                           where rownum <= l_cnt; 40                  l_cnt := sql%rowcount; 41                  commit; 42                  report('insert'); 43 44                  set_redo_size; 45                  update t set y=lower(y); 46                  l_cnt := sql%rowcount; 47                  commit; 48                   report('update'); 49 50                  set_redo_size; 51                  delete from t; 52                  l_cnt := sql%rowcount; 53                  commit; 54                  report('delete'); 55         end; 56 /

一旦有了这个例程，下面将Y列的宽度设置为2,000，然后运行以下脚本来测试3种场景：没有触发器、有BEFORE触发器，以及有AFTER触发器。

ops$tkyte@ORA 10G > exec do_work('no trigger'); insert redo size = 505960 rows = 200 2,529.8 bytes/row update redo size = 837744 rows = 200 4,188.7 bytes/row delete redo size = 474164 rows = 200 2,370.8 bytes/row PL/SQL procedure successfully completed.   ops$tkyte@ORA 10G > create or replace trigger before_insert_update_delete 2 before insert or update or delete on T for each row 3 begin 4          null; 5 end; 6 / Trigger created. ops$tkyte@ORA 10G > truncate table t; Table truncated.   ops$tkyte@ORA 10G > exec do_work('before trigger'); insert redo size = 506096 rows = 200 2,530.5 bytes/row update redo size = 897768 rows = 200 4,488.8 bytes/row delete redo size = 474216 rows = 200 2,371.1 bytes/row PL/SQL procedure successfully completed.   ops$tkyte@ORA 10G > drop trigger before_insert_update_delete; Trigger dropped. ops$tkyte@ORA 10G > create or replace trigger after_insert_update_delete 2 after insert or update or delete on T 3 for each row 4 begin 5          null; 6 end; 7 / Trigger created.   ops$tkyte@ORA 10G > truncate table t; Table truncated.   ops$tkyte@ORA 10G > exec do_work( 'after trigger' ); insert redo size = 505972   rows = 200 2,529.9 bytes/row update redo size = 856636      rows = 200 4,283.2 bytes/row delete redo size = 474176 rows = 200 2,370.9 bytes/row PL/SQL procedure successfully completed.

前面的输出是在把Y大小设置为2,000字节时运行脚本所得到的。完成所有运行后，就能查询LOG表，并看到以下结果：

ops$tkyte@ORA 10G > break on op skip 1 ops$tkyte@ORA 10G > set numformat 999,999 ops$tkyte@ORA 10G > select op, rowsize, no_trig,                                              before_trig-no_trig, after_trig-no_trig 2 from 3 ( select op, rowsize, 4          sum(decode( what, 'no trigger', redo_size/rowcnt,0 ) ) no_trig, 5          sum(decode( what, 'before trigger', redo_size/rowcnt, 0 ) ) before_trig, 6          sum(decode( what, 'after trigger', redo_size/rowcnt, 0 ) ) after_trig 7 from log 8 group by op, rowsize 9 ) 10 order by op, rowsize 11 / OP                 ROWSIZE              NO_TRIG         BEFORE_TRIG-NO_TRIG                        AFTER_TRIG-NO_TRIG ----------         --------------             ----------------      ------------------------------------                        --------------------------------- delete                   30                            291                                                 0                                            0                              100                            364                                               -1                                          -0                              500                            785                                               -0                                            0                           1,000                         1,307                                               -0                                          -0                           2,000                         2,371                                                 0                                          -0 insert                    30                            296                                                 0                                          -0                              100                            367                                                 0                                            0                              500                            822                                                 1                                            1                           1,000                         1,381                                               -0                                          -0                           2,000                         2,530                                                 0                                            0 update                  30                            147                                            358                                       152                              100                            288                                            363                                       157                              500                         1,103                                            355                                       150                           1,000                         2,125                                            342                                       137                           2,000                         4,188                                            300                                         94 15 rows selected.

现在，我想知道日志模式（ARCHIVELOG和NOARCHIVELOG模式）是否会影响这些结果。我发现答案是否定的，这两种模式得到的结果数一样。我很奇怪为什么这个结果与Expert One-on-One Oracle第1版中的结果有很大的差异。你现在读的这本书或多或少就是以那本书为基础的。出版那本书时，Oracle的最新版本是Oracle8i 8.1.7 。前面所示的Oracle 10g 结果与Oracle8i得到的结果大不相同，但是对于Oracle9i，这个表中所示的结果则与Oracle8i的结果很接近：

 

我发现，在Oracle9i Release 2和Oracle 10g 这两个版本之间，触发器对事务实际生成的redo存在不同的影响。可以很容易地看到这些执行：

q         是否存在触发器对DELETE没有影响（DELETE还是不受触发器的影响）。

q         在Oracle9i Release 2及以前版本中，INSERT会受到触发器的影响。初看上去，你可能会说，Oracle 10g 优化了INSERT，所以它不会受到影响，但是再看看Oracle 10g 中无触发器时生成的redo总量，你会看到，这与Oracle9i Release 2及以前版本中有触发器时生成的redo量是一样的。所以，并不是Oracle 10g 减少了有触发器时INSERT生成的redo量，而是所生成的redo量是常量（有无触发器都会生成同样多的redo），无触发器时，Oracle 10g 中的INSERT比Oracle9i中生成的redo要多。

q         在9i中，UPDATE会受BEFORE触发器的影响，但不受AFTER触发器的影响。初看上去，似乎Oracle 10g 中改成了两个触发器都会影响UPDATE。但是通过进一步的分析，可以看到，实际上Oracle 10g 中无触发器是UPDATE生成的redo有所下降，下降的量正是有触发器时UPDATE生成的redo量。所用与9i和 10g 中INSERT的情况恰恰相反，与9i相比，没有触发器时Oracle 10g 中UPDATE生成的redo量会下降。

表9-3对此做了一个总结，这里列出了Oracle9i及以前版本与Oracle 10g 中触发器的DML操作生成的redo量分别有怎样的影响。

表9-3     触发器对redo生成的影响

DML操作       AFTER触发器        BEFORE触发器         AFTER触发器        BEFORE触发器

                     （ 10g 以前）         （ 10g 以前）            （ 10g ）                （ 10g ）

DELETE          不影响                  不影响                     不影响                  不影响

INSERT          增加redo             增加redo                常量redo             常量redo

UPDATE          增加redo             不影响                     增加redo             增加redo

测试用例的重要性

更新这本书的第一版时，我切切实实地感受到，这是一个绝好的例子，可以充分说明为什么要用测试用例展示一件事物到底是好是坏。如果我在第一版中只是下了个结论：“触发器会如此这般影响INSERT、UPDATE和DELETE”，而没有提供一种方法来加以度量，另外倘若在这里也没有提供测试用例，那我很有可能还会沿袭同样的结论。在Oracle9i和Oracle 10g 中同样地运行这些测试用例，却会得到不同的结果，所以现在我能很容易地展示出这两个版本间的差异，并且知道Oracle数据库确实“发生了变化”。在对第一版更新的过程中，我一次次地发现，如果没有这些测试用例，我可能完全依据过去的经验妄下断言，得出许多错误的结论。

现在你应该知道怎么来估计redo量，这是每一个开发人员应该具备的能力。你可以：

q         估计你的“事务”大小（你要修改多少数据）。

q         在要修改的数据量基础上再加10%～20%的开销，具体增加多大的开销取决于你要修改的行数。修改行越多，增加的开销就越小。

q         对于UPDATE，要把这个估计值加倍。

在大多数情况下，这将是一个很好的估计。UPDATE的估计值加倍只是一个猜测，实际上这取决于你修改了多少数据。之所以加倍，是因为在此假设要取一个X字节的行，并把它更新（UPDATE）为另一个X字节的行。如果你取一个小行（数据量较少的行），要把它更新为一个大行（数据量较多的行），就不用对这个值加倍（这更像是一个INSERT）。如果取一个大行，而把它更新为一个小行，也不用对这个值加倍（这更像是一个DELETE）。加倍只是一种“最坏情况”，因为许多选项和特性会对此产生影响，例如，存在索引或者没有索引（我这里就没有索引）也会影响这个底线。维护索引结构所必须的工作量对不同的UPDATE来说是不同的，此外还有一些影响因素。除了前面所述的固定开销外，还必须把触发器的副作用考虑在内。另外要考虑到Oracle为你执行的隐式操作（如外键上的ON DELETE CASCADE设置）。有了这些考虑，你就能适当地估计redo量以便调整事务大小以及实现性能优化。不过，只有通过实际的测试才能得到确定的答案。给定以上脚本，你应该已经知道如何对任何对象和事务自行测量redo。

9.4.3             我能关掉重做日志生成吗？

这个问题经常被问到。答案很简单：不能。因为重做日志对于数据库至关重要；它不是开销，不是浪费。不论你是否相信，重做日志对你来说确确实实必不可少。这是无法改变的事实，也是数据库采用的工作方式。如果你真的“关闭了redo”，那么磁盘驱动器的任何暂时失败、掉电或每个软件崩溃都会导致整个数据库不可用，而且不可恢复。不过需要指出，有些情况下执行某些操作时确实可以不生成重做日志。

注意      对于Oracle9i Release 2，DBA可能把数据库置于FORCE LOGGING模式。在这种情况下，所有操作都会计入日志。查询SELECT FORCE_LOGGING FROM V$DATABASE可以查看是否强制为日志模式。这个特性是为了支持Data Guard, Data Guard是Oracle的一个灾难恢复特性，它依赖于redo来维护一个备用数据库（standby database）备份。

1.      在SQL中设置NOLOGGING

有些SQL语句和操作支持使用NOLOGGING子句。这并不是说：这个对象的所有操作在执行时都不生成重做日志，而是说有些特定操作生成的redo会比平常（即不使用NOLOGGING子句时）少得多。注意，我只是说“redo”少得多，而不是“完全没有redo“。所有操作都会生成一些redo——不论日志模式是什么，所有数据字典操作都会计入日志。只不过使用NOLOGGING子句后，生成的redo量可能会显著减少。下面是使用NOLOGGING子句的一个例子。为此先在采用ARCHIVELOG模式运行的一个数据库中运行以下命令：

ops$tkyte@ORA 10G > select log_mode from v$database;   LOG_MODE ------------ ARCHIVELOG   ops$tkyte@ORA 10G > @mystat "redo size" ops$tkyte@ORA 10G > set echo off   NAME                           VALUE ----------                          ---------- redo size                      5846068   ops$tkyte@ORA 10G > create table t 2 as 3 select * from all_objects; Table created.   ops$tkyte@ORA 10G > @mystat2 ops$tkyte@ORA 10G > set echo off NAME                               V                       DIFF -------------               -------------                   ---------------- redo size          11454472        5,608,404

这个CREATE TABLE生成了大约5.5MB的redo信息。接下来删除这个表，再重建，不过这一次采用NOLOGGING模式：

ops$tkyte@ORA 10G > drop table t; Table dropped.   ops$tkyte@ORA 10G > @mystat "redo size" ops$tkyte@ORA 10G > set echo off   NAME                             VALUE ------------                        ------------- redo size                        11459508   ops$tkyte@ORA 10G > create table t 2 NOLOGGING 3 as 4 select * from all_objects; Table created.   ops$tkyte@ORA 10G > @mystat2 ops$tkyte@ORA 10G > set echo off   NAME                               V                       DIFF ----------                   ----------                       ---------------- redo size               11540676                  81,168

这一次，只生成了80KB的redo信息。

可以看到，差距很悬殊：原来有5.5MB的redo，现在只有80KB。5.5MB是实际的表数据本身；现在它直接写至磁盘，对此没有生成重做日志。

如果对一个NOARCHIVELOG模式的数据库运行这个测试，就看不到什么差别。在NOARCHIVELOG模式的数据库中，除了数据字典的修改外，CREATE TABLE不会记录日志。如果你想在NOARCHIVELOG模式的数据库上看到差别，可以把对表T的DROP TABLE和CREATE TABLE换成DROP INDEX和CREATE INDEX。默认情况下，不论数据库以何种模式运行，这些操作都会生成日志。从这个例子可以得出一个很有意义的提示：要按生产环境中所采用的模式来测试你的系统，因为不同的模式可能导致不同的行为。你的生产系统可能采用AUCHIVELOG模式运行；倘若你执行的大量操作在ARCHIVELOG模式下会生成redo，而在NOARCHIVELOG模式下不会生成redo，你肯定想在测试时就发现这一点，而不要等到系统交付给用户时才暴露出来！

这么说，好像所有工作都应该尽可能采用NOLOGGING模式，是这样吗？实际上，答案很干脆：恰恰相反。必须非常谨慎地使用这种模式，而且要与负责备份和恢复的人沟通之后才能使用。下面假设你创建了一个非日志模式的表，并作为应用的一部分（例如，升级脚本中使用了CREATE TABLE AS SELECT NOLOGGING）。用户白天修改了这个表。那天晚上，表所在的磁盘出了故障。“没关系“，DBA说”数据库在用ARCHIVELOG模式运行，我们可以执行介质恢复“。不过问题是，现在无法从归档重做日志恢复最初创建的表，因为根本没有生成日志。这个表将无法恢复。由此可以看出使用NOLOGGIG操作最重要的一点是：必须与DBA和整个系统协调。如果你使用了NOLOGGING操作，而其他人不知道这一点，你可能就会拖DBA的后退，使得出现介质失败后DBA无法全面地恢复数据库。必须谨慎而且小心地使用这些NOLOGGING操作。

关于NOLOGGING操作，需要注意以下几点：

q         事实上，还是会生成一定数量的redo。这些redo的作用是保护数据字典。这是不可避免的。与以前（不使用NOLOGGING）相比，尽管生成的redo量要少多了，但是确实会有一些redo。

q         NOLOGGING不能避免所有后续操作生成redo。在前面的例子中，我创建的并非不生成日志的表。只是创建表（CREATE TABLE）这一个操作没有生成日志。所有后续的“正常“操作（如INSERT、UPDATE和DELETE）还是会生成日志。其他特殊的操作（如使用SQL*Loader的直接路径加载，或使用INSERT /*+ APPEND */语法的直接路径插入）不生成日志（除非你ALTER这个表，再次启用完全的日志模式）。不过，一般来说，应用对这个表执行的操作都会生成日志。

q         在一个ARCHIVELOG模式的数据库上执行NOLOGGING操作后，必须尽快为受影响的数据文件建立一个新的基准备份，从而避免由于介质失败而丢失对这些对象的后续修改。实际上，我们并不会丢失后来做出的修改，因为这些修改确实在重做日志中；我们真正丢失的只是要应用这些修改的数据（即最初的数据）。

2.      在索引上设置NOLOGGING

使用NOLOGGING选项有两种方法。你已经看到了前一种，也就是把NOLOGGING关键字潜在SQL命令中。另一种方法是在段（索引或表）上设置NOLOGGING属性，从而隐式地采用NOLOGGING模式来执行操作。例如，可以把一个索引或表修改为默认采用NOLOGGING模式。这说明，以后重建这个索引不会生成日志（其他索引和表本身可能还会生成redo，但是这个索引不会）：

ops$tkyte@ORA 10G > create index t_idx on t(object_name); Index created.   ops$tkyte@ORA 10G > @mystat "redo size" ops$tkyte@ORA 10G > set echo off   NAME                             VALUE ----------                           ---------- redo size                        13567908

 

ops$tkyte@ORA 10G > alter index t_idx rebuild; Index altered. ops$tkyte@ORA 10G > @mystat2 ops$tkyte@ORA 10G > set echo off NAME                               V                       DIFF -------------               --------------                  ---------------- redo size          15603436            2,035,528

这个索引采用LOGGING模式（默认），重建这个索引会生成2MB的重做日志。不过，可以如下修改这个索引：

ops$tkyte@ORA 10G > alter index t_idx nologging; Index altered.   ops$tkyte@ORA 10G > @mystat "redo size" ops$tkyte@ORA 10G > set echo off   NAME                             VALUE ----------                           ---------- redo size                        15605792   ops$tkyte@ORA 10G > alter index t_idx rebuild; Index altered.   ops$tkyte@ORA 10G > @mystat2 ops$tkyte@ORA 10G > set echo off   NAME                               V                       DIFF -------------               -------------                   ---------------- redo size          15668084            62,292

现在它只生成61KB的redo。但是，现在这个索引没有得到保护（unprotected），如果它所在的数据文件失败而必须从一个备份恢复，我们就会丢失这个索引数据。了解这一点很重要。现在索引是不可恢复的，所以需要做一个备份。或者，DBA也可以干脆创建索引，因为完全可以从表数据直接创建索引。

3.      NOLOGGING小结

可以采用NOLOGGING模式执行以下操作：

q         索引的创建和ALTER（重建）。

q         表的批量INSERT（通过/*+APPEND */提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据不生成redo，但是所有索引修改会生成redo，但是所有索引修改会生成redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。

q         LOB操作（对大对象的更新不必生成日志）。

q         通过CREATE TABLE AS SELECT创建表。

q         各种ALTER TABLE操作，如MOVE和SPLIT。

在一个ARCHIVELOG模式的数据库上，如果NOLOGGING使用得当，可以加快许多操作的速度，因为它能显著减少生成的重做日志量。假设你有一个表，需要从一个表空间移到另一个表空间。可以适当地调度这个操作，让它在备份之后紧接着发生，这样就能把表ALTER为NOLOGGING模式，移到表，创建索引（也不生成日志），然后再把表ALTER回LOGGING模式。现在，原先需要X小时才能完成的操作可能只需要X/2小时（运行是会不会真的减少50%的时间，这一点我不敢打保票！）。要想适当地使用这个特性，需要DBA的参与，或者必须与负责数据库备份和恢复（或任何备用数据库）的人沟通。如果这个人不知道使用了这个特性，一旦出现介质失败，就可能丢失数据，或者备用数据库的完整性可能遭到破坏。对此一定要三思。

9.4.4             为什么不能分配一个新日志？

老是有人问我这个问题。这样做会得到一条警告消息（可以在服务器上的alert.log中看到）：

Thread 1 cannot allocate new log, sequence 1466 Checkpoint not complete Current log# 3 seq# 1465 mem# 0: /home/ora 10g /oradata/ora 10g /redo03.log

警告消息中也可能指出Archival required而不是Checkpoint not complete，但是效果几乎都一样。DBA必须当心这种情况。如果数据库试图重用一个在线重做日志文件，但是发现做不到，就会把这样一条消息写到服务器上的alert.log中。如果DBWR还没有完成重做日志所保护数据的检查点（checkpointing）,或者ARCH还没有把重做日志文件复制到归档目标，就会发生这种情况。对最终用户来说，这个时间点上数据库实际上停止了。它会原地不动。DBWR或ARCH将得到最大的优先级以将redo块刷新输出的磁盘。完成了检查点或归档之后，一切又回归正常。数据库之所以暂停用户的活动，这是因为此时已经没地方记录用户所做的修改了。Oracle试图重用一个在线重做日志文件，但是由于归档进程尚未完成这个文件的复制（Archival required），所以Oracle必须等待（相应地，最终用户也必须等待），直到能安全地重用这个重做日志文件为止。

如果你看到会话因为一个“日志文件切换”、“日志缓冲区空间”或“日志文件切换检查点或归档未完成”等待了很长时间，就很可能遇到了这个问题。如果日志文件大小不合适，或者DBWR和ARCH太慢（需要由DBA或系统管理员调优），在漫长的数据库修改期间，你就会注意到这个问题。我经常看到未定制的“起始”数据库就存在这个问题。“起始”数据库一般会把重做日志的大小定得太小，不适用较大的工作量（包括数据字典本身的起始数据库构建）。一旦启动数据库的加载，你会注意到，前1,000行进行得很快，然后就会呈喷射状进行：1,000进行得很快，然后暂停，接下来又进行得很快，然后又暂停，如此等等。这些就是很明确的提示，说明你遭遇了这个问题。

要解决这个问题，有几种做法：

q         让DBWR更快一些。让你的DBA对DBWR调优，为此可以启用ASYNC I/O、使用DBWR I/O从属进程，或者使用多个DBWR进程。看看系统产生的I/O，查看是否有一个磁盘（或一组磁盘）“太热”，相应地需要将数据散布开。这个建议对ARCH也适用。这种做法的好处是，你不用付出什么代价就能有所收获，性能会提高，而且不必修改任何逻辑/结构/代码。这种方法确实没有缺点。

q         增加更多重做日志文件。在某些情况下，这会延迟Checkpoint not complete的出现，而且过一段时间后，可以把Checkpoint not complete延迟得足够长，使得这个错误可能根本不会出现（因为你给DBWR留出了足够的活动空间来建立检查点）。这个方法也同样适用于Archival required消息。这种方法的好处是可以消除系统中的“暂停”。其缺点是会消耗更多的磁盘空间，但是在此利远远大于弊。

q         重新创建更大的日志文件。这会扩大填写在线重做日志与重用这个在线重做日志文件之间的时间间隔。如果重做日志文件的使用呈“喷射状”，这种方法同样适用于Archival required消息。倘若一段时间内会大量生成日志（如每晚加载、批处理等），其后一段数据却相当平静，如果有更大的在线重做日志，就能让ARCH在平静的期间有足够的时间“赶上来”。这种方法的优缺点与前面增加更多文件的方法是一样的。另外，它可能会延迟检查点的发生，由于（至少）每个日志切换都会发生检查点，而现在日志切换间隔会更大。

q         让检查点发生得更频繁、更连续。可以使用一个更小的块缓冲区缓存（不太好），或者使用诸如FAST_START_MTTR_TARGET、LOG_CHECKPOINT_INTERVAL和LOG_CHECKPOINT_TIMEOUT之类的参数设置。这会强制DBWR更频繁地刷新输出脏块。这种方法的好处是，失败恢复的时间会减少。在线重做日志中应用的工作肯定更少。其缺点是，如果经常修改块，可能会更频繁地写至磁盘。缓冲区缓存本该更有效的，但由于频繁地写磁盘，会导致缓冲区缓存不能充分发挥作用，这可能会影响下一节将讨论的块清除机制。

究竟选择哪一种方法，这取决于你的实际环境。应该在数据库级确定它，要把整个实例都考虑在内。

9.4.5             块清除

在这一节中，我们将讨论块清除（block cleanout），即生成所修改数据库块上与“锁定”有关的信息。这个概念很重要，必须充分理解，在下一节讨论讨厌的ORA-01555:snapshot too old错误时会用到这个概念。

在第6章中，我们曾经讨论过数据锁以及如何管理它们。我介绍了数据锁实际上是数据的属性，存储在块首部。这就带来一个副作用，下一次访问这个块时，可能必须“清理”这个块，换句话说，要将这些事务信息删除。这个动作会生成redo，并导致变脏（原本并不脏，因为数据本身没有修改），这说明一个简单的SELECT有可能生成redo，而且可能导致完成下一个检查点时将大量的块写至磁盘。不过，在大多数正常的情况下，这是不会发生的。如果系统中主要是小型或中型事务（OLTP），或者数据仓库会执行直接路径加载或使用DBMS_STATS在加载操作后分析表，你会发现块通常已经得到“清理”。如果还记得前面“COMMIT做什么？”一节中介绍的内容，应该知道，COMMIT时处理的步骤之一是：如果块还在SGA中，就要再次访问这些块，如果可以访问（没有别人在修改这些块），则对这些块完成清理。这个 活动称为提交清除（commit cleanout），即清除已修改块上事务信息。最理想的是，COMMIT可以完成块清除，这样后面的SELECT（读）就不必再清理了。只有块的UPDATE才会真正清除残余的事务信息，由于UPDATE已经在生成redo，所用注意不到这个清除工作。

可以强制清除不发生来观察它的副作用，并了解提交清除是怎么工作的。在与我们的事务相关的提交列表中，Oracle会记录已修改的块列表。这些列表都有20个块，Oracle会根据需要分配多个这样的列表，直至达到某个临界点。如果我们修改的块加起来超过了块缓冲区缓存大小的10%，Oracle会停止为我们分配新的列表。例如，如果缓冲区缓存设置为可以缓存3,000个块，Oracle会为我们维护最多300个块（3,000的10%）。COMMIT时，Oracle会处理这些包含20个块指针的列表，如果块仍可用，它会执行一个很快的清理。所以，只要我们修改的块数没有超过缓存中总块数的10%，而且块仍在缓存中并且是可用的，Oracle就会在COMMIT时清理这些块。否则，它只会将其忽略（也就是说不清理）。

有了上面的理解，可以人为地建立一些条件来查看这种块清除是怎么工作的。我把DB_CACHE_SIZE设置为一个很低的值4MB，这足以放下512个8KB的块（我的块大小是8KB）。然后创建一个表，其中每行刚好能在一个块中放下（我不会在每块里放两行）。接下来在这个表中填入了500行，并COMMIT。我要测量到此为止生成的redo量。然后运行一个SELECT，它会访问每个块，最后测量这个SELECT生成的redo量。

让许多人奇怪的是，SELECT居然会生成redo。不仅如此，它还会把这些修改块“弄脏”，导致DBWR再次将块写入磁盘。这是因为块清除的缘故。接下来，我会再一次运行SELECT，可以看到这回没有生成redo。这在意料之中，因为此时块都已经“干净”了。

ops$tkyte@ORA 10G > create table t 2 ( x char(2000), 3 y char(2000), 4 z char(2000) 5 ) 6 / Table created. ops$tkyte@ORA 10G > set autotrace traceonly statistics ops$tkyte@ORA 10G > insert into t 2 select 'x', 'y', 'z' 3 from all_objects 4 where rownum <= 500; 500 rows created. Statistics ---------------------------------------------------------- ...                    3297580    redo   size ...                    500             rows processed ops$tkyte@ORA 10G > commit; Commit complete.

以上就是我的表，每个块中一行（我的数据库中块大小为8KB）。现在测量读数据是生成的redo量：

ops$tkyte@ORA 10G > select * 2 from t; 500 rows selected. Statistics ---------------------------------------------------------- ...                    36484             redo   size ...                    500     rows processed

可见，这个SELECT在处理期间生成了大约35KB的redo。这表示对T进行全表扫描时修改了35KB的块首部。DBWR会在将来某个时间把这些已修改的块写回到磁盘上。现在，如果再次运行这个查询：

ops$tkyte@ORA 10G > select * 2 from t; 500 rows selected. Statistics ---------------------------------------------------------- ...                     0         redo   size ...                     500    rows   processed ops$tkyte@ORA 10G > set autotrace off

可以看到，这一次没有生成redo，块都是干净的。

如果把缓冲区缓存设置为能保存至少5,000个块，再次运行前面的例子。你会发现，无论哪一个SELECT，生成的redo都很少甚至没有——我们不必在其中任何一个SELECT语句期间清理脏块。这是因为，我们修改的500个块完全可以在缓冲区缓存的10%中放下，而且我们是独家用户。别人不会动数据，不会有人导致我们的数据刷新输出到磁盘，也没有人在访问这些块。在实际系统中，有些情况下，至少某些块不会进行清除，这是正常的。

如果执行一个大的INSERT（如上所述）、UPDATE或DELETE，这种块清除行为的影响最大，它会影响数据库中的许多块（缓存中10%以上的块都会完成块清除）。你会注意到，在此之后，第一个“接触”块的查询会生成少量的redo，并把块弄脏，如果DBWR已经将块刷新输出或者实例已经关闭，可能就会因为这个查询而导致重写这些块，并完全清理缓冲区缓存。对此你基本上做不了什么。这是正常的，也在意料之中。如果Oracle不对块完成这种延迟清除，那么COMMIT的处理就会与事务本身一样长。COMMIT必须重新访问每一个块，可能还要从磁盘将块再次读入（它们可能已经刷新输出）。

如果你不知道块清除，不明白块清除如果工作，在你看来中可能就是一种好像毫无来由的神秘事务。例如，假设你更新（UPDATE）了大量数据，然后COMMIT。现在对这些数据运行一个查询来验证结果。看上去查询生成了大量写I/O和redo。倘若你不知道存在块清除，这似乎是不可能的；对我来说，第一次看到这种情况时就是这样认为的，实在是不可思议。然后你请别人一起来观察这个行为，但这是不可再生的，因为在第二次查询时块又是“干净的”了。这样一来，你就会把它当成是数据库的奥秘之一。

在一个OLTP系统中，可能从来不会看到这种情况发生，因为OLTP系统的特点是事务都很短小，只会影响为数不多的一些块。根据设计，所有或者大多数事务都短而精。只是修改几个块，而且这些块都会得到清理。在一个数据仓库中，如果加载之后要对数据执行大量UPDATE，就要把块清除作为设计中要考虑的一个因素。有些操作会在“干净”的块上创建数据。例如，CREATE TABLE AS SELECT、直接路径加载的数据以及直接路径插入的数据都会创建“干净”的块。UPDATE、正常的INSERT或DELETE创建的块则可能需要在第一次读时完成块清除。如果你有如下的处理，就会受到块清除的影响：

q         将大量新数据批量加载到数据仓库中；

q         在刚刚加载的所有数据上运行UPDATE（产生需要清理的块）；

q         让人们查询这些数据。

必须知道，如果块需要清理，第一接触这个数据的查询将带来一些额外的处理。如果认识到这一点，你就应该在UPDATE之后自己主动地“接触”数据。你刚刚加载或修改了大量的数据；现在至少需要分析这些数据。可能要自行运行一些报告来验证数据已经加载。这些报告会完成块清除，这样下一个查询就不必再做这个工作了。更好的做法是：由于你刚刚批量加载了数据，现在需要以某种方式刷新统计。通过运行DBMS_STATS实用程序来收集统计，就能很好地清理所有块，这是因为它只是使用SQL来查询信息，会在查询当中很自然地完成块清除。

9.4.6             日志竞争

与cannot allocate new log信息一样，日志竞争（log contention）也是DBA必须修改的问题，一般要与系统管理员联手。不过，如果DBA检查得不够仔细，开发人员也可以检测到这个问题。

如果你遭遇到日志竞争，可能会看到对“日志文件同步”事件的等待时间相当长，另外Statspack报告的“日志文件并行写”事件中写次数（写I/O数）可能很大。如果观察到这种情况，就说明你遇到了重做日志的竞争；重做日志写得不够快。发生这种情况可能有许多原因。其中一个应用原因（所谓应用原因是指DBA无法修正这个问题，而必须由开发人员解决）是：提交得太过频繁，例如在重复执行INSERT的循环中反复提交。在“COMMIT做什么？”一节中我们讲过，如果提交得太频繁，这不仅是不好的编程实践，肯定还会引入大量日志文件同步等待。假设你的所有事务都有适当的大小（完全遵从业务规则的要求，而没有过于频繁地提交），但还是看到了这种日志文件等待，这就有其他原因了。其中最常见的原因如下：

q         redo放在一个慢速设备上：磁盘表现不佳。该购买速度更快的磁盘了。

q         redo与其他频繁访问的文件放在同一个设备上。redo设计为要采用顺序写，而且要放在专用的设备上。如果系统的其他组件（甚至其他Oracle组件）试图与LGWR同时读写这个设备，你就会遭遇某种程度的竞争。在此，只要有可能，你就会希望确保LGWR拥有这些设备的独占访问权限。

q         已缓冲方式装载日志设备。你在使用一个“cooked”文件系统（而不是RAW磁盘）。操作系统在缓冲数据，而数据库也在缓冲数据（重做日志缓冲区）。这种双缓冲会让速度慢下来。如果可能，应该以一种“直接”方式了装载设备。具体操作依据操作系统和设备的不同而有所变化，但一般都可以直接装载。

q         redo采用了一种慢速技术，如RAID-5。RAID-5很合适读，但是用于写时表现则很差。前面已经了解了COMMIT期间会发生什么，我们必须等待LGWR以确保数据写到磁盘上。倘若使用的技术会导致这个工作变慢，这就不是一个好主意。

只有有可能，实际上你会希望至少有5个专用设备来记录日志，最好还有第6个设备来镜像归档日志。由于当前往往使用9GB、20GB、36GB、200GB、300GB和更大的磁盘，要想拥有这么多专用设备变得更加困难。但是如果能留出4块你能找到的最小、最快的磁盘，再有一个或两个大磁盘，就可以很好地促进LGWR和ARCH的工作。安排这些磁盘时，可以把它们分为3组（见图9-5）：

q         重做日志组1：磁盘1和磁盘3

q         重做日志组2：磁盘2和磁盘4

q         归档：磁盘5，可能还有磁盘6（大磁盘）

图9-5     最优的重做日志配置

将重做日志组1（包括成员A和B）放在磁盘1和磁盘3上。把重做日志组2（包括成员C和D）放在磁盘2和磁盘4上。如果还有组3、4等，将分别放在相应的奇数和偶数磁盘组上。这样做的作用是，数据库当前使用组1时，LGWR会同时写至磁盘1和3.这一组填满时，LGWR会转向磁盘2和4.等这一组再填满时，LGWR会回到磁盘1和3.与此同时，ARCH会处理完整的在线重做日志，并讲其写至磁盘5和6（即大磁盘）。最终的效果是，不论是ARCH还是LGWR都不会读正在有别人写的磁盘，也不会写正在由别人读的磁盘，所以在此没有竞争（见图9-6）。

图9-6     重做日志流

因此，当LGWR写组1时，ARCH在读组2，并写至归档磁盘。当LGWR写组2时，ARCH在读组1，并写至归档磁盘。采用这种方式，LGWR和ARCH都有各自的专用设备，不会与别人竞争，甚至不会相互竞争。

在线重做日志文件是一组Oracle文件，最适合使用RAW磁盘（原始磁盘）。如果说哪种类型的文件可以考虑使用原始分区（RAW），首先其冲地便是日志文件。关于使用原始分区和cooked文件系统的优缺点，这方面的讨论很复杂。由于这不是一本有关DBA/SA认为的书，所以我不打算过分深入。但是要指出，如果你要使用RAW设备，在线重做日志文件就是最佳候选。在线重做日志文件不用备份，所以将在线重做日志文件放在RAW分区上而不是cooked文件系统上，这不会影响你的任何备份脚本。ARCH总能把RAW日志转变为cooked文件系统文件（不能使用一个RAW设备来建立归档），在这种情况下，就大大减少了RAW设备的“神秘感”。

9.4.7             临时表和redo/undo

一般认为临时表（temporary table）还是Oracle中一个相当新的特性，只是在Oracle8i 8.1.5 版本中才引入。因此，有关临时表还存在一些困惑，特别是在日志方面。我们将在第10章介绍如何以及为什么使用临时表。这一节只是要回答这样一个问题：“关于生成修改日志，临时表是怎样做的？”

临时表不会为它们的块生成redo。因此，对临时表的操作不是“可恢复的”。修改临时表中的一个块时，不会将这个修改记录到重做日志文件中。不过，临时表确实会生成undo，而且这个undo会计入日志。因此，临时表也会生成一些redo。初看上去好像没有道理：为什么需要生成undo？这是因为你能回滚到事务中的一个SAVEPOINT。可以擦除对临时表的后50个INSERT，而只留下前50个。临时表可以有约束，正常表有的一切临时表都可以有。可能有一条INSERT语句要向临时表中插入500行，但插入到第500行时失败了，这就要求回滚这条语句。由于临时表一般表现得就像“正常”表一样，所以临时表必须生成undo。由于undo数据必须建立日志，因此临时表会为所生成的undo生成一些重做日志。

这样似乎很不好，不过没有你想像中那么糟糕。在临时表上运行的SQL语句主要是INSERT和SELECT。幸运的是，INSERT只生成极少的undo（需要把块恢复为插入前的“没有”状态，而存储“没有”不需要多少空间），另外SELECT根本不生成undo。因此，如果只使用临时表执行INSERT和SELECT，这一节对你来说意义不大。仅当要对临时表执行UPDATE或DELETE时，才需要关心这一节的内容。

我建立了一个小测试来演示使用临时表时生成的redo量，同时这也暗示了临时表生成的undo量，因为对于临时表，只会为undo生成日志。为了说明这一点，我采用了配置相同的“永久”表和“临时”表，然后对各个表执行相同的操作，测量每次生成的redo量。这里使用的表如下：

ops$tkyte@ORA 10G > create table perm 2 ( x char(2000) , 3 y char(2000) , 4 z char(2000) ) 5 / Table created.   ops$tkyte@ORA 10G > create global temporary table temp 2 ( x char(2000) , 3 y char(2000) , 4 z char(2000) ) 5 on commit preserve rows 6 / Table created.

我建立了一个小的存储过程，它能执行任意的SQL，并报告SQL生成的redo量。我会使用这个例程分别在临时表和永久表上执行INSERT、UPDATE和DELETE：

ops$tkyte@ORA 10G > 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 v$mystat.value 7                        into l_start_redo 8               from v$mystat, v$statname 9               where v$mystat.statistic# = v$statname.statistic# 10                      and v$statname.name = 'redo size'; 11 12             execute immediate p_sql; 13             commit; 14 15             select v$mystat.value-l_start_redo 16                      into l_redo 17             from v$mystat, v$statname 18             where v$mystat.statistic# = v$statname.statistic# 19                      and v$statname.name = 'redo size'; 20 21             dbms_output.put_line 22                      ( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' || 23                      substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); 24    end; 25 / Procedure created.

接下来，对PERM表和TEMP表运行同样的INSERT、UPDATE和DELETE：

ops$tkyte@ORA 10G > set serveroutput on format wrapped ops$tkyte@ORA 10G > begin 2          do_sql( 'insert into perm 3                   select 1,1,1 4                   from all_objects 5                   where rownum <= 500' ); 6 7          do_sql( 'insert into temp 8                   select 1,1,1 9                   from all_objects 10                  where rownum <= 500' ); 11         dbms_output.new_line; 12 13          do_sql( 'update perm set x = 2' ); 14         do_sql( 'update temp set x = 2' ); 15         dbms_output.new_line; 16 17         do_sql( 'delete from perm' ); 18         do_sql( 'delete from temp' ); 19 end; 20 / 3,297,752 bytes of redo generated for "insert into perm "... 66,488 bytes of redo generated for "insert into temp "... 2,182,200 bytes of redo generated for "update perm set x = 2"... 1,100,252 bytes of redo generated for "update temp set x = 2"...   3,218,804 bytes of redo generated for "delete from perm"... 3,212,084 bytes of redo generated for "delete from temp"...   PL/SQL procedure successfully completed.

可以看到：

q         对“实际”表（永久表）的INSERT生成了大量redo。而对临时表几乎没有生成任何redo。这是有道理的，对临时表的INSERT只会生成很少的undo数据，而且对于临时表只会为undo数据建立日志。

q         实际表的UPDATE生成的redo大约是临时表更新所生成redo的两倍。同样，这也是合理的。必须保存UPDATE的大约一半（即“前映像”）。对于临时表来说，不必保存“后映像”（redo）。

q         DELETE需要几乎相同的redo空间。这是有道理的，因为对DELETE的undo很大，而对已修改块的redo很小。因此，对临时表的DELETE与对永久表的DELETE几乎相同。

注意      你看到INSERT语句在临时表上生成的redo比在永久表上生成的redo还多，这实际上是数据库产品本身的问题，这个问题至少在Oracle 9.2.0 .6和10.1.0.4补丁版中（编写这本书时发布的当前版本）中得到了修正。

因此，关于临时表上的DML活动，可以得出以下一般结论：

q         INSERT会生成很少甚至不生成undo/redo活动。

q         DELETE在临时表上生成的redo与正常表上生成的redo同样多。

q         临时表的UPDATE会生成正常表UPDATE一半的redo。

对于最后一个结论，需要指出有一些例外情况。例如，如果我用2,000字节的数据UPDATE（更新）完全为NULL的一列，生成的undo数据就非常少。这个UPDATE表现得就像是INSERT。另一方面，如果我把有2,000字节数据的一列UPDATE为全NULL，对redo生成来说，这就表现得像是DELETE。平均来讲，可以这样认为：临时表UPDATE与实际表UPDATE生成的undo/redo相比，前者是后者的50%。

一般来讲，关于创建的redo量有一个常识。如果你完成的操作导致创建undo数据，则可以确定逆向完成这个操作（撤销操作）的难易程度。如果INSERT2,000字节，逆向操作就很容易，只需回退到无字节即可。如果删除了（DELETE）2,000字节，逆向操作就是要插入2,000字节。在这种情况下，redo量就很大。

有了以上了解，你可能会避免删除临时表。可以使用TRUNCATE（当然要记住，TRUNCATE是DDL，而DDL会提交事务，而且在Oracle9i及以前版本中，TRUNCATE还会使你的游标失效），或者只是让临时表在COMMIT之后或会话终止时自动置空。执行方法不会生成undo，相应地也不会生成redo。你可能会尽量避免更新临时表，除非由于某种原因必须这样做。你会把临时表主要用于插入（INSERT）和选择（SELECT）。采用这种方式，就能更优地使用临时表不生成redo的特有能力。

9.5   分析undo

我们已经讨论了许多有关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@ORA 10G > create table t 2 as 3 select object_name unindexed, 4 object_name indexed 5 from all_objects 6 / Table created.   ops$tkyte@ORA 10G > create index t_idx on t(indexed); Index created.   ops$tkyte@ORA 10G > 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@ORA 10G > 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@ORA 10G > update t set unindexed = lower(unindexed); 48771 rows updated.   ops$tkyte@ORA 10G > select used_ublk ... 10 / USED_UBLK ---------- 401   ops$tkyte@ORA 10G > commit; Commit complete.

这个UPDATE使用了401个块存储其undo。提交会“解放”这些块，或者将其释放，所以如果再次对V$TRANSACTION运行这个查询，它还会显示no rows selected。更新同样的数据时，不过这一次是加索引的列，会观察到下面的结果：

ops$tkyte@ORA 10G > update t set indexed = lower(indexed); 48771 rows updated.   ops$tkyte@ORA 10G > select used_ublk ... 10 / USED_UBLK ---------- 1938

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

9.5.2             ORA-01555:snapshot too old错误

在上一章中，我们简要分析了ORA-01555错误，并了解了导致这个错的一个原因：提交得太过频繁。这一节我们将更详细地分析ORA-01555错误的起因和解决方案。ORA-01555是最让人讨厌的错误之一。这是许多神话、谬误和不当推测的基础。

注意      ORA-01555与数据破坏或数据丢失毫无关系。在这方面，这是一个“安全”的错误；惟一的影响是：接收到这个错误的查询无法继续处理。

这个错误实际上很直接，其实只有两个原因，但是其中之一有一个特例，而且这种特例情况发生得如此频繁，所以我要说存在3个原因：

q         undo段太小，不足以在系统上执行工作。

q         你的程序跨COMMIT获取（实际上这是前一点的一个变体）。我们在上一章讨论了这种情况。

q         块清除。

前两点与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错误的几种解决方案，一般来说可以采用下面的方法：

q         适当地设置参数UNDO_RETENTION（要大于执行运行时间最长的事务所需的时间）。可以用V$UNDOSTAT来确定长时间运行的查询的持续时间。另外，要确保磁盘上已经预留了足够的空间，使undo段能根据所请求的UNDO_RETENTION增大。

q         使用手动undo管理时加大或增加更多的回滚段。这样在长时间运行的查询执行期间，覆盖undo数据的可能性就能降低。这种方法可以解决上述的所有3个问题。

q         减少查询的运行时间（调优）。如果可能的话，这绝对是一个好办法，所以应该首先尝试这种方法。这样就能降低对undo段的需求，不需求太大的undo段。这种方法可以解决上述的所有3个问题。

q         收集相关对象的统计信息。这有助于避免前面所列的第三点。由于大批量的UPDATE或INSERT会导致块清除（block cleanout），所以需要在大批量UPDATE或大量加载之后以某种方式收集统计信息。

我们还会详细讨论这些方案，因为这些都是必须掌握的重要内容。在真正开始介绍这些解决方案之前，最好先来看看具体情况是怎样的。

1.      undo段确实太小

一种场景是：你的系统中事务很小。正因如此，只需要分配非常少的undo段空间。假如，假设存在以下情况：

q         每个事务平均生成8KB的undo。

q         平均每秒完成其中5个事务（每秒生成40KB的undo，每分钟生成2,400KB的undo）。

q         有一个生成1MB undo的事务平均每分钟出现一次。总的说来，每分钟会生成大约3.5MB的undo。

q         你为系统配置了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有两种方法：

q         自动undo管理（Automatic undo management）：采用这种方法，通过UNDO_RETENTION参数告诉Oracle要把undo保留多长时间。Oracle会根据并发工作负载来确定要创建多少个undo段，以及每个undo段应该多大。数据库甚至在运行时可以在各个undo段之间重新分配区段，以满足DBA设置的UNDO_RETENTION目标。这是undo管理的推荐方法。

q         手动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@ORA 10G > create undo tablespace undo_small 2 datafile size 2m 3 autoextend off 4 / Tablespace created.   ops$tkyte@ORA 10G > alter system set undo_tablespace = undo_small; System altered.

现在，我们将建立表T来查询和修改。注意我们在这个表中随机地对数据排序。CREATE TABLE AS SELECT力图按查询获取的顺序将行放在块中。我们的目的只是把行弄乱，使它们不至于认为地有某种顺序，从而得到随机的分布：

ops$tkyte@ORA 10G > create table t 2 as 3 select * 4 from all_objects 5 order by dbms_random.random; Table created.   ops$tkyte@ORA 10G > alter table t add constraint t_pk primary key(object_id) 2 / Table altered.   ops$tkyte@ORA 10G > exec dbms_stats.gather_table_stats( user, 'T', cascade=> true ); PL/SQL procedure successfully completed.

现在可以执行修改了：

ops$tkyte@ORA 10G > 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@ORA 10G > 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个记录。要修正这个错误，我们要保证做到两点：

q         数据库中UNDO_RETENTION要设置得足够长，以保证这个读进程完成。这样数据库就能扩大undo表空间来保留足够的undo，使我们能够完成工作。

q         undo表空间可以增长，或者为之手动分配更多的磁盘空间。

对于这个例子，我认为这个长时间运行的进程需要大约600秒才能完成。我的UNDO_RETENTION设置为900（单位是秒，所以undo保持大约15分钟）。我修改了undo表空间的数据文件，使之一次扩大1MB，直到最大达到2GB：

ops$tkyte@ORA 10G > column file_name new_val F ops$tkyte@ORA 10G > select file_name 2 from dba_data_files 3 where tablespace_name = 'UNDO_SMALL';   FILE_NAME ------------------------------ /home/ora 10g /oradata/ora 10g /OR A 10G /datafile/o1_mf_undo_sma_1 729wn1h_.dbf   ops$tkyte@ORA 10G > alter database 2 datafile '&F' 3 autoextend on 4 next 1m 5 maxsize 2048m ; old     2: datafile '&F' new   2: datafile '/home/ora 10g /.../o1_mf_undo_sma_1729wn1h_.dbf' Database altered.

再次并发地运行这些进程时，两个进程都能顺利完成。这一次undo表空间的数据文件扩大了，因为在此允许undo表空间扩大，而且根据我设置的undo保持时间可知：

ops$tkyte@ORA 10G > 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错误，以下条件都必须满足：

q         首先做了一个修改并COMMIT，块没有自动清理（即没有自动完成“提交清除”，例如修改了太多的块，在SGA块缓冲区缓存的10%中放不下）。

q         其他会话没有接触这些块，而且在我们这个“倒霉”的查询（稍后显示）命中这些块之前，任何会话都不会接触它们。

q         开始一个长时间运行的查询。这个查询最后会读其中的一些块。这个查询从SCN t1开始，这就是读一致SCN，必须将数据回滚到这一点来得到读一致性。开始查询时，上述修改事务的事务条目还在undo段的事务表中。

q         查询期间，系统中执行了多个提交。执行事务没有接触执行已修改的块（如果确实接触到，也就不存在问题了）。

q         由于出现了大量的COMMIT，undo段中的事务表要回绕并重用事务槽。最重要的是，将循环地重用原来修改事务的事务条目。另外，系统重用了undo段的区段，以避免对undo段首部块本身的一致读。

q         此外，由于提交太多，undo段中记录的最低SCN现在超过了t1（高于查询的读一致SCN）。

如果查询到达某个块，而这个块在查询开始之前已经修改并提交，就会遇到麻烦。正常情况下，会回到块所指的undo段，找到修改了这个块的事务的状态（换句话说，它会找到事务的COMMIT SCN）。如果这个COMMIT SCN小于t1，查询就可以使用这个块。如果该事务的COMMIT SCN大于t1，查询就必须回滚这个块。不过，问题是，在这种特殊的情况下，查询无法确定块的COMMIT SCN是大于还是小于t1。相应地，不清楚查询能否使用这个块映像。这就导致了ORA-01555错误。

为了真正看到这种情况，我们将在一个表中创建多个需要清理的块。然后在这个表上打开一个游标，并允许对另外某个表完成许多小事务（不是那个刚更新并打开了游标的表）。最后尝试为该游标获取数据。现在，我们认为游标需要的数据每问题，应该能看到所有数据，因为我们是在打开游标之前完成并提交了表修改。倘若此时得到ORA-01555错误，就说明存在前面所述的问题。要建立这个例子，我们将使用：

q         2MB UNDO_SMALL undo 表空间（还是这个undo表空间）。

q         4MB的缓冲区缓存，足以放下大约500个块。这样我们就可以将一些脏块刷新输出到磁盘来观察这种现象。

首先创建要查询的大表：

ops$tkyte@ORA 10G > create table big 2 as 3 select a.*, rpad('*',1000,'*') data 4 from all_objects a; Table created.   ops$tkyte@ORA 10G > exec dbms_stats.gather_table_stats( user, 'BIG' ); PL/SQL procedure successfully completed.

由于使用了这么大的数据字段，每个块中大约有6～7行，所以这个表中有大量的块。接下来，我们创建将由多个小事务修改的小表：

ops$tkyte@ORA 10G > create table small ( x int, y char(500) ); Table created.   ops$tkyte@ORA 10G > insert into small select rownum, 'x' from all_users; 38 rows created. ops$tkyte@ORA 10G > commit; Commit complete.   ops$tkyte@ORA 10G > 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@ORA 10G > alter system set undo_tablespace = undo_small; System altered.   ops$tkyte@ORA 10G > update big 2                   set temporary = temporary 3          where rowid in 4                   ( 5                   select r 6                   from ( 7                            select rowid r, row_number() over                                          (partition by 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@ORA 10G > commit; Commit complete.

现在我们知道磁盘上有大量脏块。我们已经写出了一些，但是没有足够的空间把它们都放下。接下来打开一个游标，但是尚未获取任何数据行。要记住，打开游标时，结果集是预定的，所以即使Oracle并没有具体处理一行数据，打开结果集这个动作本身就确定了“一致时间点”，即结果集必须相对于那个时间点一致。现在要获取刚刚更新并提交的数据，而且我们知道没有别人修改这个数据，现在应该能获取这些数据行而不需要如何undo。但是此时就会“冒出”延迟块清除。修改这些块的事务太新了，所以Oracle必须验证在我们开始之前这个事务是否已经提交，如果这个信息（也存储在undo表空间中）被覆盖，查询就会失败。以下打开了游标：

ops$tkyte@ORA 10G > variable x refcursor ops$tkyte@ORA 10G > exec open :x for select * from big; PL/SQL procedure successfully completed.   ops$tkyte@ORA 10G > !./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@ORA 10G > 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错误，能你可以试试以下解决方案：

q         首先，保证使用的事务“大小适当”。确保没有不必要地过于频繁地提交。

q         使用DBMS_STATS扫描相关的对象，加载之后完成这些对象的清理。由于块清除是极大量的UPDATE或INSERT造成的，所以很有必要这样做。

q         允许undo表空间扩大，为之留出扩展的空间，并增加undo保持时间。这样在长时间运行查询期间，undo段事务表中的事务槽被覆盖的可能性就会降低。针对导致ORA-01555错误的另一个原因（undo段太小），也同样可以采用这个解决方案（这两个原因有紧密的关系；块清除问题就是因为处理查询期间遇到了undo段重用，而undo段大小正是重用undo段的一个根本原因）。实际上，如果把undo表空间设置为一次自动扩展1MB，而且undo保持时间为900秒，再运行前面的例子，对表BIG的查询就能成功地完成了。

q         减少查询的运行时间（调优）。如果可能的话，这总是件好事，所以应该首先尝试这样做。

9.6   小结

在这一章中，我们介绍了redo和undo，并说明了redo和undo对开发人员有什么意义。这里我主要强调的是作为开发人员要当心的问题，因为有关redo和undo的问题实际上应该有DBA或SA负责修正。重要的是，从这一章应该了解到redo和undo的重要性，要知道它们绝对不是开销，而是数据库的组成部分，它们是必要的，甚至是必不可少的。一旦很好地了解了redo和undo如何工作，以及它们做些什么，你就能更好地加以利用。要知道，如果不必要过于频繁地提交，你不仅不会因此“节省”任何资源，实际上反而会浪费资源，这才是重点；因为这会占用更多的CPU时间、更多的磁盘，还有做更多的编程工作。应该清楚数据库需要做什么，然后才让数据库真正地去做。