14
Using Oracle Trace

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

This chapter contains the following sections:

Introduction to Oracle Trace

Oracle Trace is a general-purpose data collection product and is part of the Oracle Enterprise Manager systems management product family. The Oracle server uses Oracle Trace to collect performance and resource utilization data, such as SQL Parse, Execute, Fetch statistics, and Wait statistics.

Using Oracle Trace Data

Among the many advantages of using Oracle Trace is the integration of Oracle Trace with many other applications. You can use Oracle Trace data collected for the Oracle server in the following applications, as shown in Figure 14-1:

Oracle Expert

You can use information collected with Oracle Trace as an optional source of SQL workload data in Oracle Expert. This SQL data is used when recommending the addition or removal of indexes.

See Also:
For more information, see Database Tuning with the Oracle Tuning Pack.
Oracle Trace Data Viewer

Oracle Trace Data Viewer is a simple viewer for inspecting Oracle Trace collections containing SQL and Wait statistics. You can export Oracle Trace Data to the following products for further analysis:
- SQL Analyze
  
  You can select one or more rows in Data Viewer and save the SQL statement text to a file that you can import into SQL Analyze. You can then use SQL Analyze to tune these individual statements.
- Third-Party Tools, such as Microsoft Excel
  
  SQL in Data Viewer can be saved to a CSV (Comma Separated Value) file for viewing in third-party tools, such as Microsoft Excel.

Figure 14-1 Integration of Oracle Trace with Other Applications

Importing Oracle Trace Data into Oracle Expert

You can use Oracle Trace to collect workload data for use in the Oracle Expert application. Oracle Trace collects resource utilization statistics for SQL statements executing against a database in real time. Oracle Trace allows you to collect data about all the SQL statements executing against a database during periods of poor performance.

You control the scheduling and duration of an Oracle Trace collection period. To obtain SQL workload data for a 15-minute period of poor performance, stop collection immediately after the poor performance interval ends.

Importing Data Viewer SQL Into Oracle SQL Analyze

While using Data Viewer, you can select one or more rows in the top portion of the Data View window to save to a file. When you choose SQL (SQL Analyze Format) from File/Save, a file containing query text is saved. You can then import this *.sql file into Oracle SQL Analyze for tuning of the selected statements.

Oracle SQL Analyze can show you the execution plan for individual queries and let you experiment with various optimizer modes and hints.

Importing Data Viewer Information into Third-Party Tools

While using Data Viewer, you can select one or more rows in the top portion of the Data View window to save to a file. When you choose the CSV file format, Oracle Trace creates a *.csv file that you can load into a third-party tool, such as a Microsoft Excel spreadsheet.

Using Oracle Trace Manager

Oracle Trace provides a graphical Oracle Trace Manager application to create, schedule, and administer Oracle Trace collections for products containing Oracle Trace calls.

The Oracle server has been coded with Oracle Trace API calls to collect both SQL and Wait statistics with a minimum of overhead. Using the Oracle Trace Manager graphical user interface you can:

Schedule collections.
Filter collections by user.
Filter collections by type of Wait event.
Format collected data to database tables to preserve historical data.
View SQL and Wait statistics using Oracle Trace Data Viewer.

Managing Collections

Use and control of Oracle Trace revolves around the concept of a "collection." A collection is data collected for events that occurred while a product with Oracle Trace code 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 data to be included in the collection, and the start and end times. The Oracle Trace Manager includes a Collection Wizard that facilitates the creation and execution of collections.

After you create a collection you can execute it immediately or schedule it to execute at a specific time or at specified intervals. When a collection executes, it produces a file containing the data for the products participating in the collection. You can also use a collection as a template for creating other similar collections.

Collecting 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 created with the Oracle Trace API. That is, the product is embedded with Oracle Trace API calls. 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 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, an error can occur within a transaction.

The Oracle server is instrumented for 13 events. Three of these events are:

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.

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 event data collected in the binary file by formatting the data to predefined tables which 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 or at a later time.

When formatting a collection, you identify the database where Oracle Trace Manager creates the formatted collection as follows:

Using Oracle Trace Manager, select a collection to format.
Choose the Format statement.
Specify a target database where the data is to reside.

The collection you select determines which collection definition file and data collection file is used. The formatted target database determines where the formatted collection data is stored.

