Cet article est la traduction d’un article de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici

Sur Oracle, certains concepts sont si fondamentaux qu’on doit toujours les avoir en tête à chaque fois qu’on veut étudier un problème de performance. Et voici l’un d’eux:

Les histogrammes et les bind variables existent pour des raison diamétralement opposées: sans y prêter garde, ils ne fonctionneront pas bien ensemble.

Vous utilisez des bind variables parce que vous voulez que tout le monde partage le même plan d’exécution pour une requête SQL qui va être utilisé fréquemment. Tout le monde va faire la même charge de travail avec (charge normalement faible). Son plan d’exécution sera optimal pour tout le monde. Et vous ne voulez pas ré-optimiser cette requête à chaque fois, car cette réoptimisation utiliserait à elle seule plus de ressources qu’il n’en faut ensuite pour l’exécuter.

En général on utilise beaucoup les bind variables lorsqu’on est en transactionnel (OLTP) – sauf quelques cas particuliers où on préférera des valeurs littérales.

Et vous créez des histogrammes pour des requêtes qui, mêmes si elles sont similaires, vont faire un travail très différent les unes des autres. Elles ont besoin de plans d’exécution différents. Et de toute façon le travail d’optimisation est négligeable par rapport au travail d’exécution de la requête. Alors que si on utilise un plan d’exécution qui n’est pas optimal, on peut se retrouver à gaspiller beaucoup de ressources.

En général, on a besoin des histogrammes en datawarehouse, en BI, où les requêtes peuvent être très grosses et coûteuses.

C’est là qu’est la contradiction: on a une technologie qui est censée nous donner un seul plan d’exécution partagé par tout le monde, et une autre qui elle est censée trouver pour chacun le plan qui lui convient le mieux.

Gardez celà en mémoire, et vous vous rappellerez qu’il faut être très prudent lorsqu’on met des histogrammes sur une base transactionnelle (OLTP) et qu’il ne faut pas non plus transformer absolument toutes les valeurs littérales en bind variables.

A noter les commentaires de Doug Burns et Hemant K Chitale sur le fait qu’en 10g Oracle collecte par défaut des histogrammes avec la méthode ‘FOR ALL COLUMNS SIZE AUTO’ de dbms_stats.

Mohamed Houri a traduit un chapitre de Jonathan Lewis sur le Clustering Factor qui est si important dans le coùt d’accès à une table via un index.
Voici le lien de la traduction en français: http://jonathanlewis.files.wordpress.com/2011/05/le-clustering-factor.pdf

Jonathan Lewis a mis les liens de l’article original et de la traduction sur son blog.

Ceci est une traduction de d’un ancien post de Jonathan Lewis sur forums.oracle.com, référencé récemment sur son blog. Il décrit le fonctionnement de la journalisation en mémoire (IMU – In Memory Undo), une optimisation introduite en 10g qui utilise des structures en mémoire pour diminuer la contention sur les blocs d’undo et le redo log buffer.

Le contenu des blocs d’undo et des fichiers de redo log sont quasiment les même que l’on utilise in-memory undo (et les private redo threads) ou que l’on utilise la journalisation ‘normale’.
La principale différence se trouve dans l’ordre où sont faites les choses.
Il y a aussi, avec in-memory undo, une diminution du nombre de redo records même si le nombre de change vectors reste le même.

Voici le séquencement d’une transaction courte avec gestion normale de la journalisation.

• Vous modifiez un bloc de table ou d’index. Un vecteur de changement (redo change vector) est généré pour cette modification.
• En même temps, vous devez enregistrer l’information nécessaire pour défaire (rollback) de cette modification. C’est un enregistrement d’annulation (undo record) qui est généré pour décrire ce qui a été altéré.
• Mais comme cet undo record est stocké dans un bloc d’undo (rollback segment), alors un vecteur de changement redo change vector est généré pour décrire cette modification du bloc d’undo
• Oracle combine ces deux redo change vector (vecteurs de changement du bloc de donnée et du bloc d’undo) dans en un enregistrement de redo (redo record), ce qui incrémente la statistique de session ‘redo entries’.
• Donc pour cette modification, Oracle doit acquérir de l’espace dans le tampon journalisation redo log buffer avec le latch ‘redo allocation’ et y copier l’enregistrement de redo avec le latch ‘redo copy’

Si l’on insère 10 lignes, une par une, dans une table qui a 4 indexes, alors on va générer 50 redo records et 50 undo records, et faire appel 50 fois au latches de redo: 5 redo record par ligne (un pour la table et un pour chaque index) pour 10 lignes.

Lorsque la fonctionnalité de journalisation en mémoire (in-memory undo) est activée, et parce que dans cet exemple il s’agit d’une petite transaction, voici ce qu’il se passe:

• A moment où on modifie la première ligne de la table, Oracle alloue dans la shared pool son propre buffer de redo privé (appelé redo strand) et son propre buffer de « undo ». En fait, ce buffer de « undo » contient du redo: c’est le redo qui décrit ce qui doit être modifié dans les bloc d’undo.
• Lors de la mise à jour de la table et des index, chaque change vector qui décrit la modification est écrit dans le buffer de redo privé.
• En même temps, les change vector qui décrivent le undo record correspondant sont écrits dans le buffer de « undo » privé.
• Le nombre total de change vectors, et leur contenu sont exactement les mêmes que pour les change vectors traditionnels.
• Au commit, oracle concatène ces 2 buffers pour faire un seul redo record et l’écrit dans le tampon de journalisation normal (redo log buffer)
• En même temps, ces 100 change vectors sont appliqués: 10 sur la table, 10 sur chaque index, et 50 sur les blocs d’undo. Et en dehors de cela, tout ce qui doit se faire lors d’un commit s’applique aussi.
• Le nombre de modification de blocs (« db block changes ») reste le même dans tous les cas
• La différence la plus significative dans le volume de redo généré vient de l’entête du redo record qui fait 12 octets. Avec la gestion ‘in-memory’ de l’undo il n’y qu’un seul redo record, donc un header de 12 octets, alors que la méthode traditionnelle en génère 50, donc 50*12=600 octets.

Il y a de nombreux détails et variations autour de ce qui se passe là. Par exemple au début et à la fin de la transaction, ou lorsque un des deux buffers est plein (puisqu’ils ne font que 64Ko ou 128Ko) mais la description faite ci-dessus couvre les différences essentielles.

Question: Supposons que je démarre l’instance et effectue quelques mises à jour. J’ai donc un buffer privé de redo et un buffer privé de undo, créés en shared pool. Immédiatement après le système se plante et rien n’est encore écrit dans les fichiers de redo ni dans les blocs d’undo. Dans cette situation comment fait Oracle pour récupérer les données d’undo ?

Il y a deux chose que vous devez prendre en compte dans ma description:

• la précision: ‘Il y a de nombreux détails et variations’
• la partie qui montre que les modifications faites dans les blocs tables et index est tout à la fin.

Si la session a fait un commit, elle a écrit le redo privé dans le redo thread public, qui doit être écrit sur disque avant que le commit ne soit terminé. Donc il n’y a rien de différent au niveau du recovery.

Maintenant, si la session n’a pas encore fait de commit, alors du point de vue des autres utilisateurs, rien ne s’est encore passé (ils ne sont censés voir que les effets des transactions commitées). Du coup, cela n’a pas d’importance que les redo et undo privés aient disparu.

Mais voici où ca devient plus complexe: Comment les autres sessions voient que vous êtes en train de modifier les mêmes blocs qu’elles, si vous ne les mettez à jour que lorsque vous faites le commit de votre transaction ? Comment Oracle fait pour minimiser le temps que prennent toutes les modifications de blocs qui doivent être faites lors du commit ? J’ai quelques réponses à ces questions, mais elles ne sont ni exactes, ni complètes, alors je ne préfère pas les publier.

Cependant, un point clé de ce mécanisme, c’est le fait qu’il ne s’applique qu’à des petites transactions. Les zones privées ne font que 64Ko ou 128Ko suivant qu’on est en 32 ou 64 bits, et dès que la transaction devient trop grande, Oracle les écrit dans les redo buffer et poursuit avec le mécanisme normal.