25
Using Oracle Trace

This chapter describes how to use Oracle Trace to collect Oracle Server event data. It covers:

• Introduction
• Using Oracle Trace for Server Performance Data Collection
• Using Initialization Parameters to Control Oracle Trace
• Using Stored Procedure Packages to Control Oracle Trace
• Using the Oracle Trace Command-Line Interface
• Oracle Trace Collection Results

Introduction

Oracle Trace is a general-purpose data collection product that has been introduced with the Oracle Enterprise Manager systems management product family. You can use the Oracle Trace data collection API in any software product to collect data for a variety of uses, such as performance monitoring, diagnostics, and auditing. Oracle Trace collects specific data for events defined within the host product.

The server performance data that you can collect with Oracle Trace includes:

• SQL statements and statistics on the frequency, duration, and resources used for all parse, execution, and fetch events
• Execution plan details
• Logical and physical database transactions, including the frequency, duration, and resources used by each transaction event
• A set of statistics associated with each of these duration events, including: UGA and PGA memory, block changes, block gets, consistent gets, physical reads, redo entries, redo size, sorts in memory and disk
• Resource usage for each database event measured in CPU time, file I/Os and page faults

See Also: Oracle Trace User's Guide and Oracle Trace Developer's Guide contained in the Oracle Enterprise Manager Performance Pack documentation set. These books contain a complete list of events and data that can be collected for Oracle Server.

Using Oracle Trace for Server Performance Data Collection

You can use Oracle Trace to collect server performance data for a specific database session or for the entire instance. You can also select the server event set for which you want to collect.

Oracle Trace lets you organize host application events into event sets. Doing so allows you to collect data for a specific subset of all potential host application events. Oracle has defined the following event sets: ALL, DEFAULT, and EXPERT. The ALL set includes all server events, the DEFAULT set excludes server WAIT events, and the EXPERT set is specifically defined for use in the Oracle Expert tuning application. Oracle recommends using the DEFAULT event set.

You can enable and control server collections in the following ways:

• Using the Oracle Trace Manager application, a Windows-based graphical user interface that is supplied with the Oracle Enterprise Manager Performance Pack (a database option you can license)
• Using nongraphical, server-based controls:
  • Oracle Trace database initialization parameters
  • Oracle Trace stored procedure packages
  • Oracle Trace Command-Line Interface

The following sections describe the server-based controls.

See Also: Oracle Trace User's Guide

Using Initialization Parameters to Control Oracle Trace

Six parameters are set up by default to control Oracle Trace. By logging into the internal account in your database and executing a SHOW PARAMETERS TRACE command, you will see the following parameters::

Table 25-1 Oracle Trace Initialization Parameters

Name	Type	Value
ORACLE_TRACE_COLLECTION_NAME	string	[null]
ORACLE_TRACE_COLLECTION_PATH	string	$ORACLE_HOME/rdbms/log
ORACLE_TRACE_COLLECTION_SIZE	integer	5242880
ORACLE_TRACE_ENABLE	boolean	FALSE
ORACLE_TRACE_FACILITY_NAME	string	oracled
ORACLE_TRACE_FACILITY_PATH	string	ORACLE_HOME/rdbms/admin

You can modify the Oracle Trace and use them by adding them to your initialization file.

Note: This chapter references file pathnames on UNIX-based systems. For the exact path on other operating systems, please see your Oracle platform-specific documentation.

See Also: A complete discussion of these parameters is provided in Oracle8 Reference.

Enabling Oracle Trace Collections

Note that the ORACLE_TRACE_ENABLE parameter is set to FALSE by default. A value of FALSE disables any use of Oracle Trace for that Oracle server.

To enable Oracle Trace collections for the server, the parameter should be set to TRUE. Setting the parameter to TRUE does not start an Oracle Trace collection, but allows Oracle Trace to be used for that server. Oracle Trace can then be started in one of the following ways:

• Using the Oracle Trace Manager application (supplied with the OEM Performance Pack)
• Setting the ORACLE_TRACE_COLLECTION_NAME parameter

When ORACLE_TRACE_ENABLE is set to TRUE, you can initiate an Oracle Trace server collection by entering a collection name in the ORACLE_TRACE_COLLECTION_NAME parameter. The default value for this parameter is NULL. A collection name can be up to 16 characters long. You must then shut down your database and start it up again before the parameters take effect. If a collection name is specified, when you start the server, you automatically start an Oracle Trace collection for all database sessions.

To stop the collection, shut down the server instance and reset the ORACLE_TRACE_COLLECTION_NAME to NULL. The collection name specified in this value is also used in two collection output file names: the collection definition file (collection_name.cdf) and the binary data file (collection_name.dat).

Determining the Event Set Which Oracle Trace Collects

The ORACLE_TRACE_FACILITY_NAME determines the event set that Oracle Trace will collect. The name of the DEFAULT event set is ORACLED. The ALL event set is ORACLE and the EXPERT event set is ORACLEE.

If, once restarted, the database does not start collecting data, you should check the following:

• The event set file, identified by the ORACLE_TRACE_FACILITY_NAME (with .fdf appended to it) should be located in the directory identified by ORACLE_TRACE_FACILITY_PATH.
• The following files should exist in your Oracle Trace admin directory: REGID.DAT, PROCESS.DAT, and COLLECT.DAT. If they do not, you must run the OTRCCREF executable to create them.
• The Oracle Trace parameters should be set to the values that you changed in the initialization file.
• Look for an EPC_ERROR.LOG file. It will give you more information about why a collection may have failed.

Using Stored Procedure Packages to Control Oracle Trace

Using the Oracle Trace stored procedure packages you can invoke an Oracle Trace collection for your own session or for another session. To collect Oracle Trace data for your own database session, execute the following stored procedure package:

DBMS_ORACLE_TRACE_USER.SET_ORACLE_TRACE(TRUE/FALSE, collection_name, 
server_event_set) 

• true/false = Boolean: TRUE to turn on, FALSE to turn off
• collection_name = varchar2: collection name (no file extension, 8 character maximum)
• server_event_set = varchar2: server event set (oracled, oracle, or oraclee)

Example:

EXECUTE DBMS_ORACLE_TRACE_USER.SET_ORACLE_TRACE (TRUE,"MYCOLL","oracle"); 

To collect Oracle Trace data for a database session other than your own, execute the following stored procedure package:

DBMS_ORACLE_TRACE_AGENT.SET_ORACLE_TRACE_IN_SESSION(sid, serial#, true/false, 
collection_name, server_event_set) 

• sid = number: session instance from v$session.sid
• serial# = number: session serial number from v$session.serial#

Example:

EXECUTE DBMS_ORACLE_TRACE_USER.SET_ORACLE_TRACE_IN_SESSION (8,12,TRUE,"MYCOLL", 
"oracled");  

If the collection does not occur, you should check the following:

• Be sure the server event set file identified by server_event_set exists. If there is no full file specification on this field, then the file should be located in the directory identified by ORACLE_TRACE_FACILITY_PATH in the initialization file.
• The following files should exist in your Oracle Trace admin directory: REGID.DAT, PROCESS.DAT, and COLLECT.DAT. If they do not, you must run the otrccref executable to create them.
• The stored procedure packages should exist in the database. If the packages do not exist, you must run the OTRCSVR.SQL file (in your Oracle Trace admin directory) to create the packages.

Using the Oracle Trace Command-Line Interface

Another option for controlling Oracle Trace server collections is the Oracle Trace command-line interface (CLI). The CLI collects event data for all server sessions attached to the database at collection start time. Sessions that attach after the collection is started are excluded from the collection. The CLI is invoked by the otrccol command for the following functions:

• OTRCCOL START job_id input_parameter_file
• OTRCCOL STOP job_id input_parameter_file
• OTRCCOL FORMAT input_parameter_file
• OTRCCOL DCF coll_name cdf_file
• OTRCCOL DFD coll_name username password service

The parameter JOB_ID can be any numeric value. (You must remember this value in order to stop the collection.) The input parameter file contains specific parameter values required for each function. Examples follow. COLL_NAME (collection name) and CDF_FLE (collection definition file) are initially defined in the START function input parameter file.

The OTRCCOL START command invokes a collection based upon parameter values contained in the input parameter file. For example:

otrccol  start  1234  my_start_input_file

where my_start_input_file contains the following input parameters:

col_name= my_collection

dat_file= <usually same as collection name>.dat

cdf_file= <usually same as collection name>.cdf

fdf_file= <server event set>.fdf

regid= 1 192216243 0 0 5 <database SID>

The server event sets that can be used as values for the fdf_file are ORACLE, ORACLED, and ORACLEE. See "Using Initialization Parameters to Control Oracle Trace" on page 25-4 for more information on the server event sets.

The OTRCCOL STOP command halts a running collection, as follows:

otrccol stop 1234 my_stop_input_file

where my_stop_input_file contains the collection name and cdf_file name.

The OTRCCOL FORMAT command formats the binary collection file to Oracle tables. An example of the FORMAT command is as follows:

otrccol format my_format_input_file 

where my_format_input_file contains the following input parameters

username=  <database username>
password=  <database password>
service=  <database service name>
cdf_file= <usually same as collection name>.cdf
full_format= <0/1>

A full_format value of 1 produces a full format; a value of 0 produces a partial format. See "Formatting Oracle Trace Data to Oracle Tables" on page 25-10 for information on formatting part or all of an Oracle Trace collection, and other important information on creating the Oracle Trace formatting tables prior to running the format command.

The OTRCCOL DCF command deletes collection files for a specific collection. The OTRCCOL DFD command deletes formatted data from the Oracle Trace formatter tables for a specific collection.

Oracle Trace Collection Results

Running an Oracle Trace collection produces the following collection files:

• collection_name.CDF is the Oracle Trace collection definition file for your collection.
• collection_name.DAT files are the Oracle Trace output files containing the trace data in binary format.

You can access the Oracle Trace data in the collection files in two ways:

• You can create Oracle Trace Detail Reports from the binary file.
• The data can be formatted to Oracle tables for SQL access and reporting.

Oracle Trace Detail Reports

Oracle Trace Detail Reports display statistics for all items associated with each occurrence of a server event. These reports can be quite large. You can control the report output by using command parameters. Use the following command and optional parameters to produce a Detail Report:

OTRCREP  [optional parameters]  collection_name.CDF

The first step that you may want to take is to run a report called PROCESS.txt. You can produce this report first to give you a listing of specific process identifiers for which you want to run the detail report.

The command parameter used to produce a Process report is:

OTRCREP  -P  collection_name.CDF

Other optional detail report parameters are:

output_path	specifies a full output path for the report files. If not specified, the files will be placed in the current directory
-p	creates a report for a specific process ID obtained from the PROCESS report. For example, a detail report for process 1234 would use -p1234
-w#	sets report width, such as -w132. The default is 80 characters.
-l#	sets the number of report lines per page. The default is 63 lines per page.
-h	suppresses all event and item report headers, producing a shorter report
-s	used with Net8 data only
-a	creates a report containing all the events for all products, in the order they occur in the data collection (.dat) file. The report is a text display of all items for all events.

Formatting Oracle Trace Data to Oracle Tables

Your Oracle Trace server collection can be formatted to Oracle tables for more flexible access by any SQL reporting tool. Oracle Trace will produce a separate table for each event collected. For example, a "parses" event table is created to store data for all parse events that occur during a server collection. Before you can format data, you must first set up the Oracle Trace formatter tables by executing the OTRCFMTC.SQL script on the server host machine.

Use the following command to format an Oracle Trace collection:

OTRCFMT [optional parameters] collection_name.cdf [user/password@database]

If user/password@database is omitted, the user will be prompted for this information.

Oracle Trace allows data to be formatted while a collection is occurring. By default, Oracle Trace formats only the portion of the collection that has not been formatted previously. If you want to reformat the entire collection file, use the optional parameter -f.

Oracle Trace provides several SQL scripts that you can use to access the server event tables. For more information on server event tables and scripts for accessing event data and improving event table performance, refer to the Oracle Trace User's Guide

---