TKPROF et Explain Plan

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 à l'explain plan et aux statistiques générées sous SQL*plus nous aborderons également ces fonctionnalités.

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ée 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 à 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 le 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 par le DBA et pour toute l'instance :

alter system set user_dump_dest =… ;

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 :

show parameter user_dump_dest

ou

select * from v$parameter where name ='user_dump_dest' ;

Les fichiers trace

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éé 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.

SQL> select s.username nom_utilsateur, p.spid processs_serveur, s.programm 
  2  from v$session s, v$process p 
  3  where s.username =upper('&nom_utilisateur') 
  4  and s.addr=p.addr ;

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.

SQL> alter session set sql_trace = true ;

Pour mettre d'autres sessions en mode trace, consultez l'article suivant : Oradebug

Ou différents packages système appartenant à SYS :

exec sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);

Exec SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, waits=>TRUE, binds=>TRUE);

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 :

$ORACLE_HOME/bin  

$  cd $ORACLE_HOME/bin  

$ ll tkprof

-rwxr-x--x   1 oracle     dba        8204288 Jun 18  2004 tkprof

C:\ > cd %ORACLE_HOME%/bin

C:\oracle\ora92\bin>dir tkprof*
Répertoire de C:\oracle\ora92\bin

28/04/2002  11:07            16 656 tkprof.exe

Si ce répertoire est défini dans le PATH, il suffit 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

Description des phases de traitement d'une requête

Les étapes de découpage d'une requête SQL :

• 1) Ouverture ;
• 2) Analyse ou phase de « Parse » ;
• 3) Attachement ou « Bind » ;
• 4) Exécution ;
• 5) Extraction ou « Fetch » ;
• 6) Fermeture.

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.

• PARSE
  Cette étape convertit l'instruction SQL en plan d'exécution.
  Elle vérifie également que l'utilisateur bénéficie bel et bien des autorisations appropriées et que les tables, colonnes et autres objets de référence existent. Il a également une vérification syntaxique.
• EXECUTE
  Cette étape correspond à l'exécution effective de l'instruction par le serveur Oracle.
  - Insert, update et Delete cette étape modifie les données (et effectue les tris nécessaires).
  - Select : cette étape identifie les lignes concernées.
• FETCH
  Cette étape extrait les lignes par une requête et effectue le tri nécessaire.
  Cette partie ne concerne donc que les instructions SELECT.

Concernant la phase de PARSE, il en existe deux types : « Hard et Soft parse ».

Les deux types de PARSE

• Le hard parse est une phase complète de parse (analyse…).
• Le soft parse réapplique un hard parse encore présent en mémoire (library cache)..

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êtes sans BIND variable, ou alors un nombre important 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 quant à 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 qu'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ée.
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 2000 lignes, alors dans ce cas-là il faut s'interroger :

• sur la présence ou non d'un index ?
• est-ce que cet index est défini de manière correcte (peut-être doit-il incorporer plus de lignes) ?
• est-ce que la méthode de jointure est correcte (notamment les merges… ) ?

Le rapport fournit des informations précieuses. Tout d'abord il indique quel est le mode statistique 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 égal à 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.

Création de la table explain plan

Avant d'activer l'explain plan sur une session, il faut évidemment 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.

À 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 ;
• activer l'autotrace.

Commande EXPLAIN PLAN

Sous SQL*PLUS, il suffit de faire précéder la requête par les mots clés : explain plan for

SQL> explain plan for select * from scott.emp ; 

Explicité.

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 :

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   518 |     2 |
|   1 |  TABLE ACCESS FULL   | EMP         |    14 |   518 |     2 |
--------------------------------------------------------------------
Note: cpu costing is off

9 ligne(s) sélectionnée(s).

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 :

select plan_table_output from table(dbms_xplan.display()) ;

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.

SQL> set autotrace 

Syntaxe :SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

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 plans d'exécution, 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 :

SQL> set timing on ;

Résultat d'une commande

En activant seulement la trace :

SQL> set autot trace 
SQL> select emplid, comprate from ps_job where emplid in (select emplid from ps_names where name 
  2  like 'ZO%' ) order by emplid  ; 

158 ligne(s) sélectionnée(s).

