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
NAME TYPE VALUE
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
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA