Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
22 septembre 2009 2 22 /09 /septembre /2009 13:18

Cet article s'adresse à ceux qui ont subi au moins une fois dans leur vie le "mystérieux" ORA-01555 snapshot too old, et à ceux que les histoires de lecture consistente font rigoler !

ORA-01555 est essentiellement due au mécanisme de lecture consistente (on dira aussi cohérente, vu que c'est l'idée) : cette erreur se déclenche principalement lorsqu'on essaie d'accéder à une version des données "trop vielle".

Cependant, il y a un cas un peu particulier de l'ORA-01555, liée à un mécanisme appelé le delayed block cleanout. Le mécanisme est sympa, poussé par une idée d'optimisation marrante. Cela dit, l'erreur qu'elle peut provoquee la rend quelque peu aberrante à mon goût.
Je me suis mis en tête de me construire un cas où l'erreur se produit. Et donc je vais en profiter pour parler un peu du sujet !

1) Le contexte : lecture consistente et rollback
2) COMMITer n'importe comment, c'est naze
3) Le match du jour : ORA-01555 1 - Delayed Block Cleanout 0


1) Le contexte : lecture consistente et rollback

a. Quand vous exécutez une requête assez longue par exemple, vous souhaiteriez quand même que les données lues en début et en fin de requête correspondent au même "état de l'univers", non ?
Par exemple : votre requête commence. Pendant ce temps, un autre utilisateur valide (COMMIT) un lot de modifications cohérentes.
Seulement voilà, même si ses modifications sont cohérentes, vous avez commencé à lire les données non modifiées, et vous voulez finir ainsi !
Il faut donc que le SGBD puisse garantir une image de l'univers à un instant donné.

b. Lorsque vous modifiez les données à travers une transaction, vous voulez pouvoir les annuler. Pour ce faire, il faut sauvegarder les données avant modification.
Une fois validées (COMMIT), il ne semble plus nécessaire de garder la sauvegarde des anciennes données.

Tout le problème vient du fait que dans Oracle, un mécanisme commun assure les deux fonctionnalités : la gestion de l'UNDO !
La version courte (pour les mécanismes détaillés, vous trouverez sûrement de bons sites pour expliquer tout ça) :
- Lorsqu'un bloc de données est modifié, celui-ci est copié dans l'UNDO TABLESPACE (ou ROLLBACK SEGMENTS dans les anciennes versions d'Oracle)
- Lorsqu'une requête ayant débuté avant la transaction qui modifie ces données cherche à lire le bloc, elle est redirigée vers cet UNDO TABLESPACE, afin d'y trouver la version qui correspond à son contexte
- Cet UNDO TABLESPACE est un espace restreint, comprenant donc un nombre de "slots" limité. A chaque fois qu'une modification est COMMITée, le slot est libéré. Mais lorsque ce slot est réutilisé, l'information qui devait assurer la cohérence de la lecture des requêtes ayant débuté avant la dite transaction est perdue.
Et paf, ORA-01555 snapshot too old !

2) Le mythe

Certaines personnes font l'amalgame entre les deux fonctionnalités sus-citées... et recommandent donc d'augmenter le nombre de COMMIT !
Par exemple, au lieu de faire un update global en une requête, ils recommandent de parcourir la sélection dans un curseur, et COMMITer régulièrement.
Eh bien, par rapport à ce qu'on disait, ça donne l'effet inverse : vous COMMITez plus souvent, donc vous libérez plus rapidement vos sauvegardes qui servent à la cohérence de la lecture, qui peuvent donc être plus facilement écrasés.

C'est ce qu'on appelle le 'fetch across commit'.
Petit exemple :

On veut que le curseur parcourre la table dans un ordre différent de l'ordre physique. Ainsi, il est fort probable que le curseur cherche à fetcher un bloc déjà COMMITé.

SQL> create table test_1555 as
  2  select * from (
  3     select level as id, lpad('x', 250, 'x') as c
  4     from dual
  5     connect by level <= 10000)
  6  order by dbms_random.random;

Table créée.

SQL> create index test_1555_i1 on test_1555(id);

Index créé.

Il faut obliger Oracle à réutiliser les slots d'UNDO, afin d'écraser les "sauvegardes des blocs". Pour cela, on prend un petit tablespace 500K.
Sincèrement, j'aurais aimé prendre plus petit... mais lorsque j'ai tenté le coup, aucun rollback segment ne se créait.

SQL> CREATE UNDO TABLESPACE smallundo datafile 'C:\ORACLEXE\ORADATA\XE\SMALLUNDO' SIZE 500K AUTOEXTEND OFF;

Tablespace créé.

SQL> ALTER SYSTEM SET undo_tablespace=SMALLUNDO;


L'objectif est d'obliger la requête à utiliser l'index. On pourrait forcer avec un hint. Ici, j'ai pompé l'exemple de l'Oncle Tom qui prend un prédicat bidon sur la colonne indexée. Mais pour que ça marche, faut quand même empêcher l'optimiseur d'échantilloner la table et de se rendre compte de la supercherie !

SQL> alter session set optimizer_dynamic_sampling =  0;

Session modifiée.

Et le résultat :

SQL> begin
  2  for c in (select * from test_1555 where id > 0)
  3  loop
  4     update test_1555 set c = 'a' where id = c.id;
  5     commit;
  6  end loop;
  7  end;
  8  /
begin
*
ERREUR Ó la ligne 1 :
ORA-01555: clichés trop vieux : rollback segment no , nommé "", trop petit
ORA-06512:  ligne 2


3) La spéciale Delayed Block Cleanout !

Ici, la cause de l'erreur est différente. Pour cela, une rapide explication s'impose.
Lorsqu'on lit les blocs de la table, il faut pouvoir déterminer si celui-ci est en cours de modification (non COMMITée) ou non.
Et dans le premier cas, il faut pouvoir retrouver la version précédente du bloc.
Pour cela, l'entête du bloc contient un pointeur vers l'entrée d'UNDO.
Lors du commit, il faut nettoyer le bloc de toutes ces informations, et apposer le numéro de version du bloc (COMMIT SCN).

D'autre part, comme nous le disions en 2), il faut pouvoir déterminer si un bloc de l'UNDO est commité ou non, afin de libérer le slot pour les transactions suivantes. Ainsi, il y a également un flag au niveau de l'entrée d'UNDO indiquant s'il est COMMITé ou non, ainsi que le timestamp du COMMIT.

Et voilà l'astuce :
Si la mise à jour a été longue, et que le bloc de données n'est plus présent dans le buffer cache, il faudrait aller rechercher le bloc sur le disque pour le nettoyer. Mais quand on y réfléchit, comme l'info est redondante entre le bloc et l'UNDO slot, on peut imaginer que la requête lisant le bloc sera redirigée vers l'UNDO, où elle s'apercevra que finalement celui-ci était bel et bien COMMITé.
C'est le principe du delayed block cleanout : utiliser la redondance de l'information de COMMIT pour s'épargner des accès disque et laisser le soin à la prochaine requête de faire le nettoyage.


Mais que se passe-t-il lorsque le slot a été réutilisé ?
Par chance, le système ne claque pas tout de suite. Si le slot d'UNDO a été réutilisé, on peut sensément conclure que la transaction s'est terminée.
Ensuite, il s'agit surtout de déterminer si le COMMIT SCN du bloc à lire est inférieur ou non au SCN courant de la requête.
Ainsi, on recherche le plus ancien SCN dans l'UNDO, qui sera sensément plus récent que celui du bloc cherché : en effet, le mécanisme d'allocation commence par réutiliser les plus vieux (le monde est injuste, disons le).
Mais si ce SCN "borne supérieur" (upper bound SCN en VO) est plus récent que le SCN courant de la requête (snapshot SCN), re-paf : snapshot too old.


Venons en à l'exemple.

Objectif :
- COMMITer des mises à jour assez volumineuses pour que les blocs modifiés ne soient plus dans le buffer cache.
- Commencer une longue requête de lecture
- En parallèle, réutiliser l'intégralité de l'undo, afin que le plus vieil SCN de l'UNDO soit supérieur à celui de la requête.

La table principale qui subira le delayed block cleanout :

create table test2_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 100000;
create index test2_1555_i1 on test2_1555(id);


Les deux tables, dont la mise à jour réaffectera l'ensemble de l'UNDO :

create table test3_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 30000;

create table test4_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 30000;

On réduit la taille du buffer cache :


alter system set sga_target=0;
alter system set db_cache_size=8M;


Un UNDO tablespace special pour l'occasion (assez nettement plus gros que le buffer cache pour pouvoir en faire plusieurs fois le tour avant commit, et assez petit pour pouvoir recycler tout l'UNDO sans trop d'efforts...)

CREATE UNDO TABLESPACE smallundo2 datafile 'C:\ORACLEXE\ORADATA\XE\SMALLUNDO2.DBF' size 16M AUTOEXTEND OFF;
alter system set undo_tablespace=smallundo2

C'est parti pour préparer le delayed block cleanout :

update test2_1555 set c = 'a' where id <= 35000;
commit;
update test2_1555 set c = 'a' where id between 35000 and 70000;
commit;
update test2_1555 set c = 'a' where id between 70000 and 100000;
commit;
set autotrace traceonly statistics

Il faut maintenant une requête assez longue pour pouvoir lancer tranquilement les UPDATE parallèles.
On force la matérialisation des cross joins pour être sûr que test2_1555 ne sera pas accédé en premier :

with t     as (select /*+materialize*/ count(*) cnt from dba_objects a cross join dba_objects b cross join dba_objects c)
select * from test2_1555 a cross join t


Et pendant ce temps, sur une autre session, la réallocation de l'undo sur des tables totalement indépendantes :

UPDATE test3_1555 set c = 'a';
COMMIT;

UPDATE test4_1555 set c = 'a';
COMMIT;

Et au bout de 9 heures (j'ai peut être un peu bourriné en faisant trois cross joins...) sur la première session, la récompense :

ERREUR Ó la ligne 1 :
ORA-01555: clichés trop vieux : rollback segment no 14, nommé "_SYSSMU14$", trop petit

Ecoulé : 09 :10 :43.06


Ah, ce qu'on se marre...

 

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires