Is there a tool for tracing SQLs executed on Oracle

Question

Is there a tool (that already comes with Oracle) for tracing SQLs that have been executed? In DB2 there is something called an 'event monitor' which I use to track the tables that have been updated. Is there an equivalent tool in Oracle?

I plan to

enable tracing
go on the website (that uses the db) and change an entry
disable tracing
see output file and record which table has been updated.

There is a table I am looking that should be updated when the entry is changed. I do not know what the name of the table is (and there are many tables), and so I need to trace the SQL executed to find out.

I have tried:

  ALTER SESSION SET sql_trace = true;
  -- go on website and change an entry
  ALTER SESSION SET sql_trace = false;

  tkprof the_trace_file.trc file.out EXPLAIN=system/manager SYS=no

However when following those steps above, no SQLs were recorded.

Is there a tool that Oracle provides? (I would like to avoid downloading external software)

Duplicate? http://stackoverflow.com/questions/148648/oracle-is-there-a-tool-to-trace-queries-like-profiler-for-sql-server — PaulWaldman, Feb 18 '11 at 19:00

score 4 · Accepted Answer · answered Feb 18 '11 at 19:10

There is a table I am looking that should be updated when the entry is changed. I do not know what the name of the table is (and there are many tables), and so I need to trace the SQL executed to find out.

I'm thinking you are using the word "trace" here with another meaning than what is usually meant in the Oracle world.

You basically hit some button in the app, and by looking at what SQL queries are running, you want to find what table that code was referencing? Did I get it right?
In that case, you could have a look at v$sql, and look at columns SQL_TEXT and SQL_FULLTEXT.

Yes you are absolutely correct. Sorry about the confusion. Thanks for the answer! This table is very helpful to me. — f20k, Feb 18 '11 at 19:40

score 2 · Answer 2 · answered Feb 18 '11 at 20:13

2

The ALTER SESSION commands work at the session level (ie your current connection). The website will use a different session (probably from a connection pool). You can enable tracing for all sessions using the ALTER SYSTEM SET sql_trace = true;

answered Feb 18 '11 at 20:13

Gary Myers

34,963
3
49
74

score 2 · Answer 3 · answered Feb 18 '11 at 20:13

The main reason you didn't get anything in the trace file is because you didn't do anything in the session where trace was enabled.

If you'd have done:

alter system set sql_trace = true;
-- fiddle around with the website
alter system set sql_trace = false;

You'd have gotten one or more trace files, one for each session which had activity while you were fiddling with the website.

The problem is that if the website uses connection pooling, your user activity may have been spread across several connections, and may be intermingled with other concurrent user activity.

Oh I see. The documentation I read did not mention using system. Thanks! — f20k, Feb 18 '11 at 20:53

score 1 · Answer 4 · answered Feb 18 '11 at 19:05

Maybe Oracle Audit will help you.

Here is a good explanation: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

You have to enable audit by setting the parameter AUDIT_TRAIL.

That is at server level. You can audit at client level using a third party sql tracer for OCI:

http://sourceforge.net/projects/ocimonitor/

score 1 · Answer 5 · answered Feb 18 '11 at 23:38

I find the Enterprise Manager the most useful tool for this. As has already been noted you have to alter the session that the web site is using and not your own. If you set your connection pool limit to 1 connection, you can easily find the session in the enterprise manager and then turn on the tracing. Usually a find the the top queries display in the enterprise manager tells me what queries are taking too long without having to trace anything.

Is there a tool for tracing SQLs executed on Oracle

5 Answers5