Oracle: is there a tool to trace queries, like Pro

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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
/

Answer 5

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

Answer 6

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.