Monitor Oracle UNDO


Before I go into detail about how to check your UNDO tablespace in Oracle, I’d like to very briefly outline what UNDO is used for in the Oracle database.

Quick Recap…Essential Info

In short, the UNDO tablespace is used to store before images of database blocks which have had some kind of change made to them.

For example, let’s say that I update a row in a table and I change a value of 1 to a value of 0. Before I commit the change the value of 0 is stored in the UNDO tablespace.

Why do we store this undo information? For many reasons:

  • To rollback transactions
  • Database recovery if the instance crashes before the changes are committed
  • Transaction recovery in the session exits abnormally
  • To provide other sessions with an image of the row as it was before the update for read consistency
  • For use by Oracle Flashback Query

As you can see, there are a lot of uses for UNDO and it is one of the most fundamental parts of the database, without which there would be a lot of inconsistent data in the database.

OK, so now that we have covered the basics about UNDO in Oracle let’s take a look at how you can monitor how much of it is being used:

How to Monitor the Oracle UNDO Tablespace

To put it simply, there are two main ways to monitor UNDO usage within the Oracle database. Which one you decide to use will depend upon your requirements. The two ways are:

  1. View currently active UNDO being used within the undo tablespace
  2. Using current undo usage figures, estimate percentage of UNDO required based on UNDO_RETENTION

Method 1 – Active UNDO in undo tablespace

Requires a query to look at the actively used blocks within the database. It will show you at any point in time how much UNDO is currently being used by actively running transactions.

SELECT (used_ublk * (SELECT block_size
                     FROM dba_tablespaces
                     WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction;

This query calculates your block size, providing that you have the UNDO tablespace set up correctly, and then outputs the amount of space being used by active transactions in the database in MBs. This query will display one line per session. If you would like to know the total amount used, you can just put a SUM at the beginning of the query.

Method 2 – Using the UNDO_RETENTION Parameter

Within most databases using automatic UNDO management, the UNDO_RETENTION parameter will be set. The UNDO_RETENTION parameter specifies, in seconds, how long non-active UNDO, that is UNDO that is not currently being used by a session but was used recently, should be kept for. This type of UNDO is referred to as unexpired UNDO.

This setting provides the DBA with some control over what UNDO space is overwritten first. The UNDO space which is expired – UNDO blocks which are older than the UNDO_RETENTION period – will be used first, followed by unexpired UNDO if an active transaction requires the space and there is no expired UNDO available.

The database will try to honour this setting wherever possible but this is not always possible. For example, a long running update from an active transaction will take precedence over UNDO that is unexpired.

What is the UNDO_RETENTION Parameter for?

The idea behind the UNDO_RETENTION parameter is for the DBA to provide a value for this so that the longest running SELECT statements can still be satisfied after X number of seconds of transactions happening in the database.

This brings us to the 2nd method of monitoring UNDO usage, which requires a slightly more complex query with a couple more variables.

SELECT ROUND(((ur * (ups * dbs)) + (dbs * 24))/ut*100,0) AS "%"
      FROM v$parameter
      WHERE NAME = 'undo_retention'),
  (SELECT (SUM (undoblks) / SUM (((end_time - begin_time) * 25200))
  ) AS ups
FROM v$undostat),
  (SELECT block_size AS dbs
   FROM dba_tablespaces
   WHERE tablespace_name = (SELECT VALUE
                            FROM v$parameter
                            WHERE NAME = 'undo_tablespace')),
  (SELECT sum(bytes) as ut
   FROM dba_data_files
   WHERE tablespace_name = (SELECT VALUE
                            FROM v$parameter
                            WHERE NAME = 'undo_tablespace'));

The Query Explained…

There is a view in the database called V$UNDOSTAT, and this view shows the historical usage of the UNDO tablespace within the database. As with all the other V$ views, it is cumulative since instance startup. In the case of the V$UNDOSTAT view, it shows data for a maximum of 7 days prior to the point in time.

The query above looks at the total amount of UNDO blocks used in that time, divides it by the timespan that is recorded in V$UNDOSTAT and then multiplies it by the UNDO_RETENTION period to give a value for the undo per second (ups).

Using this, some other variables and a few calculations it is possible to work out, based on the historical UNDO usage since instance startup, what percentage of the UNDO tablespace is used.

This query should be used if you have specified an UNDO_RETENTION parameter which should be adhered to all the time in order to allow long running queries to complete.

What Next?

You can also check out my other article about monitoring TEMP space in the Oracle database, as quite often you will find both the UNDO and TEMP resources being stressed at the same time as users run large inserts and updates which sort a lot of data first.

Just quick before you go…

Must Read Oracle DBA BooksHopefully that helped you out and resolved your Oracle error. While you’re here, what steps are you taking to improve your Oracle knowledge?

Check out my latest post about which Oracle books are a must read for any serious Oracle DBA. Learn from the best…

Like it, share it...

Category: Database Monitoring

Related Posts

Comments (6)

Trackback URL | Comments RSS Feed

  1. goutham says:

    hi Rob.
    i just keep on getting alerts from OEM saying undotbs used 99% all the time.and also TEMP usage.somw time application developers ask us to increase undotbs for about 10GB while they are doing some refresh on the db.they also ask us to increase temp Tablespace to around 20GB.

    what is the best way to monitor and increase the Temp space.
    Is is recommended to increase undo and temp as much as they mention for ex: 20-30GB.

    what is the best way to approach this type of issues.

  2. Hi,
    What alerts do you have configured? The TEMP and UNDO tablespaces can show as 100% used, but not actually have any active UNDO or TEMP space being used. That’s the point of the queries on this article for UNDO and the other one (http://www.ora00600.com/wordpress/scripts/databasemonitoring/monitoring-temp-space-usage-in-oracle/) for TEMP space usage.

    You can configure in the OEM user defined queries and add them, then get alerts only when the ACTIVE TEMP and UNDO is above the threshold.

    Increasing the TEMP might be necessary, it might not…It depends. The first thing to check is to make sure the SQL is efficient and that they are not using too much unneeded TEMP. If the queries are OK, then they will need that extra TEMP space.

    So, the approach is make everything as efficient as possible, sorting the least amount of data as possible and see how much space it needs.

    Hope that helps,

  3. Fabz says:

    Hi Robert,

    great articel which helps me a lot to understand and monitor the UNDO tablespace. Our old check was using the dba_free_space table, which didn’t give any helpful information about UNDO tablespace.

    But I have a question on your second query:
    If you calculate the undos per second you use 25200 as multiplier. I don’t understand that, sorry.
    How do you get this value?

    The difference of the dates is in days, right?
    So if I want to get seconds I have to multiply it
    with 24 for hours, 60 for minutes and 60 again for seconds. (24*60*60 = 86400)


Leave a Reply

Your email address will not be published. Required fields are marked *