Improvement of Display Contents for UNDO Tablespace Usage

The status of the extents in the UNDO tablespace is listed as follows on DBA_UNDO_EXTENTS (oracle.com) https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_5078.htm:

• ACTIVE

• EXPIRED

• UNEXPIRED

However, according to information from Oracle, it is correct to obtain the values excluding EXPIRED and using only ACTIVE + UNEXPIRED.

The usage rate of the UNDO tablespace is calculated as follows: UNDO tablespace usage rate = (Total size of extents [A] ACTIVE + UNEXPIRED) /((Total size of extents [A] ACTIVE + UNEXPIRED) + (Total size of extents [B] EXPIRED) + (Total size of unallocated space [C]) + (Total size of expandable data file [D]))

How to calculate the total size of extents [A] ACTIVE + UNEXPIRED: Extents with the status ACTIVE are areas currently in use by some transactions. Similarly, extents with the status UNEXPIRED are not currently in use but have not yet surpassed the time determined by UNDO_RETENTION or TUNED_UNDORETENTION after a transaction commits, and the retention has not expired.

Therefore, from the standpoint of preventing ORA-1555 errors, these are considered to be areas in use.

This size can be obtained with the following SQL statement:

SQL> select status, sum(bytes) from dba_undo_extents where tablespace_name = '<UNDO Tablespace Name>' and (status = 'ACTIVE' or status = 'UNEXPIRED') group by status;

Currently, we've been informed that Instana retrieves data in this manner, and it does not obtain the UNDO tablespace usage rate as Oracle defines it to be correct.


In case number 【2405056557】 "Regarding the calculation method for UNDO tablespace" from Instana support, we have received the following response.

--
Instana monitors the usage status of tablespaces, including UNDO tablespaces. For UNDO tablespaces, the value of USED_SPACE includes space consumed by both EXPIRED and UNEXPIRED undo segments.

Instana collects data from the table DBA_TABLESPACE_USAGE_METRICS. Below is the query we use:

TABLESPACE_USAGE_STMT SELECT dba_tablespace_usage_metrics.tablespace_name, dba_tablespace_usage_metrics.used_space, dba_tablespace_usage_metrics.used_percent FROM dba_tablespace_usage_metrics;
--

Please improve the system so that the UNDO tablespace usage rate as defined by Oracle can be checked on Instana.