Sizing your undo tablespace

It is always a puzzle for a DBA to look into the user's complaint of getting "ORA-01555 Snapshot too old : rollback segment number x with name "_SYSSMUx$" too small " error. You have looked into the database. If your UNDO_MANAGEMENT is set to AUTO, you can not do anything to size the rollback segments manually since it is being managed by oracle. All the associated tables and indexes have been analyzed and statistics are up to date. The undo tabelspace is almost full. You may advise the user that there should be frequent commits (if it is a data loading process) or if there is a long running query and other users change the data that is being selected by the query, this can happen and in that case, if possible, advise the user not to run these two at the same time.

Well! No improvement in the situation though you made your effort as above. It is time to look into the issue in a different point. There may be other jobs running in the database which is taking up the undo space. The complaining user's session is not getting his share of undspace which he deserves. You have the remedy for that. Add more space to UNDO tablespace.

But, how much?

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode.

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 1008 rows, spanning a 7 day cycle.

With the help of this view (mainly) we can estimate the total undospace you require and expand it accordingly.

Estimate the size of UNDO tablespace

Sizing an UNDO tablespace requires three pieces of data.

1. (UR) UNDO_RETENTION in seconds
2. (UPS) Number of undo data blocks generated per second
3. (DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds:

SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted,
the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.

The result of the query returns the number of undo blocks per second. This value needs
to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE.

The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" 
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), 
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), 
(select block_size as DBS from dba_tablespaces where tablespace_name= 
(select value from v$parameter where name = 'undo_tablespace'));

Follow the output as guideline to size your undo tablespace.

References used:

• Oracle Documenetation
• Metalink