Donald K. Burleson
Oracle Utilities Tips
Best Practices for Using tkprof
Enable tracing only on those sessions that are having problems. Be
selective to minimize the performance burden on the sessions and to retain
more free space in the user dump destination directory.
Rename trace files after tracing is disabled. The new file name should be
something more meaningful that will be recognizable at a later date. The
name employee_index.trc is much more meaningful than ORCL92_ora_3172.trc.
Delete trace files that are no longer needed to reduce clutter and free
Explain Plan is not as useful when used in conjunction with tkprof since
the trace file contains the actual execution path of the SQL statement. Use
Explain Plan when anticipated execution statistics are desired without
actually executing the statement.
When tracing a session, remember that nothing in v$session indicates that a
session is being traced. Therefore, trace with caution and remember to
disable tracing after an adequate amount of trace data has been generated.
tkprof does not control the contents of a trace file, it simply formats
them. Oracle provides multiple ways to actually generate the trace file.
tkprof is valuable for detailed trace file analysis. For those DBAs that
prefer a simpler tracing mechanism with instant feedback, the autotrace
utility should be used.
For more details on Oracle
utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald
K. Burleson, and Steve Callan.
You can buy it direct from the
publisher for 30% off directly from
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA