Size UNDO tablespace for Automatic Undo Management

Print this post

 

Sizing an UNDO tablespace requires three pieces of data. (Note:Overall consideration for peak/heavy vs normal system activity should be taken into account when peforming the calculations.) (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) 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 upper(value) from v$parameter where name = 'undo_tablespace'));