Tunesnin

SQL Server Profiler can configure, start, and stop a SQL trace as well as capture and show SQL Trace data In order to capture data using SQL Server Profiler, you must create a trace. For each trace you can define the specific data elements you would like to capture. Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services.

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?

closed as off-topic by Florin Ghita, EdChum, Marcelo, Vality, CristikOct 28 '15 at 13:13

This question appears to be off-topic. The users who voted to close gave this specific reason:

• 'Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it.' – Florin Ghita, EdChum, Marcelo, Vality, Cristik

11 Answers

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.

Lender Trace Report

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.

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

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.

--or

-- must be big enough:

-- Find out sid and serial# of session you interested in:

--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):

--turn off tracing with setting zero level:

/*possible levels:0 - turned off1 - minimal level. Much like set sql_trace=true4 - bind variables values are added to trace file8 - waits are added12 - both bind variable values and wait events are added*/

--same if you want to trace your own session with bigger level:

--turn off:

--file with raw trace information will be located:

As we all know, Blu-ray discs storage movie files in the form of M2TS streams, and VLC Media Player for Mac is able to play such a kind of file type, that is to say, to play Blu-ray movies with VLC Media Player for Mac, you just need remove copy protections embedded in the commercial BD movies. Actually, VLC doesn't offer direct support for Blu-ray disc, but the 2.0 and later version of VLC Media Player support Blu-ray media playback with some extra operations. So, it is possible to get VLC to play Blu-ray disc on computer. Above steps are how to use VLC to play Blu-ray movies on Windows. Now we are going to tell you about playing Blu-ray movies on Mac devices. Nothing is changed there you just need to follow the above steps and download Mac version of key database AACS DYNAMIC LIBRARY. Blu-ray reader for mac.

Chicago Gun Trace Report

--name of the file(*.trc) will contain spid:

--also you can set the name by yourself:

--finally, use TKPROF to make trace file more readable:

--to view state of trace file use:

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

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.

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

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.

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

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

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

3. Take the second snapshot.

4. Check the resultsNow 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:

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

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

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:

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

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

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

Not the answer you're looking for? Browse other questions tagged sql-serveroracleprofiler or ask your own question.