Oracle Undo系列

常用SQL

一个DML语句的处理过程描述

update undotest set object_type='VIEW' where object_type='PROCEDURE';
检查shared pool中是否存在相同的语句，如果存在，重用执行计划，执行扫描运算，如果不存在，执行硬解析生成执行计划
根据执行计划中的扫描运算，检查undotest表中的相关数据块是否存在buffer cache中，如果不存在则读取到内存中
检查数据块中符合object_type='PROCEDURE'条件的记录，如果没有符合条件的行记录，则结束语句，如果存在则进入下一步
以当前模式（current）获取符合object_type='PROCEDURE'条件的数据块，准备进行更新
在回滚表空间的相应回滚段头的事务表上分配事务槽，这个动作需要记录redo日志
从回滚段数据块上创建object_type='PROCEDURE'的前映像数据，这个动作也要记录redo日志
修改object_type='VIEW' ，这是DML操作的数据变更，而需要记录redo日志
用户提交时，在redo日志中记录提交信息，将回滚段头上的事务表和回滚段数据块标记为非活动，清除修改数据块上的事务信息（也可能延迟清除）。同时必须确保整个事务的redp日志写到磁盘上的日志文件
注意：如果最后用户回滚了事务，oracle从回滚段中将前映像数据提取出来，覆盖被更新的数据块。这个回滚动作本身也需要产生redo日志，因此，我们要知道回滚的代价非常昂贵。

基本概念和名词解释

UBA: Undo block address
RBA: Redo block address
Dba: Data block address
Rdba: Root dba
Xid: Transaction ID
ITL: Interested Transaction List 保存在数据块的头部（事务信息部分），包含XID,UBA,LCK，FLG等重要信息
Transaction Identifiers
Transaction identifiers (XID) uniquely identify a transaction within the system; they are used within the Interested Transaction List (ITL) of the data block.
A transaction identifier consists of:
Undo segment number 即v$rollname中的usn
Transaction table slot number 对应回滚段头中回滚事务表的第几条记录
Sequence number or wrap#
XID = usn# . slot# . wrap#
Undo Block Address
The undo block address (UBA) uniquely identifies the undo block for a given transaction; it is found within the ITL of the data block.
A UBA consists of:
Data block address (DBA) of the block 前映像undo 块地址
The sequence number of the block 序列号
The record number within the block undo记录的开始地址（针对该块）
UBA = DBA. seq#. rec#

Undo回滚段简介

回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。

Undo回滚段的作用

Undo主要有以下几个作用：
（1）事务回滚（Rollback Transaction）
当一个事务修改表中数据的时候，该数据修改前的值（即前镜像，Before Image）会被存放在Undo段中，当用户回滚事务（ROLLBACK）时，Oracle将会利用在数据块ITL槽中记录的Undo块地址（Undo Block Address，Uba），然后找到相应的Undo块，接着利用其中的Undo数据（即前镜像）来将修改的数据恢复到原来的值，从而实现对事务所作的改变进行回滚。
（2）事务恢复（Transaction Recovery）
实例恢复（Instance Recovery）的第一阶段称为前滚（Rolling Forward）或者缓存恢复（Cache Recovery），第二阶段称为回滚（Rolling Back）或者事务恢复。前滚和回滚是Oracle数据库实例发生意外崩溃，重新启动的时候，由SMON进行的自动恢复的过程。所谓的前滚，是应用Redo来恢复Buffer Cache的数据，将Buffer Cache恢复到Crash之前状态，所以此时Buffer Cache中既有崩溃时已经提交但还没有写入数据文件的脏块，还有事务被突然终止而导致的既没有提交又没有回滚的事务的脏块（也就是没有COMMIT，但是DBWn已经将改变的数据刷新到底层磁盘）。前滚完成之后就可以确保联机Redo日志中所有已提交的事务操作的数据写回到数据文件中。接下来，前滚之后，任何未提交的更改必须被撤消，而回滚是在数据库做完前滚操作后并打开数据库的情况下完成的，SMON会利用Undo信息将未提交的事务全部进行回滚。具体来说，SMON进程在完成前滚后，查看Undo段头（Undo段的第1个数据块）记录的事务表（每个事务在使用Undo块时，首先要在该Undo块所在的Undo段头记录一个条目，该条目里记录了该事务相关的信息，其中包括是否提交等），将其中既没有提交也没有回滚，而是在实例崩溃时被异常终止的事务全部回滚。
（3）提供一致性读（Consistent Read）
Oracle是一个多用户系统，当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改了该会话将要读取的数据。如果会话读取到修改后的数据，那么就会造成数据的不一致，出现了脏读（Dirty Read）。所以，一致性读是相对于脏读而言的。在Oracle中，一致性读是通过Undo来实现的，一致性读就是为了保证数据的一致性。在一般情况下，普通查询都是一致性读。
举例来说，假设某个表T中有1W条记录，获取所有记录需要15分钟时间。当前时间为9点整，某用户A发出一条查询语句：“SELECT * FROM T;”，该语句在9点15分时执行完毕。当用户A执行该SQL语句到9点10分的时候，另外一个用户B发出了一条DELETE命令，将T表中的最后一条记录删除并提交了。那么到9点15分时，A用户将返回多少条记录？如果返回9999条记录，那么说明发生了脏读；如果仍然返回1W条记录，那么说明发生了一致性读。很明显，在9点钟那个时间点发出查询语句时，表T中确实有1W条记录，只不过由于I/O的相对较慢，所以才会花15分钟完成所有记录的检索。对于Oracle数据库来说，必须提供一致性读，并且该一致性读是在没有阻塞用户的DML操作的前提下实现的。
那么Undo数据是如何实现一致性读的呢？在Oracle数据库中的Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。如果一个事务需要修改数据块中数据，那么会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。当其它进程读取数据块时，会先比较数据块上的SCN和自己发出SQL语句时刻的SCN，分为以下两种情况：
①　如果该数据块头部的ITL槽上记录的SCN大于自己查询时刻的SCN，那么表示该块被更新过，此时就要借助Undo块了。在该数据块头部的ITL槽上记录了对应的Undo块的地址（Uba），根据Uba就可以找到对应的Undo块。如果发现该Undo块的ITL槽的SCN号也较大，证明该Undo块也不可用，那么需要在该块的ITL槽上继续寻找上一个Undo块地址，层层递归，最终找到SCN号比发出查询的SCN号小的Undo块，将该Undo块中的被修改前的数据取出，从而构建出发出SQL语句时刻的数据块内容，这样的数据块叫做CR（Consistent Read）块。但是在查找的过程中，可能会发现当前Undo块里记录的ITL槽的SCN号比上一个Undo块里记录的SCN号还要大。这种情况说明由于事务被提交或回滚，导致当前找到的Undo块里的数据已经被其它事务覆盖了，于是就无法再找出小于等于发出查询时的那个时间点的SCN号，这时Oracle就会抛出一个非常经典的错误--ORA-1555，也就是snapshot too old（快照过旧）的错误。对于DELETE来说，其Undo信息就是INSERT，也就是说该构建出来的CR块中就插入了被删除的那条记录。
②　如果数据块头部的ITL槽（事务槽）上记录的SCN小于等于自己查询时刻的SCN，那么分为两种情况：第一，若被查询的块上没有活动的事务，则表示该块没有被更新过，是可用的，可以直接读取该数据块上的数据；第二，若被查询的块上有活动的事务，则需要找Undo的前镜像数据。
（4）实现闪回功能
闪回功能中的闪回查询（Flashback Query）、闪回版本查询（Flashback Version Query）、闪回事务查询（Flashback Transaction Query）和闪回表（Flashback TABLE）都是基于Undo表空间中的回滚信息实现的。

Undo回滚段的类型

回滚段可分为系统回滚段和非系统回滚段, 其中非系统回滚段又分为PUBLIC回滚段和PRIVATE回滚段。

回滚段：

1 系统回滚段

2 非系统回滚段：

• PUBLIC回滚段
• PRIVATE回滚段

系统回滚段用于处理涉及系统的CATALOG的事物(比如大多数的DDL), 它位于SYSTEM表空间, 由于只有SYSTEM表空间可以随时保持可用, 因此,不要把SYSTEM回滚段放在其他的表空间中.

原则1: 系统回滚段应放在SYSTEM表空间中, 并且应该永远保持ONLINE状态.

PUBLIC回滚段对于数据库的所有实例(INSTANCE)都是可用的, 除非将其显式设置为OFFLINE.

PRIVATE回滚段是指对于数据库的某个实例是私有的, 为了使用PRIVATE回滚段, 某个实例应当在其INITsid.ORA的ROLLBACK_SEGMENTS中标明所有要使用的PRIVATE回滚段, 或通过使用ALTER ROLLBACK SEGMENT XXX ONLINE来使用某一个回滚段.

建议1: 在单实例系统中,建议将所有回滚段设为PUBLIC.

建议2: 在多实例系统中(如OPS,RAC), 建议将每个实例的PRIVATE回滚段放置到访问比较快的本地设备上.

Undo段存储的内容：不同的DML操作，UNDO BLOCK中保存的前映像内容

Redo中只会记录少量信息，这些信息足以重演事务；同样Undo中也只记录精简信息，这些信息足以撤销事务。具体来说：

• 对于INSERT操作，回滚段只需要记录插入记录的ROWID，如果回退，那么只需将该记录根据ROWID删除即可；

• 对于UPDATE操作，回滚段只需要记录被更新字段的旧值即可（前镜像），回退时通过旧值覆盖新值即可完成回滚；

• 对于DELETE操作，Oracle则必须记录整行的数据，在回滚时，Oracle通过一个反向操作恢复删除的数据。

总结一下：对于相同数据量的数据操作，通常INSERT产生最少的Undo，UPDATE产生的Undo居中，而DELETE操作产生的Undo最多。所以，当一个大的DELETE操作失败或者回滚，总是需要很长的时间，并且会有大量的Redo生成。所以通常在进行大规模数据删除操作时，推荐通过分批删除分次提交，以减少对于回滚段的占用和冲击。

其中bdba表示file 4，block 1150 ,hdba表示file 4，block 810 与我们上面查询出来的地址相符。
我们可以看到，undo块中并没有直接保存rowid信息，但是oracle完全可以根据上面的几个信息（bdba,slot,objd)定位回滚时需要删除的具体数据，因此对于Insert操作，ORACLE只需保留上述信息，即可完成回滚操作

回滚段的数量规划

对于OLTP系统，存在大量的小事务处理，一般建议：

数量多的小回滚段；每四个事务一个回滚段；每个回滚段不要超过十个事务。

对于批处理，一般建议：

少的大回滚段；每个事务一个回滚段。

回滚段的查询

1 查询数据库的的回滚段情况

2 查看系统回滚段基本信息

从上面仅仅是查询到回滚段的基本信息，要了目前各个回滚段的动态信息，还要查询V$ROLLNAME和V$ROLLSTAT视图。V$ROLLNAME视图只存放各回滚段的编号和名字，V$ROLLSTATS存放各个回滚段当前的情况信息。要想得到每个回滚段的信息，就要查询两个表的信息才能得到。如：

3 查看回滚段的使用情况，哪个用户正在使用回滚段的资源(当提交或回滚后资源释放):

4 回滚段当前活动的事物(事务提交或回滚后自动清空)

5 分析 UNDO 的使用情况

6 监控undo表空间

7 查询是否有回滚段的争用

8 查看回滚段的统计信息：

9 查询回滚段的事务回退率

10 查询回滚段在使用,扩展,回缩的时候extent在循环的次数

11 查询回滚段收缩的情况

块清除

块清除（Block Cleanout）是指清除存储在数据块头部与锁相关的信息，其实质是在清除块上的事务信息，包括数据的行级锁和ITL信息（包括提交标志、SCN等），块清除不需要生成Redo日志。Oracle的块清除有两种：快速块清除（Fast Commit Cleanout）和延时块清除（Delayed Block Cleanout）。

通过命令“alter system dump undo header '回滚段名称';”可以将Undo段头信息dump出来，可以很明显地看到事务表（TRN TBL）信息，其中，状态（state）为10代表活动事务，状态（state）为9表示INACTIVE。Dba列表示该事务对应的Undo Block Dba地址。

每个事务处理只分配给一个Undo段，一个Undo段可以同时服务多个事务处理。UPDATE事务的内部流程如下所示：

①　首先当一个事务开始时，需要在Undo段事务表上分配一个事务槽。

②　在数据块头部获取一个ITL事务槽，该事务槽指向Undo段头的事务槽。

③　在修改数据之前，需要记录前镜像（Before Image）信息，这个信息以Undo Record的形式存储在回滚段中，回滚段头事务槽指向该记录。

④　锁定修改行，修改行锁定位（lb-lock byte）指向ITL事务槽。

⑤　进行数据修改。

在提交事务的时候，如果被修改过的数据块仍然在Buffer Cache之中，那么Oracle可以清除ITL信息，这叫作快速块清除（Fast Block Cleanout），也叫提交清除（Fast Commit Cleanout）。快速块清除还有一个限制，当修改的块数量超过Buffer Cache约10％，则对超出部分不再进行快速块清除。

在提交事务的时候，如果被修改过的数据块已经被写回到数据文件上（或大量修改超出Buffer Cache的10％的部分），再次读出该数据块进行修改，显然成本过于高昂，对于这种情况，Oracle选择延迟块清除（Delayed Block Cleanout），即在提交的时候只会清理Undo Segment Header中的事务表信息，而Data Block上的事务标志不会清除，等到下一次访问该Block时再来清除ITL锁定信息，这就是延迟块清除。Oracle通过延迟块清除来提高数据库性能，加快提交操作。如果Oracle不对块完成这种延迟清除，那么COMMIT的处理可能会很长，COMMIT必须重新访问每一个块，可能还要从磁盘将块再次读入内存。在一个OLTP系统中，可能很少看到这种情况发生，因为OLTP系统的特点是事务都很短小，只会影响为数不多的一些块。

如果执行一个大的INSERT、UPDATE或DELETE，会影响数据库中的许多块，那么就有可能在此之后，第一个“接触”块的查询会做延迟块清除，从而生成Redo日志，所以，SELECT语句也有可能会产生Redo日志。

如果有如下的操作，那么可能会受到块清除的影响：

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

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

• 让别人查询这些数据

因此，建议在批量加载了数据后，通过运行DBMS_STATS实用程序来收集统计信息，就能自然的完成块清除工作。Oracle提供了一个内部事件（10203事件）可以用来跟踪数据库的块清除操作，可以通过以下命令设置：

延时块清除

Delayed logging block cleanout(延时块清除)是ORACLE用来提高写性能的一种机制: 当修改操作(INSERT/UPDATE/DELETE)发生时, ORACLE将原有的内容写入回滚段, 更新每个数据块的头部使其指向相应的回滚段, 当该操作被COMMIT时, ORACLE并不再重新访问一遍所有的数据块来确认所有的修改, 而只是更新位于回滚段头部的事务槽来指明该事务已被COMMIT, 这使得写操作可以很快结束从而提高了性能接下来的任何访问该操作所修改的数据的操作会使先前的写操作真正生效, 从而访问到新的值. Delayed logging block cleanout 虽然提高了性能,但却可能导致ORA-01555. 这种情况下, 在OPEN/FETCH前对该表做全表扫描(保证所有的修改被确认)会有所帮助.

为了保证事务的回退和满足多用户的CR， oracle引入了undo 机制， 由于undo是循环使用的，在一个事务完成过程中，它与redo相互配合，其中undo在一次事务中需要完成以下工作：

(1) Transaction 开始前回滚段获取一个ITL(事务槽)，分配空间， 记录事务信息

(2) Transaction 提交后，redo完成记录，同时还清除回滚段的事务信息 包括行级锁，ITL信息(commit 标志，SCN等)

清除这些事务段的信息的过程就叫做块清除， 在完成块清除时, 我们本事务修改的数据块就会存在两种可能

(1) 所有的数据块还保存在 buffer cache 中

(2)部分数据块或者是全部数据块由于LRU管理已经被刷出了buffer cache。

oracle为了考虑到块清除的成本，以及性能，会作以下两种方式的块清除处理：

(1)快速块清除(fast block cleanout), 当事务修改的数据库全部保存在buffer cache 并且修改数据块的数据量没有超过 cache buffer 的 10%，快速清除事务信息。

(2)延迟块清除(delayed block cleanout) 当修改的数据块的阀值超过10% 或者本次事务相关的数据块已经被刷出了 buffer cache， oracle 会下次访问此block 时再清除事务信息。

Undo表空间

Undo信息存储在Undo段中，Undo段又存储在Undo表空间中。Undo表空间仅用于Undo段（在Undo表空间中不能创建其它段类型，例如表、索引等），只能与单个实例相关联。在任意指定时间，一个给定的实例只能有一个表空间是当前可写Undo表空间。Undo表空间是永久的、本地管理的表空间（具有自动区分配），它们由数据库自动进行管理。

Redo和Undo可以从以下几个方面进行区分：

Oracle Undo段中区3种状态（DBA_UNDO_EXTENTS的STATUS列）：ACTIVE、EXPIRED和UNEXPIRED：

• ACTIVE即未提交的Undo信息（活动）：表示事物还在活动，该值对应的Undo段的DBA_ROLLBACK_SEGS.STATUS一定是ONLINE状态，一旦没有活动的事务在使用Undo段，那么对应的Undo段就变成OFFLINE状态。ACTIVE状态的Undo区不会被覆盖。

• EXPIRED即过期的Undo信息（过期）：表示事务已经提交且超过了UNDO_RETENTION指定时间，该状态可以被覆盖使用。

• UNEXPIRED即提交的Undo信息（未过期）：表示事务已经提交但是还没有超过UNDO_RETENTION指定时间，该状态可以被覆盖使用。

关于Undo表空间有如下几个参数：

• UNDO_RETENTION参数指定已提交的Undo信息要保留多长时间（单位为秒），默认为900秒（即15分钟）。但是该值不是绝对的，也就是说，如果有其它事务需要Undo空间，而Undo空间出现不足时，这些信息仍然会被覆盖。只有当表空间设置为GUARANTEE时，才能确保已提交的数据保留UNDO_RETENTION参数设置的时间。RETENTION GUARANTEE是表空间属性而不是初始化参数，此属性只可使用SQL命令行语句来更改。通过更改Undo表空间来保证保留时间的语法是：

要将有保留时间保证的还原表空间返回到其常规设置，请使用以下命令：

查询保留时间状态：

如果设置UNDO_RETENTION为0，那么Oracle启用自动调整UNDO_RETENTION（auto tuning of undo_retention）以满足最长运行查询的需要，在告警日志文件中可以看到如下信息：

Autotune of undo retention is turned on.

可以通过设置“"_undo_autotune"=FALSE”来显式的关闭自动调整UNDO_RETENTION功能。

• UNDO_MANAGEMENT参数用于指定Undo数据的管理方式，分为自动Undo管理（AUM，Automatic Undo Management）和手动Undo管理（MUM，Manual Undo Management）。如果要使用AUM，那么必须设置为AUTO；如果要使用MUM，那么必须设置为MANUAL。在使用AUM时，Oracle会使用Undo表空间管理Undo数据；在使用MUM时，Oracle会使用回滚段管理Undo数据。需要注意的是，在使用AUM时，如果没有配置初始化参数UNDO_TABLESPACE，那么Oracle会自动选择第一个可用的Undo表空间存放Undo数据，如果没有可用的Undo表空间，那么Oracle会使用SYSTEM回滚段存放Undo记录，并在告警文件中记录警告。

• UNDO_TABLESPACE 在使用AUM时，该参数用于指定实例所要使用的Undo表空间。在RAC结构中，因为一个Undo表空间不能由多个实例同时使用，所以必须为每个实例配置一个独立的Undo表空间。

有关Undo表空间的恢复请参考：http://blog.itpub.net/26736162/viewspace-1458787/，这里不再详述。有关Undo表空间常用的几个视图如下所示：

系统回滚段（System Rollback Segment）与延迟回滚段（Deferred Rollback Segment）

SYSTEM回滚段创建在系统表空间中，当手工创建数据库后，在创建普通回滚段之前必须首先创建系统回滚段。但正常情况下，系统回滚段主要用于两个方面：一是系统事务，另一个就是延迟回滚段。延迟回滚段表示的是，当使一个表空间OFFLINE之后，由于表空间不可用（不能进行读写），这个时候若有事务数据位于该表空间并且执行了回滚命令，回滚完成将显示给Client，对于Client看起来该事务已经回滚，但是对于数据库来说该回滚并没有真正完成，这个时候数据库将该回滚信息写入系统回滚段（这就是延迟回滚段），等表空间重新ONLINE的时候，数据库从系统回滚段中将回滚信息写入表空间。

为什么会“ORA-01555”

在告警日志中记录的ORA-01555（snapshot too old，快照过旧）报错信息类似：

默认情况，ORA-01555错误发生时不会自动生成跟踪日志文件，但是可以在系统里设置下面的事件，让它在错误发生时同时生成跟踪日志文件：

由于回滚段是循环使用的，当事务提交以后，该事务占用的回滚段事务会被标记为非活动，此时的回滚段空间可以被覆盖重用。那么问题就出现了，如果一个查询需要使用被覆盖的回滚段构造前镜像（Before Image）实现一致性读，那么此时就会出现Oracle著名的ORA-01555（snapshot too old，快照过旧）错误。需要注意的是，ORA-01555错误是一个安全的错误，它不会造成数据丢失或者损坏，只是会让收到该错误的查询无法继续。

ORA-01555错误的另外一个原因是延迟块清除（Delayed Block Clean out）。当一个查询触发延迟块清除时，Oracle需要去查询回滚段获得该事务的提交SCN。如果事务的前镜像（Before Image）信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小提交SCN，那么Oracle将无从判断查询SCN和事务提交SCN的大小，此时出现延迟块清除会导致ORA-01555错误。

还有一种导致ORA-01555错误的情况出现在使用SQL*Loader直接方式加载（direct=true）数据时，由于不产生Redo和Undo信息，Oracle直接指定CACHED COMMIT SCN进行加载数据，在访问这些数据时，有时会产生ORA-01555错误。

知道了ORA-01555错误产生的原因就可以总结出以下方法来解决ORA-01555错误问题：

（1）扩大回滚段

因为回滚段是循环使用的，如果回滚段足够大，那么那些被提交的数据信息就能保存足够长的时间，而那些大事务就可以完成一致性读取。

（2）增加UNDO_RETENTION时间

在UNDO_RETENTION规定的时间内，任何其它事务都不能覆盖这些数据。

（3）优化相关查询语句，减少一致性读

减少查询语句的一致性读，也可以降低读取不到回滚段数据的风险。

（4）减少不必要的事务提交

提交的事务越少，产生的回滚段信息就越少。

（5）对大事务指定回滚段

通过以下语句可以指定事务的回滚段：

给大事务指定回滚段，即降低大事务回滚信息覆盖其它事务的回滚信息的几率，又降低了它自身的回滚信息被覆盖的几率。大事务的存在，往往是ORA-01555错误产生的诱因。

（6）使用游标时尽量使用显式游标，并且只在需要的时候打开游标，同时将所有可以在游标外做的操作从游标循环中拿出。当游标打开时，查询就开始了，直到游标关闭。减少游标的打开时间，就减少了ORA-01555错误发生的几率。

（7）使用回滚表空间自动管理

回滚表空间自动管理是Oracle 9i后的特性，Oracle自动管理回滚段的创建和回收，并且Oracle 10g中，这一特性大大增强了，而在大型的数据仓库或者报表系统中，会有一些很大的查询作业存在，这时可以考虑使用手动管理，为某些大作业创建单独的回滚段。

ORA-01555错误是一种在oracle数据库中非常常见的错误，甚至也可以说是一个非常经典的错误，只是由于oracle的发展越来越自动化（UNDO自动管理+加强），这个错误已经越来越少见，可能很多使用10g的DBA都没有遇到过这个错误。

这个错误在9i之前的版本（UNDO手工管理）出现的最多，也是最常见的，甚至可以说怎么样处理和避免ORA-01555 错误是令每一个DBA曾头痛，但是又必须面对的问题。从9i的undo自动管理，至现在的10g、11g中的undo auto tuning，使得ORA-01555错误越来越少，但是这个错误仍然不可避免，特别是那些分析型的系统中（OLTP）。

错误原因（一般有两种）

• SQL语句执行时，需要读取UNDO（前映像数据）来构造CR数据块，完成一致性读取。但是在读取undo前映像数据时发现，undo信息已经被覆盖（undo空间循环使用），不能构造一致性读的CR块，抛出ORA-01555错误
• SQL语句执行时，访问到的数据块，需要进行延迟块清除，但是在进行延迟块清除时，不能确定这个数据块的事务提交时间与SQL执行开始时间的先后次序，从而抛出ORA-01555错误

备注：延迟块清除是指前一个事务完成提交时(commit)，由于修改块已经刷新至磁盘等原因，未完成块事务信息的清除（ILT，LB信息等等），在后续的SQL语句访问该块时，需要清除这些信息，这个动作即延迟块清除。

第一种情况的解决方法（仅供参考）

• 增加UNDO空间，延缓UNDO信息被覆盖，也可以理解为增加undo空间循环使用一次的时间。
• 优化抛出错误的SQL语句，缩短SQL语句执行的时间，也可以避免语句需要访问undo信息被覆盖。
• 避免频繁的提交也是一种可行方法，不过需要改动的量较前两个都要大。提交频率降低后，导致undo 信息被覆盖的可能性也降低了（oracle数据库中未提交的undo是不可能被覆盖），或者undo 事务表被覆盖的可能性也降低了。

第二种情况的解决方法（仅供参考）

• 增加UNDO空间，延缓UNDO信息被覆盖，也可以理解为增加undo空间循环使用一次的时间，尽量避免延迟块清除时需要的undo信息被覆盖。
• 优化抛出错误的SQL语句，减少SQL语句需要访问的数据块，可能避免出现ORA-01555，但是这个方法治标不治本，任何后续访问该块的SQL，都会抛出ORA-01555错误。
• 加载buffer cache，尽量使事务提交时，能够完成fast commit（直接清理快上的事务信息），这个方法基本也是过于理想，很难在实际中发挥作用。
• 缩减事务大小，也尽量使事务提交时，执行fast commit。由于需要改造业务逻辑，基本也不现实，很难在实际中发挥作用。
• 我们知道这类错误一般出现在OLAP类型的业务系统中，针对这种情况，最有效的方法是收集可能出现延迟块清除并抛出ORA-01555错误的表，在业务逻辑中，完成事务后，针对这些表立即进行一次全表扫描（清理块上的事务信息），避免后续访问清理时出现的ORA-01555错误。如果业务逻辑修改较困难，可以根据业务规则，指定一个定时针对这些表的全表扫面任务，来规避延迟块清除导致的ORA-01555错误

首先，我们来看下错误说明：

ORA-01555: snapshot too old: rollback segment number XX with name "XX XX XX XX XX" too small

ORA-1555 报错发生在一个查询无法访问相关的 UNDO 信息来创建它开始的时间点的数据拷贝。Oracle 数据库同时维护已提交“版本”的数据块与未提交“版本”的数据块，所以查询可以访问到数据在它起始时间点时在数据库中的状态。这些被称为“一致性读”，并通过 Oracle UNDO 来维护。

让我们通过以下示例的时间线来解释这个错误：

注：UNDO 是一个通用的术语，既指系统管理的 UNDO（或自动 UNDO 管理 AUM），又指回滚段。

错误记录的日志文件

这个报错通常在如下文件中列出：

• 告警日志文件
  生成的消息如以下示例：
  ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

• 事件发生时生成的 trace 文件
  默认地，ORA-1555 是没有 trace 文件生成的，除非在报错发生时已经设置了 1555 event：

  alter system set events '1555 trace name errorstack level 3';

  1	alter system set events '1555 trace name errorstack level 3';

  在这种情况下，当发生 ORA-1555 时会在告警日志文件中报出 trace 文件名，例如：
  Wed Jul 22 09:20:11 2015
  Errors in file /u01/sq/diag/rdbms/DB1/ DB1/trace/ DB1_ora_35236.trc:
  ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_417799848$" too small*

为什么会发生这个错误

这个问题的根本原因主要是由于一个 UNDO 块被覆盖，查询无法建立其开始时的数据拷贝。常见的已知问题如下：

• 行的 UNDO 记录已过期。
  这意味着当前时间减去这行的提交时间大于 UNDO_RETENTION。当一个已提交行的 UNDO 记录 “已过期“，它就可以被重用。
  您可能会问：
  为何我的查询有时可以运行比我的 UNDO_RETENTION 更长的时间才遇到 ORA-01555，但有时却在运行之后不久就遇到？
  这没有准确的答案，它完全取决于数据库活动的数量及 UNDO 表空间的繁忙程度。

  注：一个活动的或未提交的事务的 UNDO 记录被标记为’ACTIVE’。当事务提交，相应的 UNDO 记录被标记为 'UNEXPIRED'。即，它会被保留一段时间，该期限由 UNDO_RETENTION 参数决定（或在 AUM 中，由系统计算出的TUNED_UNDORETENTION 决定）。在 UNDO 记录超过指定的保留时间后，它们被标记为'EXPIRED'并可被重用。

• 行的 UNDO 记录未过期却仍被覆盖。
  这个场景出现在 UNDO 表空间爆满，'UNEXPIRED' 的 UNDO 记录被覆盖。这可能会发生在 UNDO 表空间未打开 RETENTION GUARANTEE 的情况下。

• LOB 段的 LOB 段一致性读拷贝不再可用
  这取决于 LOB 列怎样配置的，in-row 或 out-of-row。In-row LOBs 使用常规的 UNDO 表空间。对于 Out-of-row LOBs 则是使用下列信息。

  LOBs 的一致性读由如下两种方法控制：

• • 使用 PCTVERSION 旧方法

  • 指定总体 LOB 存储空间的最大百分比，用于维护旧版本的 LOB。默认值是 10，意味着旧版本的 LOB 数据不会被覆盖，直到它们占用了 10% 以上的 LOB 存储空间。
    此方法的没落是由于频繁的更新或删除的 LOB 表，PCTVERSION 甚至常常超过 100%。

  • 使用 RETENTION 现在的方法，与 AUM 一起使用
    使用这个表明提示 Oracle 数据库应该保留这个 LOB 列的旧版本。
    Oracle 数据库使用初始化参数 UNDO_RETENTION 的值来决定已提交的 UNDO 数据在数据库中保留多久。
    这个方法使用与 UNDO 段同样的过期机制。如果一个 ORA-01555 发生在一个 LOB 段中，那么这意味着以下两种情况之一：

  A、PCTVERSION 超出，LOB 的一致性读视图被重写

  或

  B、LOB 超出 RETENTION 并且 LOB 在查询产生 ORA-1555 期间被覆盖。

如何诊断

如下是诊断这个问题的步骤列表：

1、检查报错信息

检查告警日志（或包含这个错误的日志文件）来确定报错信息的细节，因为有不同类型的 ORA-1555 报错信息：

a、识别段名

ORA-01555: snapshot too old: rollback segment number with name "" too small

-- 注意这里段名是空 ""

或/和

ORA-22924: snapshot too old

在这个例子中，是在访问存储在 LOB 段内的 UNDO 数据时报了 1555 错误。

b、识别查询的持续时间

失败的查询的执行时间会记录在告警日志或应用日志的一些 ORA-1555 报错信息中：

ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7)

如果查询 query duration 大于 UNDO_RETENTION 参数值，增加 UNDO_RETENTION 值以满足查询持续时间的需要。确保相应地增加 UNDO 表空间大小。

如果 query duration 等于或接近于 UNDO_RETENTION 值，进行以下分析。

2、检查 UNDO 数据文件

如果您使用非自动扩展的 UNDO 数据文件（特别是非常大的 UNDO 数据文件），这会导致 TUNED_UNDORETENTION 的值被计算的过高，导致分配的 UNDO 空间过大。

为避免这个问题，需要确保 UNDO 数据文件是 autoextensible 的（指定了 MAXSIZE ），尽管 UNDO 中有足够的空闲空间。

注：强烈建议在 UNDO 表空间里不要使用 autoextensible 和 non-autoextensible UNDO 数据文件共存的做法，这会导致 TUNED_UNDORETENTION 算错。

3、检查 TUNED_UNDORETENTION

TUNED_UNDORETENTION 低于 MAXQUERYLEN 的值：

这表明 UNDO 表空间有空间上的压力，所以 UNDO 记录无法保留足够时间。增大 UNDO 表空间能帮助解决这种情况。

TUNED_UNDORETENTION 与 MAXQUERYLEN 值相比非常高：

这个通常发生在使用非自动扩展数据文件的 UNDO 表空间中。内部算法尽可能长地保留 UNDO 记录，于是 TUNED_RETENTION 的值很高。Workaround 是把所有的 UNDO 数据文件设为自动扩展模式。（您可以给数据文件指定一个 MAXSIZE）。

持久运行的查询可能导致 TUNED_UNDORETENTION 变成非常高的值。

您需要调优这些查询来避免 UNDO 数据在 UNDO 表空间中保留更多时间。可以使用如下查询来识别这种长查询：

4、ACTIVE/UNEXPIRED 区间的利用率高

select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name;

过量的 ACTIVE/UNEXPIRED 区间分配可能由如下原因导致：

1）、UNDO_RETENTION 或 TUNED_UNDORETENTION 的值很大，如前所述。

2）、在某个时间点有大量 UNDO 数据生成，可以通过如下查询识别：

3）、大的死事务回滚

4）、使用闪回数据归档

5、UNDO_RETENTION :

我们建议将 UNDO_RETENTION 至少设置为 MAXQUERYLEN 的平均值，并且如果 ORA-1555 错误仍然报出则增大它。

Undo其它问题

undo表空间不足的问题

undo表空间不足的问题，基本可划分两类

• active类型undo段使用过高，导致undo表空间不足
• unexpired类型undo使用过高，导致DML运行缓慢（这种情况下不会导致undo空间无法扩展）

针对这两个情况的处理方法如下（仅供参考）：

1.通过下面语句查询当前实例undo空间的使用情况(active、unexpired类型段的占比）

2.进一步查看active、unexpired段的具体使用情况

3.如果是active段使用过高导致undo段无法扩展，则可以根据以下方法解决：

a.临时添加数据文件到undo表空间，暂缓问题

b.问题暂时缓解以后，需寻找问题的根本原因，分析当前session中undo段的使用情况，通过以下语句查询：

--查看系统中会话使用的回滚段

c.如果发现某个session大量使用undo，分析session连接信息和执行的语句（用户、应用主机，模块等等），最后调整应用

如果是oltp，一般情况下session不应该大量使用undo段（不超过100M），如果有，需要调整应用和优化SQL；
如果是olap，可能用于分析等，某个session会占用大量的undo，这种情况下，需要通过优化部分SQL减少undo的使用；
备注：session执行当前执行的可能不会占用undo段，这种情况下，需要查询session当前事务中历史执行的所有SQL，可以关联查询v$open_cursor,v$active_session_history等视图。

4.如果是unexpired类型的段使用过高，这种情况下，不会出现undo表空间无法扩展，oracle会将unexpired类型的段强制使用来确保DML操作得以继续执行。但是这种情况下oracle需要搜索最早的expired段，同时需要将expired段进行一些特殊处理，会导致整个操作过程速度下降，在大量并发DML操作下，由于裙带效应，会导致整个数据库运行缓慢，活动session数上升。

分析方法：

检查v$undostat中的unxpblkreucnt: Number of unexpired undo blocks reused by transactions，这个数值大于0，说明事务由于需要undo空间而从unexpired undo segment中重用空间的次数

处理方法：

a.临时添加数据文件到undo表空间，暂缓问题

b.问题暂时缓解以后，需寻找问题的根本原因，即什么原因导致unexpired类型段使用过高。可能导致这类问题的原因如下：

undo表空间大小设置不合理
undo_retention参数设置的过期时间过长
undo_retention设置合理，但是由于oracle自动调整的原因，导致expired类型段过高(通过查询v$undostat中的tuned_undoretention)。这种情况下考虑禁用自动调整（_undo_autotune参数）
其它oracle bug导致
注意：关于通过切换实例使用的undo表空间方法来解决这类问题，不在此文讨论范围。

单个session占用大量undo，导致数据库性能急剧下降的问题总结

问题现象

• 数据库表现为“latch: undo global data”或者“wait for a undo record”等其它undo相关的等待事件
• CPU资源急剧上升，可能达到100%
• 业务运行缓慢

问题原因

• session A在事务中对某张表（表T）进行DML操作，导致这张表上的回滚信息（前映像数据）超过一定阀值（一般100M以上，高并发下可能50M就算大量undo占用）。同一时刻（此时SESSION A的事务还未commit），大量并发连接（**session B,C,D......等等）需要访问表T（访问的表数据块包含被session A修改的快），这种情况下由于需要完成读一致性要求，其它并发session 需要读取对应的回滚段构造CR块，此时SQL语句的执行效率会立马下降，数据库表现为“**latch: undo global data”或者“wait for a undo record”等其它undo相关的等待事件，CPU资源急剧上升，可能达到100%，业务基本无法响应。

引申现象

1. 数据库中出现大量latch: undo global data”或者“wait for a undo record”相关的等待，但在当前实例中查询每个session的undo使用情况时，未发现有某个session占用大量undo。这种情况可能是由于其它实例的某个session使用大量undo导致，因此，需要所有实例上进行查询。
2. 数据库中出现大量latch: undo global data”或者“wait for a undo record”相关的等待，但是在数据库的所有实例上查询，未发现有某个session占用大量undo，这种情况可能是由于占用大量undo的session事务已经commit，但是SQL语句效率下降的时间点是发生在事务未commit的时间段，主机cpu资源100%，由于排队效应，数据库整体运行效率低效，即使导致产生问题的事务提交完毕后，整个数据库也无法马上恢复正常，需要等待一段时间才能恢复正常（等待的时间未知，某些重要的系统，可能无法接受长时间的等待）

解决方法（仅供参考）

1. 查询数据库所有实例中每个session的undo使用情况（也可以首先整体查询一下undo表空间的整体使用情况），具体SQL语句详见我上一篇博客“UNDO相关问题总结（一）”
2. 如果查询结果中未发现有session正在占用大量undo，如上“引申现象中的2”部分，说明造成问题的session已经commit，可以等待一段时间，数据库会慢慢恢复正常。如果无法接受等待，可以考虑kill掉undo相关等待的连接，释放CPU资源。
3. 如果查询结果中发现有session使用了大量undo（具体多少得看业务并发量，一般100M以上），首先得判断session当前正在进行的事务完成进度。如果即将完成，可以考虑等待事务完成提交后，数据库慢慢恢复正常，见步骤2。如果事务完成进度小于60%，可以考虑kill掉该session。判断事务完成进度的可以根据步骤1查询结构中的machine,program,module等信息定位具体的连接信息，然后询问开发人员、语句执行人员（如果该语句为人为执行）、查询v$session_longops也能有帮助。
4. kill掉使用大量undo的连接后，由于回滚kill掉的事务需要时间（特别是问题发生后，CPU资源100%，数据库的任何操作都会特别缓慢，平时5分钟能完成的回滚，可能在问题时间段1个小时都无法完成），数据库性能恶劣的问题会持续很长一段时间，业务也极有可能根本不能接受这么长时间的无法响应。因此，后续解决问题的关键转化为怎么让事务回滚的更快。
5. 评估回滚事务的完成进度，如下语句：

过一段时间间隔后，再次使用上面的语句查询事务的回滚进度，可以预估完成回滚大概时间。

也可以使用如下语句查询回滚进度：

加快事务回滚的解决方法，可划分为三类（仅供参考）

数据库可以重启，可以考虑的解决办法

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。