Construire des requêtes SQL efficaces: Une approche visuelle, d’après Jonathan Lewis

Cet article est la traduction d’un article de Jonathan Lewis. L’article original en anglais se trouve ici.
J’ai cependant traduit l’exemple de requête dans une syntaxe Oracle, et l’idée d’index cluster de SQL Server correspond à celle d’IOT sous Oracle.

Jonathan Lewis décrit ici une démarche visuelle pour comprendre une requête SQL en faisant un schéma des tables impliquées.

C’est parfois intéressant de s’éloigner du clavier lorsqu’on se bat avec une requête peu performante et assez complexe, et de prendre un papier et un crayon à la place. En faisant un schéma qui montre les tables impliquées, les jointures, les volumes de données manipulées, et les indexes, vous pourrez comparer plus facilement l’efficacité des différents chemins d’exécution que la requête peut prendre pour passer d’une table à une autre.
Lire la suite

Compression SQL*Net, par Jonathan Lewis

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

Il montre un fonctionnalité peu connue: la compression des données transférées par SQL*Net lorsque il y a répétition de valeurs d’une ligne à l’autre. Et l’avantage de récupérer ces données triées sur ces colonnes répétitives.

Voici une petite démonstration que j’avais l’intention d’écrire depuis ces dernières années.
C’est très simple: je crée une table, et je l’interroge plusieurs fois.

Lire la suite

WAITING, WAIT_TIME et SECONDS_IN_WAIT, par Jonathan Lewis

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

Il précise la signification des colonnes WAIT_TIME et SECONDS_IN_WAIT dans V$SESSION et V$SESSION_WAIT, en fonction de la colonne STATE
Ces vues donnent un moyen rapide pour diagnostiquer une session qui semble bloquée, et savoir si elle est en attente d’un appel système (wait events) ou en CPU.

Un post récent sur le forum OTN fait le commentaire suivant: “le wait event est: latch shared pool, et la CPU est à 100%, il tourne depuis quelques heures et semble bloqué…”

Cela me rappelle une erreur commise fréquemment lorsque les gens regardent la vue V$SESSION_WAIT s’ils oublient de regarder la valeur de la colonne STATE.

Ce que vous devriez vérifier se trouve dans la requête suivante:

select sid, state, event, wait_time, seconds_in_wait from v$session_wait

Lorsque vous interprétez le résultat, souvenez-vous que la session n’est en train d’attendre que si la colonne STATE est exactement à ‘WAITING‘.
Sinon, c’est que la session est en CPU (ou pour être plus précis techniquement, dans la runqueue) et n’est pas en train d’attendre sur un wait event.

Si STATE montre autre chose que ‘WAITING‘, alors la différence entre SECONDS_IN_WAIT et WAIT_TIME donne approximativement (à 3 secondes près puisque la valeur est mise à jour à peu près toutes les 3 secondes) le temps deuis lequel la session est en CPU, ou plus précisément dans la runqueue, depuis la fin du dernier wait event.

A noter: cela vous montre que cette colonne SECONDS_IN_WAIT est très mal nommée, et devrait plutôt être appelée
‘nombre_de_secondes_depuis_le_debut_du_dernier_wait_avec_une_erreur_jusqua_trois_secondes’, mais il y a une limite de 32 caractères pour les noms de colonnes.

A noter aussi que depuis la 10g, on peut aussi faire cette requête sur V$SESSION au lieu de V$SESSION_WAIT
Lire la suite

Le parsing en quelques mots, par Jonathan Lewis

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

Il y a beaucoup de confusion sur la statistique ‘parse calls‘.
Ce qu’il est important de retenir, c’est qu’elle compte seulement une certain type d’appel à la librairie OCI (Oracle Call Interface – l’API d’accès aux internes natives d’Oracle).
La charge de travail effectuée par un ‘parse call‘ est très variable en fonction des circonstances, et parfois tellement minime qu’il n’y a pas lieu de s’en inquiéter.

Un ‘parse call‘ peut:

  • (a) Devoir optimiser la requête car il ne l’a pas trouvée après l’avoir cherchée en library cache.
  • (b) Trouver la requête en library cache, mais avoir quand même à l’optimiser pour diverses raisons. Par exemple: le plan d’exécution précédant a été vidé du cache. Ou bien le même texte de la requête s’applique à des objets différents, on fonction de celui qui l’exécute.
  • (c) Trouver la requête en library cache, et ne pas avoir à la ré-optimiser parce que le plan est toujours disponible, et que le user a les droits appropriés.
  • (d) Passer par le cache des curseurs de la session (session cursor cache) ou celui du moteur pl/sql (pl/sql cursor cache) qui permet d’utiliser un raccourci vers la requête en library cache, sans avoir à faire de recherche.

Lorsque le compteur ‘parse calls‘ est incrémenté, vous devez toujours vous demander si cet appel s’est traduit par la situation (a), (b), (c) ou (d).

Et juste pour embrouiller les choses, oracle peut compter un ‘parse count (hard)‘ sans compter un ‘parse call‘ (voir ici).

Statistiques de parsing des requêtes, par Jonathan Lewis

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

Jonathan Lewis donne la définition des statistiques qui concernent les parsing des requêtes : session cursor cache hits parse count (total) parse count (hard) execute count
Vous trouverez aussi quelques mots sur le parsing ici

Voici un extrait d’un rapport que j’ai fait récemment sur l’activité d’une session. Cherchez l’erreur:

Name                           Value
----                           -----
session cursor cache hits          3
parse count (total)                5
parse count (hard)                31
execute count                     35

Il n’y a pas d’astuce particulière pour avoir ce résultat, même si l’activité de la base de données est un peu particulière, et je n’ai pas trafiqué les chiffres. Alors comment se fait-il qu’on ait plus de ‘hard parse‘ que de ‘parses‘ ?

Je n’aime pas du tout le terme ‘hard parse‘, je ne suis pas très enthousiaste à propos de ‘soft parse‘, et je suis irrité par la confusion fréquente où l’on ne distingue pas les ‘parse calls‘ et le parsing.

Les statistiques que j’ai cité, et qui semblent bizarres, s’expliquent facilement.
parse count (hard)‘ devrait plutôt s’appeler ‘optimizations‘, et ces optimisations d’une requête peuvent être nécessaires pour l’appel de l’exécution de la requête (‘execute call‘) aussi bien que pour l’appel de parse de la requête (‘parse call‘).

Les ‘parse count (total)‘ devraient plutôt être appelés ‘parse calls‘, et il y a des ‘parse calls‘ qui ne nécessitent aucun parsing, lorsque la requête se trouve dans le cache des curseurs de la session (session cursor cache).

Tout ce que j’ai fait pour cette démonstration se trouve dans ce simple bloc pl/sql:

declare
  m_n  number;
begin
  for i in 1..30 loop
    select count(*) into m_n from t1;
    dbms_lock.sleep(1);
  end loop;
end;
/

Mais il faut noter l’appel à dbms_lock.sleep. Lorsque ce bloc a été exécuté, j’ai fait un truncate de la table t1 toutes les secondes à partir d’une autre session.

Parce que je suis en pl/sql, le select est automatiquement retenu dans le cache des curseurs, il n’est parsé qu’une fois et exécuté 30 fois, donc il n’y a pas 30 ‘parse calls‘, mais un seul ‘parse call‘ et 30 ‘execute calls

Mais parce qu’il y a les truncate à chaque fois dans l’autre session, le plan d’exécution qui été généré pour la requête au moment du ‘parse call‘ (qui a parsé, optimisé et gardé le curseur ouvert) s’est trouvé invalidé à peu près à chaque exécution.

Alors, pratiquement à chaque exécution, il a fallu réoptimiser la requếte à nouveau. Si vous vérifiez V$SQL vous verrez que le curseur fils a subi 30 invalidations et loads.

En résumé:

‘parse count (total)’ est le nombre de demande de parse (‘parse calls‘). Si la requête n’a jamais été vue avant, cela entraîne un parse et une optimisation. S’il elle a déjà été vue, celà entraine une recherche dans le ‘library cache‘ (c’est la manière de savoir qu’elle a déjà été vue) et peut entraîner un ‘cursor authentication‘. Si elle a déjà été vue, et authentifiée, et qu’elle a une entrée dans le cache de session (session cursor cache) alors l’appel de parsing ‘parse call‘ ne fait quasiment rien, parce qu’il n’y a même pas besoin de faire de recherche dans le library cache vu qu’il a été implicitement retenu.

‘parse count (hard)’ est le nombre d’optimisation qui ont eu lieu. L’optimisation peut être la conséquence d’un ‘parse call‘ ou bien d’un ‘execute call‘. Même un curseur qui a été retenu peut avoir eu son plan d’exécution invalidé, ce qui entraîne une réoptimisation à la prochaine exécution.

‘session cursor cache hits’ est le nombre de fois où un ‘parse call’ n’a eu quasiment rien à faire parce que la requête a été retenue automatiquement lorsque la couche OCI a dérecté une utilisation répétée de la requête.

Ajout:

Il y a une autre statistique dont je n’ai pas parlé. Elle n’apparaît pas souvent dans un rapport statspack, mais pour être complet, je dois le mentionner car cela peut embrouiller les choses.

Si vous exécutez une requête qui a une erreur au parsing (comme select ssdate from dual) alors l’optimiseur retourne l’erreur qui correspond (ORA-00904 dans cet exemple). La statistique ‘parse count (failures)‘ est incrémentée, tout comme ‘parse count (hard)‘.

Mais malheureusement, ‘parse count (total)‘ peut ne pas changer. C’était le cas lorsque j’ai essayé à partir de SQL*Plus en version 9.2.0.8, mais elle a été incrémentée lorsque j’ai essayé en 10.2.0.3. C’est donc une autre cas où l’on peut avoir plus de ‘hard parses‘ que de ‘parses‘.

Si vous voyez beaucoup de ‘parse count (failures)’, vous allez aussi voir les évènements de waits correspondants: ‘SQL*Net break/reset to client‘ (ou dblink, selon d’ou vient la requête).

A quoi sert l’undo (rollback segment), par Jonathan Lewis

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

Merci à Mohamed Houri qui m’a aidé pour cette traduction.

L’article précédent Undo et Redo et Recovery en quelques mots nous montre que l’information undo est contenue dans le redo en tant que change vector pour les blocks d’undo. On peut donc se demander pourquoi Oracle a introduit les tablespaces d’undo (aussi connues comme tablespace des rollback segments) pour implémenter la lecture consistante, au lieu d’utiliser seulement les redo pour retrouver une image passée des données.

Un post sur le forum OTN pose la question suivante: Puisque le redo contient à la fois l’information passée et l’information courante, pourquoi donc Oracle n’utilise pas les redo logs pour récupérer cette information ? Pourquoi avoir besoin de l’undo alors que le redo a déjà cette information ?

Ce fil de discussion a généré des réponses intéréssantes, mais las plupart de ces réponses expliquent comment l’undo et le redo fonctionnent, plutôt que d’expliquer pourquoi les architectes d’Oracle Corp. ont choisi d’implémenter l’undo et le redo tel qu’ils l’ont fait. Comme je suis assis dans un aéroport (Zurich) à attendre un avion, je vais utiliser ce temps pour partager mon opinion sur le pourquoi.

Le redo est là pour deux raisons: pour la récupération des données en cas de panne (recoverability), et pour optimiser les performances (efficiency).
Si l’on journalise chaque modification que l’on fait sur la base de données, alors on peut toujours restaurer une ancienne copie de la base et réappliquer le journal des modifications pour amener la base de données à l’état courant. C’est la récupération en cas de panne.
Maintenant, si nous faisons en sorte que ces journaux soient le premier point de protection des modifications de nos données, alors on n’a plus besoin d’écrire sur disque chaque bloc de données au moment où on le modifie, mais seulement un faible volume d’information. C’est là qu’est l’optimisation. (Cependant, le fait de regrouper toutes les modifications dans un petit volume rends les I/O plus performants, mais on introduit aussi un point de contention vu que l’on concentre une grande activité sur un petit volume).

L’undo (d’une manière ou d’une autre) doit exister si l’on veut faire des lectures consistentes (read consistency). Personne n’est autorisé à voir les modifications de nos données tant que nous ne les avons pas comittées. Et mêmes commitées, nos modifications ne doivent pas être visibles des requêtes qui ont commencé avant notre commit. Il faut donc garder l’ancienne version des données quelque part. C’est où les choses deviennent moins intuitives, mais par contre très ingénieuses avec Oracle.
Lire la suite

Undo et Redo et Recovery en quelques mots, par Jonathan Lewis

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

Lorsque vous modifiez un bloc de donnée, en modifiant une ligne d’une table par exemple, ou en marquant une entrée d’index comme supprimée, voici ce que fait Oracle:

  1. génération d’information redo sous forme d’un vecteur de modification (change vector pour décrire les modifications qui doivent être faites sur le bloc
  2. génération d’information undo pour décrire l’ancienne version des données qui vont changer.
    Cet undo est en réalité une information redo (un autre change vector) qui décrit comment créer cette information undo
  3. écriture de ces informations dans le buffer de journalisation (redo log buffer)
    Ces 2 change vectors sont regroupés (celui de l’undo est toujours en premier) en un seul enregistrement redo record
  4. la modification sur le bloc d’undo est effectuée
  5. la modification sur le bloc de données (table ou index) est effectuée

Le redo doit être écrit sur disque avant que le bloc de donnée et le bloc d’undo ne le soient. [voir note ci-dessous]
Le bloc d’undo et le bloc de donnée seront écrits sur disque plus tard, et ce même si la transaction n’est pas encore commitée.

Question: Si l’instance se plante avant que la transaction ne soit commitée, comment Oracle va pouvoir récupérer l’ancienne version des données, puisque elle a été écrasée par la nouvelle version non commitée ?
Lire la suite

Définitions Undo, Rollback Segment et Table de transactions , par Jonathan Lewis

Cet article une traduction du glossaire de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici.

  • Bloc d’entête de segment (‘Segment Header Block’ ou simplement ‘Segment Header’)
  • L’entête des segments undo (‘Undo Segment Header’)
  • Table des transactions (‘Transaction Table’)
  • Undo
  • Rollback

Lire la suite

Statistiques ‘db block gets’ et ‘consistent gets’, par Jonathan Lewis

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

L’article explique à quoi correspondent exactement db block gets et consistent gets que l’on retrouve dans les statistiques de performance à différents endroits: autotrace, statspack, AWR, V$SESSTAT

Dans tkprof, on retrouve ‘db block gets’ dans la colonne ‘current‘ (version courante du bloc) et ‘consistent gets’ dans la colonne ‘query‘ (version consistante par rapport au début de la requête)

Dans V$SQL on retrouve leur somme, sans les différencier, dans la colonne BUFFER_GETS. Leur somme est aussi souvent appelée ‘logical I/O‘ (LIO) ou ‘Lectures en mémoire tampon‘ sous OEM et s’incrémente à chaque fois qu’oracle va lire un bloc en buffer cache en passant par le ‘cache buffer chain‘, donc en nécessitant un latch – verrou sur cette structure mémoire.

db block gets et consistent gets sont deux contextes différents de ces lectures logiques: version courante ou version consistante du bloc.

Comment décrire ‘db block gets’ and ‘consistent gets’ en quelques lignes ?
Ayant posé la question sur mon blog je suppose que je dois donc donner ma version de la réponse.

Lire la suite

nettoyage de blocs (commit cleanout) et blocs sales (dirty block), par Jonathan Lewis

Cet article est la traduction d’un article de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici.
En expliquant les significations de ‘clean block’, il détaille le fonctionnement d’Oracle lorsqu’on modifie un bloc, puis lorsque ces modifications sont commitées.

Il y a une certaine confusion à propos du terme ‘clean‘ autour d’Oracle, donc j’ai pensé que je devrais écrire une note pour expliquer les différentes notions que ce mot peut couvrir lorsqu’il est appliqué aux blocs Oracle. Il y a 5 termes:

  1. clean
  2. commit cleanout
  3. block cleanout
  4. delayed block cleanout
  5. delayed logging block cleanout

Lire la suite