[sql-server] Oracle: is there a tool to trace queries, like Profiler for sql server?

alter system set timed_statistics=true

--or

alter session set timed_statistics=true --if want to trace your own session

-- must be big enough:

select value from v$parameter p
where name='max_dump_file_size' 

-- Find out sid and serial# of session you interested in:

 select sid, serial# from v$session
 where ...your_search_params...

--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):

 begin
    sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');
 end;

--turn off tracing with setting zero level:

begin
   sys.dbms_system.set_ev(sid, serial#, 10046, 0, '');
end;

/*possible levels: 0 - turned off 1 - minimal level. Much like set sql_trace=true 4 - bind variables values are added to trace file 8 - waits are added 12 - both bind variable values and wait events are added */

--same if you want to trace your own session with bigger level:

alter session set events '10046 trace name context forever, level 12';

--turn off:

alter session set events '10046 trace name context off';

--file with raw trace information will be located:

 select value from v$parameter p
 where name='user_dump_dest'

--name of the file(*.trc) will contain spid:

 select p.spid from v$session s, v$process p
 where s.paddr=p.addr
 and ...your_search_params...

--also you can set the name by yourself:

alter session set tracefile_identifier='UniqueString'; 

--finally, use TKPROF to make trace file more readable:

C:\ORACLE\admin\databaseSID\udump>
C:\ORACLE\admin\databaseSID\udump>tkprof my_trace_file.trc output=my_file.prf
TKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
C:\ORACLE\admin\databaseSID\udump>

--to view state of trace file use:

set serveroutput on size 30000;
declare
  ALevel binary_integer;
begin
  SYS.DBMS_SYSTEM.Read_Ev(10046, ALevel);
  if ALevel = 0 then
    DBMS_OUTPUT.Put_Line('sql_trace is off');
  else
    DBMS_OUTPUT.Put_Line('sql_trace is on');
  end if;
end;
/

Just kind of translated http://www.sql.ru/faq/faq_topic.aspx?fid=389 Original is fuller, but anyway this is better than what others posted IMHO