CODEWITHSUNDEEP
oracle-databaseoracle11g
f20k
f20k

Reputation: 3106

Is there a tool for tracing SQLs executed on Oracle

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

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)

Upvotes: 2

Views: 8390

Answers (5)

JOTN
JOTN

Reputation: 6317

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.

Upvotes: 1

Adam Musch
Adam Musch

Reputation: 13583

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.

Upvotes: 2

Gary Myers
Gary Myers

Reputation: 35401

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;

Upvotes: 2

Ronnis
Ronnis

Reputation: 12833

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.

Upvotes: 4

caraca
caraca

Reputation: 205

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/

Upvotes: 1

Related Questions