Mohamed Houri’s Oracle Notes

May 28, 2013

Jonathan Lewis philosophies : french translation

Filed under: French-Translation — hourim @ 11:42 am

Les lecteurs francophones peuvent trouver, ci-dessous, une traduction de presque toutes les philosophies de Jonathan Lewis.

Philosophie 1

Il existe certains concepts Oracle très importants si bien qu’ils doivent être gravés dans votre mémoire voltigeant  autour de vos yeux à chaque fois que vous voulez investiguer un problème de performance en SQL. Voici un de ces concepts :

Les histogrammes et les variables de liaison existent pour des raisons diamétralement opposées ; ils ne vont pas fonctionner ensemble sans aide.

Vous utilisez les variables de liaison parce que vous voulez que tout le monde partage le même curseur enfant (child cursor) d’une instruction SQL ; cette instruction SQL, allant être utilisée très fréquemment, tout le monde va accomplir la même petite charge de travail en l’exécutant. Le même plan d’exécution devrait être idéal pour tous, et vous ne voulez pas ré-optimiser cette instruction SQL à chaque réutilisation parce que le coût de son optimisation va être probablement plus élevé que ne le serait celui des ressources nécessaires pour son exécution. Typiquement, on souhaite une large (mais pas exclusive) utilisation des variables de liaison dans des systèmes OLTP.

Vous créez des histogrammes parce que des instructions SQL, qui sont virtuellement identiques, génèrent des charges de travail qui diffèrent énormément, nécessitent des plans d’exécutions différents, le travail fait lors de leur optimisation est négligeable lorsqu’il est comparé à celui fait lors de leur exécution, et, si elles utilisent un mauvais plan d’exécution, elles conduiraient à une énorme perte de ressources. Typiquement, on a besoin de générer des histogrammes dans des systèmes DataWareHouse ou d’aide à la décision où les requêtes peuvent être brutales et très coûteuses.

Notez bien la contradiction : une technologie est supposée vous donner un plan d’exécution unique utilisable par tout le monde  alors que l’autre technologie est supposée donner à chacun son meilleur plan d’exécution

Garder bien cela en mémoire et vous allez être rappelé à être vigilant quant à la création des histogrammes dans des systèmes OLTP. Ainsi,  vous n’allez pas être tenté de transformer absolument chaque valeur littérale en une variable de liaison.

Philosophie 2

Voici une autre idée parmi celles qui sont tellement fondamentales que vous devez toujours les avoir à l’esprit lorsque vous travaillez avec une base de données Oracle

La stratégie fondamentale incorporée dans l’Optimisateur est basée uniquement sur deux points clés:

  • Combien de données vous voulez rapatrier ?
  • Où les avez-vous stockées ?

Si vous tentez d’améliorer la performance d’une requête SQL en jouant avec son contenu jusqu’à ce qu’elle s’exécute assez rapidement, alors vous n’êtes pas sur la bonne voie et vous perdez du temps inutilement.

Si, par contre, vous débutez votre tâche de ’’Tuning’’ en pensant à la taille du volume de données que vous êtes supposé rapatrier et à la nature de la dispersion aléatoire de ces données, alors vous allez réduire le temps nécessaire à trouver la meilleure manière d’acquérir efficacement ces données.

Philosophie 3

La performance d’une requête SQL devrait être liée à la taille du volume de données qui vous intéresse et non à la taille de la base de données.

Si ceci n’est pas votre cas alors vous avez fait une erreur dans l’implémentation physique de votre base de données. Typiquement ceci peut être lié à une pauvre indexation, mais cela peut être aussi dû à des erreurs dans le choix d’autres structures telles que le partitionnement, le clustering, IOT’s ou les vues matérialisées.

Le premier symptôme de cette erreur est la plainte suivante: “au fur et à mesure que le temps avance la base de données devient lente’’

Philosophie 4

Il existe deux aspects critiques dans l’extensibilité des applications informatiques :

  • Dans un mode mono-utilisateur : est-ce que le temps de réponse de cette année sera le même que celui de l’année prochaine (voir Philosophie – 3) ?
  • Dans un mode multiutilisateurs: est-ce que le temps de réponse va être le même lorsque le nombre d’utilisateurs double, triple, quadruple …. ?

Si vous voulez tourner cela en une informelle et compréhensible phrase, Cary Millsap en inventa une bonne au Collaborate2009 : ’’Rapide Maintenant, Rapide plus tard’’

Philosophie 5

Résolution de problème avec Statspack / AWR

Quelque chose doit être en premier dans le ‘’Top 5 Timed Waits’’…. même lorsqu’il n’y a pas de problème de performance.

Philosophie 6

Tests de conformité :

  • Avez-vous testé  en utilisant des données de qualité et  en utilisant toutes les différentes possibilités de données foireuses ?
  • Est-ce que le code fait ce qu’il doit faire… et rien de plus ?

Philosophie 7

Il n’y a pas de secrets :

Au moins, il n’existe pas de secrets impliqués dans le bon fonctionnement d’une base de données. Occasionnellement, une information nouvelle et utile est découverte ; si elle est importante,  cette information va être documentée, discutée et validée en publique. (Elle ne sera pas nécessairement documentée sur Metalink, OTN ou tahiti.oracle.com mais ceci ne fait pas d’elle un secret).

A chaque fois que je vois des personnes faire des présentations sur les ‘’secrets’’,  il s’avère qu’elles ont généralement partagé leur temps entre citer la documentation, énoncer des évidences, faire des erreurs et offrir des généralités  qui nécessitent de prudentes justifications.

J’ai une règle générale simple pour les présentations : plus leur titre est glamour, branché et excitant, moins elles ont de  chances d’être utiles (mais ceci ne va pas m’empêcher de lire le résumé – juste au cas où).

Philosophie 8

Indexes B-tree vs. Indexes Bitmap: la différence la plus critique

  • Un seul index B-tree vous permet d’accéder précisément à un petit volume de données
  • C’est la combinaison de la présence d’un ensemble d’indexes Bitmap qui offre le même degré de précision.

Vous ne devrez pas comparer l’efficacité d’un index bitmap avec l’efficacité d’un index B-tree.

(Inévitablement c’est un peu plus subtile que ça ; vous pouvez créer quelques indexes B-tree peu précis pour éviter les problèmes de verrous liés aux contraintes du type clé étrangère, l’optimisateur peut combiner des indexes B-tree etc… ; mais si vous commencez sur cette base vous allez avoir une vue rationnelle sur comment utiliser les indexes bitmap).

Note : rappelez vous aussi, que les indexes bitmap introduisent des problèmes massifs de concurrence et d’autres surplus de maintenance ; si vous les trouvez dans des systèmes OLTP il y a alors de fortes chances qu’ils y soient en train de causer des problèmes.

Mis à jour 23 déc. 2009 : J’ai écrit un article complémentaire à cette note puisque le sujet que j’ai traité ici semble avoir causé quelques confusions.

Philosophie 9

Il existe une vieille blague concernant un ingénieur, un mathématicien et un philosophe voyageant ensemble dans un train de Londres (Angleterre)  à Cardiff (Pays de Galles)***

Lorsque le train traversa la frontière, l’ingénieur jeta un coup d’œil par la fenêtre  et s’exclama : ‘’Oh, regardez ! Les moutons Gallois sont noirs ‘’.

Le mathématicien répondit :’’Non ; tout ce que vous pouvez prétendre c’est qu’il y a au moins un mouton au pays de Galles qui est de couleur noir’’

Le philosophe les corrigea tous les deux : ‘’Messieurs, tout ce que vous pouvez dire c’est qu’il parait qu’au Pays de Galles, existe un mouton qui semble être noir sur un côté.’’

(Croyez-moi, en 1970, ceci était assez drôle)

La morale de cette histoire est : le meilleur point de vue à considérer lors de la résolution des problèmes d’une base de données est celui du mathématicien ; n’allez pas, comme l’a fait  l’ingénieur, vers des conclusions extrêmes basées uniquement sur une observation ; mais ne  soyez pas, comme l’a fait le philosophe, coincé sur d’aussi petits détails de conformité théorique si bien que des hypothèses raisonnables soient mises de côté.

*** Note: Pour ceux qui ne sont pas familiers avec la géographie du royaume uni (UK) : ‘’Le Royaume uni de la Grande-Bretagne et de l’Irlande du Nord ‘’ représente l’union de l’Angleterre, de l’Ecosse (la grande partie de la moitié haute de l’ile) et du Pays de Galles (le morceau de terre à gauche en excluant la petite et fine partie du bas).

Philosophie 10

La question la plus significative à vous poser lorsque vous êtes en train de réfléchir à l’ajout d’un nouvel index est :

‘’Est-ce que cet index va éliminer significativement plus de travail que ce qu’il va introduire (au moment où cela importe vraiment) ? ‘’

Quelques exemples de ‘’moments qui importent’’

  • Insertion/suppression/mise à jour en vrac
  • Système OLTP avec une activité hautement concurrente
  • Des rapports fréquents nécessitant une très grande précision
  • Test d’acceptation pour les effets collatéraux.

Philosophie 12

Voici une description utile que j’ai récemment entendue du philosophe Daniel Dennett:

Les critères d’une bonne propagande

  1. Elle n’est pas un simple mensonge sans masque
  2. Vous devez être capable de l’énoncer très sérieusement
  3. Elle doit enlever le scepticisme sans exciter la curiosité
  4. Elle doit apparaitre comme profonde

Il semble qu’elle décrit beaucoup de choses que notre industrie publie sur internet.

Philosophie 13

Si vous lisez un commentaire du genre “ X est une mauvaise idée’’ ceci ne veut pas dire ’’ un mécanisme qui, vaguement, n’est pas ‘X’ est une bonne idée’’.

Si, par exemple, je dis

    ’’Les histogrammes ne vont pas bien fonctionner sur des chaines de caractères ayant plus de 32 octets (bytes) de longueur et généralement identiques dans leurs premiers 32 octets ’’

 Ceci n’est absolument pas équivalent à :

’’ C’est une bonne idée de créer des histogrammes sur des chaines de caractères ayant moins de 32 octets (bytes) de longueur’’

Si on était dans un monde purement mathématique on aurait invoqué une logique symbolique en montrant ceci:

(A => B) <=> (¬B => ¬A)

Qui signifie que mon énoncé est équivalant à :
  ’’ si vous avez des histogrammes qui fonctionnent bien, alors les données ne sont pas du type chaine de caractères de plus de 32 octets dont, généralement, les 32 premiers octets possèdent des valeurs identiques’’

Evidement, sous Oracle, vous allez peut-être rencontrer certaines personnes, quelque part, ayant exactement le même type d’histogramme qui semble brillamment fonctionner pour elles.  Cependant, ceci  n’aurait été  possible que parce que l’optimisateur aurait  complètement raté son arithmétique si bien qu’il leur a fourni un plan d’exécution optimal pour une raison complètement erronée. Ces personnes doivent faire attention lors d’une prochaine migration ou prochaine application d’un nouveau patch dans le cas où l’optimisateur y est amélioré.

Philosophie 14

Paraphrasant Yogi Berra:

‘’Ce n’est pas comité jusqu’à ce que ça soit comité”

Si vous vous demandez pourquoi c’est important de rappeler cet inhabituel commentaire –il répond à la question suivante  communément posée:

‘’Est-ce que les redo logs contiennent aussi bien les données non comitées que celles déjà comitées ?’’

La réponse est : oui

Lorsqu’une session est en train de créer un redo change vector elle ne sait pas si elle va finir par faire un commit ou par annuler ce qu’elle a fait (un rollback). Par contre, une session doit être capable de stocker une large liste arbitraire de vecteurs de changements (change vectors) quelque part ; cette liste doit apparaître dans le redo log (idéalement d’une manière instantanée) si la session fait un commit – ainsi Oracle évite des retards lors du commit en déposant les vecteurs de changements dans le redo log au moment de leur création***.

*** Pas strictement correct à partir de 10g et plus où Oracle a introduit un effet retardataire dont le but est de réduire la compétition (la demande) pour l’allocation des redo et lors des copies de redo latches pour de ‘’petites’’ transactions

Philosophie 15

Si vous exécutez une requête qui est supposée retourner un seul enregistrement à partir d’une très large table, et, vous disposez d’un index approprié sur cette table, vous vous attendrez probablement à ce que l’optimisateur d’Oracle identifie l’index et qu’il l’utilise. Si vous changez votre requête de telle sorte qu’elle renvoie tous les enregistrements de cette table (sans tri) vous vous attendrez probablement à ce que l’optimisateur d’Oracle choisisse un full table scan.

Ceci conduit à la très simple idée qui est souvent ignorée

“Quelques fois il suffit juste d’un enregistrement supplémentaire pour passer d’un plan d’exécution utilisant un index scan  à un plan utilisant un full table scan”

Il existe un point où l’optimisateur change d’un accès indexé à un seul enregistrement vers un accès à toute la table pour tous les enregistrements.

Si vous êtes assez chanceux et le modèle de l’optimisateur est parfait il n’y aura aucun effet significatif sur la performance, bien sûr. Mais, nous ne sommes pas aussi chanceux que ça, et c’est pour cette raison que certaines personnes finissent par poser la question : ‘Comment le plan d’exécution est devenu subitement non performant, il n’y a eu aucun changement …. sauf pour un petit supplément de données?”. Tout ce qu’il faut c’est un seul enregistrement (que l’optimisateur reconnait) pour changer d’un plan d’exécution à un autre- et parfois l’optimisateur trouve le mauvais moment pour opérer ce changement.

Philosophie 17

Vous devez comprendre l’application ainsi que ses données

Une récente question dans OTN demandant  des conseils pour faire en sorte que la partie SQL suivante s’exécute rapidement :


delete from toc_node_rel rel
where   not exists (
                    select  *
                     from    toc_rel_meta meta
                     where   rel.rel_id = meta.rel_id
                   );

Voici une très petite liste de questions sur lesquelles il faut se concentrer lors de la recherche des solutions possibles. Il existe une colonne nommée rel_id  dans les deux tables ; les colonnes ayant un “id” dans leur nom tendent à être un tout petit peu spéciales, donc est-ce que ces colonnes sont :

a)  la clé primaire d’une table et la clé étrangère de l’autre (si c’est oui dans quel sens) ?

b)  les clés primaires des deux tables ?

c)  deux clés étrangères d’une clé primaire partagée d’une autre table ?

Jusqu’à ce que vous connaissiez la réponse à ces questions vous ne pouvez pas réellement progresser dans votre cheminement vers la bonne manière d’implémenter le besoin. Et, même lorsque vous aurez eu les réponses, ceci n’est encore qu’une seule étape vers la bonne direction et  un précurseur pour le prochain lot de questions – comme “est ce que les contraintes ont été déclarées et activées ? est-ce que certaines contraintes du type clés étrangères permettent la présence de nulls ? est-ce que certaines contraintes du type clés primaires ont été validées par des indexes non uniques ?’’ . Et nous ne sommes pas encore arrivés aux volumes absolus des données,  à leurs types de regroupements et au volume de données candidat à la suppression.

Note:

Vous pouvez argumenter sur l’existence ou pas des possibilités citées dans un système proprement conçu. Soyez libre de le faire ; ce n’est pas parce qu’un concept est faux en théorie qu’il ne va se produire en pratique.

 Note 2:

Pour  une impression beaucoup plus concrète c sur la petite liste de questions :

Pour l’option  (a) PK/FK – imaginez un cas très simple d’un modèle de commande, sommes nous en train d’essayer de supprimer les produits pour lesquels il n’y a pas de commandes, ou les commandes pour lesquelles on ne stocke pas de  produits (qui n’auraient pas du être introduites dans le système si nous l’avions implémenté correctement) ?

Pour l’option(b) PK/PK – imaginez que notre simple modèle de processus de commande possède une table séparée de livraison qui clone la PK de la table de commande, allons-nous essayer de supprimer les livraisons qui n’ont pas de commande (encore une fois elles ne devraient pas exister, mais qui a dit que ce système a été bien conçus et bien implémenté voir (a)) ?

Pour l’option (c) de la FK partagée – imaginez un processus de commande différent qui permet plusieurs lignes de commandes par commande et qui clone la PK des lignes de commande  pour produire un enregistrement dans la table de livraison, allons nous essayer de supprimer les livraisons qui n’ont pas de lignes de commande (encore une autre possibilité provenant d’un système mal conçu et mal implémenté – mais je suis sûr que je ne suis pas la seule personne à avoir vu de pareils systèmes et un tel code ) ?

Philosophie 18

Une question que je me suis posé récemment est celle-ci:

Quelle est le plus mauvais tort que peut engendrer  la publication d’un article autour d’une technique (caractéristique) d’Oracle  

  1. Dire qu’un concept fonctionne alors qu’il ne fonctionne pas.
  2. Dire qu’un concept ne fonctionne pas alors qu’il  fonctionne.
  3. Dire qu’un concept fonctionne alors que dans certains cas il ne fonctionne pas.
  4. Dire que qu’un concept ne fonctionne pas alors que dans certains cas il fonctionne.

 Je ne pense pas que c’est une question à laquelle il est facile de répondre et, évidement,  ce n’est pas plus facile lorsqu’on commence à considérer le nombre de cas pour lesquels une caractéristique fonctionne ou ne fonctionne pas (combien de cas représentent “quelques cas“), et la fréquence à laquelle les différents cas peuvent apparaître.

 Je ne suis pas sûr qu’il existe une réponse correcte à cette question, par contre, en terme d’impact (temps perdu) j’ai tendance à condamner les affirmations qui stipulent que quelque chose fonctionne alors qu’elle ne fonctionne pas – imaginez les deux extrêmes scénarios suivants :

  •  Quelqu’un est en train d’essayer de résoudre un problème et trouve une publication qui offre une solution qui est supposée bien fonctionner – combien de temps va-t-il perdre en essayant de faire en sorte que cette solution fonctionne parce qu’il ‘’sait’’ qu’elle doit fonctionner.
  • Quelqu’un est tombé par hasard sur une publication qui dit que le mécanisme qu’ils ont déjà implémenté avec succès ne fonctionne pas (ils ne vont pas rechercher l’article, bien évidement, parce qu’ils ont déjà résolu le problème). Ils ne vont  perdre aucun temps – sauf s’ils décident de trouver l’erreur dans l’article.

Il existe, inévitablement,  un contre argument. Quelqu’un serait peut-être en train de chercher une idée stratégique sur comment approcher un stade majeur de leur implémentation, et écarte une ligne d’attaque très utile parce qu’une publication stipule qu’elle (ligne d’attaque) ne va pas fonctionner.  Si cela devait arriver les conséquences auraient eu un impact majeur sur la qualité et sur l’extensibilité du produit final. Par contre, je préfère penser que quelqu’un qui est en train de réfléchir stratégiquement sur des options de conception ne serait pas enclin à négliger une idée sur la base d’un seul article sauf si celui-ci contient quelques très bons arguments et bonnes démonstrations.

Peut-être que le problème n’est pas tellement ce que dit l’article mais plutôt comment il le dit. Ce n’est pas grave de vous tromper ou (comme cela arrive plus fréquemment) de vous tromper partiellement à condition que vous disposiez d’une démonstration claire du travail que vous avez fait pour arriver à votre conclusion. Si vous avez fourni des évidences (et vous êtes arrivé à le présenter proprement) ceci va fournir aux lecteurs l’opportunité de faire des observations comme ‘’ l’exemple stocke des entiers dans des colonne du type varchar2()’’, ‘’ l’exemple utilise un index à deux colonnes, mais il va avoir une seule colonne’’, ‘’l’exemple utilise le tablespsace petitfichier, mais pas grandfichier’’ et, peut-être le plus important  ‘’l’exemple tourne sur 8.1.7.4 et pas sur 11.2.0.3’’

Advertisements

December 3, 2012

Interprétation d’un fichier TKPROF

Filed under: French-Translation — hourim @ 11:08 am

J’ai été destinataire d’un fichier de trace TKPROF correspondant à une requête qui ne s’exécutait pas en un temps acceptable. Le but de cet article est de faire un résumé, étape par étape, de mes investigations relatives à la compréhension de ce problème de performance via l’analyse et l’interprétation de ce fichier TKPROF.

Tout d’abord, que pouvons-nous diagnostiquer en lisant la partie suivante extraite de ce fichier TKPROF ?

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.02          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       44     34.35     420.94     132860     161300          0         624
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       48     34.37     420.96     132860     161300          0         624

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS	
Parsing user id: 122  

Rien?

Pas vraiment. Plusieurs informations cruciales peuvent être déjà déduites des quelques lignes qui précèdent :

1. Nous avons grosso-modo le même nombre de lectures physiques (132860) que de lectures logiques (lectures à partir de la mémoire, 161300). Ceci est un symptôme de (et un pointer vers) la présence de selects agressifs (FULL TABLE SCAN) sur des tables qui doivent être examinés de plus prêt.
2. Il existe une grande différence entre le temps total consommé par la requête (elapsed : 420,96 s) et le temps consommé par la machine (CPU 34,37). Ceci veut dire que la requête a passé un bon moment dans l’attente de quelque chose que nous devons identifier.
3. La requête a utilisé un grand nombre de lectures physiques et logiques pour, à la fin, ne générer qu’un nombre très limité de lignes(624). Ce qui veut dire que la requête a commencé par manipuler un grand nombre de lignes pour enfin laisser tomber la quasi-totalité de ce volume initial. Ceci contredit un principe fondamental de la performance des requêtes ”commencer petit (volume de données) et restez petit”
4. Il y a un ”miss in Library cache during parse” qui indique la présence d’un ”hard parse” lors de l’exécution de la requête.

Avez vous noté combien d’information avons nous glanées simplement en interprétant les 4 premières lignes du fichier TKPROF ? En fait, il existe encore un point fondamental que l’on peut déduire de ces 4 lignes mais que je préfère ignorer à ce stade de l’article. J’y reviendrai dans un moment

Continuons notre investigation en abordant la partie suivante du fichier TKPROF.

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      46        0.00          0.00
  SQL*Net message from client                    46      128.81        387.60
  db file scattered read                       4844        1.28        387.95
  db file sequential read                        76        0.72          1.89

Que pouvons-nous extraire des lignes précédentes:

Rien?

Par vraiment. Oracle a passé 387,95 secondes générant les données via un accès ”full table scan” (db file scattered read, rappelez-vous de ce que j’ai mentionné plus haut à propos du nombre élevé de lectures physiques) et 387,60 secondes en attente que le client finisse de rapatrier ce qu’il a demandé (SQL*Net message from client) via 46 paquets (Times Waited) de 15 enregistrements chacun.

Vous vous demandez peut-être comment j’ai fait pour trouver que les 46 paquets de données sont formés de 15 enregistrements chacun? C’est l’information que je pouvais avoir dès le début du fichier trace et que j’ai préférée garder sous silence jusqu’à maintenant.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.02          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       44     34.35     420.94     132860     161300          0         624
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       48     34.37     420.96     132860     161300          0         624

Oracle a généré 624 lignes via 2 exécutions en utilisant 44 fetches :

rows/Fetch = 624/44=14,18 ~ 15

Ce nombre représente la valeur par défaut du paramètre ”arraysize” utilisé pour échanger de l’information entre le client et le serveur.

Observons bien: le temps consommé par Oracle générant les données est égal au temps qu’a nécessité le rapatriement de ces mêmes données vers le client !!! Ceci est un symptôme d’un problème au niveau de l’activité du réseau. Si on change le paramètre ”arraysize” de sa valeur par défaut 15 à une valeur plus élevée il se pourrait alors que le temps de réponse total de la requête s’améliore. Charles Hooper a écrit un article très intéressant à ce sujet.

Avez-vous réalisé combien d’informations avons-nous rassemblées jusqu’à présent ? Et rappelez-vous nous n’avons pas encore vu à quoi ressemble le texte de la requête !!!

Continuons nos investigations de ce fichier TKPROF. Que pouvons nous identifier avec la partie ”row source” ou la partie présentant le plan d’exécution reproduit ci-dessous:

Rows     Row Source Operation
-------  ---------------------------------------------------
    312  HASH JOIN OUTER (cr=80650 pr=66430 pw=0 time=216,681,765 us)
    312   VIEW  (cr=40325 pr=33215 pw=0 time=114,999,408 us)
    312    MERGE JOIN CARTESIAN (cr=40325 pr=33215 pw=0 time=114,999,403 us)
     26     VIEW  (cr=40325 pr=33215 pw=0 time=114,998,980 us)
     26      HASH GROUP BY (cr=40325 pr=33215 pw=0 time=114,998,974 us)
  19827       HASH JOIN SEMI (cr=40325 pr=33215 pw=0 time=114,911,666 us)
  45426        PARTITION RANGE SINGLE PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=112,077,047 us)
  45426         TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=112,031,597 us)
1721227        PARTITION RANGE SINGLE PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=7670 us)
1721227         TABLE ACCESS FULL ORDERS_SETTLMENT_TBS PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=7646 us)
    312     BUFFER SORT (cr=0 pr=0 pw=0 time=208 us)
     12      VIEW  (cr=0 pr=0 pw=0 time=72 us)
     12       UNION-ALL  (cr=0 pr=0 pw=0 time=54 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=2 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=2 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
    136   VIEW  (cr=40325 pr=33215 pw=0 time=101,678,318 us)
    136    SORT GROUP BY (cr=40325 pr=33215 pw=0 time=101,678,162 us)
  19827     HASH JOIN  (cr=40325 pr=33215 pw=0 time=101,446,253 us)
  45426      PARTITION RANGE SINGLE PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=98,542,613 us)
  45426       TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=98,542,597 us)
1721227      PARTITION RANGE SINGLE PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=1,729,734 us)
1721227       TABLE ACCESS FULL ORDERS_SETTLMENT_TBS PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=8491 us)

L’information ”row source operation” représente le vrai plan d’exécution suivi par Oracle lors de l’exécution de la requête. Je vais diviser ce plan (diviser pour régner) en deux parties distinctes :

Partie 1

    312   VIEW  (cr=40325 pr=33215 pw=0 time=114,999,408 us)
    312    MERGE JOIN CARTESIAN (cr=40325 pr=33215 pw=0 time=114,999,403 us)
     26     VIEW  (cr=40325 pr=33215 pw=0 time=114,998,980 us)
     26      HASH GROUP BY (cr=40325 pr=33215 pw=0 time=114,998,974 us)
  19827       HASH JOIN SEMI (cr=40325 pr=33215 pw=0 time=114,911,666 us)
  45426        PARTITION RANGE SINGLE PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=112,077,047 us)
  45426         TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=112,031,597 us)
1721227        PARTITION RANGE SINGLE PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=7670 us)
1721227         TABLE ACCESS FULL ORDERS_SETTLMENT_TBS PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=7646 us)
    312     BUFFER SORT (cr=0 pr=0 pw=0 time=208 us)
     12      VIEW  (cr=0 pr=0 pw=0 time=72 us)
     12       UNION-ALL  (cr=0 pr=0 pw=0 time=54 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=2 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=2 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)

Partie 2

     136  VIEW  (cr=40325 pr=33215 pw=0 time=101,678,318 us)
    136    SORT GROUP BY (cr=40325 pr=33215 pw=0 time=101,678,162 us)
  19827     HASH JOIN  (cr=40325 pr=33215 pw=0 time=101,446,253 us)
  45426      PARTITION RANGE SINGLE PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=98,542,613 us)
  45426       TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=98,542,597 us)
1721227      PARTITION RANGE SINGLE PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=1,729,734 us)
1721227       TABLE ACCESS FULL ORDERS_SETTLMENT_TBS PARTITION: 3 3 (cr=6870 pr=0 pw=0 time=8491 us)

Les deux parties sont passées en jointure du type ”HASH” via la première opération

Rows     Row Source Operation
-------  ---------------------------------------------------
    312  HASH JOIN OUTER (cr=80650 pr=66430 pw=0 time=216,681,765 us)

Générant 312 lignes en 216 seconds. Multipliez ceci par 2 exécutions et vous allez retrouver l’image exacte rapportée par le résumé du fichier TKRPOF ; c’est-à-dire 624 lignes en 420 secondes.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.02          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       44     34.35     420.94     132860     161300          0         624
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       48     34.37     420.96     132860     161300          0         624

En observant attentivement les deux parties, on peut très facilement constater que les opérations les plus gourmandes en temps de réponse sont respectivement :

Rows     Row Source Operation
-------  ------------------------------------------------------------------------------------------------------
45426         TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=112,031,597 us)
45426       TABLE ACCESS FULL ORDERS_MANAGMENTS PARTITION: 10 10 (cr=33455 pr=33215 pw=0 time=98,542,597 us)

Qui consomment 112 et 98 secondes chacune respectivement.

Ces deux opérations à elles seules nécessitent 200 seconds alors que le temps total consommé par l’entièreté des opérations contenues dans le plan d’exécution est de 216 secondes environ. Il est donc plus qu’évident que revoir les deux accès à ces deux tables peut conduire à un meilleur temps d’exécution.

Vous avez peut-être déjà constaté l’opération MERGE JOIN CARTESIAN faite sur une table directrice générant 26 lignes. Je reviendrai à cette situation plus tard dans un autre article.

Que pouvons-nous encore interpréter ? Avons-nous atteint le point où le contenu de la requête devient essentiel ?
Pas vraiment. Mais nous sommes très proches de la limite de cette nécessité.

En observant le nombre de lignes générées par l’opération TABLE ACCESS FULL (45426) une question s’est imposée d’elle-même : combien de lignes contient cette table ?

select count(*) from ORDERS_MANAGMENTS  ----> 8,999,341 

Remarquez l’évidence : cette opération produit 45,426 lignes à partir d’une table contenant 8,999,341 lignes en utilisant un FULL TABLE SCAN pour cela ; 0,005% de lignes via un FULL TABLE SCAN !. Quelque chose est à revoir ici.
A partir de là, nous ne pouvons pas aller plus loin dans nos investigations sans analyser le contenu de la requête, le type de tables, les indexes qui lui sont liés, les différentes where clauses appliquées, etc…

Ceci sera traité dans le prochain article.

February 11, 2012

Clustering Factor explored in Moliere Language

Filed under: French-Translation — hourim @ 3:15 pm

It’s has been a great pleasure for me to translate, from English to French, Chapter 5 ( Le-clustering-factor ) of Cost-Based Oracle Fundamentals Book written by Jonathan Lewis. Although I do prefer the original version to the translated one, I was surprisingly happy to see that many French oracle colleagues have been  interested by this translation. May be a sign for a new carrier 🙂

April 16, 2011

Séminaire Oracle Lille 14-04-2011 : Cloud computing et Exadata

Filed under: French-Translation — hourim @ 7:23 am

La présentation était plus axée sur ce que les ingénieurs avant vente d’Oracle appellent le cloud computing exadata ne représente qu’une partie de cette nouvelle architecture qui sera ‘bientôt’ annoncée par Oracle.

Grosso-modo, le représentant parisien d’Oracle a parlé de la stratégie Oracle qui est passée de (8i, 9i) vers (10g, 11g) et bientôt vers 12c (c venant du cloud computing). Il a forcé le trait sur les concepts suivants :

  1. Saas : Software as a service
  2. Paas : Platform as a service        ——>        à c’est à ce niveau là qu’intervient exadata
  3. Iaas : Infrastructure as a service

Quant à Exadata, il faut principalement savoir que c’est une machine créé suite au rachat de SUN. Cette machine a une capacité de stockage modulable qui peut atteindre 20 Térabytes.  Elle est livrée avec une base de données 11gR2 déjà préinstallée.  En somme, elle contient la base de données et la machine Unix de stockage qui va avec. Lorsqu’on achète cette machine Exadata, vu qu’elle est paramétrée préalablement par Oracle, sa configuration est donc connue du support ce qui facilite la résolution des problèmes.

Un détail important : cette machine coute 400.000€ (si j’ai bien entendu le prix annoncé lors de cette présentation).  On peut se contenter d’une machine exadata de 4T ou de 9T ; dans ce cas je ne sais pas si le prix sera revu à la baisse ou pas. Il me semble aussi qu’on peut augmenter la capacité de stockage très facilement.

Commercialement, il y a trois clients dans le nord de la France qui ont déjà migré vers exadata, carrefour (qui a multiplié par 5 ses temps de réponses), la redoute et un client de la SNCF. Il a parlé de certains clients en Belgique aussi.

Au niveau national, la BNP Paribas a multiplié par 17 la performance de ses applications.

Enfin, ramené à notre niveau de technicien, notre intérêt devra être concentré sur la compréhension du fonctionnement d’exadata par rapport à une base de données classique. Il semblerait qu’exadata favorise les fulls table scan par rapport aux index scans. Une des questions qu’il va falloir donc se la poser lors d’une migration vers exadata est la suivante : faut-il migrer les indexes ?

Le représentant d’Oracle a passé du temps à parler d’Amazon EC2 qui est compatible avec Oracle 11gR2 XE pour ceux qui veulent s’initier au cloud computing

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)