Histogrammes et bind variables, par Jonathan Lewis

Publié le par
Répondre

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.

Faut-il toujours indexer les clés étrangères ?

Publié le par
Répondre

Pour actualiser un peu ce blog sur lequel je n’ai rien posté depuis 1 an, voici un lien sur une présentation (en anglais) sur l’indexation des clés étrangères (foreign key):
http://prezi.com/uzdd5ttg4cu0/indexing-foreign-keys-in-oracle/

Plutôt que de la traduire, je résume ici les idées principales pour aider à la lecture pour les francophones. Et n’hésitez pas à poser des questions ici.

Comportement d’Oracle lorsqu’il n’y a pas d’index sur la clé étrangère

L’idée qu’il faut toujours indexer les clés étrangères vient des anciennes versions où on se retrouvait avec un verrou table (TM-Share) lorsqu’on on modifiait une table.
Sous Oracle 7, c’est la table parent qui est vérouillée lorsqu’on insère un enregistrement dans la table fille. Puis à partir de la 8i c’est l’inverse: la table fille est vérouillée lorsqu’on supprime un enregistrement de la table parent.

Mais cela a été vite optimisé en 9i: ce verrou est relâché dès que la ligne parent est supprimée. Le verrou ne reste pas jusqu’à la fin de la transaction. C’est la raison pour laquelle on peut lire parfois qu’il n’y a plus de problème de verrou lorsque la clé étrangère n’est pas indexée. C’est faux. C’est juste que le verrou dure moins longtemps.

Mais il ne faut pas oublier que si la clé étrangère n’est pas indexée, alors la suppression d’une ligne parent devra faire un full scan de la table fille. Parce qu’il faudra vérifier que rien ne référence le parent qu’on veut supprimer. Et si la table fille est grosse, ça peut durer longemps. Et donc le verrou va quand même durer longtemps. Et pendant ce temps, toute modification sur la table sera bloquée, même si elle n’a rien à voir avec le parent qu’on est en train de supprimer.

Sur les version courantes d’Oracle il est donc nécessaire d’avoir un index sur la clé étrangère lorsque on a les 2 conditions suivantes:

  1. On peut supprimer (delete) une ligne de la table référencée (parent). C’est aussi le cas si l’on modifie (update) la colonne référencée. Mais on ne devrait pas modifier une clé normalement. On doit aussi penser aux cas des delete cascade, au cas de merge, etc.
  2. Et si la table fille est grande et/ou a beaucoup d’activité de modification dessus. Si ce n’est pas le cas, le verrou table n’aura pas une grande conséquence. Mais si c’est le cas, on peut bloquer toute l’application pendant un long moment.

En quoi un index peut aider ?

La première chose, c’est que l’index sur la clé étrangère permettra de vérifier rapidement qu’il n’y a pas d’enregistrements qui référenceraient la ligne qu’on supprime, sans avoir à faire un full table scan. Avant tout, on crée les index pour des raisons de performance d’accès.

La deuxième chose, c’est qu’en plus, lorsque Oracle va lire cet index, il va pouvoir vérifier qu’il n’y a pas d’insert en cours, même non encore commité. Parce que un insert va toujours dans un block bien précis dans un index.

S’il n’y a pas d’index, mais seulement la table pour vérifier, alors un insert peut aller n’importe où et il faut donc verrouiller toute la table pour empêcher cela. S’il y a un index, il suffit de verrouiller le bloc d’index correspondant à la valeur du parent pour la durée de l’opération de delete.

Quel index est possible ?

Si l’on crée systématiquement un index sur toutes les clés étrangères, on va se retrouver avec de nombreux index superflus. Parce ce que ce qu’il suffit d’avoir c’est un index qui commence par les colonnes indexées.
Je rappelle l’idée: si l’index permet de retrouver directement une (potentielle) ligne fille, alors il peut aussi servir à éviter de verrouiller toute la table.

Exemple:

Sur la table suivante:

table CHILD (a,b,c) ... references PARENT(a,b)

Les index suivants permettent d’éviter les verrous table lors d’un delete sur la table PARENT:

create index on (a,b)
create index on (b,a)
create index on (a,b,c)
create index on (a,b,c) compress
create unique index on (a,b,c)
create index on (a,b,upper(c))
CHILD is an IOT with primary key (a,b,c)

Par contre, ceux-là ne peuvent pas être utilisés pour ce but:

create index on (c,b,a)
create index on (a,c,b)
create index on (a,upper(b))
create index on (a,b desc)
create bitmap index on (a,b)

il est donc important de ne pas indexer aveuglément, mais de choisir les index qui sont utiles pour les performances des accés aux données. Ensuite, on vérifie qu’ils permettent aussi d’éviter les cas de verrou table s’ils peuvent avoir lieu.

Comment vérifier quels verrous sont posés ?

On a vu que le verrou est relâché dès que l’opération de suppression est finie. il ne reste pas jusqu’à la fin de la transaction. il est donc parfois difficile de le voir en test avec peu d’utilisateurs concurrents. On peut par contre utiliser l’event 10704 pour vérifier exactement quels verrous sont posés. La présentation montre un exemple avec son interprétation.

