Oracle Undo: Check Extent Status in DBA_UNDO_EXTENTS

calendar May 11, 2024 / · 8 min read · oracle undo dba_undo_extents dba_rollback_segs dba_tablespaces v$undostat  ·
Share on: linkedin copy

Oracle Undo: Check Extent Status in DBA_UNDO_EXTENTS

Purpose

A report query runs for forty minutes on a busy system and ends with ORA-01555: snapshot too old. The query is correct. The undo it needed to build a consistent picture of the data was overwritten before the query finished. Somewhere between the size of the undo tablespace, the retention setting and the load on the system, the undo ran short.

DBA_UNDO_EXTENTS is the first place to look. It describes the extents of every undo segment in every undo tablespace, and each extent carries a status: ACTIVE, UNEXPIRED or EXPIRED. Counting the extents by status shows how much of the undo tablespace is held by running transactions, how much is held back for read consistency and flashback, and how much is free to reuse.

The query below does that count. A second query turns the count into megabytes, and a third reads the last day of history from V$UNDOSTAT.

Code

1select tablespace_name
2, status
3, count(*) as how_many
4from dba_undo_extents
5group by tablespace_name
6, status
7order by tablespace_name
8, status
9/

The same grouping, in megabytes instead of extents:

 1col tablespace_name format a20
 2select tablespace_name
 3, status
 4, count(*) extents
 5, round(sum(bytes) / 1024 / 1024, 1) mb
 6from dba_undo_extents
 7group by tablespace_name
 8, status
 9order by tablespace_name
10, status
11/

The history of the last day, read from V$UNDOSTAT:

 1select to_char(begin_time, 'DD-MON HH24:MI') period
 2, undoblks
 3, txncount
 4, maxquerylen
 5, tuned_undoretention
 6, ssolderrcnt
 7, nospaceerrcnt
 8from v$undostat
 9where begin_time > sysdate - 1
10order by v$undostat.begin_time desc
11/

The retention guarantee setting of each undo tablespace:

1select tablespace_name
2, retention
3from dba_tablespaces
4where contents = 'UNDO'
5/

Code Breakdown

The first query

  • dba_undo_extents returns one row for each extent of each undo segment. The view covers all undo tablespaces in the database.
  • tablespace_name is the undo tablespace that holds the extent. A database can have more than one undo tablespace.
  • status is the state of the undo in the extent. It has three values: ACTIVE, EXPIRED and UNEXPIRED.
  • count(*) as how_many counts the extents in each group. The GROUP BY clause makes one row for every pair of tablespace and status.
  • The ORDER BY clause is an addition. Without it, the row order from a GROUP BY is not defined.

The second query

  • bytes is the size of one extent. Summing it by status turns the count into space, which is the number you compare with the tablespace size. Extent counts alone mislead when extents have different sizes.
  • round(... / 1024 / 1024, 1) converts the sum to megabytes with one decimal.

The third query

  • V$UNDOSTAT has one row for every 10-minute interval over the last four days. Older history is in DBA_HIST_UNDOSTAT. The WHERE clause keeps the last 24 hours, which is 144 rows.
  • undoblks is the number of undo blocks used, and txncount is the number of transactions in the interval.
  • maxquerylen is the length in seconds of the longest query in the interval. Compare it with tuned_undoretention. A query that runs longer than the retention can fail with ORA-01555.
  • tuned_undoretention is the time in seconds that undo is kept from the moment of commit before it can be recycled.
  • ssolderrcnt counts how often ORA-01555 happened in the interval, and nospaceerrcnt counts how often space was requested in the undo tablespace with none free.

The fourth query

  • dba_tablespaces.retention shows GUARANTEE, NOGUARANTEE or NOT APPLY. NOT APPLY is the value for tablespaces that are not undo tablespaces, so the contents = 'UNDO' filter keeps only the ones that matter here.

