1
Overview

Oracle Trace is a general-purpose data collection system that collects data for any instrumented software product. Use Oracle Trace to collect a wide variety of data, such as performance statistics, diagnostics data, system resource usage, and business transaction details.

The components of Oracle Trace are:

• Oracle Trace Manager
• Oracle Trace Collection Services
• Oracle Trace Application Programming Interface (API)

This manual describes the Oracle Trace Manager.

Software developers can use the Oracle Trace API to preconfigure, or instrument, their products for Oracle Trace data collection. Users of a product containing the Oracle Trace API calls can then use the Oracle Trace Collection Services to collect data about specific events that occur in that product.

Refer to the Oracle Enterprise Manager Oracle Trace Developer's Guide for information about the Oracle Trace Collection Services and how to instrument applications using the Oracle Trace API.

Oracle Trace Manager

Oracle Trace provides a graphical Oracle Trace Manager application to create, schedule, and administer Oracle Trace collections for products instrumented with the Oracle Trace API.

The Oracle Trace Manager is a client-based Windows application that runs on the Oracle Enterprise Manager console. The Oracle Trace Manager automatically discovers Oracle Trace instrumented products that are installed on all nodes that are known to the Oracle Enterprise Manager console.

Most Oracle Trace users manage collections for products that are already instrumented with the Oracle Trace API. Therefore, most users only need to be familiar with the data that can be collected for the products and how to use the Oracle Trace Manager application to create and administer data collections.

Note: Oracle Corporation is currently using the Oracle Trace data collection API in two products: Oracle Server release 7.3 or higher (for performance data collection) SQL*Net release 2.3 or higher (for diagnostics data collection) The Oracle Server performance data collected by Oracle Trace includes SQL statements, detailed statistics on SQL events, transaction events, and other useful information. Oracle Server events and data are described in Appendix A. SQL*Net events are described in Appendix B.

Managing Collections

The use and control of Oracle Trace revolves around the concept of a collection. A collection is the data that was collected for events that occurred while an instrumented product was running.

With the Oracle Trace Manager, you can schedule and manage collections. When creating a collection, you define the attributes of the collection, such as the collection name, the products and event sets to be included in the collection, and the start and end time. The Oracle Trace Manager includes a Collection Wizard that facilitates the creation and execution of collections.

Once you create a collection, it can be executed immediately, scheduled to execute at a specific time, or executed at specified intervals. When a collection executes, it produces a file containing the event data for the products that participated in the collection. You can also use a collection as a template for creating other similar collections.

Note: Some instrumented applications also provide their own mechanism for starting and stopping Oracle Trace collections. For example, Oracle Server release 7.3 provides a set of database initialization parameters, as described in Appendix A that you can use to start and stop an Oracle Trace collection. In some situations, using an instrumented product's own controls may be useful. However, you should control most Oracle Trace collections using the Oracle Trace Manager application.

Collecting Event Data

An event is the occurrence of some activity within a product. Oracle Trace collects data for specific, predefined events that occur within a software product that is instrumented with the Oracle Trace API, that is, embedded with Oracle Trace API calls.

There are two types of events:

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

  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.

Items are specific pieces of information about an event. If your product has been instrumented with Oracle Trace API calls, the developer has identified specific events and data items. These items, such as a transaction type or dollar amount, are specific to the instrumented product. Data items can also include statistics on the resources used by that event, such as the CPU time and number of input/output operations (I/Os) performed by the event. For example, the Oracle Server release 7.3 and higher is instrumented for 13 events. Three of these events are:

• Database Connection: A point event that records data such as the server login username.
• SQL Parse: One of the series of SQL processing duration events. This event records a large set of data such as sorts, reads, and cursor number.
• 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.

A complete list of the server events and data items is contained in Appendix A, "Using Oracle Trace for Oracle Server Data Collections."

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.

Accessing Collected Data

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

Oracle Trace Manager provides a mechanism for formatting collection data immediately after a collection is run or at a later time.

Note: Formatter tables are required to format an Oracle Trace collection. To create Oracle Trace formatter tables, use the vobsh command. See the Oracle Enterprise Manager Configuration Guide for more information.

When formatting a collection, you identify the database where the formatted collection is to be created, as follows:

1. Through the Oracle Trace Manager, select a collection to format.
2. Choose the Format command.
3. Specify a target database where the data is to reside.

The collection selected determines which collection definition file and data collection file will be used. The formatted target database determines where the formatted collection data will be stored.

Once the data is formatted, you can access the data using SQL reporting tools and scripts. Oracle Trace ships with a set of predefined SQL scripts for accessing the formatter tables created for the Oracle Server events. In addition, Oracle Trace data can be preconfigured for use in other applications, for example, for use by Oracle Expert. The Oracle Expert database tuning application can access and analyze the Oracle Server SQL event data from the Oracle Trace formatter tables.

Also, you can access event data by running a Detail report from the Oracle Trace reporting utility. This report provides a basic mechanism for viewing a collection's results. You have limited control over what data is reported and how it is presented.

Oracle Trace Environment

Oracle Trace and its associated components run in a client/server environment. Figure 1-1 shows these components.

Figure 1-1 Oracle Trace Environment

Components on the Client

To collect data, the following components must be running on the client:

• Oracle Enterprise Manager (OEM) console

  The OEM repository can reside on either the client or the server. Oracle Trace Manager tables reside within the OEM repository and thus Oracle Trace Manager interacts with the repository.

• Oracle Trace Manager

  The Oracle Trace Manager reads information about managed nodes from the OEM console. The Oracle Trace Manager then communicates information with the Intelligent Agent running on the managed node to start, stop, format, and delete Oracle Trace collections.

  Note: Before you run Oracle Trace for the first time, you must perform discovery within the OEM console. Then, when you run Oracle Trace for the first time, the Oracle Trace Manager automatically leads you through the Oracle Trace discovery process of nodes defined within the OEM console.

Components on the Server

On the server, the following components are required:

• Instrumented products, for example, Oracle Server release 7.3 or higher, other applications (Application 1 and Application 2)
• Oracle Intelligent Agent

  The Oracle Intelligent Agent is an OEM component that ships with the Oracle Server. It contains scripts that control operations submitted by the Oracle Trace Manager.

• Oracle Trace Collection Services

  The Oracle Trace Collection Services component collects the data and creates the collection definition (.cdf) files and the data (.dat) files.

  You can store data collected by Oracle Trace Collection Services in Oracle database tables, referred to as Oracle Trace formatter tables, for access by SQL reporting tools and other products.

  Note: For Oracle Server release 7.3 databases, Oracle Trace stored procedures are required. For Oracle Server release 8 and higher databases, stored procedures are not required. To create stored procedures, you need to run the otrcsvr script (otrcsvr.sql) on the Windows NT platform. On all other platforms, the otrcsvr script executes as part of the server installation.

Configured Components Checklist

This chapter has referred to several Oracle Trace components and other programs that must be configured or running in order for you to use Oracle Trace. This section provides a checklist of these required Oracle Enterprise Manager and Oracle Trace components.

Before you run Oracle Trace, check the following:

• If you are using Oracle Trace for Oracle8 server collections, you must set the value of the ORACLE_TRACE_ENABLE parameter in your INITsid.ORA file to TRUE.
• The Oracle Enterprise Manager console is running, and the nodes that you want to use Oracle Trace on are listed in the console Navigator.
• The Oracle Intelligent Agent is running on the nodes that are targets for Oracle Trace collections.
• The Oracle Trace Collection Services control files exist in the $ORACLE_HOME directory on the target nodes.

  These control files include: regid.dat, process.dat (process.dat has been renamed to facility.dat in Oracle8), and collect.dat. The files are located in $ORACLE_HOME/otrace/admin on UNIX systems and in $ORACLE_HOME\otracexx\admin on Windows NT systems.

  If the control files are missing, run the otrccref.exe executable file, located in the $ORACLE_HOME/bin subdirectory.

• On the Windows NT platform, the Oracle Trace Collection Services are made available in the form of a dynamic link library (dll) that must be present at run time. The dynamic link library file is called otracexx.dll, where xx represents the release number, and it is located in $ORACLE_HOME\bin.

  On all other platforms, the Oracle Trace Collection Services are linked into the application image when the image is built.

• The Oracle Trace formatter tables have been created.

  These tables are required by the Oracle Trace formatter mechanism, which converts and loads an Oracle Trace collection binary file (collection_name.dat) into Oracle tables for access.

  Create these tables in the schema that you want to use for Oracle Trace formatting by using the vobsh command. For information on creating Oracle Trace formatter tables, see the Oracle Enterprise Manager Configuration Guide.

  If you are using Oracle Trace for Oracle 7.3 server collections, check that the Oracle Trace user account, TRACESVR, and the Oracle Trace stored procedure packages: DBMS_ORACLE_TRACE_AGENT and DBMS_ORACLE_TRACE_USER, exist. If they do not, then you must create them by running the otrcsvr.sql script as SYS. The otrcsvr.sql script is located in $ORACLE_HOME/otrace/admin on UNIX systems, and in $ORACLE_HOME\otracexx\admin on NT systems.

  The otrcsvr.sql script is run automatically during database installation on most platforms. However, if your server platform is NT, you must run this script manually.

---