- Introduction
- How Is Undo Space Managed by Oracle9i?
- What Initialization Parameters Control Undo Space?
- Can You Use Multiple Undo Tablespaces?
- How Can You Monitor Undo Space Utilization in the SMU Mode?
- How Can Contention for Rollback Segment Be Reduced?
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
How Can Contention for Rollback Segment Be Reduced?
Answer: Contention for rollback segments is an issue for Oracle DBAs in the RMU mode. The following techniques can be useful to reduce contention for rollback segments:
Increase the number of rollback segments.
Set the storage parameter NEXT to be the same as INITIAL for rollback segments.
Set the storage parameter MIN_EXTENTS for rollback segments to be at least 20.
Set the storage parameter OPTIMAL for rollback segments to be equal to INITIAL x MIN_EXTENTS.
Ensure plenty of free space in rollback tablespace.
In the SMU mode, the undo segment contention is handled by Oracle. However, this doesn't really free the DBA from the task of planning undo space properly because of the following facts:
There can be only one active undo tablespace at a time.
All undo segments in a given undo tablespace are of equal size.
The size of undo segments may not be suitable for all transactions.
All undo segments in a given undo tablespace are taken online/offline at the same time when that undo tablespace is taken online/offline, respectively.
Undo segments are managed by Oracle but they still take up space.
Undo segment contention is managed by Oracle but they can still have issues such as extends, shrinks, and wraps. All of these issues can lead to performance problems related to space management if undo segments are not sized properly.
The creation of undo segments—number and size—is influenced by the SESSIONS parameter setting.
To improve performance and minimize undo segment-related problems in an SMU mode database, the Oracle DBA can take the following action:
Determine the undo space utilization, transaction mix, and load times of the database instance and set the SESSIONS parameter appropriately.
Create multiple undo tablespaces of different sizes to cater to various transaction mixes.
Dynamically change the UNDO_TABLESPACE parameter as needed by currently running transactions to activate the most appropriate undo tablespace.
Monitor the V$UNDOSTAT view to determine how Oracle is managing undo space using the active undo tablespace; react to any performance degradation by changing the active undo tablespace or creating new undo tablespaces.