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


 

HTML Text

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:

ALTER SESSION SET SQL_TRACE = TRUE;

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:

ALTER SESSION SET SQL_TRACE = FALSE;

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:

ora_09213.trc

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