oracle 体系结构（七）

Introduction to Automatic Undo Management自动撤销管理简介

Oracle maintains information to nullifychanges made to the database. Such information consists of records of theactions of transactions, collectively known as undo. Oracle uses the undo to dothe following:

■ Rollback an active transaction

■ Recover a terminated transaction

■ Provide read consistency

■ Recovery from logical corruptions

Oracle保存了用于恢复数据库操作的信息。这些信息由有效的事务处理记录构成，这些记录被共同的称作是undo（撤销）。Oracle使用undo可处理如下情况：

■回滚有效的事务

■恢复终止的事务

■提供一致性读

■恢复语句的逻辑错误

 

Automatic undo management isundo-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 thecomplexities of managing rollback segment space and lets you exert control overhow long undo is retained before being overwritten. Oracle strongly recommendsthat you use undo tablespaces to manage undo rather than rollback segments. Thesystem automatically tunes the period for which undo is retained in the undotablespace to satisfy queries that require undo information. If the currentundo tablespace has enough space, then you can set the UNDO_RETENTIONparameter to a low threshold value so that the system retains the undo for atleast the time specified in the parameter.

自动撤销管理消除了管理回滚段的复杂性，使用户只需专注于控制撤销信息（undo）的保存期限。Oracle强烈建议用户使用undo表空间的方式管理undo而不要使用回滚段的方式管理。系统能够自动调整撤销信息在撤销表空间内的保存时间，供需要使用撤销信息的查询使用。如果当下的undo表空间有充足的空间，那么用户可以设置UNDO_RETENTION参数来设置一个底阀值，这样系统就会保留指定参数值内的所有undo数据。

 

Use the V$UNDOSTAT view to monitor and configure your database system to achieve efficient use ofundo space. V$UNDOSTAT shows various undo and transaction statistics, such asthe amount of undo space consumed in the instance.

用户可以通过V$UNDOSTAT视图来监督和配置自身数据库系统以实现undo空间的高效利用。V$UNDOSTAT视图展现了各种各样undo和事务的统计信息，比如实例中消耗的undo空间数量。

 

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

注：早起的oracle版本是使用回滚段的方式存储undo，这种管理模式被称作是手动撤销管理模式。这种回滚段的空间管理模式是非常复杂的，当下oracle已经放弃这种存储undo的模式。

 

The Oracle Database contains an UndoAdvisor that provides advice on and helps automate the establishment of yourundo environment.

Oracle数据库提供了 Undo Advisor，能为用户提供建议，并自动地建立用户的撤销环境。

 

Undo Mode  （撤销）undo管理模式

Undo mode provides a more flexible way tomigrate from manual undo management to automatic undo management. A databasesystem can run in either manual undo management mode or automatic undomanagement mode. In manual undo management mode, undo space is managed throughrollback segments. In automatic undo management mode, undo space is managed inundo tablespaces. To use automatic undo management mode, the databaseadministrator needs only to create an undo tablespace for each instance and set the UNDO_MANAGEMENT initialization parameter to AUTO. Youare strongly encouraged to run in automatic undo management mode.

Undo管理模式提供了一个非常灵活的途径用来实现手动撤销管理模式到自动撤销管理模式的迁移。数据库系统既可以在手工撤销管理模式下运行，也可以在自动撤销管理模式下运行。在手动撤销管理模式下，undo空间通过回滚段的方式管理。在自动撤销管理模式下，undo空间通过undo表空间管理。使用自动撤销管理模式，DBA只需要为每个实例创建undo表空间和为自动管理设置UNDO_MANAGEMENT初始参数即可。再次声明，oracle强烈建议使用自动撤销管理模式。

 

Undo Quota （撤销）undo配额

In automatic undo management mode, thesystem controls exclusively the assignment of transactions to undo segments,and controls space allocation for undo segments. An ill-behaved transaction canpotentially consume much of the undo space, thus paralyzing the entire system.The Resource Manager directive UNDO_POOL is a more explicit way tocontrol large transactions. This lets database administrators group users intoconsumer groups, with each group assigned a maximum undo space limit. When thetotal undo space consumed by a group exceeds the limit, its users cannot makefurther updates until undo space is freed up by other member transactionsending.

