How to analyze Undo statistics to proactively avoid undo space issues

Introduction

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 12.2.0.1 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;

REQUIRED_UNDO_SIZE

--------------------------------------------------------------------------------

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.

Conclusion