LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon