LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon