Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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. These include:

ALTER SESSION SET SQL_TRACE = FALSE;

DBMS_SESSION.SET_SQL_TRACE(FALSE);

To disable tracing for another user’s session use:

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);

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 timestamps below.

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.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.