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;
This will create a .trc file in your /udump directory.

From the command prompt:

/uo1/oracle/admin/udump> tkprof ora_instance_1234.trc trace.txt
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.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.