Talking Tuning: Tracing SQL in Oracle Database 10g

exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'xxxx');
Or
alter system set sql_trace=true;

The default log is in $ORACLE_BASE\instance_name\admin\udump

To analyse:
# trcsess output="*.trc" service="xxxxx"
# cat *.trc > udump.trc
# tkprof udump.trc output.txt sys=no ( explain=cpsuser/cpsuser sort=(execpu))

To disable:
exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'xxxx');

» Read More