Oracle8i Documentation Addendum
Release 3 (8.1.7)

Part Number A85455-01

Library

Product

Contents

Index

Go to previous page Go to next page

13
Using Oracle Trace

This chapter describes new and changed features for release 8.1.7. The topics in this chapter include:

Overview of 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, and fetch statistics, and wait statistics.

See Also:

Oracle Enterprise Manager Oracle Trace User's Guide and Oracle Enterprise Manager Oracle Trace Developer's Guide

These books contain a complete list of events and data that you can collect for the Oracle server, as well as information on how to implement tracing in your own products and applications.  

Using Oracle Trace Data

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

Figure 13-1 Integration of Oracle Trace with Other Applications


Text Description of stu81144.gif follows
Text description of the illustration stu81144.gif

Using Oracle Trace Data in Oracle Expert

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

You control the scheduling and duration of an Oracle Trace collection period. To get 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 the selected statements.

Oracle SQL Analyze can show 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 Microsoft Excel.

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 resource overhead. Using the Oracle Trace Manager graphical user interface, you can do the following:

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 run it immediately or schedule it to run at a specific time or at specified intervals. When a collection runs, 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:

The Oracle server has 17 events. The following are three of these 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 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, identify the database where Oracle Trace Manager creates the formatted collection as follows:

  1. Using Oracle Trace Manager, select a collection to format.

  2. Choose the Format statement.

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

See Also:

Oracle Enterprise Manager Oracle Trace Developer's Guide for more information about predefined SQL scripts and the Detail reports 

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 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 can be misleading due to other concurrent activities on the database or node. Combining statistics for all executions can show a clearer picture about the typical resource utilization occurring when a given query is run.


Note:

You can filter out SQL statements run by SYS


Using 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 do the following:

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 13-2. A description of the selected data view is shown on the right-hand side of the screen.

Figure 13-2 Oracle Trace Data Viewer - Collection Screen



Text description of trdvcol.gif follows.
Text description of the illustration trdvcol.gif

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

Table 13-1 Predefined Data Views Provided By Oracle Trace
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.

This can be a rough indication of the 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.

This can be a rough indication of the 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. 

Reparse 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 runs the statement immediately.

If missed in the library cache, then recheck 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 rechecked 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. They are 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 runs 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. This can 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 indicates the level to which array fetch capabilities have been utilized. A ratio close to one indicates 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 the 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 the 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 the wait events or wait descriptions that appear most frequently within the collection. 

Viewing Oracle Trace Data

Double click on SQL or wait event data views to query the collection data and display data sorted by criteria described in the data view's description.

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 13-3 shows data in the "Average Elapsed Time" data view. Query text and statistics appear in the top portion of the window. Click any column headers to sort rows by the statistic in that column.

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


Text description of trdvdv.gif follows.
Text description of the illustration trdvdv.gif

The current 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 current data view also appear in the Details property sheet.

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

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.

Understanding the SQL Statement Property Page in Oracle Trace

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

Understanding the Details Property Page in Oracle Trace

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 selected SQL statement is posted at the end of the property page.

Example of the Details Property Page in Oracle Trace

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 in Oracle Trace

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

  1. To modify a data view to add or remove statistics or items, select Modify from the Data View menu. You can add or remove statistics in the Items property sheet. These statistics appear as new columns in the data view. The selected query in Figure 13-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 might 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.

  2. 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 in Figure 13-4.

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



Text description of trdvdrl.gif follows.
Text description of the illustration trdvdrl.gif

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

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

See Also:

Oracle8i Designing and Tuning for Performance for more information on TKPROF 

Table 13-2 Drill down Data Views
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 that 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 can 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

Although the Oracle Trace Manager GUI is currently the primary interface to Oracle Trace, you can optionally collect and format Oracle Trace data using one of the following non-GUI mechanisms:

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:

The job_id parameter is a carryover from the Oracle Trace Manager use of the CLI, for managing multiple collection jobs. When the CLI is used directly, the job_id value is not used. (Therefore, it can be any positive numeric value greater than 0 and less than 10K). The job_id values used for CLI START and STOP commands do not need to be equal.

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 on parameter values contained in the input parameter file. For example:

