Commit : Oracle l'exécute le plus rapidement possible - Commit: Oracle executes it as quickly as possible

14 avril 2019

Commit : Oracle l'exécute le plus rapidement possible - Commit: Oracle executes it as quickly as possible

Introduction
Lire des données sur disque dur est infiniment plus lent que des lire en mémoire (à l'échelle informatique, j'entends). Par exemple, pour des disques durs à plateaux (non SSD donc) avec une vitesse de rotation de 15 000 tours minutes, une lecture de bloc demande au mieux 2 milli secondes. Pour une mémoire de type RAM, la même lecture se fera entre quelques dizaines ou centaines de nanosecondes, soit entre dix mille et cent mille fois moins que pour un disque dur.

Oracle a donc tout intérêt à tenir en mémoire un maximum de blocs de données pour accélérer ses traitements; c'est le rôle du Database Buffer Cache.

Mais, curieusement (du moins en apparence), c'est l'inverse pour le type de données le plus vital pour Oracle : les fichiers journaux, appelés Redo Logs. Ces fichiers sont les plus importants car les plus à jour : par exemple, on peut avoir des redo logs sur disque dur plus à jour que les datafiles.
Si on perd des données en mémoire non écrites sur disque dur (crash de la base), les Redo Logs enregistrés lors du Commit peuvent les recréer sans problème au redémarrage de la base; n'oubliez pas que LGWR s'exécute lors d'un Commit mais pas DBWn, donc lors d'un Commit, les dirty blocks ne sont pas écrits sur disque dur.

Oracle garde en mémoire les blocs qui, a priori, peuvent être accédés par N transactions, mais cela n'a pas de sens dans le cas des Redo Logs puisqu'ils dépendent d'une transaction et d'une seule; jamais une transaction T1 n'ira lire les Redo Logs d'une transaction T2. Il n'y a donc aucun intérêt à garder en mémoire les redo logs si on se base sur ce concept de réutilisation; c'est pourquoi leur zone mémoire (Redo Buffers) est si petite.

L'autre point, encore plus vital, est que si on garde trop de Redo Logs en mémoire et qu'il y a un crash de la base, cela peut poser un vrai problème :
     - soit le user n'a pas fait de Commit, alors la transaction est perdue car non commitée
     - soit le user a fait un Commit et le crash intervient AVANT que la totalité des redo logs de la transaction soient écrits... gros problèmes en perspective puisque au redémarrage de la base, seule une partie des redo logs qui étaient en mémoire ont été écrits sur disque dur... est-ce que Oracle récupère une partie de la transaction? Je ne pense pas, ce serait violer le D du modèle ACID; à nouveau, la transaction est perdue.

Pour cette raison, il faut donc que le Commit ait lieu le PLUS RAPIDEMENT POSSIBLE entre le moment où le user saisi la commande COMMIT et où Oracle rend la main : ce qui signifie que lors du COMMIT, Oracle ait un minimum de travail à faire, quelle que soit la taille de la transaction!

ATTENTION, je parle ici du Commit normal, pas du Commit asynchrone apparu avec Oracle 10!

Pour mettre en oeuvre ce mécanisme, Oracle gère le redo log buffer, une zone mémoire qui est minuscule et le process d'arrière plan LGWR (Log Writer) travaille en continu pour écrire les logs du redo buffer dans les redo log online sur disque dur.

Pour rappel, LGWR écrit sur disque dur le contenu du redo buffers selon les règles suivantes:
     - Toutes les 3 secondes
     - Quand un tiers du redo buffer est plein ou bien qu'il contient 1Mo de données
     - Lors d'un commit d'une transaction
     - Quand survient un redo log switch
     - Avant qu'un processus DBW n'écrive des dirty blocks dans les fichiers de données

Vous avez compris, LGWR travaille en flux tendu et dès qu'il y a assez de données en mémoire, il les écrit sur disque dur sans même attendre le Commit. S'il attendait, le risque serait d'avoir une trop grande quantité de redo logs à écrire et donc un intervalle de temps dangereux entre la saisi du Commit et la fin du traitement d'Oracle.

Points d'attention
Aucun.

Base de tests
Une base Oracle 12.

Exemples
============================================================================================
Etat de la base
============================================================================================
On a une base 12c.
     SQL> select banner from v$version;
     BANNER
     --------------------------------------------------------------------------------
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     PL/SQL Release 12.2.0.1.0 - Production
     CORE   12.2.0.1.0   Production
     TNS for Linux: Version 12.2.0.1.0 - Production
     NLSRTL Version 12.2.0.1.0 - Production

La mémoire fait 840Mo.
   Database Buffer cache : 566 Mo
     Redo Logs : 3,8 Mo soit 220 fois moins que la SGA et 150 fois moins que le buffer cache.
     0,6% de la taille du cache.
     SQL> select (3.8/566)*100 from dual
     (3.8/566)*100
     -------------
     .671

     SQL> show sga
     Total System Global Area 838860800 bytes
     Fixed Size            8798312 bytes
     Variable Size          260050840 bytes
     Database Buffers      566231040 bytes
     Redo Buffers            3780608 bytes

Un seul process LGWR actif.
     SQL> ! ps -aef | grep -i lgw
     oracle    2807     1 0 03:54 ?        00:00:00 ora_lgwr_orcl12c
     oracle    4806 4652 0 04:03 pts/0    00:00:00 /bin/bash -c ps -aef | grep -i lgw
     oracle    4808 4806 0 04:03 pts/0    00:00:00 grep -i lgw


============================================================================================
Les tests : de 1 à 10 000 000 (dix millions) d'Inserts
============================================================================================
Nos tests sont simples : insérer des lignes et vérifier, grâce à la commande SET TIMING ON, le temps pris par chaque Commit selon la quantité de données à valider.

Combien de temps prends un Commit quand on insère 1, 1000, 100 000, 1 000 000 et 10 000 000 (dix millions) de données?

1 ligne
     SQL> set TIMING ON

     SQL> BEGIN
     for i in 1..1 loop
          insert into log_speed values (i);
     end loop;
     END;
     /
     PL/SQL procedure successfully completed.
     Elapsed: 00:00:00.00

     SQL> commit;
     Commit complete.
     Elapsed: 00:00:00.00

     SQL> truncate table log_speed;


1000 lignes
     SQL> BEGIN
     for i in 1..1000 loop
          insert into log_speed values (i);
     end loop;
     END;
     /
     Elapsed: 00:00:00.06

     SQL> commit;
     Commit complete.
     Elapsed: 00:00:00.00

     SQL> truncate table log_speed;


100 000 lignes
     SQL> BEGIN
     for i in 1..100000 loop
          insert into log_speed values (i);
     end loop;
     END;
     /
     PL/SQL procedure successfully completed.
     Elapsed: 00:00:01.92

     SQL> commit;
     Commit complete.
     Elapsed: 00:00:00.02

     SQL> truncate table log_speed;


1 000 000 (un million) de lignes
     SQL> BEGIN
     for i in 1..1000000 loop
          insert into log_speed values (i);
     end loop;
     END;
     /
     PL/SQL procedure successfully completed.
     Elapsed: 00:00:18.80

     SQL> commit;
     Commit complete.
     Elapsed: 00:00:00.03

     SQL> truncate table log_speed;


10 000 000 (dix millions) de lignes
     SQL> BEGIN
     for i in 1..10000000 loop
          insert into log_speed values (i);
     end loop;
     END;
     /
     PL/SQL procedure successfully completed.
     Elapsed: 00:03:24.73

     SQL> commit;
     Commit complete.
     Elapsed: 00:00:00.06


Pour se faire une idée de la taille des données créées pour 10 000 000 de rows : la table fait 130Mo.
     SQL> select sum(bytes) from dba_segments where segment_name = 'LOG_SPEED';
     SUM(BYTES)
     ----------
     132120576

     SQL> truncate table log_speed;


On constate quoi? L'Insert passe de 0 seconde à 3 minutes 25 secondes MAIS le Commit passe de 0 secondes à 6 centièmes de secondes soit quasiment le même temps!

On a bien la preuve qu'au moment du Commit, dans le cas de mises à jour volumineuses, le travail d'écriture des redo buffers par LGWR a déjà été fait à 99% et que ce qui reste à écrire se fait en un clin d'oeil. Oracle parle d'ailleurs de FAST COMMIT dans sa doc :-)

MAIS ATTENTION, parfois la mise à jour a été si importante, qu'Oracle décide, justement pour rendre la main le plus vite possible, de ne pas faire TOUTES les opérations classiques du Commit. Par exemple il peut choisir de faire un "Delayed Cleanout"; je vous laisse chercher sur Google ce que cela signifie mais Oracle, pour accélérer le traitement du COMMIT, à décidé de décaler dans le temp le nettoyage de la zone ITL des headers des data blocks modifiés.

Voilà c'est prouvé, au moment du COMMIT, la quasi intégralité des écritures des redo logs a lieu avant que le user saisisse ce COMMIT.

Dernier point, il va de soi que si le user saisi un ROLLBACK au lieu d'un COMMIT, toutes les écritures sur disque dur représentent du travail perdu mais combien de fois faites vous un ROLLBACK par rapport à un COMMIT? Hum? Oracle a parié avec raison qu'on valide les transactions DML et qu'on les annule rarement, d'où sa politique pour un LGWR toujours actif.