Users in your PROD database complain about the slow response of the transactions. While investigating the reason, you find that the transactions are waiting for the undo segments to be available, and undo retention has been set to zero.
What would you do to overcome this problem?
A. increase the undo retention
B. create more undo segments
C. create another undo tablespace
D. increase the size of the undo tablespace
Answer: D
从Oracle10g开始，如果设置UNDO_RETENTION为0，那么Oracle会自动调整UNDO_RETENTION的值以满足运行最长查询的需要。
undo自动调优在固定大小undo表空间中表现更好，如果undo表空间设置过小，会导致DML语句失败或者查询时间过长。
聊聊UNDO_RETENTION作用（上）
Undo_Retention是Oracle提供出的用于控制Undo过期数据保留的“调节Tune”参数。注意：这里不是控制参数。Oracle中的控制参数起到强制作用，比如目录空间位置，归档文件存储等。但是另一部分参数起到的是目标调节的作用，比如检查点间隔控制、SGA_TARGET。
事实上，Undo_retention是一个“目标期望值”。用户设置出这个值之后，Oracle内部会尽量保证将Undo数据保留超过undo_retention设置的时间。在这个过程中，Oracle会涉及到比如尝试拓展Undo表空间数据文件、Undo Segment管理等内容。但是，如果“现实比较残酷”，比如说Undo使用紧张、没有额外的方法，那么这个时间段也是不能保证的。
undo retention设置成0说明自动调节undo过期数据保留时间为建议的，
Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. With automatic undo management, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database. An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA).
The database can also run in manual undo management mode. In this mode, undo space is managed through rollback segments, and no undo tablespace is used.
-
The
UNDO_RETENTIONparameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See "Automatic Tuning of Undo Retention" for more information. -
For an undo tablespace with the
AUTOEXTENDoption enabled, the database attempts to honor the minimum retention period specified byUNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If theMAXSIZEclause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. TheUNDOTBS1tablespace that is automatically created by DBCA is auto-extending.
Automatic Tuning of Undo Retention
Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.
-
If the undo tablespace is configured with the
AUTOEXTENDoption, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting insnapshottooolderrors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate Oracle Flashback features, you can either set theUNDO_RETENTIONparameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size. -
If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.
If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:
-
DML could fail because there is not enough space to accommodate undo for new transactions.
-
Long-running queries could fail with a
snapshottooolderror, which means that there was insufficient undo data for read consistency.
-
Automatic tuning of undo retention typically achieves better results with a fixed-size undo tablespace.