background success stories

Paramètre UNDO_RETENTION et erreur « ORA-01555 »

Souvent après une erreur « ORA-01555« , le réflexe est d’agrandir la valeur du paramètre UNDO_RETENTION. Mais cela ne fonctionne pas vraiment . . .

 

L’explication se trouve dans la séquence de traitement d’Oracle présente notamment dans la note oracle 269814.1 « ORA-01555 Using Automatic Undo Management – Causes and Solutions » :

  1. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool. No new extent need be allocated.
  2. If we need to extend the segment then first we look for free space in the tablespace WITHOUT extending any datafiles regardless of whether AUTOEXTEND is set or not.
  3. NOTE: This step is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above.
  4. If we hints to shrink any undo segments to release space then we do that, and see if the space released will satisfy our request.
  5. We next look first at offline segments for EXPIRED extents to reuse. If we know all offline segments have no space then we skip this step.
  6. We see if we can steal any EXPIRED extents from other online segments.
  7. If the tablespace has any autoextend datafiles then we now try to allocate space in the tablespace allowing datafiles to extend. If the tablespace has no autoextend datafile we skip this step.
  8. If retention is guaranteed and we are not auto tuning undo then we raise an error.
  9. We try using a lower UNDO_RETENTION value to see if we can free some space without dishonouring any retention guarantee by doing any hinted shrinking of segments.
  10. If we still have no space and are guaranteeing retention then an error is raised.
  11. To be here we are auto tuned and not guaranteed. We start trying to steal UNEXPIRED extents from offline segments.
  12. Try using UNEXPIRED space from the undo segment allocated to the current transaction.
  13. Try stealing UNEXPIRED space from other online segments.

 

L’utilisation des extents EXPIRED (point 5, source de l’erreur ORA-01555) se fait avant l’agrandissement éventuelle d’un datafile (point 6) et l’utilisation du paramètre UNDO_RETENTION (point 9).
Ainsi une solution est d’agrandir le tablespace. Ceci n’exclue pas la possibilité d’identifier le traitement (par exemple un export) et le planifier à une période moins « chargée ». Dans ce cas, la vue v$undostat est un bon indicateur.

 

Il est intéressant de connaître l’algorithme avec un tablespace créé en « NO AUTOEXTEND » (note Oracle 1579779.1 « Automatic Tuning of Undo Retention Common Issues ») :

  1. If the current extent has more free blocks then the next free block is allocated.
  2. Otherwise, if the next extent expired then wrap in the next extent and return the first block.
  3. If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
  4. If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment and add it to the undo segment. Return the first free block of the extent.
  5. If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment.  Return the first free block of the extent.
  6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.
  7. Tune down retention in decrements of 10% and steal extents that were unexpired, but now expired with respect to the lower retention value.
  8. Steal unexpired extents from any offline undo segments.
  9. Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10. Otherwise, wrap into the next extent.
  10. Try to steal unexpired extents from any online undo segment.
  11. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’

 

On notera la diminution de la retention de 10% (point 7), l’utilisation des extents UNEXPIRED (point 8, 9, 10).

Cela signifie qu’avec un tablespace créé en « NO AUTOEXTEND » même si le paramètre UNDO_RETENTION est positionné, Oracle fera de son mieux (soit plus, soit moins que la valeur du paramètre UNDO_RETENTION).