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.
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:
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 U WHERE 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
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;
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';
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";
TKPROF to make trace file more readable:
C:ORACLEadmindatabaseSIDudump> C:ORACLEadmindatabaseSIDudump>tkprof my_trace_file.trc output=my_file.prf TKPROF: Release 184.108.40.206.0 - Production on Wed Sep 22 18:05:00 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. C:ORACLEadmindatabaseSIDudump>
–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