Conclusion et conseils

Lorsque une table parent peut avoir des delete dessus, même si c’est rare, même si c’est indirect (delete cascade) alors les tables filles peuvent se retrouver verrouillées s’il n’y a pas un index qui commence par les colonnes de la clé étrangère.
Si La table fille est grande et/ou si il y a beaucoup de modifications dessus, ce verrou peut durer et peut bloquer toute modification.
Dans ce cas, il faut indexer les clés étrangère. Mais pas aveuglément: on doit réfléchir à l’ordre des colonnes, à utiliser un index qui sert aussi à autre chose, etc.

Par contre, les clés étrangères vers des tables de référence statiques n’ont pas besoin d’index. Sauf bien sûr pour des raisons d’accès si on a besoin pour une jointure entre les deux table et que la sélectivité est assez forte pour bénéficier d’un index..

En test, on peut savoir exactement si une opération est susceptible de verrouiller la table fille avec l’event 10704 . C’est important car il y a quelques cas particuliers (des bugs par exemple). au moindre doute, il faut donc tester avec votre version d’Oracle, sur votre modèle de données.

En production, il faut réagir si on voit des wait events ‘enq: TM‘ avec des verrous en mode 4 (Share) ou 5 (SSX). Même s’ils sont anodins maintenant, ils seront bloquants un jour lorsqu’il y aura plus de données ou plus d’activité.

Enfin, il ne faut pas voir la nécessité d’indexer certaines clés étrangères comme quelque chose de contraignant. Dans la plupart des cas l’index en question est nécessaire pour des raisons de performance, pour que le delete n’ait pas à faire un full scan de la table fille. Et le comportement d’Oracle est alors plutôt performant: aucun verrou n’est posé. Et cela depuis les plus anciennes version d’Oracle.

Un dernier point: si vous pensez que le mieux est de ne pas déclarer les contraintes d’intégrité référentielles en base de données, mais de les gérer au niveau de l’application, alors vous aurez des problèmes dès que vous aurez plusieurs utilisateurs concurrents. Soit vous risquez d’avoir une inconsistance de données, soit vous devrez verrouiller toute la table jusqu’à la fin de la transaction. Car les mécanismes interne d’Oracle que l’on vient de voir (relâcher le verrou avant la fin de la transaction, ou utiliser le bloc d’index pour voir les insert non commités) et qui sont très performants ne sont pas accessibles par l’application…

Clustering Factor, Jonathan Lewis traduit par Mohamed Houri

Publié le par
Répondre

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.

Les principes fondamentaux d’un datawarehouse – traitement batch, par Greg Rahn

Publié le par
1

Cet article est la traduction d’un article de Greg Rahn publié sur son blog. L’article original en anglais est: The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing. Cet article fait partie d’une série sur les principes fondamentaux des datawarehouse, mais s’applique à tous les traitements de type batch.

Durant 6 ans à faire des Proof Of Concept et des Benchmarks sur des datawarehouse pour les clients, il y a un domaine qui s’est toujours montré problématique: les traitements par lots (batch). La plupart du temps, ces batchs prennent la forme de procédures et packages PL/SQL, qui font du chargement de donnée, de la transformation, du traitement, ou quelque chose de similaire.
La raison pour laquelle c’est souvent problématique, c’est que les développeurs y ont codé en dur la lenteur du traitement. Je suis certain que les développeurs ne savaient pas qu’ils faisaient cela, lorsqu’ils ont codé leur PL/SQL, mais en tout cas, c’est ce qui est arrivé.

Alors comment ont-ils codé ‘en dur’ cette lenteur en PL/SQL ?
Lire la suite →

Droit au but en lisant un rapport AWR ou Statspack, par Franck Pachot

Publié le par
2

Il n’a a pas eu beaucoup d’articles traduits ces derniers mois. Pour patienter, voici un lien sur une présentation:
Interpreting AWR Report – Straight to the Goal en anglais, mais très visuel…

C’est parfois difficile d’aborder un rapport AWR ou Statspack qui comprends 50 pages de statistiques, dont seulement quelques unes sont utiles dans un contexte donné. J’ai vu plusieurs personnes qui ne savent pas vraiment par où commencer. Et sans une approche méthodique, c’est facile de perdre du temps sur des choses qui ne feront pas gagner un un temps significatif dans l’amélioration du temps de réponse.

Cette présentation montre une approche méthodique: en partant du temps passé en base de données (DB time) pour voir si le rapport est pertinent ou pas, puis en prenant les éléments du ‘Top 5 events’ en montrant où aller chercher les détails qui permettent de comprendre la raison d’un problème de performance, comment le résoudre, et estimer le gain en temps de réponse que peut apporter la résolution.

Redo privé et Undo en mémoire (In Memory Undo), par Jonathan Lewis

Publié le par
4

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.

Unité de temps dans les vues V$

Publié le par
3

Historiquement, les vues V$ collectent les durées en centisecondes (100ème de secondes).
Cependant, certaines vues utilisent une autre unité:

microsecondes (1000000ème de seconde):

TIME_WAITED_MICRO, WAIT_TIME dans V$SESSION_WAIT, V$SYSTEM_EVENT, V$SESSION_EVENT, V$ACTIVE_SESSION_HISTORY
CPU_TIME, ELAPSED_TIME dans V$SQL, V$SQLAREA
WAIT_TIME dans V$LATCH, V$LATCH_PARENT, V$LATCH_CHILDREN
ACTIVE_TIME dans V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE

millisecondes (1000ème de seconde):

CPU_WAIT_TIME dans V$ENQUEUE_STAT

secondes:

CTIME dans V$LOCK

La liste est non exhaustive, et les commentaires sont bienvenus pour la mettre à jour ;)

Design physique d’une table pour des performances maximales, par Tom Kyte

Publié le par
Répondre

Cet article est la traduction d’une réponse de Tom Kyte sur son site AskTom décrivant rapidement les points à considérer lorsqu’on a une table a fort volume transactionnel et forte concurrence (L’article original en anglais se trouve ici).

Question

Que puis-je faire du point de vue du design physique pour maximiser les performances et la concurrence lorsque une table va être la cible de centaines de milliers de select et probablement autour de 80000 insert, autant d’update et delete par heure, de manière transactionnels sur une base OLTP.
Ces débits de insert/update/delete sont juste un exemple. En réalité ils seront beaucoup plus élevés, même si on ne sait pas à quel point ils seront plus élevés car nous sommes toujours en phase de design.

Je suis à la recherche de quelques lignes directrices que je pourrais essayer sur mon application.

Réponse

On pourrait écrire un livre là dessus :) Le mien est ‘Expert Oracle Database Architecture’ et vous serez surement intéressé par de nombreux chapitres, plus particulièrement ceux sur les types de données, les tables et les index.

  • Vous pourriez avoir besoin de partitionner: répartir les inserts sur de nombreux segments, afin d’éviter des contentions sur la partie droite des index (sur les dates ou les séquences par exemple)…
  • Vous pourriez avoir besoin d’IOT (tables organisées index), plus lent pour les insert dans la plupart des cas, mais si vous faites des requêtes qui ramènent de nombreuses lignes qui sont arrivées dans la table à des moments différents dans le temps, l’IOT peut permettre de regrouper (cluster) ces lignes afin de rendre plus efficace le fait de les récupérer ensembles.
  • Vous pourriez aussi utiliser ASSM (Automatic segment space management) pour améliorer la concurrence, pour éviter de chercher les bonnes valeurs de PCTUSED, FREELISTS et FREELIST GROUP (mais vous devez comprendre ce qu’il y a de différent entre ASSM et MSSM…)
  • Vous pourriez chercher à comprendre comment les types de données sont stockés physiquement, réfléchir à PCTFREE, et comment maximiser les performances possibles sur les LOB, si vous les utilisez, etc.

En bref, vous voulez comprendre comment fonctionnent les choses à un certain niveau. Le concepts guide de la documentation Oracle et un bon point de départ. Si vous aimez ma manière d’écrire, vous pouvez commencer aussi par ‘Expert Oracle Database Architecture’.

Vous aurez besoin de réfléchir à la concurrence, aux choses comme ASSM, le partitionnement, voire les technique de regroupement de données (clustering): IOT, hash/btree clusters.

Vous aurez besoin de réfléchir sur l’archivage des données dans le temps.

Vous devrez peut-être envisager la nécessité de faire une réorganisation des tables à l’occasion, et donc prévoir le design qui permettra de le faire: à nouveau le partitionnement.

Fragmentation РIndex, par Jonathan Lewis (4̬me partie)

Publié le par
Répondre

Ceci est une traduction de d’un post de Jonathan Lewis sur son blog – la quatrième et dernière partie d’une série de quatre sur la fragmentation (original en anglais). Il est conseillé de lire avant: Fragmentation – Introduction, Fragmentation – Disque et Tablespace, Fragmentation – Table

Fragmentation Index

La fragmentation en extents multiples et la fragmentation due à ASSM que j’ai décrit dans la note précédente à propos des tables s’appliquent aussi aux indexes, bien sûr, et nous importe de la même manière, c’est à dire presque jamais. Lorsque les gens parlent de fragmentation d’index, ils pensent en général au problème des blocs avec un faible taux de remplissage (sparsely populated blocks) qui est aussi un phénomène que j’ai décrit à propos de la fragmentation des tables, mais il y a quelques différences entre une table et un index, que nous allons examiner tout de suite.
Il est intéressant de considérer aussi un autre sens possible pour la fragmentation d’un index, que nous allons aussi examiner: c’est l’effet de bord de la division d’un bloc feuille (leaf block splitting) qui fait que des blocs qui sont logiquement à la suite se retrouvent physiquement dispersés.
Lire la suite →