After the data is formatted, you can access the data using the Oracle Trace Data Viewer or by using SQL reporting tools and scripts.

Also, you can access event data by running the 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.

Using Oracle Trace Data Viewer

After using Oracle Trace to collect data, run the Data Viewer by selecting "View Formatted Data..." from the Oracle Trace Collection menu. Or you can select it directly from the Oracle Diagnostics Pack toolbar. Data Viewer can compute SQL and Wait statistics and resource utilization metrics from the raw data that is collected. After Data Viewer computes statistics, targeting resource intensive SQL becomes a much simpler task.

Data Viewer computes SQL statistics from data collected by Oracle Trace Manager for all executions of a query during the collection period. Resource utilization during a single execution of a SQL statement may be misleading due to other concurrent activities on the database or node. Combining statistics for all executions may lend a clearer picture about the typical resource utilization occurring when a given query is executed.

Note:
You can filter out SQL statements executed by SYS.

Oracle Trace Predefined Data Views

SQL and Wait statistics are presented in a comprehensive set of Oracle Trace predefined data views. Within Wait statistics, a data view is the definition of a query into the data collected by Oracle Trace. A data view consists of items or statistics to be returned and optionally a sort order and limit of rows to be returned.

With the data views provided by Data Viewer, you can:

Examine important statistical data, for example, elapsed times or disk-to-logical-read hit rates.
Drill down as needed to get additional details about the statement's execution.

In addition to the predefined data views, you can define your own data views using the Create Data View Wizard.

After Data Viewer has computed SQL and Wait statistics, a dialog box showing the available data views appears. SQL Statistic data views are grouped by I/O, Parse, Elapsed Time, CPU, Row, Sort, and Wait statistics as shown in Figure 14-2. A description of the selected data view is shown on the right-hand side of the screen.

Figure 14-2 Oracle Trace Data Viewer - Collection Screen

Table 14-1 explains the predefined data views shown in the previous figure as provided by Oracle Trace.

Table 14-1 Predefined Data Views Provided By Oracle Trace (Page 1 of 5)

View Name	Sort By	Data Displayed	Description
Logical Reads	Total number of logical reads performed for each distinct query.	Total number of blocks read during parses, executions and fetches. Logical reads for parses, executions and fetches of the query.	Logical data block reads include data block reads from both memory and disk. Input/output is one of the most expensive operations in a database system. I/O intensive statements can monopolize memory and disk usage causing other database applications to compete for these resources.
Disk Reads	Queries that incur the greatest number of disk reads.	Disk reads for parses, executions, and fetches.	Disk reads also known as physical I/O are database blocks read from disk. The disk read statistic is incremented once per block read regardless of whether the read request was for a multiblock read or a single block read. Most physical reads load data, index, and rollback blocks from the disk into the buffer cache. A physical read count can indicate a high miss rate within the data buffer cache.
Logical Reads/Rows Fetched Ratio	Number of logical reads divided by the number of rows fetched for all executions of the current query.	Total logical I/O. Total number of rows fetched.	The more blocks accessed relative to the number of rows actually returned the more expensive each row is to return. Can be a rough indication of relative expense of a query.
Disk Reads/Rows Fetched Ratio	Number of disk reads divided by the number of rows fetched for all executions of the current query.	Total disk I/O. Total number of rows fetched.	The greater the number of blocks read from disk for each row returned the more expensive each row is to return. Can be a rough indication of relative expense of a query.
Disk Reads/Execution Ratio	Total number of disk reads per distinct query divided by the number of executions of that query.	Total disk I/O. Logical I/O for the query as well as the number of executions of the query.	Indicates which statements incur the greatest number of disk reads per execution.
Disk Reads/Logical Reads Ratio	Greatest miss rate ratio of disk to logical reads.	Individual logical reads. Disk reads for the query as well as the miss rate.	The miss rate indicates the percentage of times the Oracle server needed to retrieve a database block on disk as opposed to locating it in the data buffer cache in memory. The miss rate for the data block buffer cache is derived by dividing the physical reads by the number of accesses made to the block buffer to retrieve data in consistent mode plus the number of blocks accessed through single block gets. Memory access is much faster than disk access, the greater the hit ratio, the better the performance.
Re-Parse Frequency	Queries with the greatest reparse frequency.	Number of cache misses. Total number of parses. Total elapsed time parsing. Total CPU clock ticks spent parsing.	The Oracle server determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, then the user process uses this parsed representation and executes the statement immediately. If missed in the library cache, then re-check the statement for syntax, valid objects, and security. Also, the optimizer must determine a new execution plan. The parse count statistic is incremented for every parse request, regardless of whether the SQL statement is already in the shared SQL area.
Parse/Execution Ratio	Number of parses divided by the number executions per statement.	Individual number of parses. Number of executions.	The count of parses to executions should be as close to one as possible. If there are a high number of parses per execution, then the statement has been needlessly reparsed. This could indicate the lack of use of bind variables in SQL statements or poor cursor reuse. Reparsing a query means that the SQL statement has to be re-checked for syntax, valid objects and security. Also, a new execution plan needs to be determined by the optimizer.
Average Elapsed Time	Greatest average time spent parsing, executing and fetching on behalf of the query.	Individual averages for parse, execution and fetch.	The average elapsed time for all parses, executions and fetches-per-execution are computed, then summed for each distinct SQL statement in the collection.
Total Elapsed Time	Greatest total elapsed time spent parsing, executing and fetching on behalf of the query.	Individual elapsed times for parses, executions and fetches.	The total elapsed time for all parses, executions and fetches are computed, then summed for each distinct SQL statement in the collection.
Parse Elapsed Time	Total elapsed time for all parses associated with a distinct SQL statement.	SQL cache misses. Elapsed times for execution and fetching. Total elapsed time.	During parsing the Oracle server determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, then the user process uses this parsed representation and executes the statement immediately. If missed in the library cache, then the statement needs to be rechecked for syntax, valid objects and security. Also, a new execution plan needs to be determined by the optimizer.
Execute Elapsed Time	Greatest total elapsed time for all executions associated with a distinct SQL statement.	Total elapsed time. Individual elapsed times for parsing and fetching.	The total elapsed time of all execute events for all occurrences of the query within an Oracle Trace collection.
Fetch Elapsed Time	Greatest total elapsed time for all fetches associated with a distinct SQL statement.	Number of rows fetched. Number of fetches. Number of executions. Total elapsed time. Individual elapsed times for parsing and executing.	The total elapsed time spent fetching data on behalf of all occurrences of the current query within the Oracle Trace collection.
CPU Statistics	Total CPU clock ticks spent parsing, executing and fetching on behalf of the SQL statement.	CPU clock ticks for parses, executions and fetches. Number of SQL cache misses and sorts in memory.	When SQL statements and other types of calls are made to an Oracle server, a certain amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data, a runaway query, in memory sorts or excessive reparsing can potentially consume a large amount of CPU time. CPU time displayed is in terms of the number of CPU clock ticks on the operating system housing the database.
Number of Rows Returned	Greatest total number of rows returned during execution and fetch for the SQL statement.	Number of rows returned during the fetch operation as well as the execution rows.	Targets queries that manipulate the greatest number of rows during fetching and execution. May mean that high gains can be made by tuning row intensive queries.
Rows Fetched/Fetch Count Ratio	Number of rows fetched divided by the number of fetches.	Individual number of rows fetched. Number of fetches.	This ratio shows how many rows were fetched at a time. It may indicate the level to which array fetch capabilities have been utilized. A ratio close to one may indicate an opportunity to optimize code by using array fetches.
Sorts on Disk	Queries that did the greatest number of sorts on disk.	Sort statistics for SQL statements. Number of in memory sorts. Total number of rows sorted.	Sorts on disk are sorts that could not be performed in memory, therefore they are more expensive because memory access is faster than disk access.
Sorts in Memory	Queries that did the greatest number of sorts in memory.	Sort statistics for SQL statements. Number of disk sorts. Total number of rows sorted.	Sorts in memory are sorts that could be performed completely within the sort buffer in memory without using the temporary tablespace segments.
Rows Sorted	Queries that sorted the greatest number of rows.	Number of in memory sorts. Number of sorts on disk.	Returns sort statistics for SQL statements ordered by queries that sorted the greatest number of rows.
Waits by Total Wait Time	Highest total wait time per distinct type of wait.	Average wait time, total wait time and number of waits per wait type.	Waits are sorted by wait description or type that had the greatest cumulative wait time for all occurrences of the wait type within the collection.
Waits by Average Wait Time	Highest average wait time per wait type.	Average wait time, total wait time and number of waits per wait type.	Waits are sorted by wait description or type that had the greatest average wait time for all occurrences of the wait type within the collection.
Waits by Event Frequency	Frequency of waits per wait type.	Number of waits per wait type, average wait time, and total wait time.	Waits are sorted by wait events or wait descriptions that appear most frequently within the collection.

Viewing Oracle Trace Data

Double clicking on SQL or Wait event data views provided by Data Viewer causes Oracle Trace to query the collection data and display data sorted by criteria described in the data view's description.

For example, double clicking the "Disk Reads/Log Reads Ratio" view returns data sorted by queries with the highest data buffer cache miss rate. This also displays the individual disk and logical read values.

Double clicking the "Average Elapsed Time" data view returns data sorted by queries that took the greatest average elapsed time to parse, execute, and fetch. It also displays the average elapsed times for parsing, execution, and fetching.

Figure 14-3 shows data in the "Average Elapsed Time" data view. Query text and statistics appear in the top portion of the window. Clicking any column headers causes the Data Viewer to sort rows by the statistic in that column.

Figure 14-3 Oracle Trace Data Viewer - Data View Screen

The currently selected data view's SQL text is shown in the lower portion of the window in the SQL Statement property sheet. Full statistical details about the currently selected data view also appear in the Details property sheet.

When examining a data view like that shown in Figure 14-3, you can print either of the following:

Data view statistics, located in the top portion of the screen.
Current SQL statement text in formatted output plus details on all statistical data collected for the currently selected query, located in the Details property page.

Window focus at the time of printing determines which portion of the screen is printed. For example, if focus is on the top portion of the screen, then the tabular form of all statistics and SQL for this data view is printed.

SQL Statement Property Page

The SQL Statement property page displays the currently selected query in a formatted output.

Details Property Page

The Details property page displays a detailed report on statistics for all executions of a given query within an Oracle Trace collection. Text for the currently selected SQL statement is posted at the end of the property page.

Example of Details Property Page

Statistics for all parses, executions, and fetches of the SQL statement.

The number of misses in library cache during Parse: 1.000000

Elapsed time statistics for the SQL statement:

Average Elapsed Time:     0.843000
Total Elapsed Time:       0.843000

Total Elapsed Parse:      0.000000
Total Elapsed Execute:    0.843000
Total Elapsed Fetch:      0.000000

Average Elapsed Parse:    0.000000
Average Elapsed Execute:  0.843000
Average Elapsed Fetch:    0.000000

Number of times parse, execute and fetch were called:

Number of Parses:       1
Number of Executions:   1
Number of Fetches:      0

Logical I/O statistics for parse, execute and fetch calls:

Logical I/O for Parses:              1
Logical I/O for Executions:        247
Logical I/O for Fetches:             0
Logical I/O Total:                 248

Disk I/O statistics for parse, execute and fetch calls:

Disk I/O for Parses:               0
Disk I/O for Executions:          28
Disk I/O for Fetches:              0
Disk I/O Total:                   28

CPU statistics for parse, execute and fetch calls:

CPU for Parses:               0
CPU for Executions:       62500
CPU for Fetches:              0
CPU Total:                62500

Row statistics for execute and fetch calls:

Rows processed during Executions:         104
Rows processed during Fetches:              0
Rows Total:                               104

Sort statistics for execute and fetch calls:

Sorts on disk:             0
Sorts in memory:           2
Sort rows:               667

Hit Rate - Disk I/O divided by Logical I/O: 0.112903

Logical I/O performed divided by rows actually processed: 2.384615

Disk I/O performed divided by number of executions: 28.000000

The number of parses divided by number of executions: 1.000000

The number of rows fetched divided by the number of fetches: 0.000000

   INSERT INTO tdv_sql_detail
    (collection_number, sql_text_hash,
     "LIB_CACHE_ADDR")
    SELECT DISTINCT collection_number,
                    sql_text_hash, 
                    "LIB_CACHE_ADDR"
        FROM v_192216243_f_5_e_7_8_0
        WHERE collection_number = :b1;

Getting More Information on a Selected Query

There are two convenient ways to obtain additional data for the currently selected SQL statement:

To modify a data view to add or remove statistics or items, select Modify from the Data View menu. You may add or remove statistics in the Items property sheet. These statistics appear as new columns in the data view. The selected query in Figure 14-3 is:
```
SELECT COUNT(DISTINCT WAIT_TIME) 
FROM WAITS 
WHERE COLLECTION_NUMBER = :1;
```
This query counts distinct values in the wait_time column of the waits table. By modifying the existing data view you can add other statistics that may be of interest such as "Execute Rows", which is the number of rows processed during execution, or "Execute CPU", which is the number of CPU clock ticks during execution.

You can also remove existing columns, change the sort order, or change the default number of rows to view. You can save the modified data view. Oracle stores user-defined data views in the Custom data view container following the Data Viewer supplied list of SQL and Wait data views.
Drill to statistics on all parses, executions and fetches of the selected query by clicking the Drill icon in the toolbar. The Drill down Data View dialog is displayed as shown in Figure 14-4.

Figure 14-4 Oracle Trace Data Viewer - Drill Down Data View Screen

Drill-down data views show individual statistics for all parses, executions, and fetches.

In Figure 14-4 the "Basic Statistics for Parse/Execute/Fetch" drill-down data view is selected. It displays statistics similar to those from TKPROF.

Note:
For more information on TKPROF, see Chapter 11, "Overview of Diagnostic Tools".

Table 14-2 Drill-down Data Views (Page 1 of 2)

Drill-down Name	Sort By	Data Displayed	Description
Basic Statistics for Parse/Execute/Fetch	Greatest elapsed time	For each distinct call:. CPUs Elapsed time Disk I/O Logical I/O Number of rows processed	Parse, execution, and fetch statistics which are similar to statistics from `TKPROF`.
CPU Statistics for Parse/Execute/Fetch	Greatest number of CPUs	CPU total Pagefaults	CPU and pagefault statistics for parses, executions, and fetches of the current query. CPU total is the number of clock ticks in both user and system mode. The clock tick granularity is specific to the operating system on which the database resides.
I/O Statistics for Parse/Execute/Fetch	Greatest number of disk I/Os	Logical and Disk I/O statistics Pagefault I/O (number of hard pagefaults) Input I/O (number of times the file system performed input) Output I/O (number of times the file system performed output)	I/O statistics for parses, executions, and fetches.
Parse Statistics	Greatest elapsed time	Current user identifier Schema identifiers	Parse statistics, for example, whether the current statement was missed in library cache, Oracle optimizer mode, current user identifier, and schema identifier.
Row Statistics for Execute/Fetch	Greatest number of rows returned	Number of rows returned Number of rows sorted Number of rows returned during a full table scan	Execution and fetch row statistics.
Sort Statistics for Parse/Execute/Fetch	Greatest elapsed time	Sorts on disk Sorts in memory Number of rows sorted Number of rows returned from a full table scan	Parse, execution, and fetch sort statistics.
Wait Parameters	Wait_time	Description Wait_time P1 P2 P3	Investigating waits may help identify sources of contention. P1, P2, and P3 parameters are values that provide more information about specific wait events. The parameters are foreign keys to views that are wait event dependent. For example, for latch waits, P2 is the latch number that is a foreign key to `V$LATCH`. The meaning of each parameter is specific to each wait type.

Manually Collecting Oracle Trace Data

Though the Oracle Trace Manager is the primary interface to Oracle Trace, you can optionally force a manual collection of Oracle Trace data. You can do this by using a command-line interface, editing initialization parameters, or by executing stored procedures.

Using the Oracle Trace Command-Line Interface

Another option for controlling Oracle Trace server collections is the Oracle Trace CLI (Command-line Interface). 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 statement 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 col_name cdf_file
OTRCCOL DFD col_name username password service

The parameter job_id can be any numeric value, but it must be unique and you must remember this value to stop the collection. 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.

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

OTRCCOL START 1234 my_start_input_file

Where file 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, ORACLEC, ORACLED, ORACLEE, and ORACLESM.

The OTRCCOL STOP statement 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 statement 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 value of 0 produces a partial format.

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

Using Initialization Parameters to Control Oracle Trace

