Oracle Enterprise Manager Oracle Trace User's Guide

Library

Product

Contents

Index

Using Oracle Trace for Oracle Server Data Collections

• Using Oracle Trace for Server Performance Data Collection
• Using INIT.ORA Parameters to Control Oracle Trace
• Using Stored Procedure Packages to Control Oracle Trace
• Using the Oracle Trace Command Line Interface
• Oracle Trace Collection Results
• Additional Information: Oracle Trace INIT.ORA Parameters
• Oracle Server Release 7.3 Events
• Items Associated with Each Event

Server performance data can be collected using the Oracle Trace product for Oracle Server release 7.3 or higher. 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 page faults

• instructions for creating Oracle Server collections

• information on Oracle Server events and data that can be collected with Oracle Trace

Oracle Trace allows host application events to be organized into event sets. This allows you to collect data for a specific subset of all potential host application events. Oracle Corporation 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 Corporation recommends using the DEFAULT event set.

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

• 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)

• Non-graphical, server-based controls:
  • Oracle Trace database initialization parameters

  • Oracle Trace stored procedure packages

  • Oracle Trace command line interface

Using INIT.ORA Parameters to Control Oracle Trace

For a complete list and description of the Oracle Trace INIT.ORA parameters, see Additional Information: Oracle Trace INIT.ORA Parameters on page A-7.

The Oracle Trace parameters may be modified and used by adding them to your INITsid.ORA file. To start tracing for a database using these parameters, you must minimally add the following 2 parameters to your .ORA file:

oracle_trace_enable = TRUE 

oracle_trace_facility_name = oracled  

You must then shut down your database and start it up again before the parameters take effect. Once restarted, the database begins collecting data for the class of data that you selected. To stop the collection, you must shut down the database, set the INITsid.ORA 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 Oracle Trace parameters are set to the values that you changed in the INITsid.ORA file. There is a 35-character maximum to the strings, so 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.

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)

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

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#

EXECUTE dbms_oracle_trace_user.set_oracle_trace_in_session (8,12,TRUE,"NEWCOLL", "oracled");  

• The server event set file identified by server_event_set exists. If there is not a full file specification on this field, then the file should be located in the directory identified by oracle_trace_facility_path in the INITsid.ORA 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.

• The user has the EXECUTE privilege on the stored procedure.

• 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 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

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

otrccol stop 1234 my_stop_input_file

The otrccol format command formats the binary collection file to tables in the Oracle database. An example of the format command is as follows:

	otrccol format my_format_input_file 

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

Use the otrccol dcf command to delete collection files for a specific collection. Use the otrccol dfd command to delete formatted data from the Oracle Trace formatter tables for a specific collection.

Oracle Trace Collection Results

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

• Create Oracle Trace Detail reports from the binary file.

• Format the data to the tables in the Oracle database for SQL access and reporting.

ORACLE_TRACE_COLLECTION_NAME This parameter specifies the Oracle Trace collection name. This parameter is also used in the output filenames (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 $ORACLE_HOME/rdbms/log/<collection name>.cdf and <collection name>.dat. This specification may be different for non-UNIX systems.

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 to 4294967295

ORACLE_TRACE_ENABLE This parameter disables or enables an Oracle Trace collection. If ORACLE_TRACE_ENABLE is 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, you must shut down the instance and reset the parameter 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 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). The Oracle Server has multiple event sets and, therefore, multiple product definition files. Oracle Corporation recommends that you use the default event set for Server collections <oracled.fdf>.

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

The Oracle7 Server Application Registration, Wait, Fetch, Execute, and Parse events can be associated with the transaction and database connection from which they occurred using cross-product items 3 and 4.

There are two types of events: point events and duration events. Point events represent an instantaneous occurrence of something in the instrumented product. An example of a point event is an error occurrence. Duration events have a beginning and ending. An example of a duration event is a transaction. Duration events can have other events occur within them; for example, the occurrence of an error within a transaction.

Table A-1 lists the Oracle Server events instrumented for Oracle Trace. For more detailed descriptions, refer to the section for the event in which you are interested.

Table A-1: Oracle Server Events

Event	Description	Type of Event
Connection	Records each connection to a database.	Point
Disconnect	Records each disconnection from a database.	Point
ErrorStack	Code stack for core dump.	Point
Migration	Session migration between shared server processes.	Point
ApplReg	Application context information.	Point
RowSource	Row information.	Point
SQLSegment	Text of SQL statement.	Point
Wait	Records a generic WAIT event. Context is provided in the event strings.	Point
Parse	Event containing SQL query information (actual text of query).	Duration
Execute	Event containing information for execution of SQL query plan.	Duration
Fetch	Event containing actual row retrieval information.	Duration
LogicalTX	Event marking the first time a database update is performed that may change the database status.	Duration
PhysicalTX	Event marking a definite change in database status.	Duration

Data Items Collected for Events

• resource utilization items

• cross-product items

• product-specific items

The standard resource utilization items are described in Table A-2. Items specific to Oracle Server release 7.3 are described in Table A-4.

An Oracle Trace collection can be formatted to Oracle tables for access, analysis, and reporting. The last column contains the data type for data items formatted to the Oracle database.

Table A-2: Standard Resource Utilization Items

Item Name	Description	Item ID	Datatype of Formatted Data
UCPU	Amount of CPU time in user mode	129	NUMBER
SCPU	Amount of CPU time in system mode	130	NUMBER
INPUT_IO	Number of times file system performed input	131	NUMBER
OUTPUT_IO	Number of times file system performed output	132	NUMBER
PAGEFAULTS	Number of hard and soft page faults	133	NUMBER
PAGEFAULT_IO	Number of hard page faults	134	NUMBER
MAXRS_SIZE	Maximum resident set size used	135	NUMBER

Cross-Product Items

Cross-product items are reserved for specific products or product types as described in Table A-3. Cross-product item 1 (referred to as CROSS_FAC_1) will only contain data if data is supplied from an instrumented application.

Cross-product 2 (CROSS_FAC_2) is reserved for use by a future release of Oracle Forms. Instrumented applications and Oracle Forms will pass identification data to the Oracle Server collection through these cross-product items.

Cross-product item 3 (CROSS-_FAC_3) is reserved for use by SQL*Net. SQL*Net supplies the connection ID to Oracle Trace through CROSS-_FAC_3. CROSS_FAC_3 is the key element in coordinating client/server Oracle Trace collections. Oracle Trace uses the SQL*Net global connection ID as the common element that gets matched up in the merging of the client and server collection files. The global connection ID is the same for the client and the server connection. It is used as the Oracle Trace registration ID that gets logged with the CROSS_FAC_3 event collection.

Each Oracle Server event will record cross-product items 1-5.

Note:

Items Specific to Oracle Server Events

Items Associated with Each Event

When you format data, Oracle Trace creates a table for each event collected. The name of the event data table is V_vendor#_F_product#_E_event#_version. Any periods in the product version are replaced with underscores. You can use the otrcsyn.sql script, described in Defining Synonyms on page 4-8, to create synonyms for these tables.

The Oracle Trace formatter creates a column for each event item. For point events, the column name is the same as the item name. For duration events, the items for the start event have _START appended to the item name and the items for the end event have _END appended to the item name.

The formatter automatically includes additional columns for collection number, process identifier, and timestamp information as described in Table A-5.

Table A-5: Additional Columns Included By Oracle Trace Formatter

Column Name	Description	Datatype
collection_ID	collection number, automatically assigned by the formatter	NUMBER(4)
epid	process ID number	NUMBER(8)
timestamp	logged time for point events	DATE
timestamp_nano	nanoseconds of logged time for point events	NUMBER
timestamp_start	duration event start time	DATE
timestamp_nano_start	nanoseconds of duration event start time	NUMBER
timestamp_end	duration event end time	DATE
timestamp_nano_end	nanoseconds of duration event end time	NUMBER

Event Statistics Block

Connection Event

The Oracle Server uses the combination of Session_Index and Session_Serial to uniquely identify a connection. SQL*Net uses the connection ID, stored in CROSS_FAC_3, to uniquely identify a connection.

Disconnect Event

A Disconnect event will correspond to at most one Connection event. Therefore, the same fields uniquely identify a disconnect: either the combination of Session_Index and Session_Serial, or CROSS_FAC_3.

ErrorStack Event

The ErrorStack event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific ErrorStack event.

Migration Event

The Migration event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific Migration event.

ApplReg Event

The ApplReg event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific ApplReg event.

RowSource Event

The combination of Session_Index, Session_Serial, Cursor_Number, and Position_ID uniquely identifies a RowSource event.

SQLSegment Event

An SQL segment does not have an explicit identifier. The SQL_Text_Hash field will always be the same for each occurrence of an SQL statement but multiple statements can have the same hash value. If a statement is forced out of the library cache and then swapped back in, the same statement can have multiple values for Lib_Cache_Addr. The combination of Session_Index, Session_Serial, SQL_Text_Hash, and Lib_Cache_Addr should usually identify a particular SQL statement for a session. If you add Cursor_Number, you will identify a particular occurrence of an SQL statement within the session.

Wait Event

The Wait event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Description, Timestamp, and Timestamp_Nano should uniquely identify a specific Wait event.

Parse Event

The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific Parse event.

Execute Event

The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific Execute event.

Fetch Event

The combination of Session_Index, Session_Serial, Cursor_Number, SQL_Text_Hash, Timestamp, and Timestamp_Nano uniquely identifies a specific Fetch event.

LogicalTX Event

The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction.

PhysicalTX Event

The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction.

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index