在自动撤销管理模式下，数据库系统仅仅控制为事务分配undo段，并且控制此段的空间分配工作。一个不当的事务可能会消耗很多的undo空间，甚至造成整个系统的瘫痪。资源管理器指令UNDO_POOL可以更加直接地控制大型事务。DBA可以将用户分成不同的用户组，通过为每个组分配最大的undo空间限制。当一个组使用的撤销空间之和超过了设定的限制值时，这个组的用户就不能执行新的更新（update）操作，直到本组内其他用户的事务结束并释放撤销空间。

 

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

UNDO_POOL的默认值是unlimited，也就是说只要undo表空间有空间，用户就可以无限制的使用这个空间作为undo空间。DBA可以通过使用UNDO_POOL指令限制某个特别的用户。

 

Automatic Undo Retention 自动撤销信息滞留期管理

OracleDatabase 10g automatically tunes a parameter called the undo retention period.The undo retention period indicates the amount of time that must pass beforeold undo information—that is, undo information for committed transactions—canbe overwritten. The database collects usage statistics and tunes the undoretention period based on these statistics and on undo tablespace size.Provided that automatic undo management is enabled, the database automaticallytunes the undo retention period as follows:

■ For an AUTOEXTEND undotablespace, the database tunes the undo retention period to be slightly longerthan the longest-running query, if space allows. In addition, when there isadequate free space, the tuned retention period does not go below the value ofthe UNDO_RETENTION initialization parameter.

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

■ Automatic tuning of undo retention is not supported for LOBs. Thetuned retention value for LOB columns is set to the value of the UNDO_RETENTIONparameter.

Oracle10g数据库会自动调节一个叫做undo保留周期的参数。Undo保留周期是指undo表空间中留的undo信息（指已经提交的事务）在覆盖前至少需要保存的时间。数据库会收集撤销信息的使用情况，并根据统计结果及undo表空间的大小对撤销信息保存周期进行调整。如果数据库采用了自动undo管理模式，那么数据库就会在遇到以下情况时自动的调整undo保留周期：

■对于自动扩展的undo表空间来说，数据库会在空间资源允许的情况下调整undo保留周期为系统中最长的查询时间稍长的时间。此外，当拥有充足的空闲空间的时候，调整后的保留周期不会比系统默认的初始undo_retention参数的值更低。

■对于固定大小的undo表空间，数据库会调整最大可能的undo保留周期。这意味着数据库可以使用接近撤销表空间极限的容量为用户提供最长的撤销信息保存周期。如果没有启用retention guarantee，那么自动undo管理模式下，undo_retention初始参数会被忽略。

■自动调整undo保留周期不支持LOB字段。LOB列的undo保留周期由undo_retention参数决定。

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

一个固定容量的undo表空间与一个自动扩展的undo表空间容量相同时，前者使用的撤销信息保存周期调整方法通常能够提供更长的保存时间。这使回闪（flashback）操作可以追溯的更远，也使运行时间长的查询有更多的undo数据可用。

 

External Views 外部视图

Monitortransaction and undo information with V$TRANSACTION and V$ROLLSTAT. Forautomatic undo management, the information in V$ROLLSTAT reflects the behaviorsof the automatic undo management undo segments.

Oracle通过V$TRANSACTION和V$ROLLSTAT两个视图监控事务和undo信息。对于自动撤销管理来说，V$ROLLSTAT中的信息反映了自动撤销管理中undo段的行为。

 

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

V$UNDOSTAT 可以显示撤销空间运行的历史统计信息。用户可以查询撤销空间使用率，事务并发性，实例中最长的查询的运行时间等统计信息。通过这个视图，用户可以更好地估计在当前工作负荷下系统所需的undo空间容量。