LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon