SQL Trace (incl. 10g new tracing features)

--
-- sql trace
--
--related parameters
timed_statistics
max_dum_file_size
user_dump_dest

--to get session to be traced
select sid,serial# from v$session where ...
--or
select s.sid,s.serial#,s.username,s.osuser from v$session s,v$process p where s.paddr=p.addr;

use dbms_support to enable and disable the trace
($ORACLE_HOME/rdbms/admin/dbmssupp.sql)

--session level
alter session set sql_trace =true;
--instance level
alter system set sql_trace =true;

--trace other session
--to start tracing
--
--dbms_support(available in Oracle 7.2 and above)
exec dbms_support.start_trace_in_session(,);
exec dbms_support.start_trace_in_session(,,binds=true);
exec dbms_support.start_trace_in_session(,,waits=true,binds=true);
--
----dbms_system(undocumented since Oracle 7.3 to 10g R2)
see a guide at http://orafaq.com/papers/dbms_sys.doc
exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

--to check trace file under user_dum_dest
tkprof source output explain=user/pwd sys=no

--to stop tracing
--dbms_support
exec dbms_support.stop_trace_in_session(,);
--dbms_system
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

--Introduced in Oracle 10.1
DBMS_MONITOR
trcsess
DBA_ENABLED_TRACES
see http://julian.dyke.users.btopenworld.com/com/Presentations/OracleDiagnostics.ppt

Further Notes:
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
http://www.nyoug.org/Presentations/SIG/DBA/hotsos_perf.PDF