Labels

Sunday, December 30, 2012

What should be the UNDO Tablespace Size for a database

What should be the UNDO Tablespace size for a oracle database?

UR=UNDO_RETENTION
UBS=UNDO block Generation per sec
DBS=DB_BLOCK_SIZE

Size of UNDO needed = UR x [UBS x DBS] + Overhead(30xDBS)

we can use below command to find UNDO_RETENTION and DB_BLOCK_SIZE
SQL>show parameter UNDO_RETENTION
SQL>show parameter DB_BLOCK_SIZE

To find out “UNDO Blocks per second”

we can  fetched from v$undostat

SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UBS"
FROM v$undostat;


UBS
------------------------------
8.11985583

No comments:

Post a Comment