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


The Oracle Trace Utility

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.

Oracle Corporation provides tools that access and display the data generated by Oracle Trace, including Trace Data Viewer (Figure 5.2) within OEM.

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.

Oracle Trace has a limited future while Trace Analyzer is just beginning.
 



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.


 


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