Oracle TKPROF Hidden/Undocumented Parameters

While I was examining with tkprof, I noticed that there are 2 undocumented parameters (Oracle 11gR2): verbose and diag. Let’s take a look at them.

Verbose: If you set verbose=y, tkprof will provide some extra information on output files. It adds “SQL Text addres(s)” and “SQL Text Hash Value” lines for each query:

It also adds a summary part to the end of the output file:

Tracing Oracle Sessions

Tracing is invaluable for DBAs who want to solve performance problems. With trace data, you can follow all database calls and track the execution of a given set of SQL statements of a session. To gather trace at your own session, you can issue the following commands:

If you exit from your session or issue the following command, tracing will stop:

Trace files will be located in user_dump_dest:

If you use parallel queries then some of your trace files will be located in background_dump_dest: