How to analyze Undo statistics to proactively avoid undo space issues

    Jul 25, 2017 12:38:00 PM by Deiby Gomez


    In my previous articles I explained two very important concepts about Undo Data; one is how Oracle manages the retention time and the other is how Oracle reuses the undo extents. You can also check my presentation "How to avoid ORA-01555" if you want to know more about that error. In this article, I will show you how the view V$UNDOSTAT can give you useful information about how everything is going regarding your undo data in your database. First, let me give you a small definition about two views:

    V$UNDOSTAT: Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

    DBA_HIST_UNDOSTAT: This view contains snapshots of V$UNDOSTAT. Basically is has the history of V$UNDOSTAT.

    As you can see, the main view is V$UNDOSTAT; the other is just its history. There are several columns in the view. Here are the ones we’ll focus on:

    UNDOBLKS: Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system

    TXNCOUNT: Identifies the total number of transactions executed within the period

    UNXPBLKREUCNT: Number of unexpired undo blocks reused by transactions

    EXPBLKRELCNT: Number of expired undo blocks stolen from other undo segments

    ACTIVEBLKS: Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period

    UNEXPIREDBLKS: Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period

    EXPIREDBLKS: Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period.

    NOSPACEERRCNT: Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

    By using these columns, there are some interesting combinations that every DBA can use to tune undo data generation. If we combine UNDOBLKS and TXNCOUNT, for instance, we can find out the consumption rate of undo blocks per transaction.  Use the following query:


    You can also combine UNDOBLKS, the Undo tablespace’s block size, and the retention time in order to learn how many MB you will need for your undo tablespace’s size to match with a specific retention time.

    And even more interesting, we can extract the data from V$UNDOSTAT in a CSV format and create line charts in order to understand the undo behavior of our databases.

    Let’s see how this would work. As an example, I have created a EE database, where I have loaded some workload with SLOB. The SLOB was configured to perform 95% UPDATES and 5% SELECTs, a WORK_UNIT=8192, 5 SLOB schemas and 5 threads per schema in order to generate a lot of undo data. 

    For each chart that I will show, SLOB was running for around 60 minutes. This means that we will have 6 rows in V$UNDOSTAT, since every row is a sample of 10 mins.

    Before you study the charts, I really recommend that you first read these two articles to master the two principal concepts:

    How does Oracle reuse the Expired and Unexpired undo extents?

    Undo retention time with autoextend=on and autoextend=off

    Let’s begin. The following charts use the columns: NOSPACEERRCNT, ACTIVEBLKS, UNEXPIREDBLKS, EXPIREDBLKS (but you can build more complex charts using the others columns of V$UNDOSTAT).


    First type of workload 

    The chart below characterizes an OLTP database; the database is receiving transactions (because there are active undo extents) but the transactions seem to happen infrequently since most of the undo extents are "expired" and the active extents have not increased enough to require reusing expired/unexpired extents.

    If you have your undo data behavior looking like this chart, you would say your database is healthy from an undo space perspective. This would be a "perfect" environment. In this chart, there is no reason to be worried regarding undo space.


    First Workload Example


    Second type of workload

    This workload is quite different.. In the previous chart, the higher line was of “Expired Blocks” and the lower line was of “Unexpired Blocks”; however, in this second chart this is reversed. Now we can see that the higher line is of “Unexpired Blocks”. This means that the database is receiving the workload and the undo retention time is high enough to keep the undo data of the completed transactions (Unexpired extents) stored.

    Here, you have to review whether there are Unexpired extents that are being reused by new transactions. This happens more frequently when the line of Unexpired extents is getting close to the line of the active extents (the next two charts). If you see that “UNXPBLKREUCNT” has a value greater than one, you probably should tune undo retention. If the undo retention has the value that you require, then you can increase the size of your undo tablespace; otherwise, unexpired extents will be overwritten by other transactions if Oracle requires it. In that case you would see some ORA-01555 in your SELECT operations.

    In the chart below, however, there is no reason to be worried regarding space.

    Second Workload Example


    Third type of workload

    The chart below is very similar to the previous one; however, in this chart the line of “Unexpired extents” is closer to the line of Active extents. This behavior increases the probability of getting ORA-01555 in your SELECT operations. If you want to avoid ORA-01555, you can increase the undo retention time or increase the size of the undo tablespace.

    In this chart, there is no reason to be worried regarding space, only about ORA-0155, but you should look a little bit deeper because if you don’t pay attention, your database might reach the status of either of the two charts we’ll be looking at later on.

    Third Workload Example


    Fourth type of workload 

    This chart indicates a worse situation than the two previous charts. Here, the number of transactions is increased such that the number of active undo extents has also increased, and started to overwrite (reuse) some unexpired undo extents.

    In a database with this undo behavior there will surely be some SELECTs failing with ORA-01555, and space issues will be around the corner. I recommend in this case that you make a deep analysis of why expired undo extents have started to be reused.

    If you just ignore the type status shown in this chart, your database will at some point reach the behavior shown in next chart. There will be space problems and your transactions (INSERT, UPDATE, DELETE) will start failing because there is no free space in the undo tablespace to be assigned for new extents.

    Fourth Workload Example


    Fifth type of workload

    You should avoid having your database in this status as much as possible. In this status, some transactions (INSERT, UPDATE, DELETE) have already started to fail because there was no free space in undo tablespace to create new active undo extents.You should definitely increase the size of some datafiles of undo tablespace.

    Fifth Workload Example

    I’ve just shown you five charts created from the view V$UNDOSTAT that allows you to chart up to 4 days of historic data. You could  use DBA_HIST_UNDOSTAT if you want to chart several days in the past.


    Determining the proper undo tablespace size

    Oracle provides the function dbms_undo_adv.required_undo_size , which you can use to determine the proper undo tablespace size to comply with an specific undo retention time.

    SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is '
    || dbms_undo_adv.required_undo_size(128) || ' MB' required_undo_size FROM dual;



    The Required undo tablespace size using Statistics In Memory is 79 MB

    You can use this function as a starting point, but I recommend that you set the size of the undo tablespace based on your analysis of the behavior and historic statistics of your undo data.



    In this article I demonstrated that the view V$UNDOSTAT has very useful information that you can review, or even better, that you can chart. You can build charts as complex as you want in order to analyze the behavior of your database from the undo usage perspective and then make decisions to properly tune undo retention time and undo tablespace size.

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle ( in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator