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

i work with sql server, but i must migrate to an application with Oracle DB. for trace my application queries, in Sql Server i use wonderful Profiler tool. is there something of equivalent for Oracle?

GI Oracle Profiler v1.2

It's a Tools for Oracle to capture queries executed similar to the SQL Server Profiler. Indispensable tool for the maintenance of applications that use this database server.

you can download it from the official site iacosoft.com

Apparently there is no small simple cheap utility that would help performing this task. There is however 101 way to do it in a complicated and inconvenient manner.

Following article describes several. There are probably dozens more... http://www.petefinnigan.com/ramblings/how_to_set_trace.htm

This is an Oracle doc explaining how to trace SQL queries, including a couple of tools (SQL Trace and tkprof)

link

Try PL/SQL Developer it has a nice user friendly GUI interface to the profiler. It's pretty nice give the trial a try. I swear by this tool when working on Oracle databases.

http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw

Oracle, along with other databases, analyzes a given query to create an execution plan. This plan is the most efficient way of retrieving the data.

Oracle provides the 'explain plan' statement which analyzes the query but doesn't run it, instead populating a special table that you can query (the plan table).

The syntax (simple version, there are other options such as to mark the rows in the plan table with a special ID, or use a different plan table) is:

explain plan for <sql query>

The analysis of that data is left for another question, or your further research.

There is a commercial tool FlexTracer which can be used to trace Oracle SQL queries

Seeing as I've just voted a recent question as a duplicate and pointed in this direction . . .

A couple more - in SQL*Plus - SET AUTOTRACE ON - will give explain plan and statistics for each statement executed.

TOAD also allows for client side profiling.

The disadvantage of both of these is that they only tell you the execution plan for the statement, but not how the optimiser arrived at that plan - for that you will need lower level server side tracing.

Another important one to understand is Statspack snapshots - they are a good way for looking at the performance of the database as a whole. Explain plan, etc, are good at finding individual SQL statements that are bottlenecks. Statspack is good at identifying the fact your problem is that a simple statement with a good execution plan is being called 1 million times in a minute.

The Catch is Capture all SQL run between two points in time. Like the way SQL Server also does.

There are situations where it is useful to capture the SQL that a particular user is running in the database. Usually you would simply enable session tracing for that user, but there are two potential problems with that approach.

1. The first is that many web based applications maintain a pool of persistent database connections which are shared amongst multiple users.
2. The second is that some applications connect, run some SQL and disconnect very quickly, making it tricky to enable session tracing at all (you could of course use a logon trigger to enable session tracing in this case).

A quick and dirty solution to the problem is to capture all SQL statements that are run between two points in time.

The following procedure will create two tables, each containing a snapshot of the database at a particular point. The tables will then be queried to produce a list of all SQL run during that period.

If possible, you should do this on a quiet development system - otherwise you risk getting way too much data back.

1. Take the first snapshot Run the following sql to create the first snapshot:

   create table sql_exec_before as
   select executions,hash_value
   from v$sqlarea
   /
   

2. Get the user to perform their task within the application.

3. Take the second snapshot.

   create table sql_exec_after as
   select executions, hash_value
   from v$sqlarea
   /
   

4. Check the results Now that you have captured the SQL it is time to query the results.

This first query will list all query hashes that have been executed:

select  aft.hash_value
from sql_exec_after aft
left outer join sql_exec_before bef
  on aft.hash_value  =  bef.hash_value 
where aft.executions > bef.executions
   or bef.executions is null;
/

This one will display the hash and the SQL itself: set pages 999 lines 100 break on hash_value

select  hash_value, sql_text
from    v$sqltext
where   hash_value in (
    select  aft.hash_value
    from sql_exec_after aft
    left outer join sql_exec_before bef
      on aft.hash_value  =  bef.hash_value
    where aft.executions > bef.executions
       or bef.executions is null;
)
order by
    hash_value, piece
/

5. Tidy up Don't forget to remove the snapshot tables once you've finished:

drop table sql_exec_before
/

drop table sql_exec_after
/

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.

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

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.