As a DBA, one of your key responsibilities is to ensure that the undo tablespace is sized correctly to support your transaction workloads. Insufficient undo retention can result in ORA-01555: snapshot too old errors, especially during long-running queries or batch jobs. In this post, we’ll walk through how to analyze, calculate, and tune the undo retention setting using SQL queries and Oracle internal views. This includes checking the current undo tablespace usage, estimating the optimal undo retention, and resizing when necessary.
Initial Checks
SQL> set line 9999 pages 9999 SQL> select name,database_role,open_mode,log_mode from v$database; NAME DATABASE_ROLE OPEN_MODE LOG_MODE --------- ---------------- -------------------- ------------ ESCORT PRIMARY READ WRITE NOARCHIVELOG SQL> @tbs TABLESPACE TOTAL SIZE(MB) FREE SPACE(MB) % FREE -------------------- -------------- -------------- ---------- SYSTEM 1770 4 0 SRL_IDX 38711 1273 3 SRL_DATA 47625 1909 4 RSLT_DATA 25191 1048 4 AUDIT_IDX 18580 1018 5 AUDIT_DATA 22185 1056 5 SYSAUX 5970 292 5 GATEWAY_DATA 4921 234 5 QCLMS_DATA 32724 7410 23 SRL_CRM 200 99 50 CRM_IDX 200 124 62 QCLMS_IDX 1727 1115 65 USERS 1843 1333 72 GATEWAY_IDX 200 198 99 UNDOTBS1 23540 23310 99 15 rows selected. SQL> show parameter undo NAME TYPE VALUE ---------------------- ----------- --------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
You can see here that the undo tablespace UNDOTBS1 is online and has ~99% free space. But we still need to verify if this size is appropriate for the workload and undo retention goal.The current undo retention is just 900 seconds (15 minutes), which might not be enough for longer queries.
Calculate Optimal Undo Retention
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE_MB",
2 SUBSTR(e.value,1,25) "UNDO RETENTION",
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION"
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /
ACTUAL UNDO SIZE_MB UNDO RETENTION OPTIMAL UNDO RETENTION
------------------- -------------- ----------------------
23540 900 2400
The result shows an OPTIMAL UNDO RETENTION of 2400 seconds (approximately 40 minutes)! This is significantly higher than our current 900 seconds. This suggests that for current workload patterns, our undo tablespace is large enough to retain undo for a much longer period, and increasing UNDO_RETENTION would prevent "ORA-01555: snapshot too old" errors for long-running queries.
UNDO Tablespace Size Needed As Per UNDO_RETENTION
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE_MB",
2 SUBSTR(e.value,1,25) "UNDO RETENTION",
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 "NEEDED UNDO SIZE_MB"
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = 'UNDO'
12 AND c.status = 'ONLINE'
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
19 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
20 undo_block_per_sec
21 FROM v$undostat
22 ) g
23 WHERE e.name = 'undo_retention'
24 AND f.name = 'db_block_size'
25 /
ACTUAL UNDO SIZE_MB UNDO RETENTION NEEDED UNDO SIZE_MB
------------------- --------------- -------------------
23540 900 201.984375
This shows that to sustain the current UNDO_RETENTION of 900 seconds, we only need about 202 MB of undo space. Our ACTUAL UNDO SIZE is a massive 23540 MB. This again highlights that we have ample space to increase our undo retention. Given the large OPTIMAL UNDO RETENTION and generous available undo space, increasing UNDO_RETENTION is a sensible optimization. Let's aim for 2400 seconds (40 minutes).
SQL> ALTER SYSTEM SET UNDO_RETENTION = 2400 scope=both; System altered. SQL> show parameter undo NAME TYPE VALUE ----------------------- ----------- ---------------- undo_management string AUTO undo_retention integer 2400 undo_tablespace string UNDOTBS1 SQL>Notes:
The UNDO_RETENTION parameter required the minimum time (in seconds) undo information is preserved. This is crucial for maintaining read consistency for long-running queries and enabling features like Flashback Query.
Oracle's best practice is to configure UNDO_RETENTION based on your database's workload and ensure your UNDO tablespace is adequately sized. This prevents common issues like "ORA-01555: snapshot too old" errors.
You can dynamically adjust this value using:
ALTER SYSTEM SET UNDO_RETENTION = <seconds> SCOPE=BOTH;
Important: If your database is not using an SPFILE, you'll first need to create one and restart the instance before a SCOPE=BOTH change can persist:
CREATE SPFILE FROM PFILE;
For comprehensive details and sizing guidelines, consult the official Oracle Database 19c: Managing Undo documentation.
Toufique Khan