Key Points

  • Status has a defined meaning. Undo that a transaction in progress still needs is ACTIVE. Committed undo that is younger than the undo retention period is UNEXPIRED, and the database keeps it for consistent read and Oracle Flashback operations. Committed undo that is older than the retention period is EXPIRED, and its space can be overwritten by new transactions.
  • An unexpired extent is not free space. The database can still overwrite unexpired undo when the tablespace runs short of space, and that is how a long query ends with ORA-01555. Do not count UNEXPIRED as available when you plan capacity.
  • The retention period is a minimum that the database tunes. UNDO_RETENTION is the low threshold in seconds, and the default is 900. With automatic undo management, the database tunes the actual period from the size of the undo tablespace and the system activity. The current value is TUNED_UNDORETENTION in V$UNDOSTAT.
  • The behavior of a fixed-size tablespace depends on the release. Starting with Oracle Database 19c Release Update 19.7, both autoextend and fixed-size undo tablespaces keep undo for at least UNDO_RETENTION seconds. In earlier 19c releases, a fixed-size undo tablespace ignored UNDO_RETENTION unless retention guarantee was on for the tablespace.
  • Autoextend with a limit can still lose undo. An auto-extending undo tablespace grows instead of overwriting unexpired undo. If it has a MAXSIZE and reaches it, the database may start to overwrite unexpired undo.
  • RAC instances see different pictures. The status in DBA_UNDO_EXTENTS is correct for the undo tablespace that is active on the instance where you run the query. For a tablespace active on another instance, in-memory information can differ and the report can be off. Query each undo tablespace from the instance that uses it.
  • Two columns are deprecated. COMMIT_JTIME and COMMIT_WTIME are kept for compatibility only. Do not build reports on them.

Insights and Best Practices

Read the three statuses together

The three statuses split one tablespace. ACTIVE space cannot be reused, because live transactions need it for rollback. UNEXPIRED space is held for readers and flashback. EXPIRED space is what new work can take.

A healthy system at a quiet hour has little ACTIVE space and plenty of EXPIRED space, with UNEXPIRED in proportion to the retention you set. Trouble shows up as EXPIRED space near zero while UNEXPIRED and ACTIVE fill the tablespace. At that point, new transactions have to take space from UNEXPIRED extents, and queries that depend on them can fail.

Look at the shape over time, not one snapshot

One run of the query tells you the state at one second. Run it at the same hours on several days, or keep the output of the V$UNDOSTAT query. A tablespace that is mostly UNEXPIRED right after a heavy batch is expected. The same split on a quiet night needs an explanation.

Match retention to the longest query

If maxquerylen is often larger than tuned_undoretention, long queries are at risk. Enlarge the undo tablespace or shorten the queries. Oracle recommends leaving UNDO_RETENTION at its default; the documented reasons to raise it are flashback features such as Flashback Query and Active Data Guard, which need undo kept longer than the longest-running query, and a higher value can also reduce ORA-01555 errors. Even then it is a best-effort minimum for the autotuning, not a guarantee.

Use retention guarantee with care

Retention guarantee makes the database keep unexpired undo even if new transactions then fail for lack of space. It is off by default. Turn it on with RETENTION GUARANTEE in ALTER TABLESPACE, and off again with RETENTION NOGUARANTEE. A guarantee protects a flashback window, and it moves the failure from the reader to the writer. Enable it only if you have sized the tablespace for the load.

Join to segment details when needed

dba_undo_extents and dba_rollback_segs both have a segment_name column, so a join gives the segment status next to the extent status. DBA_ROLLBACK_SEGS describes rollback segments and adds the segment status and the file and block of the segment header.

When to Run This Check

  • After an ORA-01555 report, to see how much undo is UNEXPIRED and how much is ACTIVE
  • After an error that reports no free space in the undo tablespace, to see whether active transactions hold it
  • Before a batch window, to confirm the tablespace has EXPIRED space to take the load
  • When a large transaction runs long, to see the ACTIVE space grow
  • After resizing the undo tablespace or changing UNDO_RETENTION, to confirm the effect
  • During capacity planning, once per month and at the busiest hours

Troubleshooting Common Issues

If the ACTIVE count stays high long after the batch ended, look for a session that holds an open transaction. Active extents stay until the transaction commits or rolls back. Find the session first, then decide whether to wait or to end it.

If nospaceerrcnt is above zero in V$UNDOSTAT, transactions asked for undo space and found none free. Add datafile space to the undo tablespace or make sure it can autoextend, then check whether a runaway transaction is the cause.

If ssolderrcnt is above zero while the EXPIRED count is large, the failing query may have needed undo older than the retention period. Compare maxquerylen with tuned_undoretention for the same interval.

In a RAC database each instance uses its own undo tablespace, and V$UNDOSTAT shows only the instance you are connected to. Query GV$UNDOSTAT to see every instance; DBA_UNDO_EXTENTS already covers all undo tablespaces.

If the query returns no rows, the instance may use manual undo management, where undo space is allocated as rollback segments instead of in an undo tablespace. UNDO_MANAGEMENT shows the mode, and AUTO is the default.

References

Posts in this series