Tracing SQL activity with SQL Trace and TKPROF

In this recipe, we will see how to use SQL Trace and TKPROF to trace SQL statements in a session.

There could be situations when we have to diagnose and tune a database, on which an application is running for which we don't have the source code, so we don't know which SQL statements are executed. In these situations, or when we want to investigate deeper than the AUTOTRACE feature we have used until now, the use of these tools is invaluable.

Getting ready

To trace SQL in our session, we have to make some modifications to the database parameters (if not set according to our needs).

The first parameter to set is TIMED_STATISTICS=TRUE, it can be set at the system or session level, to allow the database to trace the timing of the operations. It adds a very little overhead to the operations, so it can be left in place forever.

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

We have to set the destination for our trace files also. When using dedicated servers...