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

You can use The Oracle Enterprise Manager to monitor the active sessions, with the query that is being executed, its execution plan, locks, some statistics and even a progress bar for the longer tasks.

See: http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955

Go to Instance -> sessions and watch the SQL Tab of each session.

There are other ways. Enterprise manager just puts with pretty colors what is already available in specials views like those documented here: http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage

And, of course you can also use Explain PLAN FOR, TRACE tool and tons of other ways of instrumentalization. There are some reports in the enterprise manager for the top most expensive SQL Queries. You can also search recent queries kept on the cache.

I found an easy solution

Step1. connect to DB with an admin user using PLSQL or sqldeveloper or any other query interface

Step2. run the script bellow; in the S.SQL_TEXT column, you will see the executed queries

SELECT             S.LAST_ACTIVE_TIME,      S.MODULE, S.SQL_FULLTEXT,  S.SQL_PROFILE, S.EXECUTIONS, S.LAST_LOAD_TIME, S.PARSING_USER_ID, S.SERVICE                                                                       FROM SYS.V_$SQL S,  SYS.ALL_USERS UWHERE S.PARSING_USER_ID=U.USER_ID  AND UPPER(U.USERNAME) IN ('oracle user name here')   ORDER BY TO_DATE(S.LAST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS') desc;

The only issue with this is that I can't find a way to show the input parameters values(for function calls), but at least we can see what is ran in Oracle and the order of it without using a specific tool.

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 pwhere 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 off1 - minimal level. Much like set sql_trace=true4 - bind variables values are added to trace file8 - waits are added12 - 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.prfTKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004Copyright (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