Checking Free Space of UNDO Tablespaces

Introduction

This blog is a high-level overview of the fundamental Oracle feature UNDO tablespace and its space management.

Oracle DB uses UNDO tablespace for keeping track of undo change vectors, which are used to provide features such as transaction rollback, read consistency, flashback query, and more. Each undo change vector is an instruction to reverse data modification.

The Structure

UNDO tablespace is similar to DATA tablespaces: physically it consists of one or more data files; logically it's a bunch of segments, each consisting of two extents minimum. Transactions are assigned an UNDO segment and use UNDO blocks from the extents of that segment of that segment only. Each transaction writes undo change vectors it produces in a chain of UNDO blocks spanning 1 or more extents of the same segment, starting from the transaction UNDO segment header slot pointing to the latest generated record. Multiple active transactions producing UNDO change vectors can use UNDO blocks of the same extent. An UNDO block can be used by just one active transaction and can be reused after the transaction ends.

The Management

Since transactions are running and completing non-stop, Oracle ought to reuse limited UNDO space. At the same time, the database needs to keep old UNDO data of past transactions available for a reasonable period for read consistency purposes. The database keeps track of UNDO segment extents in a cyclic structure: all UNDO extents of a segment are logically connected in a ring. Each extent can be either ACTIVE, UNEXPIRED, or EXPIRED, something like this: 

Oracle keeps track of the "Head" and "Tail" of segment extents: the Head points to the current extent used by new transactions assigned to this segment, and the Tail points to the oldest ACTIVE extent (possibly there is a reference to the oldest UNEXPIRED extent as well). All extents between the Head and the Tail are ACTIVE, and none of them can be reused if there's at least one active transaction in the tail extent.

As new transactions request new UNDO blocks, they are assigned from the Head extent. The Head moves on to reuse EXPIRED extents and make those ACTIVE with new transactions. The Tail moves forward, too, when transactions are complete and ACTIVE extents slowly become UNEXPIRED. UNEXPIRED extents become EXPIRED at a later point, depending on the (tuned) undo retention. So, Oracle reuses EXPIRED extents. It can also reuse UNEXPIRED extents under certain conditions and will never reuse ACTIVE extents.

Free Space

As UNDO is similar to DATA tablespace, dictionary views relevant to DATA tablespaces report UNDO tablespace, as well. For example, DBA_FREE_SPACE contains data about UNDO tablespaces as well. But FREE space in this view only shows those UNDO extents, which are currently completely unused by UNDO segments. Same with DBA_TABLESPACE_USAGE_METRICS: it does not make a difference for UNDO tablespaces (it is even mentioned in the docs).

Below is a picture demonstrating the issue with DBA_FREE_SPACE. On the left, you can see UNDO tablespace is not actively used, and as a result, Oracle keeps most of the UNDO extents unused - FREE extents as reported by DBA_FREE_SPACE. As the transaction activity on the database increases, more and more FREE UNDO extents are getting used by the UNDO segments, and the ACTIVE/EXPIRED/UNEXPIRED extents grow. The FREE extents decrease and can reach zero. Since there are EXPIRED extents to reuse, lacking FREE extents is not an issue.

So whenever you see a monitoring alert showing something like "UNDO tablespace is low on space", the first thing you need to do is to make sure that the alert is based on relevant information. There are two reliable sources: GV$UNDOSTAT - this is a historical 10-minute aggregated/sampled overview of the transaction activity. The most important columns here are ACTIVEBLKS, UNEXPIREDBLKS, EXPIREDBLKS, and TUNED_UNDORETENTION. If the ACTIVEBLKS goes up and EXPIREDBLKS goes down consistently, then it means UNDO free space is going down. The up-to-date source of information is DBA_UNDO_EXTENTS which reports UNDO extents details including STATUS. Combining EXPIRED extents with FREE space of the UNDO tablespace in DBA_FREE_SPACE gives the actual FREE space of the UNDO.

Summary

UNDO tablespace is a DATA-like tablespace that is re-used by transactions for storing auxiliary information. Common data dictionary views for DATA tablespaces report correct but irrelevant stats for UNDO tablespaces. It's not that easy to tell exactly how much free space a UNDO tablespace has. The sum of EXPIRED extents gives a good approximation of the available reusable UNDO space. DBA_UNDO_EXTENTS or GV$UNDOSTAT are the proper sources for checking UNDO free space.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?