Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
How Is Undo Space Managed by Oracle9i?
Is it true that as an Oracle DBA I don't have to worry about managing undo space anymore, because Oracle9i takes care of it for me?
Answer: When a transaction begins, it's assigned to a rollback segment. Rollback segments store the "before" image of data—the data as it existed prior to the beginning of a transaction. Normally, Oracle DBAs have to spend a considerable amount of time and effort managing the undo space and have to make a number of important decision in this regard:
It's important to have a sufficient number of rollback segments in your database. Too few rollback segments can cause new transactions that attempt to write data to the rollback segments to wait temporarily. Too many rollback segments may not necessarily increase the concurrency of transactions but instead waste space.
Rollback segments should be sized properly. If the rollback segments are too small and you have long-running transactions, large transactions may need more space than is already allocated in the rollback segment. The rollback segment may extend into the free space remaining in the tablespace. If the rollback segment needs more space than is available in the tablespace, the transaction will fail.
The extents of rollback segments should be sized properly. Ideally, a transaction should fit entirely in an extent of a rollback segment. If a transaction is larger than the extent, the transaction will "wrap" from one extent to another in the same rollback segment. Excessive wrapping can lead to performance degradation and internal space-management issues.
Prior to Oracle9i, rollback segments had to be managed "manually" by DBAs. Improper choices about the number and size of the rollback space had a significant impact on the performance of the system and the concurrency of transactions. Oracle9i allows undo space to be managed in either of two ways:
Manually. This mode is commonly referred to as rollback-managed undo (RMU). It's established by setting the parameter UNDO_MANAGEMENT to MANUAL. This mode essentially allows rollback segment management to be performed as before (pre-Oracle9i).
Automatically. This mode is commonly referred to as system-managed undo (SMU) or automatically managed undo (AMU). It's established by setting the parameter UNDO_MANAGEMENT to AUTO. In this mode, Oracle databases can manage their own undo space. This self-managing of undo space allows Oracle to take care of issues such as undo space utilization, undo block contention, and consistent read retention. In this mode, Oracle9i allows administrators to allocate their undo space in a single undo tablespace. Undo segments are created automatically in the undo tablespace during its creation.
Oracle9i allows DBAs to configure Oracle9i databases to run in either RMU or SMU mode. However, even if SMU mode is used, DBAs still have to worry about sizing the undo tablespace properly. Further, all the undo segments in a given undo tablespace are of equal size. This can make it very difficult in applications where the transaction mix is diverse; for example, when some transactions are long-running and make a large number of changes to data, while others are very small transactions that are short-lived and don't make many data changes.