Thursday, January 20, 2011

Session Trace

Follow steps below to trace the current session or other running session:

1. Find the session info[for current session use v$mystat to get current session info]:
select a.sid, a.serial#, b.spid, a.username
from v$session a, v$process b
where a.username = '[USERNAME]'
and a.sid = [session id]
and a.PADDR = b.ADDR;

2. Start the trace[in this case event = 10046,trace level=12], trace file will be created at user_dump_dest as [DBNAME]_ora_[SPID].trc :
exec dbms_system.set_ev(&sid,&serial,10046,12,'');

3. stop the trace :
exec dbms_system.set_ev(&sid,&serial,10046,0,'');

4. convert the trace to readable format [ommit sys=no is you want sys queries in output file] :
tkprof [trace file name] [output file name] sys=no

**Trace Levels:
0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.

Event Numbers:
10046: enable SQL statement timing
10053: CBO Enable optimizer trace

List of Trace Events:

More about Trace:

No comments:

Post a Comment