Introduction to Automatic Undo Management【每日一译】--2010-10-25

Oracle maintains information to nullify changes made to the database. Such
information consists of records of the actions of transactions, collectively known as

undo. Oracle uses the undo to do the following:

ORACLE维护信息去使数据库的改变信息无效。这些信息由事务的活动记录组成，

众所周知的UNDO。ORACLE使用UNDO做以下事情：

■ Rollback an active transaction

■ Recover a terminated transaction
■ Provide read consistency

■ Recovery from logical corruptions

#回滚一个活动的事务

#还原一个终止的事务

#提供读的一对待性

#逻辑故障还原

Automatic undo management is undo-tablespace based. You allocate space in the form
of an undo tablespace, instead of allocating many rollback segments in different sizes.
Automatic undo management eliminates the complexities of managing rollback
segment space and lets you exert control over how long undo is retained before being
overwritten. Oracle strongly recommends that you use undo tablespaces to manage
undo rather than rollback segments. The system automatically tunes the period for
which undo is retained in the undo tablespace to satisfy queries that require undo
information. If the current undo tablespace has enough space, then you can set the
UNDO_RETENTION parameter to a low threshold value so that the system retains the

undo for at least the time specified in the parameter.

自动存储管理是基于UNDO表空间。你可以以UNDO表空间的形式分配空间，代替原来

分配许多回滚段以不同的大小。自动UNDO管理消除了管理回滚段空间的复杂性并且

让你尽力的控制UNDO保留时间的长短当被重写之前。ORACLE强烈推荐你用UNDO

表空间去管理替代原来的回滚段管理。系统自动地调整UNDO保留在UNDO表空间的时

期以满足查询需要的UNDO信息。如果当前的UNDO表空间拥有足够的空间，那么你可

以设置UNDO_RETENTION参数到一个低的阀值，这样系统保留的UNDO时间至少为指

定的时间。

Use the V$UNDOSTAT view to monitor and configure your database system to achieve
efficient use of undo space. V$UNDOSTAT shows various undo and transaction

statistics, such as the amount of undo space consumed in the instance.

使用V$UNDOSTAT视图去监控和配置你的数据库系统以达到UNDO空间的有效使用。

V$UNDOSTAT显示多个UNDO和事务信息，比如在实例中UNDO空间消耗的数量。

Note: Earlier releases of Oracle used rollback segments to store
undo, also known as manual undo management mode. Space
management for these rollback segments was complex, and Oracle has
now deprecated that method of storing undo.

备注：早期版本的ORACLE使用回滚段去存储UNDO，也清楚手动UNDO管理模式。

对这些回滚段的空间管理是复杂的，ORACLE已经不支持这种存储UNDO的方法。

The Oracle Database contains an Undo Advisor that provides advice on and helps
automate the establishment of your undo environment.

ORACLE包含一个UNDO顾问，它提供了建议和帮助自动创建UNDO环境。

Undo Mode
Undo mode provides a more flexible way to migrate from manual undo management
to automatic undo management. A database system can run in either manual undo
management mode or automatic undo management mode. In manual undo
management mode, undo space is managed through rollback segments. In automatic
undo management mode, undo space is managed in undo tablespaces. To use
automatic undo management mode, the database administrator needs only to create
an undo tablespace for each instance and set the UNDO_MANAGEMENT initialization
parameter to AUTO. You are strongly encouraged to run in automatic undo
management mode.

UNDO模式

UNDO模式提供了更为灵活的方式去将手动UNDO管理迁移为自动UNDO管理。一个数据

库系统可以运行在手动UNDO管理模式或者是自动的UNDO管理模式。在手动UNDO管理

模式，UNDO空间的管理通过回滚段。在自动UNDO管理模式，UNDO空间是在UNDO表空间

进行管理。要使用UNDO管理模式，数据库管理员仅需在创建UNDO表空间时设置UNDO_MANAGEMENT

的初始化参数为AUTO对每个实例。ORACLE强烈建议用户运行在自动的UNDO管理模式下。

Undo Quota
In automatic undo management mode, the system controls exclusively the assignment
of transactions to undo segments, and controls space allocation for undo segments. An
ill-behaved transaction can potentially consume much of the undo space, thus
paralyzing the entire system. The Resource Manager directive UNDO_POOL is a more
explicit way to control large transactions. This lets database administrators group
users into consumer groups, with each group assigned a maximum undo space limit.
When the total undo space consumed by a group exceeds the limit, its users cannot
make further updates until undo space is freed up by other member transactions
ending.

UNDO分配

在自动UNDO管理模式，系统独自控制事务的任务在UNDO段，并且控制空间分配对于UNDO段。

一个坏习惯的事务可以隐式占有大量的UNDO空间，这样会拖跨整个系统。资源管理向导UNDO_POOL

是一个更直接的方式去控制大事务。它需要数据库管理员将用户进行耗能分组，对于每一个组指定一个

最大的UNDO空间限制。当总的UNDO空间耗用被一个组超出限制时，这个组的用户不能进行进一步

的更新直到UNDO空间在其它成员事务结束后释放才可进行。

The default value of UNDO_POOL is UNLIMITED, where users are allowed to consume
as much undo space as the undo tablespace has. Database administrators can limit a
particular user by using the UNDO_POOL directive.

默认值UNDO_POOL是无限制的，它允许耗所有的UNDO表空间所拥有的容量。DBA可以限制一个

特定的用户使用UNDO_POOL向导。

Automatic Undo Retention
Oracle Database 10g automatically tunes a parameter called the undo retention period.
The undo retention period indicates the amount of time that must pass before old
undo information—that is, undo information for committed transactions—can be
overwritten. The database collects usage statistics and tunes the undo retention period
based on these statistics and on undo tablespace size. Provided that automatic undo
management is enabled, the database automatically tunes the undo retention period as
follows:

自动UNDO周期

ORACLE10G自动调整一个参数叫“UNDO时期”。UNDO时期确保了必须达到的时间数在

老的UNDO信息被覆盖前--也就是，对于提交事务的UNDO信息--可以被重新写入。数据库

收集使用的统计信息并且调整UNDO的时期基于这些统计信息和UNDO表空间大小。自动

的UNDO管理被开启时，数据库自动调整UNDO周期如下几点：

■ For an AUTOEXTEND undo tablespace, the database tunes the undo retention
period to be slightly longer than the longest-running query, if space allows. In
addition, when there is adequate free space, the tuned retention period does not
go below the value of the UNDO_RETENTION initialization parameter.

#对于一个自动扩展的UNDO表空间，数据库调整UNDO周期到一个明显的长大最长运行的

查询，如果空间允许。另外，当有合适的空闲空间时，那个调整周期不会低于UNDO_RETENTION

初始化参数的值。

■ For a fixed size undo tablespace, the database tunes for the maximum possible
undo retention. This means always providing the longest possible retention period
while avoiding out-of-space conditions and near out-of-space conditions in the
undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless
retention guarantee is enabled.

#对于一个固定大小的UNDO表空间，数据库调整最大可能的UNDO周期。这意味着通

常指供最长的可能周期以避免超出和接近超出空间大小的条件在UNDO表空间中。

UNDO_RETENTION初始化参数将被忽略的当周期确保（RETENTION GUARANTEE被

开启时候）

■ Automatic tuning of undo retention is not supported for LOBs. The tuned
retention value for LOB columns is set to the value of the UNDO_RETENTION
parameter.

自动的UNDO周期调整不支持LOB对象。对于LOB列的调整周期值被设为UNDO_

RETENTION的参数值。

For fixed size and AUTOEXTEND undo tablespaces of equal size, depending on the
queries that you run, the tuning method used in fixed size tablespaces tends to
provide a longer retention period. This enables flashback operations to flash back
farther in time, and maximizes the amount of undo data available for long-running
queries.

对于固定大小和自动扩展的UNDO表空间，取决于你运行的查询，在固定大小的UNDO

表空间的调整方法趋向于提供一个更长的周期。它开启了闪回操作在时间上可闪回更长

时间，并且对于一个长运行的查询最大化UNDO数据的可用数量。

External Views
Monitor transaction and undo information with V$TRANSACTION and V$ROLLSTAT.
For automatic undo management, the information in V$ROLLSTAT reflects the
behaviors of the automatic undo management undo segments.

内部视图

监控事务和UNDO信息可用视图V$TRANSACTION和V$ROLLSTAT.对于自动的UNDO管理，

在V$ROLLSTAT中的信息反应了自动UNDO管理UNDO段的行为。

The V$UNDOSTAT view displays a histogram of statistical data to show how well the
system is working. You can see statistics such as undo consumption rate, transaction
concurrency, and lengths of queries run in the instance. Using this view, you can better
estimate the amount of undo space required for the current workload.

V$UNDOSTAT视图显示了统计数据的柱状图去显示系统工作的情况。你可以看到统计信息

比如UNDO的消耗率，事务并发，在实例中查询的长度。使用这个视图你可以更好地预估

对于当前工作负载的UNDO空间需求的数量。