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

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

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 2

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.