Calculating the Undo Tablespace Size
This chapter describes how to calculate the size of the undo tablespace.
If data is updated frequently in a system, a large volume of I/O happens in the undo tablespace. In such systems, data needs to be distributed by creating the undo tablespace with multiple data files.
The undo tablespace is automatically managed, providing the following advantages.
Helps design and manage efficiently with rollback capability and segments
Prevents undo data from being overwritten
Provides scalability
Supports Flashback queries
The undo tablespace cannot be manually managed in Tibero.
Considerations
Consider the following when calculating the size of an undo tablespace.
Data file size, UNDO_RETENTION, and undo segments
Individual spaces in an undo segment are automatically managed, therefore you only need to set the minimum/maximum number of undo segments and the maximum size of a data file.
Set the minimum number of undo segments (USGMT_ONLINE_MIN) to 10 (default value). However, if there are too many concurrent transactions, overhead occurs to create additional undo segments. To decrease the overhead, set USGMT_ONLINE_MIN to the number of concurrent transactions.
If USGMT_ONLINE_MIN is greater than the number of existing undo segments, additional undo segments are created at boot time. USGMT_ONLINE_MIN can be set by node in TAC. For example, USGMT_ONLINE_MIN can be set to 30 and 40 for NODE1 and NODE2 respectively.
Set the maximum number of undo segments (USGMT_ONLINE_MAX).
Tibero automatically creates additional undo segments to prevent multiple transactions from sharing a single undo segment. However, the number of undo segments cannot exceed USGMT_ONLINE_MAX.
In TAC, a total number of Undo segments in all nodes cannot exceed USGMT_ONLINE_MAX. For example, if USGMT_ONLINE_MAX is set to 300 and the numbers of undo segments are 100 and 200 in NODE1 and NODE2 respectively, additional undo segments cannot be created in both nodes. Therefore, it is recommended to set USGMT_ONLINE_MAX to the number of maximum concurrent transactions.
Calculating the Size
The minimum size, expected size (based on the expected concurrent transactions count), and actual size of an undo tablespace can be calculated as follows. The expected size is used when a database is installed for the first time, and then the size can be checked based on the number of Undo blocks per second under the heaviest TPR load.
The following are the formulas for calculating the size of an undo tablespace.
Minimum size
Expected size
Actual size
The following describes each item in the formulas.
The minimum number of undo segments for each undo
tablespace
USGMT_ONLINE_MIN value for both single instance and TAC.
The maximum number of undo segments for each undo tablespace
Expected maximum number of concurrent transactions.
Single instance: USGMT_ONLINE_MAX value
TAC: USGMT_ONLINE_MAX / the number of nodes
_USGMT_UNIFORM_EXTSIZE
Size of an undo extent. An undo segment includes at least two undo extents.
Undo blocks per second
Number of undo blocks divided by a TPR interval (seconds). Use the value of undo blocks shown in Undo Statistic of TPR at the time under the heaviest load.
UNDO_RETENTION
Setting value in a tip file. (Default value: 900 seconds)
Margin
Preparative for irregularly long transactions (including INDEX rebuild). (Unit: KB)
Last updated