Tracing sessions, analyzing output

Ce sont des notes pour moi, ne cherchez pas d'explications où d'exemples...

1) Identifier le fichier de trace

SQL> alter session set tracefile_identifier='montraceid'

=> le nom du fichier trace contiendra l'identifiant (pratique quand il y en a des floppées)

2) Répertoire traces

En SQL : 

SQL> select name, value from v$parameter where name = 'user_dump_dest'

Sous SQL*Plus : 

SQL> sho parameter user_dump_dest

=> Pour savoir où chercher son fichier trace !

3) Lancer la trace : 

Lancer la trace (changer system par session pour le faire au niveau de la session active) : 

SQL> alter system set events '10046 trace name context forever, level 8';

=> levels : 0 rien, 1 trace normale, 4 bind variables, 8 wait events, 12 la totale

Couper la trace (changer system par session pour le faire au niveau de la session active) : 

SQL> alter system set events '10046 trace name context off';

4) Analyse tkprof : 

A lancer dans son shell : 

tkprof fichier_trace fichier_sortie SORT=EXEELA

Options pour le paramètre SORT (copié/collé de la doc Oracle) : 

PRSCNT	Number of times parsed.
PRSCPU	CPU time spent parsing.
PRSELA	Elapsed time spent parsing.
PRSDSK	Number of physical reads from disk during parse.
PRSQRY	Number of consistent mode block reads during parse.
PRSCU	Number of current mode block reads during parse.
PRSMIS	Number of library cache misses during parse.
EXECNT	Number of executes.
EXECPU	CPU time spent executing.
EXEELA	Elapsed time spent executing.
EXEDSK	Number of physical reads from disk during execute.
EXEDSK	Number of physical reads from disk during execute.
EXEQRY	Number of consistent mode block reads during execute.
EXECU	Number of current mode block reads during execute.
EXEROW	Number of rows processed during execute.
EXEMIS	Number of library cache misses during execute.
FCHCNT	Number of fetches.
FCHCPU	CPU time spent fetching.
FCHELA	Elapsed time spent fetching.
FCHDSK	Number of physical reads from disk during fetch.
FCHQRY	Number of consistent mode block reads during fetch.
FCHCU	Number of current mode block reads during fetch.
FCHROW	Number of rows fetched.