Donald K. Burleson
Oracle Utilities Tips
Analyzing tkprof Results
Step 3: Turn Tracing Off
The same options that we use to enable tracing are used to disable it.
ALTER SESSION SET SQL_TRACE = FALSE;
To disable tracing for another user’s session use:
This process is a perfect candidate for automation. The code below (start_trace.sql)
creates a stored procedure that automates all the steps discussed so far.
It can also serve as a wrapper for the standard methods of enabling
tracing. Start_trace.sql accepts the sid and serial# for the session that
needs tracing. It requires that a time interval, in seconds, be set to run
the trace so that it doesn’t run perpetually and bog down the session. When
the time has elapsed, it will disable tracing for the session and send the
relevant trace information: user, time, and trace file name.
SEE CODE DEPOT
The output from start_trace.sql is displayed below. The time interval
specified was 30 and we can see the elapsed time of the trace in the
SQL> exec start_trace(17, 6157, 30);
Tracing Started for User: SCOTT
Tracing Start Time: 12-26-2002 14:55:12
Tracing Stop Time: 12-26-2002 14:55:42
Trace Directory: C:\Oracle\admin\ORCL92\udump
Trace Filename: ORCL92_ora_5472.trc
The next step is to run tkprof against the trace file
To learn more about these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it
directly from the
publisher and get instant access to the code depot of utilities scripts.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA