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:
select min(UNDOBLKS/TXNCOUNT), avg(UNDOBLKS/TXNCOUNT), max (UNDOBLKS/TXNCOUNT) from V$UNDOSTAT
select BEGIN_TIME, END_TIME, UNDOBLKS/TXNCOUNT from V$UNDOSTAT;
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 220.127.116.11 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.
Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS).
Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.