Undo Tablespace Oracle

What should be the size of UNDO tablespace?

Generally this question is vaguely answered saying that sizing comes with experience (of DBA) or looking at load on server or sometimes even by the number of ORA-1555 or out of space errors.

This is to help DBA’s in calculating the size of UNDO tablespace by using a simple formula.

While designing an application, generally it is tough to know about the number of transactions and subsequently number of rows changed per second.

So I suggest having a “big undo tablespace” to start with and based on load, after doing some calculations and resize your UNDO tablespace.

In my case one of the applications was going to production (live), and I had no idea that how many transactions will happen against this database. All what I was told that there will be optimum (transactional) activity on this database. The word “optimum” itself is vague.

So I started with UNDO tablespace with size of 2GB and datafiles with autoextend “on” .

Note:

In production, you must be very careful in using this (autoextend on) as the space may grow to inifinity very fast. So my advice is either dont use this option, or use with "maxsize" or continuously monitor space (which is tough).

I month later, I noticed the activity from V$undostat.

Here is the step by step approach:

Step 1: Longest running query.

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)

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

1793

This gives you ideal value for UNDO_RETENTION. To be on the safer size you should add few more seconds to get the right value. So in my case, the size of undo retention should be say 2000 secs.

Step 2: Size of UNDO tablespace.

Size of UNDO needed = UNDO_RETENTION x [UNDO block Generation per sec x DB_BLOCK_SIZE] + Overhead(30xDB_BLOCK_SIZE)

Out of these we know UNDO_RETENTION and DB_BLOCK_SIZE

All we need is to find out “UNDO Blocks per second”

Which can be easily fetched from v$undostat

SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UPS"

2 FROM v$undostat;

UPS

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

8.11985583

V$undostat stores data for every 10 mins and begin/end times are start/end time of those intervals. We multiplied it with 24*60*60 because the difference between two dates will be in days and to get to seconds, we need it to multiply with 24hrs*60mins*60secs

So now we have all the values needed.

Undo size needed = [8.12 x 2000 x 8192] + [30 x 8192] = 133283840 bytes = 127.11 MB