Preparing for a Trace
Before you can use TKPROF, you must collect information about SQL statements into a trace file. To use the SQL Trace utility, you must have the following settings in the initialization file for the target Oracle database:
TIMED_STATISTICS must be set to TRUE. For Oracle9i Release 2, setting the STATISTICS_LEVEL parameter to TYPICAL or ALL will also collect timing statistics, and the value of DB_CACHE_ADVICE.TIMED_STATISTICS or TIMED_OS_STATISTICS will override the value set for STATISTICS_LEVEL.
MAX_DUMP_FILE_SIZE must be big enough to hold all the information written to the trace file.
USER_DUMP_DEST must point to a valid directory.
Tip
The SQL Trace utility writes all trace files to the USER_DUMP_DEST directory, so make sure you have a way to recognize your particular trace file.
All of these parameters are session parameters and can be changed dynamically with Oracle9i.