12
Using Oracle Trace

This chapter describes how to use Oracle Trace to collect Oracle server event data.

The topics in this chapter include:

• Overview of Oracle Trace
• Collecting Oracle Trace Data
• Accessing Oracle Trace Collection Results
• Oracle Server Events
• Troubleshooting Oracle Trace

  Note: Oracle Trace will be deprecated in a future release. Oracle Corporation strongly advises the use of SQL Trace and TKPROF instead.

  See Also: Chapter 10, "Using SQL Trace and TKPROF"

Overview of Oracle Trace

Oracle Trace is a general-purpose event-driven data collection product, which the Oracle server uses to collect performance and resource utilization data, such as SQL parse, execute, and fetch statistics, and wait statistics.

Event Data

An event is the occurrence of some activity within a product. Oracle Trace collects data for predefined events occurring within a software product instrumented with the Oracle Trace API. That is, the product has embedded calls to the Oracle Trace API. An example of an event is a parse or fetch.

There are two types of events:

• Point events

  Point events represent an instantaneous occurrence of something in the product. An example of a point event is an error occurrence.

• Duration events

  Duration events have a beginning and an ending. An example of a duration event is a transaction. Duration events can have other events occur within them; for example, an error can occur within a transaction.

The Oracle server has more than a dozen events. The following are three of these events:

• Database Connection: A point event that records data, such as the server login user name.
• SQL Parse: One of the series of SQL processing duration events. This event records a large set of data, such as sorts, resource use, and cursor numbers.
• RowSource: Data about the execution plan, such as SQL operation, position, object identification, and number of rows processed by a single row source within an execution plan.

Event Sets

Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.

Each event set is described by its own product definition file (.fdf). The product definition file is a set of events and their associated data items. The complete set of events defined for an instrumented product is referred to as the ALL event set. Other event sets are then derived from the ALL set. For example, the Oracle Server includes an event set known as the EXPERT set. This set includes SQL event data used by the Oracle Expert tuning application, but excludes other events, such as wait events.

Accessing Collected Data

During a collection, Oracle Trace stores event data in memory and periodically writes it to a collection binary file. This method ensures low resource overhead associated with the collection process. You can access event data collected in the binary file by formatting the data to database tables, which makes the data available for fast, flexible access. These database tables are called Oracle Trace formatter tables.

Collecting Oracle Trace Data

You can collect Oracle Trace data using one of the following mechanisms:

• Oracle Trace Command-Line Interface (CLI)
• Database ORACLE_TRACE_* initialization parameters
• Oracle Trace stored procedures run from PL/SQL

Using the Oracle Trace Command-Line Interface

You can control Oracle Trace server collections with the Oracle Trace CLI (command-line interface). The CLI is invoked by the OTRCCOL executable for the following functions:

• OTRCCOL START job_id input_parameter_file
• OTRCCOL STOP job_id input_parameter_file
• OTRCCOL CHECK collection_name
• OTRCCOL FORMAT input_parameter_file
• OTRCCOL DCF col_name cdf_file
• OTRCCOL DFD col_name username password service [col_id]

The job_id parameter should be set to a value of 1.

The input parameter file contains specific parameter values required for each function, as shown in the following examples. col_name (collection name) and cdf_file (collection definition file) are initially defined in the START function input parameter file.

Starting Collections

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

OTRCCOL START 1 my_start_input_file

where file my_start_input_file contains at least the following input parameters:

col_name= collection name
cdf_file= collection name.cdf
dat_file= collection name.dat
fdf_file= facility definition file.fdf

The server event sets that can be used as values for the fdf_file parameter are ORACLE, ORACLEC, ORACLED, ORACLEE, and ORACLESM, plus CONNECT, SQL_ONLY, SQL_PLAN, SQL_TXN, SQLSTATS, SQLWAITS, and WAITS.

Collection .cdf and .dat files are created in the directory $ORACLE_HOME/otrace/admin/cdf by default for collections started using the CLI (or PL/SQL procedures), unless overridden by EPC_COLLECTION_PATH environment variable.

The collections name parameter can be any valid unique filename.

For Oracle database collections, one additional parameter is required:

regid= 1 192216243 0 0 5 database_SID

The regid parameter record identifies a database by SID where Oracle Trace collection is to be performed. The six elements making up the regid parameter record are as follows, in this order:

• flag (this should be 1 for this usage)
• vendor number (Oracle's vendor number is 192216243)
• cf_num
• cf_val
• facility number (the Oracle server is facility number 5)
• database SID

The cf_num and cf_val elements should set to zero in this basic Oracle database collection regid.

Limiting Data Collected

There are several ways of limiting the amount of data collected. For example, additional regid records can be specified to reduce the amount of collected data, and nonzero cf_num and cf_val can be specified in those situations. In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values.

Restrict By User

In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values. So, for example, if you provide an additional regid record with cf_num = 6 and cf_val = some_DB_userID, then the collection of database event data is limited to only those events performed by that database user.

If you are interested only in collecting database activity for users 23 and 45, then you would provide the following 3 regid records:

regid= 1 192216243 0  0 5 ORCL
regid= 1 192216243 6 23 5 ORCL
regid= 1 192216243 6 45 5 ORCL

Restrict By Process

The input parameter file used by the CLI when starting a collection can also contain the following optional parameters, for both database and nondatabase Oracle Trace collections:

prores= process restriction
max_cdf= maximum collection file size

If no process restriction records are specified, then there are no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process of interest.

Setting a Limit on File Size

The max_cdf parameter is often useful, in several different modes of use. This parameter specifies the maximum amount of Oracle Trace data that should be collected, in bytes (in other words, size of the collection.dat file).

A zero value indicates that no limit should be imposed; otherwise, a positive value up to 2 GB can be specified to stop the data collection when that size limit is reached. In addition, a negative value can be specified (but not less than -2 GB), which instructs Oracle Trace to collect data in its "circular data file" mode: when collection.dat reaches magnitude(max_cdf), then save that data (and delete any previously saved dat file), and then start collecting to a new collection.dat file. This limits the total amount of disk space used, but allows Oracle Trace data collection to continue until you manually stop collection.

Checking the Status of a Collection

Verify that the collection was started.

otrccol check collection_name

The collection should show as active, not active, or not found.

Stopping Collections

The OTRCCOL STOP function halts a running collection as follows:

OTRCCOL STOP 1 my_stop_input_file

where my_stop_input_file contains the collection name and cdf_file name.

Formatting Collections

The OTRCCOL FORMAT function formats the binary collection file to Oracle tables. An example of the FORMAT statement is:

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 full_format value of 0 produces a partial format, which only formats new data; in other words, data collected since any previous format.

Deleting Collections

The OTRCCOL DCF (delete collection files) function deletes collection .cdf and .dat files for a specific collection. The OTRCCOL DFD (delete formatted data) function deletes formatted data from the Oracle Trace formatter tables for a specific collection. You can specify an optional col_id parameter for a selective DFD, where more than one col_id has been created for a collection by multiple (full) formats.

Using Initialization Parameters to Control Oracle Trace

Six Oracle database initialization parameters are set up by default to control Oracle Trace. By logging into a privileged account in the database and executing the SHOW PARAMETER ORACLE_TRACE statement, you see the following parameters:

Table 12-1 Oracle Trace Initialization Parameters

You can modify these Oracle Trace server parameters to allow Oracle Trace collection of server event data and use them by adding them to the initialization file.

However, this method for controlling the Oracle Trace collection is rather inflexible: the collection name cannot be changed without performing a database shutdown. (For Oracle releases prior to 8.1.7, the collection can only be stopped by doing a shutdown, then setting ORACLE_TRACE_ENABLE = FALSE before restarting.) However, with ORACLE_TRACE_ENABLE = TRUE but ORACLE_TRACE_COLLECTION_NAME = "" [that is, empty name string], Oracle Trace collections of database event data can be performed using one of the other collection control mechanisms; for example, the Oracle Trace CLI. These other mechanisms are more flexible than the database initialization parameters. In general, they are preferred over using parameters for collection control.

Enabling Oracle Trace Collections

The ORACLE_TRACE_ENABLE database initialization parameter is false by default. This disables any collection of Oracle Trace data for that server, regardless of the mechanism used to control the collection.

Setting ORACLE_TRACE_ENABLE to true in DBinit.ora enables Oracle Trace collections for the server, but it does not necessarily start a collection when the database instance is started. If the database parameters alone are to be used to start an Oracle Trace collection of database event data, then all 6 ORACLE_TRACE_* parameters must be specified, or have nonnull values by default. Typically, this means that both ORACLE_TRACE_ENABLE must be set to true and a nonnull ORACLE_TRACE_COLLECTION_NAME must be provided (up to 16 characters in length).

ORACLE_TRACE_ENABLE is now a dynamic parameter (as of Oracle8i, Release 3), so it can be set to true or false while the database is running. This can be done for the current database session or for all sessions (including future ones), using ALTER SESSION or ALTER SYSTEM statements. When the database is subsequently shut down and then restarted, the DBinit.ora setting for ORACLE_TRACE_ENABLE is again used to initially enable or disable Oracle Trace collection of database event data.

Determining the Event Set that Oracle Trace Collects

The ORACLE_TRACE_FACILITY_NAME database initialization parameter specifies the event set that Oracle Trace collects, if the database parameters are used to control data collection. The default for this parameter is ORACLED (in other words, Oracle "default" event set).

With database parameters set to start an Oracle Trace Collection, if the database does not begin collecting data, then check the following:

• The event set file, identified by ORACLE_TRACE_FACILITY_NAME, with an .fdf extension, should be in the directory specified by the ORACLE_TRACE_FACILITY_PATH initialization parameter. The exact directory that this parameter specifies is platform-specific.
• The following files should exist in the Oracle Trace admin directory: COLLECT.DAT, FACILITY.DAT (or PROCESS.DAT for Oracle 7.3), and REGID.DAT. If they do not, then run the OTRCCREF executable to create or re-create them.
• The Oracle Trace parameters should be set to the values that you changed in the initialization file. Use Instance Manager to identify Oracle Trace parameter settings.
• Look for an EPC_ERROR.LOG file to see more information about why a collection failed. Oracle Trace creates the EPC_ERROR.LOG file in the current default directory if the Oracle Trace Collection Services OTRCCOL image must log an error.
• Look for *.trc files in the directory specified by the server USER_DUMP_DEST initialization parameter. Searching for "epc" in the *.trc files might give errors. These errors and their descriptions may be found in $ORACLE_HOME/otrace/mesg/epcus.msg (assuming US installation), depending on availability for a given platform.

Controlling Oracle Trace Collections from PL/SQL

Oracle provides an additional Oracle Trace library that allows control of both database and nondatabase Oracle Trace collections from PL/SQL.

Both the name and the location of this new library are platform-dependent. On Unix platforms, the library is $ORACLE_HOME/lib/libtracepls9.so.

On Win32 platforms (for example, Windows NT), the library is %ORACLE_HOME%\bin\oratracepls9.dll.

The otrace/admin directory contains two new SQL scripts that can be used to define a database LIBRARY object for this library, and to define the procedures that can be used to call out to the library from PL/SQL:

• OTRCPLSLIB.SQL
• OTRCPLSCMD.SQL

  Note: By default, OTRCPLSLIB.SQL grants access to the LIBRARY to all database users. You can edit this SQL script to restrict access as appropriate.

In addition, the otrace/demo directory contains several SQL scripts showing PL/SQL examples that start, stop, and then format an Oracle Trace collection. These are respectively:

• OTRCPLSSC.SQL
• OTRCPLSCC.SQL
• OTRCPLSFC.SQL

In the "start collection" example script OTRCPLSSC.SQL, the regid_list contains only a single element: "1 192216243 0 0 5 ORCL". The inner double quotes are required to form a single regid string from its six components. These components are the following, in the order shown:

• flag (this should be 1 for this usage)
• vendor number (Oracle's vendor number is 192216243)
• cf_num
• cf_val
• facility number (Oracle server is facility number 5)
• database SID

For an Oracle Trace database collection, a regid string like this example is required, basically to identify the database SID and to specify that you are collecting for an Oracle server. The cf_num and cf_val should be zero in this basic regid record.

Additional regid records can be specified in order to reduce the amount of collected data. This is when the cf_num and cf_val items are used. In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values. So, if you provide an additional regid record with cf_num = 6 and a cf_val = some_DB_userID, then the collection of database event data is limited to only those events performed by that database user. For example, if you are only interested in collecting database activity for users 23 and 45, then the regid_list consists of three records:

regid_list   VARCHAR2(256) := '"1 192216243 0  0 5 ORCL",
                               "1 192216243 6 23 5 ORCL",
                               "1 192216243 6 45 5 ORCL"';

Similarly, the fdf_list argument could specify the name of a single .fdf file (facility definition file). Typically, this is the case. However, more than one .fdf could be specified in fdf_list if multiple facilities are involved in the collection. Of course, only one .fdf can be specified for any given facility; for example, the database.

On the other hand, the process restriction list prores_list can be empty. This indicates that there are to be no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process.

Other arguments in the "start collection" example in OTRCPLSSC.SQL are single numeric or string values, as shown. For example, the collection name and maximum collection data file size specified by the col_name and maxsize variables, respectively.

Accessing Oracle Trace Collection Results

Running an Oracle Trace collection produces the following collection files:

• collection name.cdf is the binary Oracle Trace collection definition file for the collection. It describes what is to be collected.
• collection name.dat is the output file containing the collected Oracle Trace event data in binary form. When newly created, the empty .dat file contains only 35 bytes of file header information.

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

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

Formatting Oracle Trace Data to Oracle Tables

You can format Oracle Trace binary collection data to Oracle database tables, and you can then access this formatted data using SQL or other tools. The Oracle Trace format produces a separate table for each event type collected; for example, a parse event table is created to store data for all database parse events that were recorded during a server collection.

Use the following syntax to format an Oracle Trace collection with the OTRCFMT formatter utility:

OTRCFMT [options] collection_name.cdf [user/password@database]

If collection .cdf and .dat are not located in the current default directory, then specify the full file path for the .cdf file.

If you omit user/password@database (or any part of it, such as password or database), then OTRCFMT prompts you 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, then use the optional parameter -f (which generate a new collection ID in the formatter tables).

Oracle Trace provides several sample SQL scripts that you can use to access the formatted server event data tables. These are located in OTRCRPT*.SQL in the otrace directory tree.

Running the Oracle Trace Reporting Utility

The Oracle Trace reporting utility displays data for items associated with each occurrence of a server event. These reports can be quite large. You can control the report output by using optional statement qualifiers. Use the following report utility statement syntax:

OTRCREP [options] collection name.cdf

If collection .cdf and .dat are not located in the current default directory, then specify the full file path for the .cdf file.

First, you might want to run a report called PROCESS.txt. You can produce this report to provide a listing of specific process identifiers for which you want to run another report.

You can manipulate the output of the Oracle Trace reporting utility by using the following optional report qualifiers:

Default OTRCREP report output, with no optional qualifiers specified, consists of one text file for each event type collected. Data from all participating processes are combined in each of these text files.

Oracle Server Events

The following sections describe events that have been instrumented in Oracle Server. Most of the events are useful for performance analysis and tuning and workload analysis by Oracle Expert.

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 12-2 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 12-2 Oracle Server Events  

Data Items Collected for Events

Specific kinds of information, known as items, are associated with each event. There are three types of items:

• Resource Utilization Items
• Cross-Product Items
• Items Specific to Oracle Server Events

Resource Utilization Items

Oracle Trace has a standard set of items, called resource utilization items, that it can collect for any instrumented application, including the Oracle Server. In addition, all duration events in the Oracle Server include items for database statistics specific to the Oracle Server.

The standard resource utilization items are described in Table 12-3.

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

Table 12-3 Standard Resource Utilization Items

The implementation of the item is platform specific; if the item is not implemented, the value is 0. For example, currently only CPU times are recorded on Windows NT.

Cross-Product Items

Oracle Trace provides a set of 14 items called cross-product items (also known as cross-facility items for historical reasons). These data items allow programmers to relate events for different products. For example, a business transaction may generate events in two products: an application and the database. The cross-product data items allow these disparate events to be joined for analysis of the entire business transaction.

Cross-product items are reserved for specific products or product types as described in Table 12-4. If you do not use the products for which items are reserved, then you can use those items for your own purposes.

Table 12-4 Cross-Product Items  

Cross-product item 1 (referred to as CROSS_FAC_1) contains data only if data is supplied by an instrumented application.

Cross-product item 2 (CROSS_FAC_2) is reserved for use by a future release of Oracle Forms. Instrumented applications and Oracle Forms 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 Oracle Net. Oracle Net supplies the connection ID to Oracle Trace through CROSS_FAC_3. CROSS_FAC_3 is the key element in coordinating client/server or multitier Oracle Trace collections. Oracle Trace uses the Oracle Net global connection ID as the common element to match in the merger, for example the client and server collection files. The global connection ID is the same for the client and the server connection.

Most Oracle Server events record cross-product items 1 through 6. (Cache I/O does not.)

Items Specific to Oracle Server Events

The Oracle Server product (or facility) definition files (that is, *.fdf) defines items specific to the Oracle Server. Use the item's number to locate it within the list. The formatted datatype describes how the Oracle Trace formatter defines the item when it formats data into an Oracle database.

The Oracle Server items are listed in Table 12-5.

Table 12-5 Oracle Server Items  

Items Associated with Each Event

The following sections describe each event in more detail and provide tables that list the items associated with each event. For item descriptions, refer to Table 12-5.

When you format data, Oracle Trace creates a table for each event type collected. The name of the event data table is V_vendor#_F_product#_E_event#_version, where version is the number of the Oracle Server release. Any periods in the product version are replaced with underscores. You can use the otrcsyn.sql script 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 12-6.

Table 12-6 Additional Columns Included by Oracle Trace Formatter  

collection_number

epid

timestamp

timestamp_nano

timestamp_start

timestamp_nano_start

timestamp_end

timestamp_nano_end

Event Statistics Block

Items relating to database performance appear in several events. For convenience, these items are referenced as the Event Statistics Block. The items in the Event Statistics block are shown in the following list:

UGA_Memory
PGA_Memory
DB_Block_Change
DB_Block_Gets
Consistent_Gets
Physical_Reads
Redo_Entries
Redo_Size
T_Scan_Rows_Got
Sort_Memory
Sort_Disk
Sort_Rows
CPU_Session
Elapsed_Session

Connection Event

The Connection event (event=1) records every time a connection is made to a database. Items associated with the Connection event are shown in the following list:

Session_Index
Session_Serial
Oracle_PID
Login_UID
Login_UName
SID
OS_UName
OS_Term
OS_Mach
OS_Image
Cross-Product Items 1-6 

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

Disconnect Event

The Disconnect event records every time a database disconnection is made. Items associated with the Disconnect event are shown in the following list.

Session_Index
Session_Serial
Event Statistics Block 
Oracle_PID
Cross-Product Items 1-6 

A Disconnect event corresponds 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 identifies the process that has the error. Items associated with the ErrorStack event are shown in the following list.

Session_Index
Session_Serial
Oracle_PID
P1
P2
P3
P4
P5
P6
P7
P8
Cross-Product Items 1-6 

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 is logged each time a session migrates to a shared server process. This event is currently disabled in the Oracle server code.

Items associated with the Migration event are shown in the following list:

Session_Index
Session_Serial
Oracle_PID
Cross-Product Items 1-6 

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 (event=5) registers with Oracle Trace where the application is at a certain point in time. Items associated with the ApplReg event are shown in the following list:

Session_Index
Session_Serial
App_Module
App_Action
Cross-Product Items 1-6 

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 RowSource event logs the number of rows processed by a single row source within an execution plan. Items associated with the RowSource event are shown in the following list:

Session_Index
Session_Serial
Cursor_Number
Position_ID
Row_Count
Cross-Product Items 1-5 

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

RowSource Event

The RowSource event logs the number of rows processed by a single row source within an execution plan. Items associated with the RowSource event for Oracle Server release 8.0.2 or higher are shown in the following list:

Session_Index
Session_Serial
Cursor_Number
Operation_ID
Row_Count
Parent_Op_ID
Position
Object_ID
Operation
Cross-Product Items 1-6 

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

SQLSegment Event

The SQLSegment event is a description of a SQL statement. Items associated with the SQLSegment event are shown in the following list:

Session_Index
Session_Serial
Cursor_Number
SQL_Text_Hash
Lib_Cache_Addr
SQL_Text_Segment
SQL_Text
Cross-Product Items 1-6 

A SQL segment does not have an explicit identifier. The SQL_Text_Hash field is always the same for each occurrence of a 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 usually should identify a particular SQL statement for a session. If you add Cursor_Number, you identify a particular occurrence of a SQL statement within the session.

Wait Event

The wait event shows the total waiting time in hundredths of seconds for all responses. Items associated with the wait event are shown in the following list:

Session_Index
Session_Serial
P1
P2
P3
Description 
Cross-Product Items 1-7 

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 Parse event records the start and end of the parsing phase during the processing of a SQL statement. The parsing phase occurs when the SQL text is read in and broken down (parsed) into its various components. Tables and fields are identified, as well as which fields are sort criteria and which information needs to be returned. Items associated with the parse event are shown in the following lists:

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

Execute Event

The Execute event is where the query plan is executed. That is, the parsed input is analyzed to determine exact access methods for retrieving the data, and the data is prepared for fetch if necessary. Items associated with the Execute event are shown in the following lists:

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

Fetch Event

The Fetch event is the actual return of the data. Multiple fetches can be performed for the same statement to retrieve all the data. Items associated with the Fetch event are shown in the following lists:

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 LogicalTX event logs the start and end of a logical transaction (that is, statements issued that may cause a change to the database status). Items associated with the LogicalTX event are shown in the following lists:

The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction. Or, use Session_Index, Session_Serial, and TX_SO_Addr.

PhysicalTX Event

The PhysicalTX event logs the start and end of a physical transaction (that is, statements issued that caused a change in database status). Items associated with the PhysicalTX event are shown in the following lists:

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

Event Set File Names

Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.

Table 12-7 Server Event Set File Names  

Troubleshooting Oracle Trace

Use the following sections to troubleshoot problems while using Oracle Trace.

Oracle Trace Configuration

If you suspect an Oracle Trace configuration problem:

• Examine the EPC_ERROR.LOG file for details of any logged Oracle Trace errors.
• Look for the administration files on $ORACLE_HOME/otrace/admin (*.dat files) and run otrccref to re-create the Oracle Trace *.dat files if the files do not exist.
• Verify that the .fdf files are in the $ORACLE_HOME/otrace/admin/fdf directory.
• Verify the correct version of Oracle Trace Collection Services match the appropriate Oracle Server version

  % $ORACLE_HOME/bin/otrccol version
  
  

  If the Returned Value Is:	Then the Command-Line Interface Release Is:
  1	733
  2	803
  3	734
  4	804
  5	805
  6	813
  7	814
  8	815
  9	806
  10	816
  11	817
  12	901

• To verify that a collection is currently running, use the command-line interface to check the status:

  % $ORACLE_HOME/bin/otrccol check collection_name
  
  

To test the CLI:

1. CLI needs to run from a privileged account, for example, the Oracle operating system user account.
2. The Oracle home and SID environmental variables must be set properly.

   To check settings on UNIX:

   printenv ORACLE_HOME
   printenv ORACLE_SID
   

   To set settings on UNIX:

   setenv ORACLE_HOME path
   setenv ORACLE_SID sid
   
   

   There should be one CLI for each ORACLE_HOME. For example, if you have two Oracle Server release 7.3.3 instances sharing the same ORACLE_HOME, there should be only one CLI.

3. Verify that the collection name has not already been used before you start the collection.

   Look for collection name.cdf and .dat files in:

   • $ORACLE_HOME/otrace/admin/cdf directory
   • The directory specified in database parameter ORACLE_TRACE_COLLECTION_PATH
   • The directory specified by EPC_COLLECTION_PATH environment variable
4. If you want to generate database activity for this collection, connect to the database.
   • For Oracle Server release 7.3.x, connect to the service before you create your collection.
   • For Oracle Server release 8.0, you can connect to the database anytime and the processes are registered.

Server Environment

If you suspect a server environment problem, verify the following:

• The server node has sufficient disk space for the collection output files. If there is not sufficient disk space, the collection stops. To limit the size of collections, use the Trace option for limiting collection size. For a description on how to limit collection size, see the Using the Oracle Trace CLI section in this chapter.

  To solve the immediate problem, stop the collection, and free up space so Oracle Trace can end the collection.

  Initially limiting the collection to specific users or wait events also helps to limit the amount of data collected. Limiting users and wait events is available for Oracle Server releases 8.0.4 and higher.

• Your session does not participate in more than five collections. Sessions log data for the five most recent collections. Thus, if you have more than five collections, data is missing for the oldest collection.

Missing Data

Wait Times Were Not Collected

Wait times are collected only if the INITsid.ORA parameter, TIMED_STATISTICS, is set to true.

Missing SQL Statement from Collection

If an expected SQL statement does not appear to be in your collection, it may be because a small amount of data in the Oracle Trace data collection buffers may not have been flushed out to the collection data file, even though the collection has been stopped. Additional database activity should flush these buffers to disk, and shutting down the database also forces a flush of these buffers.

Collection Is Too Large

There may be times when a collection is too large. Starting with Oracle Server release 8.0.4, you can collect data for specific users and specific wait event types to minimize the size of the collection. Because, almost always, the server is waiting for a latch, lock, or resource, wait event data for a brief collection can be quite extensive.

Collection Is Empty

In Oracle8 databases (prior to Oracle 8.1.7), the ORACLE_TRACE_ENABLE parameter in the INITsid.ORA file on the server must be set to true before the database is started. Starting with Oracle 8.1.7 it is dynamic and may be modified through ALTER_SESSION or ALTER_SYSTEM. (For Oracle7 the ORACLE_TRACE_ENABLE parameter should be left as false, unless you are using the init.ora parameter method to start or stop collections.) You can also see this problem if there are too many collections running concurrently.

Oracle Trace Could Not Access Memory

On Windows NT systems, if you are running Oracle Trace collections and an error occurs indicating Oracle Trace could not access memory, the collect.dat file has become full. You must create a new .dat file by running the otrccref.exe image located in the $ORACLE_HOME/bin directory. However, database services must be shutdown to release the collect.dat file for the otrccref script to be able to create the new collect.dat file. You can also increase the number of collect.dat records to more than the default of 36 records (for example, otrccref -c50 to create a new otrace/admin/collect.dat file containing 50 records).

Oracle7 Stored Procedures

If the attempt to collect Oracle Trace data for an Oracle7 database results in the message "Error starting/stopping Oracle7 database collection," this may be due to missing database stored procedures that Oracle Trace uses to start and stop Oracle7 collections.

• Check for stored procedures (for Oracle Server releases 7.3.x)

  To check for stored procedures using the Oracle Enterprise Manager console, use the Navigator and the following path:

  Networks > Databases > your_database > Schema Objects > Packages > SYS
  
  

• Look for stored procedures starting with DBMS_ORACLE_TRACE_xxx.

  To check for stored procedures using Oracle Server Manager or Oracle SQL*Plus Worksheet:

  select object_name from dba_objects where object_name like '%TRACE%' 
  and object_type = 'PACKAGE'; 
  OBJECT_NAME
  DBMS_ORACLE_TRACE_AGENT
  DBMS_ORACLE_TRACE_USER
  2 rows selected.
  
  

For Oracle7, Oracle Trace required that these stored procedures be installed on the database. These SQL scripts may be automatically run during database installation depending on the platform-specific installation procedures. If they are not executed during database installation, you must run these scripts manually. You can add these stored procedures to the database by running the otrcsvr.sql script from $ORACLE_HOME/otrace/admin) from a privileged database account (SYS or INTERNAL). To run the script, set the default to the path were the script is located. This script runs other scripts that do not have the path specified. These other scripts fail if you are not in the directory where these scripts will run.

EPC_ERROR.LOG File

The EPC_ERROR.LOG file provides information about the collection processing, specifically the Oracle Trace Collection Services errors.

The EPC_ERROR.LOG file is created in the current default directory.

Formatter Tables

Oracle Server releases 7.3.4 and 8.0.4 and later automatically create the formatter tables. Prior to Oracle Server releases 7.3.4 and 8.0.4, you must run the otrcfmtc.sql script from Oracle Server Manager or Oracle SQL*Plus Worksheet as the user who will be formatting the data. If you must manually execute otrcfmtc.sql to create the formatter tables, use the SQL script from the same Oracle home as your collections to be formatted.

The otrcfmtc.sql script is located in the $ORACLE_HOME/otrace/admin directory.

Formatting error might be due to one of the following causes:

1. The user did not run the script to create the formatter tables (valid for releases of Oracle Server prior to 7.3.4 and 8.0.4).
2. The formatter tables were not created by the same user ID that was used when the collection was created (valid for releases of Oracle Server prior to 7.3.4 and 8.0.4).

Look for EPC_COLLECTION.

To check for formatter tables using SQL Worksheet:

CONNECT username/password@service name
DESCRIBE epc_collection