How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]

Sizing an UNDO tablespace requires three pieces of data.

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

The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)

 

Two of the pieces of information can be obtained from the instance configuration: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query being run against the database. The maximum number of undo blocks generated per second can be acquired from V$UNDOSTAT.

Note: Overall consideration for peak/heavy vs. normal system activity should be taken into account when peforming the calculations. Autoextend OFF vs. ON will change the behavior for UNDO_RETENTION growth and use of UNEXPIRED extents. See Note 461480.1 for more information.

The following formula calculates the peak undo blocks generated per second:

 

SQL> SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);

 

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the resulting value is the # of days between both dates. To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).

The following query calculates the number of bytes needed to handle a peak undo activity:

 

 

SQL> SELECT (UR * (UPS * DBS)) AS "Bytes"     FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),          (SELECT undoblks/((end_time-begin_time)*86400) AS UPS           FROM v$undostat           WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),          (SELECT block_size AS DBS           FROM dba_tablespaces           WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

 

 

 

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the resulting value is the # of days between both dates. To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).

The following query calculates the number of bytes needed to handle a peak undo activity: