LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon