Trace SQL et trace d'événements Oracle
Activer, retrouver et analyser la trace SQL (10046) et les traces d'événements ERRORSTACK et SYSTEMSTATE dans Oracle.
Oracle peut enregistrer les requêtes SQL exécutées par une session, avec les temps d'exécution et d'attente, grâce à la trace SQL étendue (événement 10046). La trace d'événements (par exemple ERRORSTACK) écrit des dumps de diagnostic lorsqu'une erreur donnée est levée. Les deux types de trace écrivent leurs fichiers dans la destination de diagnostic.
Localiser les fichiers de trace
Trouver les sessions actives :
SELECT sid, serial#, username, status FROM v$session WHERE status = 'ACTIVE';
Trouver l'emplacement par défaut des fichiers de trace :
SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';
Associer les sessions à leurs fichiers de trace du système d'exploitation :
select s.sid,s.serial#,s.audsid,s.username,s.osuser, s.client_identifier, s.sql_trace,s.action, p.spid, p.TRACEFILE
from v$session s,v$process p
where s.paddr=p.addr
and s.username is not null;
Pour donner au fichier de trace un nom reconnaissable, définir un identifiant avant le traitement tracé :
ALTER SESSION SET tracefile_identifier = 'SQLID_fua0hb5hfst77';
Tracer toute l'instance
ALTER SYSTEM SET sql_trace = TRUE;
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
Désactivation :
ALTER SYSTEM SET sql_trace = FALSE;
ALTER SYSTEM SET EVENTS '10046 trace name context off';
Variante avec le niveau 4 pour toutes les sessions :
alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
Arrêt de cette variante : alter system set events '10046 TRACE NAME CONTEXT OFF';.
Tracer une session
alter session set timed_statistics=true;
alter session set sql_trace = true;
Arrêter la trace de la session courante :
ALTER SESSION SET SQL_TRACE = FALSE;
Activer ou arrêter la trace d'une autre session, identifiée par son SID et son SERIAL# :
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial, TRUE);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(sid, serial);
Sans argument, DBMS_MONITOR.SESSION_TRACE_DISABLE arrête la trace de la session courante. C'est équivalent à ALTER SESSION SET EVENTS '10046 trace name context off';.
Mettre en forme la trace avec TKPROF
tkprof orcl_ora_4412.trc orcl_ora_4412.tkprofs
Plusieurs fichiers de trace d'un même service peuvent d'abord être fusionnés avec trcsess :
trcsess output=main_trace_file.trc service='SYS$USERS' orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc
tkprof main_trace_file.trc main_formatted_trace.txt
Trace d'événements
Syntaxe d'un événement : '<numéro de l'événement> TRACE NAME <type de dump> LEVEL <n>'.
| Portée | Effet |
|---|---|
SCOPE=SPFILE |
Enregistré dans le SPFILE, appliqué au prochain redémarrage de l'instance |
SCOPE=MEMORY |
Appliqué à l'instance en cours uniquement |
SCOPE=BOTH |
Les deux précédents |
ALTER SESSION SET EVENTS |
Appliqué immédiatement à la session courante |
Exemples :
| Instruction | Effet |
|---|---|
ALTER SYSTEM set events '904 trace name ERRORSTACK LEVEL 3'; |
Produit un dump de la pile d'erreur lorsque ORA-00904 est levée |
ALTER SYSTEM set events 'IMMEDIATE trace name SYSTEMSTATE LEVEL 266'; |
Produit un dump de l'état système au moment de l'exécution |
ALTER SESSION set events '10046 trace name CONTEXT FOREVER, LEVEL 12'; |
Trace SQL de niveau 12 dans la session |
Exemple : tracer ORA-01422 au niveau système, reproduire l'erreur, puis chercher l'erreur dans le répertoire de trace :
ALTER SYSTEM SET EVENTS '1422 TRACE NAME ERRORSTACK LEVEL 3';
cd <diagnostic_dest>/diag/rdbms/orcl/orcl/trace
find . -type f -print | xargs grep -li "ORA-01422"
Désactiver les traces d'événements une fois le diagnostic terminé :
alter session set events '1555 trace name errorstack off';
ALTER SYSTEM SET EVENTS '1422 TRACE NAME ERRORSTACK off';
Paramètres des types de dump :
| Type de dump | Niveau | Contenu |
|---|---|---|
| ERRORSTACK | 0 | Pile d'erreur uniquement |
| ERRORSTACK | 1 | Pile d'erreur et pile des appels de fonctions (si implémentée) |
| ERRORSTACK | 2 | Comme 1, plus l'état des processus |
| ERRORSTACK | 3 | Comme 2, plus la zone de contexte (tous les curseurs, curseur courant mis en évidence) |
| SYSTEMSTATE | 1 | Informations de base sur les processus |
| SYSTEMSTATE | 10 | Niveau le plus courant, arbres des objets d'état de tous les processus |
| SYSTEMSTATE | 11 | RAC uniquement : inclut aussi les ressources absentes de la liste libre |
| SYSTEMSTATE | niveau + 256 | Ajoute les informations de pile courtes |
| CONTEXT | FOREVER ou OFF |
Active ou désactive l'événement |
Vérifier les traces activées :
SELECT * FROM dba_enabled_traces;
Remarques
- Désactiver les traces dès que le diagnostic est terminé. Elles produisent beaucoup de fichiers dans le répertoire de trace et peuvent saturer le point de montage.
- Pour aller plus loin : Oracle Base : SQL trace, 10046, trcsess et TKPROF.