Oracle Trace (otrccl)

otrccl

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_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.

Starting Oracle Trace

Once oracle_trace_enable=true, tracing can be activated. When tracing is active, the current sessions will be logged. The otrccol executable is used to start Oracle Trace and requires a job_id and an input parameter file. Any number can be specified for the job_id, but the input file must contain certain tags.

otrccol start 1 otrace_input_file

The contents of otrace_input_file are:

col_name=test_collection

dat_file=test_collection.dat

cdf_file=test_collection.cdf

fdf_file=ORACLED.fdf

regid=1 192216243 0 0 5 ORCL92

The col_name is the name of the collection determined by the person initiating the trace. The dat_file and cdf_file parameters are simply the files that will be used by Oracle Trace to store internal information. For consistency, use the same name as specified in the col_name and add the appropriate file extension (.dat and .cdf). The fdf_file parameter specifies the facility name to be used by Oracle Trace and maps to a server the event specified by the user. The server event sets that can be used as values for the fdf_file are ORACLE, ORACLEC, ORACLED, ORACLEE, and ORACLESM. And finally, the reg_id parameter is always the number 1 192216243 0 0 5 concatenated with the SID of the database.

Oracle Trace Server Event Sets

· ORACLE – collects all statistics, including wait events.

· ORACLEC – the Cache I/O event set that collects buffer cache I/O statistics.

· ORACLED – the default server event set that collects statistics for the database.

· ORACLEE – the expert event set that collects statistics for the Oracle Expert tool in OEM.

· ORACLESM – the summary event set that collects statistics for the Summary Advisor application.

Enabling tracing will create two files in the directory specified for oracle_trace_collection_path. It uses the collection name as part of the file name:

54,243 test_collection.cdf

5,433,236 test_collection.dat

The .cdf file contains collection profile information while the dat file holds the binary trace data. The .dat file can become extremely large depending on the amount of time tracing is enabled.

Stopping Oracle Trace

When the DBA decides tracing has run long enough, it can be stopped by:

otrccol stop 1 otrace_stop_file

The number (1) is the job_id that matches the one used in the start command. The contents of otrace_stop_file include the cdf file name along with the collection name.

cdf_file=test_collection.cdf

col_name=test_collection

Reporting Tracing Results

The otrcrep executable is used to format the trace file results to a file. The minimum requirement for the otcrep utility is the cdf file name.

otrcrep test_collection.cdf

Oracle Trace generated many .txt files in the current working directory.

10,456,044 test_collection.txt

938,166 test_collectioncacheIO.txt

1,087 test_collectionErrorStack.txt

1,993,638 test_collectionExecute.txt

1,990,157 test_collectionFetch.txt

62,151 test_collectionLogicalTX.txt

2,043,251 test_collectionParse.txt

63,951 test_collectionPhysicalTX.txt

148 test_collectionPROCESS.txt

1,869,186 test_collectionRowSource.txt

1,162,699 test_collectionSQLSegment.txt

675,929 test_collectionWait.txt

21.2 MB of data was generated when two simple statements were executed from one connected session in SQL*Plus.

The rest of the command line options for otrcrep include:

· Output_path – output directory for generated files. If not specified, Oracle Trace will use the current directory.

· -p – creates a report for a specific process obtained from the PROCESS.TXT report. Below is an example of the contents of PROCESS.TXT. This option should be used to pinpoint sessions that need analysis.

30-DEC-02 19:55:54 Oracle Trace Detail Report Page1

Collection:test_collection Oracle Trace V9.2

Epid User Nam CPU Type OS Version Node Name Timestamp

2308 SYSTEM MOOREPC Windows NT MOOREPC 30-DEC-02 19:30:29.920

2308 5 9.2 30-DEC-02 19:30:29.936 ORCL92

· -w – sets the width of the report. The default is 80 characters.

· -l – sets the number of lines per page. The default is 63 lines.

· -h – suppresses all event and item report headers resulting in a much smaller report.

· -a – creates a report containing all of the events combined into one file. Beware when using this option since a great deal of output will be generated.

Other than process.txt, the .txt files themselves are very difficult to interpret. For example, a partial listing from testCollectionExecute.txt. is shown below.

Event:Execute Product:oracle Version:9.2

2308 19:30:34.342 8 1 207888

3248896 1767193 1226573 1603899 199 886969

189759504 1813758 7056 0 1896 0

0 1 127735 10163 0 0

0 0 0 0 0 0

0 3223513021 0

2308 19:30:34.342 8 1 207888

3248896 1767193 1226573 1603899 199 886969

189759504 1813758 7056 0 1896 0

0 1 1 1 0 3223513021

0000000012f0d374 127735 10163 0 0 0

There’s not much information in this file that is easily decipherable. The next step involves loading this data into the database so that the DBA can query the tables to get exactly the information needed.

Loading Trace Data into the Database

Although this step is technically optional, it is required in order to get data in a useful format that is understandable. The command to load the data into the database is:

otrccol format otrace_format_file

The contents of otrace_format_file include:

username=scott

password=tiger

service=ORCL92

cdf_file=test_collection.cdf

full_format=0

All of the parameters above have been explained except full_format. The options for full format include 0 (partial format) or 1 (full format).

Oracle creates the following named tables in the database to be used by Oracle Trace. Based on the naming scheme, it is clear that Oracle designed these tables for internal use only, to be used with Oracle tools. These tables will be created automatically if they don’t already exist in the schema of the connected user, defined by the username parameter.

V_192216243_F_5_E_10_9_2

V_192216243_F_5_E_11_9_2

V_192216243_F_5_E_12_9_2

V_192216243_F_5_E_13_9_2

V_192216243_F_5_E_14_9_2

V_192216243_F_5_E_15_9_2

V_192216243_F_5_E_16_9_2

V_192216243_F_5_E_17_9_2

V_192216243_F_5_E_1_9_2

V_192216243_F_5_E_2_9_2

V_192216243_F_5_E_3_9_2

V_192216243_F_5_E_4_9_2

V_192216243_F_5_E_5_9_2

V_192216243_F_5_E_6_9_2

V_192216243_F_5_E_7_9_2

V_192216243_F_5_E_8_9_2

V_192216243_F_5_E_9_9_2

Oracle provides many SQL scripts that access these tables and provide meaningful data, including wait events, logical transactions per second, sorts, and more. These scripts are located in the $ORACLE_HOME/otrace/demo directory. The otrcsyn.sql script can be executed to create meaningful synonyms for the tables above.

Deleting Trace Data

There are two places from which trace data needs to be deleted. The first is the generated cdf and dat files. The otrccol command is used with an argument of dcf, along with the collection name and name of the cdf file. This will not delete the .txt files produced by the otrcrep executable – those will need to be deleted manually.

otrccol dcf test_collection test_collection.cdf

The data also needs to be deleted. The otrccol command is used again, this time specifying dfd, the collection name, and connection information for the database.

otrccol dfd test_collection scott tiger ORCL92

otrccol , otrcrep , otrcref and otrcfmt are all executables of the Oracle Trace utility. Different components within Oracle Enterprise Manager (Oracle Expert, Oracle Trace Data Viewer) access and display the information generated by Oracle Trace. However, Oracle Trace has lost its usefulness with the progression of tools like bstat/estat, oradebug, Statspack and tkprof. These alternatives provide much of the same information as Oracle Trace while proving to be much easier to use.