Thursday, February 15, 2007

Size your Undo tablespace

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 paper 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” .

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;


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;


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


nipun said...

amazing funda, very helpful

Sachin said...

If anyone feels keeping autoextend on for a month could be dangerous from space perpective, you can set it off and put a alert system (i guess evenyone uses it) to see if undo tablespac gets filled up or not.

Rajeev said...

Sachin, Can you elaborate abt the Alert System to monitor the space managemnt of tablespace.

Rajeev said...

Sachin, Can you elaborate abt the Alert System to monitor the space managemnt of tablespace.

Sachin said...

Hi Rajeev,

Thanks for your comments.
But I suggest, if you can expalin/elaborate your question.

"Alert System to monitor space Management of tablespace"

- Is it for all tablespace or UNDO tablespace.

- When you say Alert System, it means EM Alerts?


Rajeev said...

I was talking abt UNDO tablespace.

I have got the Oracle documentation (Oracle 9i 5 weeks Traing Material); Can u suggest me the road map to master oracle database.

Vidhya said...

We were running an Insert statement within a procedure which commits crores of records and then commit. Since undo tablespace was getting filled, we killed the process. Even the default tablespace was also increasing quite fast. After killing the process, it has not released the space it has grown to. What could b the reason?

Is it because the cursor may not have closed? Do we need to bounce the db to release the space?

Please update.

Sachin said...

Hi Vidhya,

Space from UNDO segment is not realeased immediately the transaction commits.
when the transaction commits, the blocks used by transaction are in reusable status.

So if you see "tablespace free space", you may not see the space releasing from UNDO tablespace. But thats normal. the space is still in reusable status.
One more thing you need to keep in mind is UNDO segment are written in circular fashion so before wrapping, undo segment need to have free extent in the starting of the circle or else undo segment will extend further, depending upon the space left in UNDO tablespace.


Anonymous said...

The calculation is not perfect. The actual undo tablespace size=(size for retention period)+(size for concurrent transactions)

size for concurrent transactions=max(undoblks)*undo_retention