 |
|
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. |
