TKPROF et Explain PlanDate de publication : Août 2005
Par
Jaouad Comment tracer une session ou une instance et interpréter le résultat avec l'outil TKPROF I. Introduction II. Paramètres d'initialisation III. Tracer une session IV. Exécuter TKPROF V. Interpréter le résultat de TKPROF VI. Explain Plan VII. Trace Analyzer TRCANLZR I. Introduction
Ce document a pour but d’expliquer le fonctionnement du binaire Tkprof, ainsi que la compréhension du rapport formaté par cette commande.
Cette partie étant liée a l’explain plan et aux statistiques générées sous SQL*plus nous aborderons également ces fonctionalités. II. Paramètres d'initialisation
Avant de pouvoir formater un fichier trace, il faut pouvoir le générer. Pour ce faire une trace peut être mise en place sur une instance ou sur une session:
Trace sur session: Oracle génère un unique fichier qui contient toutes les statistiques concernant les instructions SQL exécutées pendant cette session. Tracer une instance: La base de données créé dans ce cas un fichier pour chaque processus. Tracer une instance représente une certaine surcharge, notamment en terme d’écriture sur le disque (RAID5), il vous faut donc utiliser la trace sur la session avec parcimonie et vigilance. De plus cela peut également amener une surcharge du dossier USER_DUMP_DEST par la génération de fichier trace inutile. Paramètre INIT.ora : sql_trace=true Les paramètres d’initialisation a activer pour avoir une trace: Timed_statistics Positionné à true au niveau instance (PFILE , SPFILE ou par le biais de la commande « alter system ou alter session …») , ce paramètre va permettre à Oracle de fournir des informations temporelles. Les statistiques temporelles sont exprimées en microsecondes. Dans les versions 9.2 et ultérieures les statistiques sont collectées de manière automatique pour l’instance si STATISTICS_LEVEL est à TYPICALL ou ALL. Si par contre, sa valeur est BASIC alors dans ce cas là il faut que la paramètre TRUE soit affecté à TIMED_SATISTICS. Max_dump_file_size On définit ici la taille maximale d’un fichier trace. Il est exprimé en octets. Si la trace dépasse cette taille le fichier est tronqué avec génération d'un message. Afin de ne pas être limité par ce dernier il suffit de lui donner la valeur : « UNLIMITED ». Il est également modifiable au niveau de la session par la commande : « Alter session … » User_dump_dest Ici on s’attache à la destination des rapports. Ce paramètre est positionné au niveau de l’instance dans le fichier d’initialisation. Il ne peut être modifié que part le DBA et pour toute l’instance:
Il varie en fonction des systèmes d’exploitation mais est généralement localisé dans $ORACLE_ADMIN/$ORACLE_SID/udump (norme OFA).
Ce répertoire peut être localisé grâce à la commande:
ou
Les fichiers traces
Les fichiers trace sont générés par l’OS, et ils en dépendent. Si vous activez ou désactivez plusieurs fois la trace sur une même session, vous ne faites que rajouter des informations au fichier crée initialement. leur nom est composé ainsi: <$ORACLE_SID>_ora_<Process_id> Process_id est l’identificateur du processus serveur. Celui-ci peut être retrouvé dans la colonne SPID de la vue système V$PROCESS.
Avant tout, il faut savoir que ce mode d’analyse avec un fichier n’est valable qu’en mode Dedicated (Mode dédié). En effet lorsque nous sommes en mode partagé (Shared Mode), chaque processus génère un fichier trace. III. Tracer une session
Pour mettre d’autres sessions en mode trace, consultez l'article suivant : Oradebug
Ou différents packages système appartenant à SYS:
IV. Exécuter TKPROF
Ici nous aborderons l’aspect mise en œuvre du produit, ou comment formater le fichier trace que l’on a obtenu en sortie.
TKPROF est un binaire. Il ne peut donc être appelé sous SQL*PLUS. Pour l’exécuter il suffit de lancer une fenêtre de commande et d’appeler le binaire. Ce dernier se trouve dans le répertoire suivant:
Si ce répertoire est défini dans le PATH, il suffit juste de taper TKPROF sans le chemin pour pouvoir s’en servir.
La syntaxe est la suivante: TKPROF nom_du_fichier_en_entrée nom_du_fichier_en_sortie options Pour obtenir l'aide sur la commande, il suffit de taper TKPROF sans argument. Détaillons un peu les options:
Paramètre Explain Lorsque ce paramètre est spécifié, Oracle, au moment du Tkprof, se connecte à la base et détermine le plan d’exécution. Il faut donc faire attention aux changements survenus et pouvant influencer le calcul du plan d’exécution (création d’index, calcul de statistiques …). Requêtes récursives Concernant les requêtes récursives, celles-ci sont émises par le serveur Oracle et non pas par le user. C'est par exemple le cas lorsqu’une instruction d’insertion ne dispose pas d’assez de place dans la table. Oracle émet alors une requête récursive afin d’allouer de l’espace. Il faut noter que lorsque l’option sys=no est activée, les statistiques, notamment de temps des requêtes récursives figurent avec cette dernière et non pas avec la requête principale. L’option waits est une option très intéressante mais qui n'est disponible que depuis la version 9i. Quelles options peut-on utiliser avec SORT ? Elles peuvent être découpées en quatre parties: - La phase d’analyse : (PARSE)
- La phase d’exécution : (EXEC)
- La phase d’extraction : (FETCH)
- Autre
V. Interpréter le résultat de TKPROF
Description des phases de traitement d’une requête
Les étapes de découpage d'une requête SQL
L'exécution d'une requête SQL comprend quatre phases essentielles. Avec le parse, l’exécute et le Fetch, il existe une autre phase qui intervient juste après celle de parse : la phase de Bind. Cela est logique car ainsi oracle partage le même plan d’exécution pour les requêtes bindées.
Concernant la phase de PARSE , il en existe deux types : « Hard et Soft parse »
Les deux types de PARSE
Lorsque l’option AGGREGATE est positionnée à YES, un nombre élevé de PARSE indique une phase d’analyse à chaque instruction. Cela peut être symptomatique de requête sans BIND variable, ou alors un nombre omportant d’invalidations (calcul de statistiques, création suppression d’index ..). Les statistiques de suivi:
Remarques: Ici on prendra toujours l’habitude d’additionner les valeurs de Query et Current. Ces étapes correspondent respectivement aux parties : Consistents Gets et DB Blocks gets de l’explain plan. Disk correspond quand à lui à la partie physical Reads. Lorsque dans la commande de TKPROF, le paramètre EXPLAIN est renseigné il y a alors une connexion à l’instance et pour chaque instruction, Oracle va déterminer le plan d’exécution. Ce point peut être d’une certaine importance : Admettons par exemple que l’on trace une requête le jour j, que l'on revienne le jour j+1 et que l’on lance le formatage du rapport avec cette option, on n’est pas à l’abri d’avoir un plan d’exécution qui diffère du plan d’exécution choisi par Oracle le jour de l’exécution de la requête. Cela peut être dû à plusieurs facteurs : suppression ou création d’index , calcul de statistiques … Si ce paramètre n'est pas utilisé alors c’est l’explain plan réel qui est utilisé. Colonne Rows : la valeur est bien évidemment à diviser par le nombre d’exécutions (sauf dans le cas de aggregate=no ). Cette colonne est toujours à mettre en évidence avec le nombre de buffers inspectés et décrits dans les colonnes Current et Query. Un nombre important de blocs inspectés pour un nombre relativement restreint de lignes traitées peut être symptomatique d’une absence d’index . Colonne Elapsed : Ici on vérifie bien que le temps total CPU d’une requête (exécution, ouverture de fichier, etc.), n’est pas éloigné du temps d’exécution de la requête (colonne Elapsed), cela peut mettre en avant certaines contentions dues à l’OS, CPU trop sollicité, beaucoup d’I/O, répartition physique mal adapté. Lorsque dans le rapport, l’explain plan est activé, une colonne ROWS apparaît en indiquant le nombre de lignes manipulées pour chaque étape. Il est parfois intéressant de voir combien chaque étape manipule de lignes et les renvoie au niveau supérieur. Je m’explique : Si une sous partie manipule 10 000 lignes mais que la partie supérieure n’en manipule plus que 2 000 lignes, alors dans ce cas la il faut s’interroger:
Le rapport fournit des informations précieuses. tout d’abord il indique quel est le mode statistiques choisi (notamment si celui ci a été modifié pour la session, l’ID et le nom du schéma utilisé durant cette trace (le user SYS a toujours un ID égale à 0 permettant ainsi de définir les requêtes récursives). Mais également un récapitulatif pour les opérations récursives et non récursives . VI. Explain Plan
Création de la table explain plan Avant d’activer l’explain plan sur une session, il faut évidement créer la table qui va accueillir les informations. Oracle met à notre disposition un script situé dans $ORACLE_HOME/rdbms/admin et qui s’appelle UTLXPLAN.sql. Ce script crée une simple table nommée PLAN_TABLE. Après avoir créé cette table, le DBA doit pouvoir accorder le rôle PLUSTRACE aux utilisateurs afin que ces derniers puissent accéder à différentes tables dynamiques. Ce rôle peut être créé par le script « plustrce.sql » présent dans le même répertoire. A partir de la version 9i il existe une vue appelée V$SQL_PLAN, qui est une vue de performance et qui permet de connaître l’explain plan des curseurs exécutés récemment. Il existe deux manières de connaître l’explain plan sous SQL*plus:
Commande EXPLAIN PLAN Sous SQL*PLUS, il suffit de faire précéder la requête par les mots clés : explain plan for
Syntaxe de la commande: EXPLAIN PLAN set statement_id =’identifiant’ into nom_plan_table for requête ; Pour pouvoir extraire ce plan il existe un script sous Oracle: $ORACLE_HOME/rdbms/admin/utlxpls.sql Ce qui donne pour une version supérieure à la 8i;
Remarques: Ici nous voyons que le calcul du coût CPU est désactivé. Il est possible de modifier ce comportement en calculant des statistiques système. Pour afficher les informations concernant les « PARALLEL QUERY » utilisez la syntaxe suivante:
Définitions de COST, CARD et BYTES Cost : C’est le coût. Ici il n’y a pas d’unité de valeur, d’ailleurs il faut prendre avec précaution la valeur, et y préférer plutôt la lecture du plan. Ici il n’y aura de valeur que pour le CBO. Attention, si nous sommes en mode CHOOSE et que la requête fait appel à plusieurs tables dont une n’a pas de statistiques calculées, Oracle choisira quand même le mode CBO Cardinality ( Card ) : Le nombre de lignes qu’Oracle pense transférer. Ici il faut vérifier que ce nombre est en adéquation avec le nombre réel de lignes ramenées. Ici il s’agit d’une estimation basée sur les statistiques de la table, d’où l’importance de calculer ces statistiques. Bytes : Nombre d'octets qu’oracle pense transférer Commande Auto trace: Dans SQL*Plus il est possible d’obtenir directement le plan d’exécution et quelques statistiques supplémentaires (que l’on retrouve dans le rapport TKPROF) en utilisant la commande AUTOTRACE. La commande s’utilise tout simplement en tapant : set autotrace. Saisie sans argument, elle permet d’obtenir la syntaxe.
Détaillons ces options: OFF : Désactive le suivi d’exécution automatique des instructions SQL. ON : Active le suivi d’exécution automatique des instructions SQL. TRACEONLY : active le suivi d’exécution automatique des instructions SQL et supprime la sortie des instructions. EXPLAIN : Affiche les plan d’exécutions mais pas les statistiques. STATISTICS : Affiche les statistiques mais pas les plans d’exécution. Si les deux dernières options sont omises, les statistiques et les plans d’exécution sont affichés par défaut. Je conseille également d’activer le TIMING lorsque l’on active l’explain plan sur la session. Cela permet de connaître le temps précis d’une requête. (Ici le temps est exprimé en centième de seconde) Cela s’active tout simplement par:
Résultat d'une commande En activant seulement la trace :
La partie statistiques: consistent gets et db block gets : Tout comme l’explain plan il est important d’additionner les valeurs de « consistent gets » et « db block gets » pour avoir le nombre de buffers lus logiquement. Une autre croyance qui est : « Soit nous avons des lectures logiques soit nous avons des lectures physiques ». C’est Faux. En effet, lorsqu’Oracle a besoin d’un bloc de données, si celui ci se trouve en mémoire, alors il y accède avec une lecture logique, sinon il effectue une lecture physique, pour le placer en mémoire puis effectue une lecture logique pour le lire finalement. Cependant il peut exister des lectures physiques sans lecture logiques (Tablespace temporaire , lecture de LOB ) Une autre précaution à prendre lorsque l’on est en phase de Tuning , et que l’on lance la même requête plusieurs fois , les blocs sont déjà en mémoire. 0 recursive calls et 0 redo size : Cela est logique car ici il s’agit d’une opération de sélection ne nécessitant aucun accroissement ni aucune écriture dans le fichier de journalisation. C’est différent dans le cas d’une opération d’insertion .Sauf dans la cas suivant: Lors de la modification d'un block, si le dirty block a déjà été écrit sur le disque, alors dans ce cas le prochain processus qui va "visiter" ce block va vérifier dans l'entête du header du segment d'annulation et voir qu'un changement a été effectué et comité. Le processus obtient, de l'entête du segment UNDO, le SCN du commit et l'écrit dans l'entête du block de données qui a été modifié. Si l'opération est un select, alors dans ce cas là il peut y avoir une génération de REDO.
sorts (memory) et sorts (disk) : Indique le nombre de tris effectués en mémoire et sur le disque.
Surveillez le nombre de ces tris et les paramètres de la zone de tri (ainsi que la méthode de jointure utilisée). Pourquoi un index B-tree n’est pas utilisé ? Ici nous allons nous interroger sur l'inutilisation d’un index, alors que celui existe et que les statistiques sont bonnes : - Where colonne is NULL : En effet l’index B-tree étant un index composé en arbre ( vue schématique ) , il ne stocke pas les valeurs nulles :
Where fonction( colonne ) = 1 : Ici l’index ne sera pas utilisé à moins d’avoir un index basé sur les fonctions , Il faut préférer dans ce cas déporter la fonction sur la partie droite de la requête afin de bénéficier de l’index : Where colonne = fonction(valeur)
Where colonne like ‘%valeur’ : ici l’index n’a pas de point d’entrée dans l’index b-tree et ne peut pas à ce titre utiliser l’index ( Par contre , sur le même principe cette requête passera par l’index : Where colonne like ‘valeur%’).
Where colonne_texte = 7500 : ici l’index ne sera pas utilisé car le critère de recherche n’est pas considéré comme caractère. Par contre dès que celui a des guillemets, l’index est pris en compte.
Where colonne != valeur : ici l’index ne sera pas utilisé à moins qu’il est beaucoup de 7500 et peux d’autres valeurs :
Par contre si nous reprenons le même exemple en insérant à chaque fois un compteur
VII. Trace Analyzer TRCANLZR
TRCANLZR est un outil qui permet l’interprétation des traces SQL avec des variables liées (bind variables) et /ou des évènements d’attentes : WAITS, générés par les traces avec l’évènement 10046 :
Plus précisément, un rapport HTML va être généré, rapport qui reprend les informations contenues dans le rapport TKPROF en incluant d’autres données pertinentes telles que:
Cet outil, disponible à partir de la 8i, n’est pas un binaire comme TKPROF mais tout comme STATSPACK, est un ensemble d’objets contenus dans un schéma : TRCANLZR. Cependant à l’instar de ce dernier, il peut être exécuté de n’importe quel autre schéma sans droit spécifique.
Installation de TRCANLZR Avant de commencer l’installation, il faut télécharger un fichier zippé ici. Note 224270.1 (le téléchargement s’effectue via Métalink, il faut donc avoir un compte pour pouvoir s’y connecter) Version 9i et supérieure : trca.zip Version 8i: trca_old.zip L’installation s’effectue avec un user ayant le privilège SYSDBA, et démarre ainsi :
Le fichier tacreate.sql crée un utilisateur nommé TRCANLZR, avec tous les objets nécessaires:
Durant cette installation, on vous demandera un mot de passe, ainsi qu’un tablespace temporaire et par défaut pour TRCANLZR:
Remarques:
Le script TACREATE.SQL appelle trois autres scripts, tacuser.sql, tactab.sql et tacpkg.sql. Chaque script créé un fichier de sortie listant les différentes erreurs et dont l’extension est .lis Voici un exemple du tacpkg.lis (script qui crée les packages TRCA$D et TRCA$I):
Remarques: Dés que le script créé le USER applicatif, la fin de l’installation s'effectue avec ce dernier. Erreurs durant l’installation: Si durant l’installation se produisent des erreurs, par exemple l’installation s’effectue avec « server manager » au lieu de sql*plus, alors il est possible d’effectuer une désinstallation avant de relancer correctement l’installation.
Puis relancer la création.
Se servir de TRACE ANALYZER: Pour cela, il suffit de se connecter au schéma qui a effectué la trace et de lancer la procédure de génération.
Remarques:
Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur.
La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.
|