Category: Ops

This section details various ways of actually getting an Oracle session level trace file, interpreting the trace file and useful links to MYOS notes about the trace data:

/* Oracle 10g+ trace session using DBMS_MONITOR includes BINDS and WAITS */
/* However, this is limited to one session only */
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id =>1181,
                                       serial_num =>7218,
                                       waits      => TRUE,
                                       binds      => TRUE);

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 1181,
                                        serial_num => 7218);

/* Oracle 9i Trace excludes BINDS and WAITS */
/* Again, limited to one session only */
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,FALSE);

/ * Trace your own session with BINDS and WAITS */
ALTER SESSION SET SQL_TRACE=TRUE;
or
ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'';

/* Trace using OraDebug with BINDS and WAITS */
connect / as sysdba
oradebug setorapid 9834  << Insert your Oracle PID here.
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

/* And switch it off again */
oradebug event 10046 trace name context off

/* Logon trigger trace for Oracle 9i+ */
/* This is the best way to trace when using Oracle Forms because it allows for multiple spontaneous sessions.
I can also confirm that this traces sessions executed on a database link */
/* First you must grant ALTER SESSION to the user */
GRANT ALTER SESSION TO <USERNAME> ;

/ * Now create the trigger */
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

/* Simply disable the trigger when done */
ALTER TRIGGER SYS.SET_TRACE DISABLE;

/* List non-system users, very useful when attempting to trace a session */
SELECT username,sid,serial#,status,module
   FROM v$session
 WHERE username is not null
      AND username <> 'SYS'
 ORDER BY username;

/* Enable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Enabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => c1_row.sid, serial_num => c1_row.serial# ,waits => TRUE, binds => TRUE);
DBMS_OUTPUT.PUT_LINE('Tracing for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;

/* Disable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Disabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => c1_row.sid, serial_num => c1_row.serial#);
DBMS_OUTPUT.PUT_LINE('Tracing disabled for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;

/* Run TKProf as sysdba with EXPLAIN plan included */
/* See TKProf Interpretation (9i and above) [ID 760786.1] */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk explain=" / as sysdba"

/* TKProf sorting by highest elapsed time */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk sort=fchela,exeela,prsela

Once you have your trace file and you have run it through TKProf and are still struggling, you may need to read it raw.
Check out the following MYOS articles:
“Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1]”.

The excellent but slightly unheard of “Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]”.