Overview

Prior to ALTIBASE HDB 5.5.1, only ALLOC of undo tablespace could be checked, but USED could not be checked.
This chapter describes how to check the actual usage tablespaces using V$DISK_UNDO_USAGE added from ALTIBASE HDB version 5.5.1.

The undo tablespace is made up of segments, and segments are made up of smaller units called extents.
When a change transaction occurs in a disk table, it is allocated in units of extents. Whenever an extent is newly allocated, the size of the undo tablespace ALLOC increases.
When the transaction ends, the extent becomes reusable.
However, if any of the extents within a segment are in use, even if there are reusable extents, all of the extents within that segment become unusable. (UNSTEALABLE EXTENT)
Either the extent is used because a change transaction is executing, or the UNSTEALABLE extent is counted as USED for the undo tablespace.

Version

This monitoring query is available in the version below.

ALLOC and USED may increase while a change transaction is being performed on the disk table.
If the transaction is committed, the USED that is increased during the execution of the transaction is reduced.
If the transaction is rolled back, the ALLOC and USED that have increased during the execution of the transaction will be reduced.

Undo tablespace usage query

Example of output

Column Name	Description
TBS_NAME	Tablespace Name
MAX(M)	Maximum Size of Undo Tablespace
TOTAL(M)	Total Allocated Size for Undo Tablespace
ALLOC(M)	Total Used Pages (excluding free pages) among Allocated Pages
USED(M)	Size of Extents Used or Unusable Due to Active Transactions
USAGE(%)	Usage (USED vs. MAX)
STATE	Tablespace Status
AUTOEXTEND	Auto Extend Enabled up to Maximum Size

Column name	Data type	Description
TX_EXT_CNT	BIGINT	Number of extents for a segment (Transaction Status Segment, TSS) that stores transaction status information. It is allocated when an update transaction occurs in the disk table.
USED_EXT_CNT	BIGINT	Number of extents in the undo segment being used in the transaction. When an update transaction occurs in the disk table, it is allocated as needed.
UNSTEALABLE_EXT_CNT	BIGINT	Number of extents that cannot be taken from other undo segments. The extent that belongs to the online undo segment and the extent that has header information. Contains the extent the transaction is accessing. Even if the undo segment has reusable extents, it cannot be taken from other undo segments.
REUSABLE_EXT_CNT	BIGINT	Number of reusable extents USED_EXT_CNT used during transaction progress increases as USED_EXT_CNT decreases when the transaction is committed.
TOTAL_EXT_CNT	BIGINT	The total number of extents allocated from the undo tablespace. When a transaction is rolled back, the extents used in the transaction are returned to the undo tablespace. When a transaction is committed, the extents used in the transaction are held in the corresponding undo segment.

Bug Description

Improved an issue where undo areas that cannot be reused were incorrectly counted as available space. (Extents corresponding to the UNSTEALABLE_EXT_CNT column were mistakenly calculated as usable extents.) (BUG-39985)

Applied Versions