Sizing undo tablespace
According to the document 262066.1 How to Size UNDO Tablespace For Automatic Undo Management, I issue the query for one of our databases that use undo tablespace.
SQL> spool check_undo_block_size.rpt
SQL> select (UR *(UPS*DBS)) + (DBS*24) AS "Bytes"
2 from (select value AS UR FROM v$parameter WHERE name ='undo_retention'),
3 (select(SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
4 (select block_size as DBS from dba_tablespaces where tablespace_name =
5 (select upper(value) from v$parameter where name ='undo_tablespace'));
Bytes
----------
4089507270
1 row selected.
This is the results I got. Supposely if I set the undo_tablespace to 4G according to the query, the database should be happy. The actual space I gave when I create the undo was 8G. I thought this should be more than sufficient than the recommended 4G. However, I still sometimes get the space fill up error. My undo_retention is set at 72000 which is about 20 hours. My block_size is 8192 and UPS is 6.9 blocks per seconds. Does that mean I need to add more space? Any suggestions?