Saturday, January 13, 2007

Tracing an Oracle session in 10g

Setting permissions:
GRANT EXECUTE ON SYS.dbms_monitor TO username; 
Turn the trace on and off:
EXECUTE dbms_monitor.session_trace_enable
    (session_id=>NULL, serial_num=>NULL, waits=>TRUE, binds=>TRUE);
EXECUTE dbms_monitor.session_trace_disable
    (session_id=>NULL, serial_num=>NULL); 
Older versions of Oracle:
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='Hello';
alter session set events '10046 trace name context forever, level 12'; 
Look at the results with tkprof. The "aggregate" flag is especially useful.