Oracle7 Server Tuning

Library

Product

Contents

Index

Oracle Trace Methodology

Oracle Trace Methodology
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
Oracle Trace Detail Reports
Formatting Oracle Trace Data to Oracle Tables
Oracle Trace Initialization Parameters

Oracle Trace Methodology

This chapter provides an overview of the Oracle Trace product. It covers:

Introduction

Server performance data can be collected using the Oracle Trace product for Oracle Server release 7.3 or higher. Oracle Trace is a general-purpose data collection product that has been introduced with the Oracle Enterprise Manager systems management product family. The Oracle Trace data collection API can be used 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 can be collected by Oracle Trace includes:

SQL statements and statistics on the frequency, duration, and resources used for all parse, execution, and fetch events
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 pagefaults

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 allows host application events to be organized into event sets. This allows the user to collect data for a specific subset of all potential host application events. Oracle has defined the following event sets for Oracle Server release 7.3: 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.

Server collections can be enabled and controlled in the following ways:

The Oracle Trace management application; a Windows-based graphical user interface that is supplied with the Oracle Enterprise Manager Performance Pack (a database option you can license)
Non-graphical, Server-based controls:

The following sections describe the server-based controls. For more information on using the Oracle Trace management application, refer to the 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:

NAME	TYPE	VALUE
oracle_trace_collection_name	string	oracle7
oracle_trace_collection_path	string	?/rdbms/log
oracle_trace_collection_size	integer	5242880
oracle_trace_enable	boolean	FALSE
oracle_trace_facility_name	string	oracle7
oracle_trace_facility_path	string	?/rdbms/admin

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.

A complete discussion of these parameters is provided in "Oracle Trace Initialization Parameters" on page 22-9.

The Oracle Trace parameters may be modified and used by adding them to your initialization file. To start tracing for a database using these parameters, you must at least add the following two parameters to your file:

oracle_trace_enable = TRUE

oracle_trace_facility_name = oracled

The "d" selects the server DEFAULT event set. Use oraclea for the server ALL event set, and oraclee for the Oracle Expert event set.

You must then shut down your database and start it up again before the parameters take effect. Once restarted, the database will start collecting data for the class of data that you selected. To stop the collection, you must shut down the database, set the parameter ORACLE_TRACE_ENABLE = FALSE, and restart the database.

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) is located in the directory identified by oracle_trace_facility_path.
The following files exist in your $ORACLE_HOME/otrace/admin directory: regid.dat, process.dat, and collect.dat. If they do not, you must run the otrccref executable to create them.
The Trace parameters are set to the values that you changed in the initialization file. There is a 35 character maximum to the strings, so on UNIX-based systems always try to use the ?/ to indicate ORACLE_HOME instead of using the full path.
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

You can invoke an Oracle Trace collection for your own session or for another session by using the Oracle Trace stored procedure packages. 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 char maximum)
server_event_set = varchar2: server event set (oracled, oraclea, or oraclee)

Example:

						EXECUTE dbms_oracle_trace_user.set_oracle_trace(TRUE,"MYCOLL","oracled");

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 for specified "osuser"
serial# = number: session serial number from v$session.serial# for specified "osuser"

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:

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 exist in your $ORACLE_HOME/otrace/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 exist in the database. If the packages do not exist then you must run the $ORACLE_HOME/otrace/admin/otrcsvr.sql file 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 job_id can be any numeric value. The user must remember this value in order to stop the collection. The input parameter file contains specific parameter values required for each function. Examples follow. The coll_name (collection name) and cdf_file (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=    <flag> <vendor> <cfnum> <cfval> <fac#> (regid_str>

The server event sets that can be used as values for the fdf_file include oraclea, oracled, and oraclee. See Using Initialization Parameters to Control Oracle Trace on page 22-3 for more information on the server event sets.

The values that must be used for the regid input parameter are: 1 for flag, 192216243 for vendor, 0 for cfnum (cross-product number), 0 for cfval (cross-product value), 5 for fac# (product number). For regid_str, type the service name for the database. For example:

 regid=  1   192216243   0   0   5   allofa1.world

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 will produce a full format, and a value of 0 will produce a partial format. See Formatting Oracle Trace Data to Oracle Tables on page 22-8 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 is used to delete collection files for a specific collection. The otrccol dfd command is used to delete 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:

ORACLE_HOME/otrace/admin/cdf/<collection_name>.cdf is the Oracle Trace collection definition file for your collection
ORACLE_HOME/otrace/admin/cdf <collection_name>.dat files are the Oracle Trace output files containing the trace data in binary format

The Oracle Trace data in the collection files can be accessed in two ways:

The user 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.

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. This is done 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 the 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 will only format the portion of the collection that has not been formatted previously. If the user wants to reformat the entire collection file, the optional parameter -f can be used.

The formatter tables produced by Oracle Trace have names that are a composite of several identifiers relevant to the Oracle Trace collection. Users are advised to use synonyms for these tables in order to make them more easy to use in SQL scripts and reporting tools. The otrcsyn.sql script can be used to create synonyms for the server event tables created. You can edit this script to create your own synonyms for server events.

Oracle Trace provides several SQL scripts that can be used 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

Oracle Trace Initialization Parameters

ORACLE_TRACE_COLLECTION_NAME

This parameter specifies the Oracle Trace collection name. This parameter is also used in the output file names (collection definition file <.cdf> and data file <.dat>).

Default Value: operating system specific

Range of Values: valid collection name up to 16 characters long

ORACLE_TRACE_COLLECTION_PATH

This parameter specifies the directory pathname where Oracle Trace collection definition and data files are located. If you accept the default, the complete file specification is generally (may be different for non-UNIX systems) $ORACLE_HOME/rdbms/log/<collection name>.cdf and <collection name>.dat.

Default Value: operating system specific

Range of Values: full directory pathname

ORACLE_TRACE_COLLECTION_SIZE

This parameter specifies the maximum size, in bytes, of the Oracle Trace collection file. Once the collection file reaches this maximum, the collection is disabled.

Default Value: 5242880

Range of Values: 0 - 4294967295

ORACLE_TRACE_ENABLE

This parameter disables or enables an Oracle Trace collection. If set to TRUE, when you start the server, you automatically start an Oracle Trace collection. Server event data is collected for all database user sessions. To stop a collection, the instance must be shut down and the parameter must be reset to FALSE.

Default Value: FALSE

Range of Values: TRUE/FALSE

ORACLE_TRACE_FACILITY_NAME

This parameter specifies the Oracle Trace product definition file (.fdf file). The file must be located in the directory pointed to by the ORACLE_TRACE_FACILITY_PATH parameter. The product definition file contains definition information for all the events and data items that can be collected for a product that uses the Oracle Trace data collection API. Products can have multiple product definition files (multiple event sets and data items). The Oracle Server has multiple event sets and therefore multiple product definition files. Oracle recommends that you use the "default" event set for Server collections <oracled.fdf>. (See the Oracle Trace documentation for more information on the Server event sets.)

Default Value: operating system specific

Range of Values: valid product definition file name up to 16 characters long

ORACLE_TRACE_FACILITY_PATH

This parameter specifies the directory pathname where Oracle Trace product definition files are located.

Default Value: operating system specific

Range of Values: full directory pathname

Prev Next

Library

Product

Contents

Index

Oracle Trace Methodology

NAME

TYPE

VALUE