Tracing made easy !!

It has always been a challenge to trace a Oracle session or tuning a query..

1) In order to tune SQL there are few commmands which are important.

set autotrace on

set autotrace traceonly

set autotrace traceonly explain

set autotrace traceonly statistics

set autotrace off

2) Tracing a session

Tracing your own session
alter session set tracefile_identifier='EOD_TEST';
alter session set statistics_level = all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever, level 12';

Tracing other session

-- select OSPID from v$process for specific session

oradebug setospid X
oradebug unlimit
oradebug Event 10046 trace name context forever, level 12

Another method

connect sys as sysdba

exec dbms_system.set_sql_trace_in_session(session's id,serial number, true)


There are few other trace level which i use often to tune the system.

Active Session History alter session set events 'immediate trace name ashdump level 10';
Control File Dump alter session set events 'immediate trace name CONTROLF level 10';

Error Stack (Exception) Trace alter session set events ' trace name errorstack level 10';
alter session set events '60 trace name errorstack level 10';

File Header Dump alter session set events 'immediate trace name FILE_HDRS level 10';
Library Cache Dump alter session set events 'immediate trace name LIBRARY_CACHE level 10';
Process State Dump alter session set events 'immediate trace name PROCESSSTATE LEVEL 10';
Redo Log Headers Dump alter session set events 'immediate trace name REDOHDR LEVEL 10';
System State Dump alter session set events 'immediate trace name SYSTEMSTATE LEVEL 10';