Six parameters are set up by default to control Oracle Trace. By logging into the administrator account in your database and executing the SHOW PARAMETERS TRACE statement, you see the following parameters as shown in Table 14-3:

Table 14-3 Oracle Trace Initialization Parameters

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

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

Enabling Oracle Trace Collections

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, set the parameter to true. Having the parameter set to true does not start an Oracle Trace collection, but instead allows Oracle Trace to be used for that server. You can then start Oracle Trace in one of the following ways:

Using the Oracle Trace Manager application supplied with the Oracle Diagnostics Pack.
Setting the ORACLE_TRACE_COLLECTION_NAME parameter.

When ORACLE_TRACE_ENABLE is set to true, you can start and stop an Oracle Trace server collection by either using the Oracle Trace Manager application that is supplied with the Oracle Diagnostics Pack, or you can enter 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 in length. You must then shut down your database and start it up again to activate the parameters. If a collection name is specified, then when you start the server, you automatically start an Oracle Trace collection for all database sessions, which is similar in functionality to SQL Trace.

To stop the collection that was started using the ORACLE_TRACE_COLLECTION_NAME parameter, 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 that Oracle Trace Collects

The ORACLE_TRACE_FACILITY_NAME initialization parameter specifies the event set that Oracle Trace collects. The name of the DEFAULT event set is ORACLED. The ALL event set is ORACLE, the EXPERT event set is ORACLEE, the SUMMARY event set is ORACLESM, and the CACHEIO event set is ORACLEC.

After it is restarted, if the database does not begin collecting data, then check the following:

The event set file, identified by ORACLE_TRACE_FACILITY_NAME, with .fdf appended to it, 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 your Oracle Trace administrative directory: REGID.DAT, PROCESS.DAT, and COLLECT.DAT. If they do not, then 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. 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 of the Oracle Intelligent Agent when it runs the Oracle Trace Collection Services OTRCCOL image. Depending on whether you are running Oracle Trace from the Oracle Trace Manager or from the command-line interface, you can find the EPC_ERROR.LOG file in one of the following locations:
- $ORACLE_HOME or $ORACLE_HOME/network/agent on UNIX
- %ORACLE_HOME%\network\agent or %ORACLE_HOME%\net80\agent on NT
- $ORACLE_HOME\rdbmsnn on NT or $ORACLE_HOME\rdbms on UNIX
- In your current working directory, if you are using the command-line interface
- To find the EPC_ERROR.LOG file on UNIX, change directories to the $ORACLE_HOME directory and execute the statement:
```
find . -name EPC_ERROR.LOG -print . 
```
  Note:
  On UNIX, the EPC_ERROR.LOG file name is case sensitive and is in uppercase.
- Look for *.trc files in the directory specified by the USER_DUMP_DEST initialization parameter. Searching for "epc" in the *.trc files may give errors. These errors and their descriptions are located in the $ORACLE_HOME/otrace/include/epc.h file.

Using Stored Procedures to Control Oracle Trace

Using the Oracle Trace stored procedures 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 syntax:

DBMS_ORACLE_TRACE_USER.SET_ORACLE_TRACE(true/false, 
COLLECTION_NAME, SERVER_EVENT_SET)

where:

true/false

Boolean: true to activate, false to deactivate.

COLLECTION_NAME

VARCHAR2: collection name (no file extension, eight character maximum).

SERVER_EVENT_SET

VARCHAR2: server event set file name (CONNECT, ORACLE, ORACLEC, ORACLED, ORACLEE, ORACLSM, SQL_ONLY, SQL_PLAN, SQL_TXN, SQL_WAITS, or WAITS).
See Table 14-4 for a description of each of these event set file names.

Example:

EXECUTE DBMS_ORACLE_TRACE_USER.SET_ORACLE_TRACE (true,'MYCOLL','oracle');

Table 14-4 Server Event Set File Names

Event Set File Name (.fdf)	Description
CONNECT	`CONNECT_DISCONNECT` event set. Collects statistics about connects to the database and disconnects from the database.
ORACLE	`ALL` event set. Collects all statistics for the Oracle Server including wait events.
ORACLEC	`CACHEIO` event set. Collects caching statistics for buffer cache I/O.
ORACLED	Oracle Server `DEFAULT` event set. Collects statistics for the Oracle Server.
ORACLEE	`EXPERT` event set. Collects statistics for the Oracle Expert application.
ORACLESM	`SUMMARY` event set. Collects workload statistics for the Summary Advisor application.
SQL_ONLY	`SQL_TEXT_ONLY` event set. Collects statistics about connects to the database, disconnects from the database, and SQL text.
SQL_PLAN	`SQL_STATS_AND_PLAN` event set. Collect statistics about connects to the database, disconnects from the database, SQL statistics, SQL text, and row source (`EXPLAIN` `PLAN`).
SQL_TXN	`SQL_TXNS_AND_STATS` event set. Collects statistics about connects to the database, disconnects from the database, transactions, SQL text and statistics, and row source (`EXPLAIN` `PLAN`).
SQL_WAITS	`SQL_AND_WAIT_STATS` event set. Collects statistics about connects to the database, disconnects from the database, row source (`EXPLAIN` `PLAN`), SQL text and statistics, and wait events.
WAITS	`WAIT_EVENTS` event set. Collects statistics about connects to the database, disconnects from the database, and wait events.

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

DBMS_ORACLE_TRACE_AGENT.SET_ORACLE_TRACE_IN_SESSION
(sid, serial#, true/false, COLLECTION_NAME, SERVER_EVENT_SET)

Where:

sid

Number: session instance from V$SESSION.SID.

serial#

Number: session serial number from V$SESSION.SERIAL#.

Example:

EXECUTE DBMS_ORACLE_TRACE_AGENT.SET_ORACLE_TRACE_IN_SESSION
(8,12,true,'NEWCOLL','oracled');

If the collection does not occur, then 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 database initialization file.
The following files should exist in your Oracle Trace admin directory: REGID.DAT, PROCESS.DAT, and COLLECT.DAT. If they do not, then you must run the OTRCCREF executable to create them.
For Oracle Server release 8.0 and later, the stored procedure packages do not exist in the database. If the packages do not exist, then run the OTRCSVR.SQL file (in your Oracle Trace admin directory) to create the packages.
The user has the EXECUTE privilege on the stored procedure.

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 the following ways:

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

Formatting Oracle Trace Data to Oracle Tables

You can format Oracle Trace server collection Oracle tables for more flexible access SQL reporting tools. Oracle Trace produces a separate table for each event collected. For example, a parse event table is created to store data for all parse events occurring 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.

Note:
Oracle server releases 7.3.4 and later automatically create the formatter tables.

Use the following syntax to format an Oracle Trace collection:

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

If you omit user/password@database, then Oracle 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.

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.

Oracle Trace Statistics Reporting Utility

The Oracle Trace statistics reporting utility displays 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 statement parameters. Use the following statement and optional parameters to produce a report:

OTRCREP  [optional parameters]  collection_name.CDF

First, you may 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 parameters:

output_path

Specifies a full output path for the report files. If not specified, then the files are placed in the current directory.

-p

Organizes event data by process. If you specify a process ID (pid), then you have one file with all the events generated by that process in chronological order. If you omit the process ID, then you have one file for each process that participated in the collection. The output files are named collection_Ppid.txt.

-P

Produces a report called collection_PROCESS.txt that lists all processes that participated in the collection. It does not include event data. You could produce this report first to determine the specific processes for which you might want to produce more detailed reports.

-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. This option creates a file similar to the SQL*Net Tracing file.

-a

Creates a report containing all the events for all products, in the order they occur in the data collection (.dat) 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	1 192216243 0 0 5 <database SID>

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

sid	Number: session instance from `V$SESSION`.`SID`.
serial#	Number: session serial number from `V$SESSION`.`SERIAL#`.

output_path	Specifies a full output path for the report files. If not specified, then the files are placed in the current directory.
-p	Organizes event data by process. If you specify a process ID (pid), then you have one file with all the events generated by that process in chronological order. If you omit the process ID, then you have one file for each process that participated in the collection. The output files are named collection`_P`pid.`txt`.
-P	Produces a report called collection`_PROCESS`.`txt` that lists all processes that participated in the collection. It does not include event data. You could produce this report first to determine the specific processes for which you might want to produce more detailed reports.
-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. This option creates a file similar to the SQL*Net Tracing file.
-a	Creates a report containing all the events for all products, in the order they occur in the data collection (.`dat`) file.

14 Using Oracle Trace