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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Utilities Tips

The Oracle Trace Utility

Oracle Trace (otrccl) is a data collection utility used mainly with Oracle Enterprise Manager. However, the command line API can be used with any software to gather data for performance tuning. Oracle Trace collects the following types of data:

• SQL statements and their execution statistics

• SQL Execution plan statistics

• Logical and physical database transactions

• Resource Usage for each database event – CPU time and I/O.

Before Oracle Trace can be started, the environment must be configured. The show_parameter command can be used from SQL*Plus to display all of the Oracle Trace parameters.

SQL> show parameter oracle_trace

------------------------------------ ----------- ------------------------------
oracle_trace_collection_name string
oracle_trace_collection_path string %ORACLE_HOME%\OTRACE\ADMIN\CDF\
oracle_trace_collection_size integer 5242880
oracle_trace_enable boolean TRUE
oracle_trace_facility_name string oracled
oracle_trace_facility_path string %ORACLE_HOME%\OTRACE\ADMIN\FDF\

The Oracle Trace instance parameters include the following:

• oracle_trace_collection_name - The name of a set of collection statistics – one collection run. This is an optional parameter that can only be changed in the configuration file and not with an ALTER SYSTEM command. There is really no benefit to having this set in the init.ora since it can be performed more easily with methods described later in this section.

• oracle_trace_collection_path – Specifies the path where the Oracle Trace .cdf and .dat files are located.

• oracle_trace_collection_size – Specifies, in bytes, the maximum size that the collection file can be. The default is 5242880.

• oracle_trace_enable – (True | False) When true, this enables Oracle Trace to be executed against the instance. A value of true doesn’t mean that tracing is active. Instead, it means that tracing can be activated.

• oracle_trace_facility_name – Specifies the Oracle Trace product definition file - .fdf file to use. The default is Oracled.fdf.

• oracle_trace_facility_path – The directory where the Oracle Trace definition files are located.

In the Oracle Trace Admin directory ($ORACLE_HOME/otrace/admin), the following files should be present as required by Oracle Trace – regid.dat, process.dat and collect.dat. If these files are not there, execute the otrccref executable (in $ORACLE_HOME\bin) to create them.


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


Download your Oracle scripts now:

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.