TKPROF et Explain Plan

Date de publication : Août 2005

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:

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

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

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.

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.

III. Tracer une session

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

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:

Unix

$ORACLE_HOME/bin  
$  cd $ORACLE_HOME/bin  
$ ll tkprof
-rwxr-x--x   1 oracle     dba        8204288 Jun 18  2004 tkprof

Window

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

SORT option	Ordre dans lequel sont triées les instructions
INSERT Nom_de_fichier	Créé un fichier SQL pour charger les résultats de TKPROF dans une table de la base de données.
EXPLAIN utilisateur/mot_de_passe	Se connecte et exécute EXPLAIN FOR dans le schéma indiqué Vérifier que la table explain plan est créée
SYS no	Ne pas afficher le SQL récursif exécuté par SYS
AGGREGATE no	Désactive le recoupement d’enregistrements identiques en une seule requête SQL
TABLE schéma.nom_de_table	Indique la table de stockage temporaire des plans d’exécution avant leur écriture dans le fichier de sortie. Ce paramètre est ignoré si EXPLAIN n’est pas renseigné.
RECORD Nom_de_fichier	Créé un fichier SQL avec toutes les instructions non récursives.
Waits y	Indique si les récapitulatifs doivent être enregistrés pour les évènements d’attentes
PRINT n	Génère un rapport sur ce nombre (trié) d’instructions uniquement

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)

prscnt	Nombre d’appels d’analyse (PARSE)
prscpu	Temps CPU consommé pour l’analyse
prsela	Temps écoulé pour l'analyse
prsdsk	Nombre de lectures sur le disque
prsqry	Nombre de mémoire tampon pour une lecture cohérente au cours de l’analyse
prscu	Nombre de buffers durant la phase de parse
prsmis	Nombre d’échecs dans le cache « library » au cours de l’analyse

- La phase d’exécution : (EXEC)

execnt	Nombre d’exécutions appelées
execpu	Temps CPU consommé pour l’exécution
exeela	Temps écoulé durant l’exécution
exedsk	Nombre de lectures sur le disque durant l’exécution
exeqry	Nombre de mémoire tampon pour une lecture cohérente au cours de l’exécution
execu	Nombre de buffers durant la phase d’exécution
exerow	Nombre de lignes traitées durant l’exécution
exemis	Nombre d’échecs dans le cache « library » au cours de l’exécution

- La phase d’extraction : (FETCH)

fchcnt	Nombre d’appels d’extraction (FECTH)
fchcpu	Temps CPU consommé pour l’extraction
fchela	Temps écoulés pour l’extraction
fchdsk	Nombre de lectures sur le disque au cours du FETCH
fchqry	Nombre de mémoires tampon pour une lecture cohérente au cours de l’extraction
fchcu	Nombres de mémoires tampon pour la lecture en cours lors de l’extraction
fchrow	Nombre de lignes traitées durant l’extraction.

- Autre

userid

ID de l’utilisateur qui a analysé le curseur

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

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écessaire ).
- 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ête sans BIND variable, ou alors un nombre omportant d’invalidations (calcul de statistiques, création suppression d’index ..).

Les statistiques de suivi:

Count	Nombre d’analyses, exécutions et récupération effectuées. (Bien vérifier la présence d’une valeur supérieur à 0 avant d’interpréter les autres colonnes). A moins d’avoir spécifié AGGREGATE=no , TKPROF regroupe les instructions SQL identiques .
CPU	Temps CPU total, exprimé en secondes, consacré aux étapes d’analyse, d'exécution et de récupération.
Elapsed	Temps total, en secondes, consacré à tous les appels d’analyse, d’exécution ou de récupération (Ici les waits sont ajoutés et donc ce temps comprend aussi bien les opérations Oracle que les opérations CPU ). Il faut vérifier qu’il n’y a pas de différence notable entre CPU et Elapsed
Disk	Nombre total de blocs de données lus physiquement dans les fichiers de données pour les étapes d’analyse, d'exécution ou de récupération.
Query	Nombre total de buffers extraits en mode cohérent pour toute les étapes.
Current	Nombre total de buffers extraits en mode courant pour toute les étapes.
Rows	Nombre total de lignes traitées (mais cela ne concerne pas les sous-requêtes) - Select : ce nombre se trouve dans la colonne Fetch - Insert , Update et Delete : ce nombre se trouve dans la colonne Execute

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:

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

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

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

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:

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

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.