2009/08/19

Estimate size for undo tablespace

Sizing an UNDO tablespace requires three pieces of information:

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)
or, when the guesstime equates to zero, then add a multiplier (24) to the overhead (DBS) to derive more appropriate results:
UndoSpace = (UR * (UPS * DBS)) + (DBS * 24)

UNDO_RETENTION and DB_BLOCK_SIZE can be obtained from the initialization file. UPS can be acquired from the following query:

SELECT (sum(undoblks))/sum((end_time-begin_time)*86400)
FROM v$undostat;

No comments: