ORA-01555 snapshot too old: sizing UNDO_RETENTION and the UNDO tablespace
Understand ORA-01555, calculate a suitable UNDO_RETENTION value and identify the sessions consuming undo space.
The error ORA-01555 occurs when a query needs an older version of a row (read consistency) but the undo records it needs have been overwritten by other transactions.
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number ... with name "..." too small
ORA-06512: at line 3
According to the Oracle documentation, the undo records needed for a consistent read are overwritten by other writers. With automatic undo management (AUM, the default mode since Oracle 11g), increase UNDO_RETENTION. Otherwise, use larger rollback segments.
Adjust UNDO_RETENTION
The UNDO_RETENTION parameter is expressed in seconds. Before increasing it, make sure the UNDO tablespace can extend automatically and has enough free space.
Connect as SYSDBA, then change the parameter:
alter system set undo_retention=1800;
show parameter undo_retention;
Calculate the optimal UNDO_RETENTION
Current size of the UNDO tablespace:
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#;
Number of undo blocks generated per second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;
Database block size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE (Byte)" FROM v$parameter
WHERE name = 'db_block_size';
Optimal retention for the current UNDO size:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MB)", SUBSTR(e.value,1,25) "UNDO RETENTION (Sec)",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION (Sec)"
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)*3600*24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';
Calculate the UNDO size needed
UNDO tablespace size required for a given retention:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MB)", SUBSTR(e.value,1,25) "UNDO RETENTION (Sec)",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE (MB)"
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)*3600*24)) undo_block_per_sec FROM
v$undostat ) g
WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';
Undo extents by status (active, expired, unexpired):
select
tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*16/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Find the sessions consuming undo
Sessions and the amount of undo they generate:
select s.sid,
s.username,
decode(round(sum(ss.value) / 1024 / 1024),0,null,round(sum(ss.value) / 1024 / 1024)) as undo_size_mb
from v$sesstat ss
join v$session s on s.sid = ss.sid
join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL group by s.sid, s.username;
Active transactions, sorted by undo blocks used:
SELECT a.sid, a.username, a.logon_time, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;
SQL statements that generated the most undo in the AWR history:
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID in
(select distinct MAXQUERYSQLID from
(select * from
(select SNAP_ID,
MAXQUERYLEN,
UNDOBLKS,
MAXQUERYSQLID,
to_char(BEGIN_TIME,'yyyy/mm/dd hh24:mi') begin,
to_char(END_TIME,'yyyy/mm/dd hh24:mi') end
from DBA_HIST_UNDOSTAT
order by UNDOBLKS desc,
MAXQUERYLEN desc
) where rownum<11
)
);
Periods in which ORA-01555 errors were counted:
select * from dba_hist_undostat where SSOLDERRCNT > 0;
Size of the UNDO tablespaces:
SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDOTBS%'
GROUP BY TABLESPACE_NAME;
Notes
The sizing formulas above are estimates based on the undo volume generated during the period kept in v$undostat. Check them against a period that is representative of the database workload.
References: Oracle discussion on dba.stackexchange and article on undo sizing.