Q

Tool in Oracle 10g to keep track of execution of queries

How can I keep track of the execution of stored procedures, functions and queries in Oracle 10g, as I used to do using SQL Server Profiler? Is there any tool in 10g for this purpose?

How can I keep track of the execution of stored procedures, functions and queries in Oracle 10g, as I used to do using SQL Server Profiler? Is there any tool in 10g for this purpose? Thanks and regards.

The Oracle SQL Trace facility is what you use here. In your session, issue the following:

ALTER SESSION SET sql_trace=TRUE;

Then run your application. At the end, set SQL_TRACE to FALSE. This will create a trace file in the USER_DUMP_DEST directory.

If you want to start a trace in another user's session, you'll have to determine that session's SID and SERIAL# values as can be seen in V$SESSION. Then sign on to SQL*Plus as SYSTEM and issue the following:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(sid,serial);

To stop the trace, issue the following:

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(sid,serial);
Details on the SQL Trace package can be found in the Oracle documentation.
This was first published in May 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close