Current session trace:

NORMAL TRACE:

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set sql_trace = true;

FULL LEVEL TARCE with WAIT and BIND :

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set events = ‘10046 trace name context forever, level 12’;

Other running session trace:

NORMAL TRACE:

execute dbms_system.set_sql_trace_in_session ($sid,$serial,true);

FULL LEVEL TARCE with WAIT and BIND :

execute dbms_system.set_ev($sid,$serial,10046,12,’’);

TRACE FILE LOCATION:

<DIAGNOSTIC_DEST>/diag/rdbms/<SID>/trace

TKPROF :
tkprof utility can translate the trace file into a more human readable format

tkprof <tracefile> <outfile> explain=username/password sort ='(prsela,exeela,fchela)’

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
prsela  elapsed time parsing
exeela  elapsed time executing
fchela  elapsed time fetching