Q
Problem solve Get help with specific problems with your technologies, process and projects.

Utility to trace SQL (like Microsoft's SQL Profiler)

I'm a new Oracle DBA (HP UX, 8i) and I'm looking for a utility to trace SQL like SQL Profiler that Microsoft puts out for SQL Server 7 and 2000. Is there a comparable tool for Oracle?

You can generate trace information for a given session by turning SQL_TRACE on, running the SQL statement(s) you...

want to trace and then turning SQL_TRACE off. The results of your trace will be stored in the /udump directory (USER_DUMP_DEST init.ora parameter). You can then use a utility called TKPROF to format the trace file into something a bit more readable.

Here's how it would look from SQL*Plus:

SQL> alter session set SQL_TRACE=TRUE;
SQL> select emp_no, lastname from emp where lastname = 'SMITH' ;
SQL> alter session set SQL_TRACE=FALSE;

From the command prompt:

/uo1/oracle/admin/udump> tkprof ora_instance_1234.trc trace.txt

If you need a guide to follow and a place to get more info, see the Oracle document: Oracle8i Designing and Tuning for Performance, Chapter 6. There are also other tracing/tuning options described in that chapter that may be of help.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This will create a .trc file in your /udump directory. This will create a formatted file named trace.txt from the data captured in the .trc file. You can then review the trace.txt file to see the SQL statements, execution plans and counts for statement parses, executes and fetches.
This was last published in February 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close