Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 195

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, the
parameter is USER_DUMP_DEST . In the multi-threaded server environment the parameter is
BACKGROUND_DUMP_DEST , and it will be a nightmare to trace the SQL statements with the
session hopping from one shared server to another shared server.
For example, we can also set the maximum size of our trace file to 100 MB:
ALTER SYSTEM SET MAX_DUMP_FILE_SIZE='100M';
You can use the following statement to change the name of the generated
trace file:
ALTER SESSION SET TRACEFILE_IDENTIFIER = SYSDUMP_SESSION;