ORA-01555 snapshot too old : dimensionner UNDO_RETENTION et le tablespace UNDO
Comprendre l'erreur ORA-01555, calculer une valeur UNDO_RETENTION adaptée et identifier les sessions qui consomment l'annulation.
L'erreur ORA-01555 survient lorsqu'une requête a besoin de l'ancienne version d'une donnée (lecture cohérente) mais que les informations d'annulation (undo) nécessaires ont été écrasées par d'autres transactions.
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number ... with name "..." too small
ORA-06512: at line 3
Selon la documentation Oracle, les enregistrements d'annulation nécessaires à une lecture cohérente sont écrasés par d'autres écritures. En gestion automatique de l'undo (AUM, mode par défaut depuis Oracle 11g), il faut augmenter UNDO_RETENTION. Sinon, il faut utiliser des segments d'annulation plus grands.
Ajuster UNDO_RETENTION
Le paramètre UNDO_RETENTION est exprimé en secondes. Avant de l'augmenter, vérifiez que le tablespace UNDO peut s'étendre automatiquement et qu'il dispose de l'espace nécessaire.
Connectez-vous en SYSDBA, puis modifiez le paramètre :
alter system set undo_retention=1800;
show parameter undo_retention;
Calculer le UNDO_RETENTION optimal
Taille actuelle du tablespace UNDO :
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#;
Nombre de blocs UNDO générés par seconde :
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;
Taille des blocs de la base :
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE (Byte)" FROM v$parameter
WHERE name = 'db_block_size';
Rétention optimale pour la taille actuelle de l'UNDO :
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';
Calculer la taille UNDO nécessaire
Taille du tablespace UNDO requise pour une rétention donnée :
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';
Taille des extensions UNDO par statut (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;
Identifier les sessions qui consomment l'UNDO
Sessions et volume d'UNDO généré :
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;
Transactions en cours, classées par blocs UNDO utilisés :
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;
Requêtes les plus consommatrices d'UNDO dans l'historique AWR :
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
)
);
Périodes où une erreur ORA-01555 a été comptabilisée :
select * from dba_hist_undostat where SSOLDERRCNT > 0;
Taille des tablespaces UNDO :
SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDOTBS%'
GROUP BY TABLESPACE_NAME;
Remarques
Les formules de dimensionnement ci-dessus sont des estimations basées sur le volume d'UNDO généré pendant la période conservée dans v$undostat. Vérifiez-les sur une période représentative de l'activité de la base.
Références : discussion Oracle sur dba.stackexchange et article sur le dimensionnement de l'UNDO.