Ecoulé : 00 :00 :00.05

Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=17 Bytes=340
         )
   1    0   SORT (ORDER BY) (Cost=34 Card=17 Bytes=340)
   2    1     NESTED LOOPS (Cost=30 Card=17 Bytes=340)
   3    2       VIEW OF 'VW_NSO_1' (Cost=6 Card=6 Bytes=42)
   4    3         SORT (UNIQUE) (Cost=6 Card=6 Bytes=150)
   5    4           INDEX (RANGE SCAN) OF 'PS0NAMES' (NON-UNIQUE) (Cost=2 Card=6 Bytes=150)
   6    2       TABLE ACCESS (BY INDEX ROWID) OF 'PS_JOB' (Cost=4 Card=84288 Bytes=1095744)
   7    6         INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE) (Cost=2 Card=84288)

Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        227  consistent gets
         14  physical reads
          0  redo size
       3694  bytes sent via SQL*Net to client
        616  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
        158  rows processed

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 logique (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 le 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.

SQL> insert into formation.DVP values ('a',1)  ;

1 ligne créée.

Ecoulé : 00 :00 :00.03

Plan d'exécution
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE


Statistiques
----------------------------------------------------------
         18  recursive calls
          3  db block gets
          5  consistent gets
          1  physical reads
        664  redo size
        461  bytes sent via SQL*Net to client
        330  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

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 :

SQL> select * from dvp where a= 255 ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (UNIQUE SCAN) OF 'DVP' (UNIQUE) (Cost=1 Card=1 Bytes=3)


SQL> select * from dvp where a IS NULL ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   TABLE ACCESS (FULL) OF 'DVP' (Cost=1 Card=1 Bytes=3)

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)

SQL> Create index index_name on table_name (function(colonne)) ;

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%').

SQL> SELECT *
  2      FROM ps_personal_data
  3      WHERE emplid  like  '%021131' ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=143 Card=1438 Bytes=503300)
   1    0   TABLE ACCESS (FULL) OF 'PS_PERSONAL_DATA' (Cost=143 Card=1438 Bytes=503300)

SQL> SELECT *
  2      FROM ps_personal_data
  3      WHERE emplid  like  '021131%' ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=700)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PS_PERSONAL_DATA' (Cost=3 Card=2 Bytes=700)
   2    1     INDEX (RANGE SCAN) OF 'PS_PERSONAL_DATA' (UNIQUE) (Cost=2 Card=2)

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.

SQL> desc dvp ; 

 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(10)

SQL>  select * from dvp where a=200 ;

A
----------
200


Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=10 Bytes=30)
   1    0   TABLE ACCESS (FULL) OF 'DVP' (Cost=1 Card=10 Bytes=30)

SQL> 

SQL> select * from dvp where a='200' ;

A
----------
200

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (RANGE SCAN) OF 'DVP' (NON-UNIQUE) (Cost=1 Card=1 Bytes=3)

Where colonne != valeur : ici l'index ne sera pas utilisé à moins qu'il est beaucoup de 7500 et peu d'autres valeurs :

SQL> create table dvp (a number) ; 

Table créée.

SQL> begin 
  2  for i in 0..1000
  3  loop 
  4  insert into dvp values (7500) ; 
  5  end loop; 
  6  commit  ;
  7  end ; 
  8  /

Procédure PL/SQL terminée avec succès.

SQL> begin 
  2  for i in 0..10
  3  loop 
  4  insert into dvp values (100) ; 
  5  end loop; 
  6  commit  ;
  7  end ; 
  8  /

Procédure PL/SQL terminée avec succès.

SQL> begin 
  2  for i in 0..12
  3  loop 
  4  insert into dvp values (50) ; 
  5  end loop; 
  6  commit  ;
  7  end ; 
  8  /

Procédure PL/SQL terminée avec succès.

SQL> create index dvp on dvp (a) ;

Index créé.

SQL> analyze index dvp compute statistics ; 

Index analysé.

SQL> analyze table dvp compute statistics ; 

Table analysée.

SQL> set autot trace exp 

SQL> select * from dvp where a != 7500 ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=684 Bytes=1368)
   1    0   INDEX (FAST FULL SCAN) OF 'DVP' (NON-UNIQUE) (Cost=1 Card=684 Bytes=1368)

Par contre si nous reprenons le même exemple en insérant à chaque fois un compteur

SQL> create table dvp (a number, b varchar2(10)); 

Table créée.

SQL> begin 
  2  for i in 0..1022
  3  loop 
  4  insert into dvp values (i,'a') ; 
  5  end loop; 
  6  commit  ;
  7  end ; 
  8  /

Procédure PL/SQL terminée avec succès.

SQL> create index dvp on dvp (a) ;

Index créé.

SQL> analyze index dvp compute statistics ; 

Index analysé.

SQL> analyze table dvp compute statistics ; 

Table analysée.

SQL>  select * from dvp where a != 200 ;

Plan d'exécution
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1022 Bytes=4088)
   1    0   TABLE ACCESS (FULL) OF 'DVP' (Cost=1 Card=1022 Bytes=4088)

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 :

• I/O
• Bloc chaud
• Valeur des Binds variables
• Les latches
• Explain plan
• Les statistiques CBO
• (…)

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.

SQL> create user DVP identified by DVP ;
 
Utilisateur crée.
 
SQL> grant  create session to dvp ;
 
Autorisation de privilèges (GRANT) accepte.
 
SQL> conn dvp/dvp 
Connect.
SQL> set serverout on ;
SQL> exec trca$i.trace_analyzer('test.txt') ;
 
Trace Analyzer Report trcanlzr_0_2.html has been created in
c:\oracle\admin\oidprd\udump
 
Procédure PL/SQL termine avec succès.
 
SQL>

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 :

SQL*Plus: Release 9.2.0.1.0 - Production on Me Aou 3 12:04:25 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> set instance oidprd

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> connect /as sysdba

Connect.

SQL> @tacreate.sql

Le fichier tacreate.sql crée un utilisateur nommé TRCANLZR, avec tous les objets nécessaires :

• les tables TRCA$ ;
• Index, séquences, vues, et package TRCA$I et TRCA$D.

Durant cette installation, on vous demandera un mot de passe, ainsi qu'un tablespace temporaire et par défaut pour TRCANLZR :

SQL> @tacreate.sql
... Creating TRCA$ UDUMP Directory

... Creating TRCANLZR user...

Choose the TRCANLZR user's password.

Not specifying a password will result in the installation FAILING

Specify TRCANLZR password

Entrez une valeur pour trcanlzr_password : trcanlzr

trcanlzr

Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the Trace Analyzer tables

and indexes.  This will also be the TRCANLZR user's default tablespace.
 

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for tools data is not supported.
 

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
CERTIF                         PERMANENT
DRSYS                          PERMANENT
EXAMPLE                        PERMANENT
INDX                           PERMANENT
ODM                            PERMANENT
OLTS_ATTRSTORE                 PERMANENT
OLTS_CT_CN                     PERMANENT
OLTS_CT_DN                     PERMANENT
OLTS_CT_OBJCL                  PERMANENT
OLTS_CT_STORE                  PERMANENT
OLTS_DEFAULT                   PERMANENT
OLTS_IND_ATTRSTORE             PERMANENT
OLTS_IND_CT_CN                 PERMANENT
OLTS_IND_CT_DN                 PERMANENT
OLTS_IND_CT_OBJCL              PERMANENT
OLTS_IND_CT_STORE              PERMANENT
OLTS_TEMP                      TEMPORARY
P1TS_ATTRSTORE                 PERMANENT
P1TS_IND_STORE                 PERMANENT
TEMP                           TEMPORARY
TOOLS                          PERMANENT
UNDOTBS1                       UNDO
USERS                          PERMANENT
XDB                            PERMANENT

Specify TRCANLZR user's default tablespace
Entrez une valeur pour default_tablespace : example
Using example for the default tablespace

Choose the TRCANLZR user's temporary tablespace.
Entrez une valeur pour default_tablespace : example
Using example for the default tablespace

Choose the TRCANLZR user's temporary tablespace.


Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Specify TRCANLZR user's temporary tablespace.
Entrez une valeur pour temporary_tablespace : temp
Using temp for the temporary tablespace

Remarques

• Il est interdit de designer le tablespace SYSTEM comme tablespace par défaut (sinon l'installation échoue).
• Avant de choisir le TBS par défaut, oracle vous rappelle la liste de vos tablespaces.
• Il est préférable de créer un tablespace dédié à cet utilisateur.

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) :

Creating Package Specs trca$d...
Pas d'erreur.
Creating Package Body trca$d...
Pas d'erreur.
Creating Package Specs trca$i...
Pas d'erreur.
Creating Package Body trca$i...
Pas d'erreur.

NOTE:
TACPKG complete. Please check tacpkg.lis for any errors.

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.

SQL> @trcadrop.sql

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.

SQL> set serverout on
SQL> exec trca$i.trace_analyzer('test.txt') ;

Trace Analyzer Report trcanlzr_0_2.html has been created in

c:\oracle\admin\oidprd\udump

SQL>

Remarques

• Le fichier doit être présent dans le répertoire UDUMP.
• Le fichier HTML généré se trouve également dans le répertoire UDUMP.