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

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.

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.