Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Setting Up a Trace File
Oracle Tips by Burleson

Before using TKPROF, you must create a trace file. You can create a trace file by executing the following command in SQL*Plus:


This command instructs SQL*Plus to set up a trace file for all SQL statements and PL/SQL blocks that you execute. When you have finished executing the statements that you wish to examine, execute the following command to stop writing to the trace file:


Keep in mind that ALTER SESSION is not a DML statement and cannot be run from inside your PL/SQL blocks. Also keep in mind that disconnecting from SQL*Plus or connecting as another user via the CONNECT statement will also cause SQL*Plus to stop writing to the trace file.

The location of your trace file depends on how the DBA has set the USER_DUMP_DEST parameter in the init.ora file for your database. Consult your DBA to determine the location of the trace files, or look at the init.ora file yourself. Each trace file is given a specific ID number by Oracle (in Unix, this is the system process ID for the SQL*Plus session) and has a .trc extension. Trace files are named according to this convention:

ora_ + <id> + .trc

Thus, a valid trace file name might be:


By listing the files in the directory specified by the USER_DUMP_DEST parameter, you can easily determine which trace file is yours by simply taking the most recent trace file (the trace file with the highest ID number). Once you’ve created a trace file for the statement(s) that you want to tune, exit SQL*Plus and run the TKPROF utility.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter