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.
