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

Note

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

(The minimum number of undo segments for each undo tablespace
* _USGMT_UNIFORM_EXTSIZE * 2 * Block size (8KB)) + Margin

Expected size

(The maximum number of undo segments for each undo tablespace
* _USGMT_UNIFORM_EXTSIZE * 2 * Block size (8KB)) + Margin

Actual size

(Undo blocks per second * UNDO_RETENTION * Block size (8KB)) + Margin

The following describes each item in the formulas.

Item	Description
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)