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

Question

Hello all. 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? Thank you for any Advice.

Answer 1

EXPLAIN PLAN and tkprof

http://www.dbspecialists.com/files/presentations/use_explain.html

Many utilities (e.g. TOAD, Oralce Enterprise Manager, Oracle SQLDeveloper) have interfaces to make this more straight forward than lots of playing around on the command line.

Answer 2

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 3

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

link

Answer 4

You can use The Oracle Enterprise Manager to monitor the active sessions, with the the query that are beeing 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 put with pretty colors what is already avalaible in specials views like thouse documented here: http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage

And, of course yuu can also use Explain PLAN FOR, TRACE tool and tons of other ways for instrumentalization. There are some reports in the enterprise manager for top SQL Queries and you can query the las queries keept on the cache.

Answer 5

try this (it is also free): http://www.aboves.com/Statement%5FTracer%5Ffor%5FOracle.exe

Answer 6

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

Answer 7

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.

Answer 8

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