OTRCCOL START 1234 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

For Oracle database collections, one additional parameter is required:

regid= 1 192216243 0 0 5 <database SID>


Note:

Older CLI versions required this syntax exactly, with no whitespace before '=' but at least some whitespace after '='. This is no longer true: CLI is not whitespace sensitive. 


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:

The cf_num and cf_val elements should set to zero in this basic Oracle database collection regid above. However, additional regid records can be specified to reduce the amount of collected data, and non-zero 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.

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


Note:

Oracle Trace collections for Oracle databases prior to release 8.1.7 collected cross facility item 1 values at the end of each duration event (in other words, parse, execute, fetch, plus logical and physical transaction events), rather than the indicated cross_fac_6_end values. However, all cross facility values collected at the start of each duration event are correct. This includes cross_fac_1_start, which records an optional application ID if provided, and cross_fac_6_start, which records database userID values. 


The input parameter file used by the CLI when starting a collection can also contain the following optional parameters, for both database and non-database 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.

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.

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.

See Also:

Table 13-4 for a description of the server event sets 

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

See Also:

"Formatting Oracle Trace Data to Oracle Tables" for more information on formatting part or all of an Oracle Trace collection. 

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 Oracle database initialization parameters are set up by default to control Oracle Trace. By logging into the administrator account in the database and executing the SHOW PARAMETER ORACLE_TRACE statement, you see the following parameters:

Table 13-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 the initialization file.


Note:

This chapter refers to file path names on UNIX-based systems. For the exact path on other operating systems, see your Oracle platform-specific documentation. A complete discussion of these parameters is provided in Oracle8i Reference. 


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 non-null values by default. Typically, this means that both ORACLE_TRACE_ENABLE must be set to true and a non-null ORACLE_TRACE_COLLECTION_NAME must be provided (up to 16 characters in length).


Note:

The collection name is also used to form the <collection name>.cdf and .dat binary file names, so 8.3 file naming conventions may apply on some platforms. 8.3 file naming means systems where filenames are restricted to 8 or fewer characters, plus a file extension of 3 or fewer characters.  


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 = "" [in other words, 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 or the Oracle Trace Manger GUI. These other mechanisms are more flexible than the database initialization parameters. They are generally preferred over using parameters for collection control.

ORACLE_TRACE_ENABLE is a dynamic parameter, 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).


Note:

The ORACLE_TRACE_FACILITY_NAME parameter does not use a file extension. So, the .fdf extension should not be specified as part of this parameter. 



Table 13-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). 

SQLSTATS
 

SQL_AND_STATS event set.

Collects SQL text and statistics only. 

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

SQLWAITS
 

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. 

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

Controlling Oracle Trace Collections from PL/SQL

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


Note:

Older versions of the Oracle server, back to release 7.3.3, provided other stored procedures to start and stop Oracle Trace database collections, but with significant limitations. For example, only database sessions already active when the collection was started participated in the collection: no database event data was collected for sessions that began after the Oracle Trace collection started.

As of Oracle8, these limitations were eliminated for database collections started via the Oracle Trace Manager or CLI. However, these limitations still applied to database collections controlled via the older Oracle7 stored procedures. The new procedures provided with Oracle Trace 8.1.7 remove these limitations, permitting the same level of collection control as the Oracle Trace CLI, and for both database and non-database collections. 


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

On Win32 platforms (for example, Windows NT), the library is %ORACLE_HOME%\bin\oratracepls8.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:

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:

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:

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"';


Note:

For better readability, the layout of this regid string has been simplified. 


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:

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

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, such as the Oracle Trace Data Viewer. 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.


Note:

For Oracle server releases 7.3.4 and later, the Oracle Trace formatter automatically creates the formatter tables as needed. 


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.

See Also:

Oracle Trace User's Guide for more information on server event tables and scripts for accessing event data and improving event table performance 

Running the 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 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 parameters:

output_path
 

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

-p[<pid>]
 

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 PID, then you have one file for each process that participated in the collection. The output files are named <collection_name>_Ppid.txt

-P
 

Produces a report file name PROCESS.txt that lists all processes that participated in the collection. It does not include event data. You can produce this report first to determine the specific processes for which you 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. 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index