More powerful tracing


Most people will be familiar with the 'alter session set sql_trace = true' commands to enable a trace file for your session. A quick hunt through $ORACLE_HOME/rdbms/admin also reveals a PL/SQL equivalent (dbms_session.set_sql_trace). This is fine for anyone who cares about performance of their OWN code.

A great boon for DBA's and anyone trying to track SQL performance of OTHER people's code is the use of dbms_system.set_sql_trace_in_session, typically only executable by SYS.

All of the above is "well" documented in the Oracle manuals and directories. A more powerful (and totally undocumented) routine is also available

dbms_system.set_ev( sid,serial#,event,level,name)

To initiate a trace for a connected session, simply issue:

dbms_system.set_ev( sid,serial#,10046,8,'')

For a more detailed trace, which show waits and the value of bind variables, you can use:

dbms_system.set_ev( sid,serial#,10046,12,'')