Oracle SQL trace and event tracing
Enable, locate and analyse the Oracle SQL trace (10046) and the ERRORSTACK and SYSTEMSTATE event traces.
Oracle can record the SQL executed by a session, with timing and wait information, using the extended SQL trace (event 10046). Event tracing (for example ERRORSTACK) writes diagnostic dumps when a given error is raised. Both write trace files to the diagnostic destination.
Locate the trace files
Find the active sessions:
SELECT sid, serial#, username, status FROM v$session WHERE status = 'ACTIVE';
Find the default trace file location:
SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';
Map the sessions to their operating system trace files:
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;
To give the trace file a recognisable name, set an identifier before the traced work:
ALTER SESSION SET tracefile_identifier = 'SQLID_fua0hb5hfst77';
Trace a whole instance
ALTER SYSTEM SET sql_trace = TRUE;
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
Disable it:
ALTER SYSTEM SET sql_trace = FALSE;
ALTER SYSTEM SET EVENTS '10046 trace name context off';
Alternative with level 4 for all sessions:
alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
Stop it with alter system set events '10046 TRACE NAME CONTEXT OFF';.
Trace one session
alter session set timed_statistics=true;
alter session set sql_trace = true;
Stop tracing the current session:
ALTER SESSION SET SQL_TRACE = FALSE;
Trace or stop tracing another session, identified by its SID and SERIAL#:
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial, TRUE);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(sid, serial);
DBMS_MONITOR.SESSION_TRACE_DISABLE without arguments stops the current session's trace. It is equivalent to ALTER SESSION SET EVENTS '10046 trace name context off';.
Format the trace file with TKPROF
tkprof orcl_ora_4412.trc orcl_ora_4412.tkprofs
Several trace files of one service can first be merged with 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
Event tracing
Event syntax: '<event number> TRACE NAME <dump type> LEVEL <n>'.
| Scope | Effect |
|---|---|
SCOPE=SPFILE |
Stored in the SPFILE, applied at the next instance restart |
SCOPE=MEMORY |
Applied to the running instance only |
SCOPE=BOTH |
Both of the above |
ALTER SESSION SET EVENTS |
Applied immediately to the current session |
Examples:
| Statement | Effect |
|---|---|
ALTER SYSTEM set events '904 trace name ERRORSTACK LEVEL 3'; |
Dumps the error stack when ORA-00904 is raised |
ALTER SYSTEM set events 'IMMEDIATE trace name SYSTEMSTATE LEVEL 266'; |
Dumps the system state when the command is issued |
ALTER SESSION set events '10046 trace name CONTEXT FOREVER, LEVEL 12'; |
Level 12 SQL trace in the session |
Example: trace ORA-01422 at system level, reproduce the error, then search the trace directory for it:
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"
Disable event traces when done:
alter session set events '1555 trace name errorstack off';
ALTER SYSTEM SET EVENTS '1422 TRACE NAME ERRORSTACK off';
Parameters of the dump types:
| Dump type | Level | Content |
|---|---|---|
| ERRORSTACK | 0 | Error stack only |
| ERRORSTACK | 1 | Error stack and function call stack (if implemented) |
| ERRORSTACK | 2 | As 1, plus the process state |
| ERRORSTACK | 3 | As 2, plus the context area (all cursors, current cursor highlighted) |
| SYSTEMSTATE | 1 | Very basic process information |
| SYSTEMSTATE | 10 | Most common level, state object trees for all processes |
| SYSTEMSTATE | 11 | RAC only: also dumps resources not on the freelist |
| SYSTEMSTATE | level + 256 | Adds short stack information |
| CONTEXT | FOREVER or OFF |
Enables or disables the event |
Check the enabled traces:
SELECT * FROM dba_enabled_traces;
Notes
- Disable traces once the diagnosis is complete. Traces produce many files in the trace directory and can fill the mount point.
- Further reading: Oracle Base: SQL trace, 10046, trcsess and TKPROF.