L’utilisation des segments UNDO

Chaque fois qu'une instruction « INSERT », « UPDATE » ou « DELETE » met à jour un ou plusieurs enregistrements dans une table les blocs qui contient les enregistrements sont stockés dans les segments « UNDO ». Les segments d’annulation sont des zones de stockage gérées automatiquement par Oracle. Ils sont stockés dans un tablespace de type « UNDO ».

Chaque fois qu'une instruction « INSERT », « UPDATE » ou « DELETE » met à jour une ou plusieurs lignes dans la table, un verrou LMD ROW EXCLUSIVE est placé. Il permet à des transactions multiples de mettre à jour la table aussi longtemps qu'elles ne mettent pas à jour les mêmes lignes.

en savoir

Oracle gère le stockage, la rétention et l'emploi de l'espace pour les données d’annulation par l'intermédiaire des segments de type SMU (System-Managed Undo). Aucun objet permanent n'est placé dans le tablespace de type « UNDO ».

Les undo segments sont utilisés aux fins de gérer :

La lecture cohérente des données de la base.

L’annulation d’une transaction.

La récupération des transactions après un arrêt brutal du serveur ou la perte intempestive d’une connexion. La restauration des transactions est possible car toutes les modifications apportées aux segments « UNDO » sont également protégées par des fichiers journaux.

La conservation des blocs « UNDO » après la fin des transactions pour pouvoir mettre en œuvre les technologies « FLASHBACK ». La récupération transcendantale des données dans l’état où elles étaient plusieurs heures auparavant.

La lecture cohérente

Une des caractéristiques d'Oracle est sa capacité à gérer l'accès concurrent aux données, c'est-à-dire l'accès simultané de plusieurs utilisateurs à la même donnée.

La lecture consistante, telle qu'elle est prévue par Oracle assure que :

Les données interrogées ou manipulées, dans un ordre SQL, ne changeront pas de valeur entre le début et la fin.

Les lectures ne seront pas bloquées par des utilisateurs effectuant des modifications sur les mêmes données.

Les modifications ne seront pas bloquées par des utilisateurs effectuant des lectures sur ces données.

Un utilisateur ne peut lire les données modifiées par un autre si elles n'ont pas été validées.

Il faut attendre la fin des modifications en cours dans une autre transaction afin de pouvoir modifier les mêmes données.

Il est possible de subdiviser une transaction en plusieurs étapes en sauvegardant les informations modifiées à la fin de chaque étape, tout en gardant la possibilité soit de valider l'ensemble des mises à jour, soit d'annuler tout ou partie des mises à jour à la fin de la transaction.

Le découpage de la transaction en plusieurs parties se fait en insérant des points de repère, ou « SAVEPOINT ».

Les points de repère « SAVEPOINT » sont des points de contrôle utilisés dans les transactions pour annuler partiellement l'une d'elles. Dans ce cas, un point de repère est défini par un identifiant et peut être référencé dans la clause « ROLLBACK ».

La taille et la rétention

L’exemple suivant démontre l’impact de la taille du tablespace de type « UNDO » par défaut d’une base de données sur la lecture cohérente et tout simplement sur le fonctionnement des transactions dans la base de données. Le tablespace « UNDO_PETIT » d’une taille de « 1M » est défini comme tablespace de type « UNDO » par défaut pour notre base de données. La table « UNDO_DEMO » est créée avec « 300 » enregistrements. Un premier essai d’insertion de « 90000 » enregistrements est rejeté faute de place dans le tablespace « UNDO_PETIT ».

SYS@agate>create bigfile undo tablespace undo_petit datafile

  2  size 1M autoextend off;

 

Tablespace créé.

 

SYS@agate>alter system set undo_tablespace=undo_petit;

 

Système modifié.

 

SYS@agate>create table undo_demo tablespace users

  2  as select * from all_objects where rownum < 301;

 

Table créée.

 

SYS@agate>insert into undo_demo

  2  select a.* from undo_demo a,undo_demo b;

insert into undo_demo select a.* from undo_demo a,undo_demo b

            *

ERREUR à la ligne 1 :

ORA-30036: impossible d'étendre le segment par 8 dans le tablespace d'annulation 'UNDO_PETIT'

 

SYS@agate>declare

  2     procedure proc_u is

  3        pragma autonomous_transaction;

  4     begin

  5        update undo_demo set object_name = object_name;

  6        commit;

  7     end;

  8  begin

  9   for l in ( select * from undo_demo) loop proc_u; end loop;

 10  end;

 11  /  

declare

*

ERREUR à la ligne 1 :

ORA-01555: clichés trop vieux : rollback segment no 7, nommé "_SYSSMU7_2489749128$", trop petit

ORA-06512: à ligne 10

Le block PL/SQL effectue deux traitements dans deux sessions distinctes :

Le bloc principal utilise un curseur implicite dans une boucle « FOR » qui lit la table « UNDO_DEMO » du premier au dernier enregistrement. La procédure « PROC_U » est appelée pour chaque enregistrement de la table.

La procédure « PROC_U » met à jour tous les enregistrements de la table « UNDO_DEMO » en validant chaque fois la transaction. Ce traitement écrit dans les segments « UNDO » du tablespace « UNDO_PETIT » l’ensemble des blocs de la table « UNDO_DEMO » chaque fois qu’il est appelé. Il faut également remarquer le mot clé « PRAGMA » avec l’option « AUTONOMOUS_TRANSACTION » qui permet d’exécuter cette procédure « PROC_U » dans une transaction indépendante.

Il faut se rappeler qu’un curseur lit entièrement toutes les données au moment de l’ouverture. Apres l’ouverture du curseur, les modifications ne sont pas prises en compte.

Alors, la procédure « PROC_U » une fois exécutée, tous les blocs nécessaires pour la lecture du curseur sont déplacés dans les segments « UNDO » et y resteront jusqu’à la fermeture du curseur. Mais le tablespace « UNDO_PETIT » ne peut pas assurer le stockage des ces blocs trop longtemps car la procédure « PROC_U » à besoin de l’espace pour stocker successivement tous les blocs de la table « UNDO_DEMO ».

SYS@agate>begin

  2     for i in 1..100

  3     loop

  4         update undo_demo set owner = rpad(owner,10,'*');

  5     end loop;

  6     commit;

  7  end;

  8  /

begin

*

ERREUR à la ligne 1 :

ORA-30036: impossible d'étendre le segment par 8 dans le tablespace d'annulation 'UNDO_PETIT'

ORA-06512: à ligne 4

 

SYS@agate>begin

  2     for i in 1..100

  3     loop

  4         update undo_demo set owner = rpad(owner,10,'*');

  5         commit;

  6     end loop;

  7  end;

  8  /

 

Procédure PL/SQL terminée avec succès.

Le premier bloc PL/SQL est rejeté suite à un manque d’espace dans le tablespace « UNDO_PETIT ». En effet, la validation de la transaction est effectuée après que la boucle « FOR » ait modifié les « 300 » enregistrements « 100 » fois.

Le deuxième bloc PL/SQL est identique au premier du point de vue volume de données à traiter. Mais la validation de la transaction s’effectue après chaque ordre « UPDATE », ce qui signifie que les blocs « UNDO » peuvent être réutilisés.

L’erreur Oracle « ORA-01555: clichés trop vieux » se produit lorsque les blocs « UNDO » ont été écrasés par une autre transaction. Cette erreur survient également si le temps des transactions est trop important, car dans ce cas les segments « UNDO » doivent garder longtemps les blocs, ce qui augmente la probabilité qu’ils soient écrasés par les autres transactions.

La conservation des blocs

Le paramètre d’initialisation « UNDO_RETENTION » permet de définir en secondes le temps de conservation des informations dans les segments « UNDO ». Il peut avoir une valeur entre « 0 » et « 232 ». Les blocs « UNDO » ne sont pas libérés par les validations des transactions « COMMIT », mais sont gardés afin de pouvoir être utilisés par les technologies « FLASHBACK ».

RETENTION NOGUARANTEE

C’est l’option de conservation des blocs « UNDO » par défaut, et dans ce cas la valeur du paramètre « UNDO_RETENTION » est purement indicative. La gestion est assurée suivant le mode d’agrandissement du tablespace de type « UNDO » utilisé comme suit :

Pour un tablespace qui n’est pas en agrandissement automatique, le paramètre « UNDO_RETENTION » est dimensionné pour le maintien des meilleures conditions possibles suivant la taille du tablespace et le volume des transactions de la base de données.

Pour un tablespace qu’est en agrandissement automatique, le paramètre « UNDO_RETENTION » est dimensionné pour assurer au moins le temps spécifié dans le paramètre, et règle automatiquement la période de conservation pour satisfaire aux exigences des requêtes. En somme votre tablespace est automatiquement agrandi pour stocker les blocs nécessaires aux transactions courantes, la période est régulièrement agrandie mais, attention, c’est insuffisant pour les technologies « FLASHBACK ».

Une exception est à remarquer pour toutes les transactions qui modifient des enregistrements avec des champs de type « LOB » ; ces transactions ne bénéficient pas de l’optimisation automatique de la période de conservation. La valeur du paramètre « UNDO_RETENTION » est effective pour ces transactions.

RETENTION GUARANTEE

Les blocs « UNDO » sont conservés dans le tablespace, pour tous les segments, même si des nouvelles transactions n’aboutissent pas. En effet, la gestion des segments « UNDO » est paramétrée de manière à donner priorité à la conservation des modifications plutôt qu’aux transactions.

Il faut prendre soin d’avoir l’espace de stockage nécessaire pour l’ensemble des données d’annulation pour la période de conservation précisée dans le paramètre d’initialisation « UNDO_RETENTION ».