How to Calculate Oracle Undo Retention

Hi folks, I decided to write a practical article for the Undo retention calculation. The transaction undo information generated in the database is stored in the rollback segments until the commit or rollback is performed. Sometimes our long-running queries in the database are met with the error ”ORA-01555:Snapshot Too Old”.
This is due to the fact that our UNDO parameters are not configured correctly.

If you are wondering how many ORA-1555 errors our database receives in total, you can find it with the following query.

select ‘”ORA-01555 (Snapshot too old)” hata sayisi: ‘
|| sum(ssolderrcnt)
from v$undostat;

Formula

UNDO SIZE=UNDO RETENTION TIME × DB BLOCK SIZE × UNDO BLOCK PER SECOND

Let’s simplify the job a little and find the optimal value.

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
SUBSTR(e.value,1,25) as UNDO_RETENTION,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)360024))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’;