7
Using Oracle Trace Data Viewer

Oracle Trace Data Viewer (hereafter referred to as Data Viewer) is an Oracle Enterprise Manager application that allows you to view formatted data collected by the Oracle Trace command line interface. For more information about using the command line interface to collect data, see the Oracle9i Database Performance Guide and Reference.

This chapter includes the following:

• Overview
• Usage Scenarios for Data Viewer
• Starting Data Viewer
• Using Data Viewer
• Using the Data View Wizard
• Data Viewer Problems
• Isolating an Oracle Trace Problem

Overview

An Oracle Trace server collection typically contains a large volume of valuable information that can be used for troubleshooting and investigating SQL or wait activity and resource utilization. Data Viewer handles the complex task of extracting data and aggregating key server performance metrics on a large Oracle Trace collection. Once you select an Oracle Trace collection, you can have Data Viewer compute SQL or Wait statistics or both.

Once you select an Oracle Trace collection, Data Viewer runs through all of the Oracle Trace formatter tables, extracting, processing, and aggregating key performance metrics. This processed data is presented in a comprehensive set of Oracle Trace predefined data views.

A data view is the definition of a query into the formatted 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
• Drill down as needed to get additional details about the data

If you want to define your own data views, you can use the Oracle Trace Data View Wizard.

Usage Scenarios for Data Viewer

Using Data Viewer you can:

• Look at Oracle Server SQL statistics

  Data Viewer provides predefined data views that can accelerate the process of identifying poorly performing SQL statements. Oracle Server SQL data views are grouped by categories. Some specific data views include:

  • Elapsed Time statistics

    Average Elapsed Time (sorts SQL by greatest average elapsed time per execution of queries within the collection)

  • Sort statistics

    Sorts in memory and sorts on disk and number of rows sorted

  • I/O statistics

    Disk Reads/Logical Reads Ratio (sorts SQL by worst disk to logical I/O data buffer cache hit rate)

• View Logical and Physical Transaction level statistics
• View Wait statistics

Starting Data Viewer

You can access Data Viewer in the following ways:

• From the Oracle Enterprise Manager program group
• From the Start menu

  Note: Oracle Trace data must be formatted before you can use Data Viewer.

The following sections explain how to use these methods.

Starting Data Viewer from Oracle Enterprise Manager

To start Data Viewer from Oracle Enterprise Manager, either click on Trace Data Viewer from the Diagnostics Pack drawer or select Tools=>Diagnostics Pack=>Trace Data Viewer. The Oracle Trace Data Viewer Login box appears. Provide login information to connect to a schema containing formatted collections. Data Viewer displays all collections formatted to the schema (see Figure 7-1).

Figure 7-1 Data Viewer Collection Screen

Text description of the illustration Dvapp.gif

Decide which formatted collection you want to investigate and double-click on the collection name. The Work in Progress dialog box appears.

Starting Data Viewer from the Start Menu

To start Data Viewer from the Start menu, select Start=>Programs=>ORACLE_HOME=>Diagnostics Pack=>Trace Data Viewer.

The Oracle Trace Data Viewer Login box appears. Provide login information to connect to a schema containing formatted collections. Data Viewer displays all collections formatted to the schema (see Figure 7-1).

Decide which formatted collection you want to investigate and double-click on the collection name. The Work in Progress dialog box appears.

Using Data Viewer

The first time a collection is accessed by Data Viewer, the Compute Statistics dialog box appears. At this time, Data Viewer can compute SQL and Wait statistics.

Computing Statistics - Preparing Data for Fast Access and Analysis

The first time you run Data Viewer on a formatted collection containing Oracle Server data, Data Viewer asks whether you want to calculate SQL statistics, Wait statistics, or both (see Figure 7-2).

Figure 7-2 Compute Statistics

Text description of the illustration Compstat.gif

This step prepares the data for fast access and simplified problem analysis. Oracle Corporation strongly recommends that you allow this processing to occur.

Displaying Data Views

Data Viewer displays the predefined data views for a collection, as shown in Figure 7-3.

Figure 7-3 List of Predefined Data Views

Text description of the illustration Trdvcol.gif

Depending on the events collected, the predefined data view list will vary. For example, the Oracle Server collection will contain predefined data views appropriate for data collected in an Oracle Server collection.

If the Oracle Trace ALL class was used for the collection, Wait events will also display in the navigator tree. Unless you need to examine wait events, Oracle Corporation suggests that you collect data for the DEFAULT or EXPERT class to minimize the amount of data collected.

Table 7-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. 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, the user process uses this parsed representation and executes the statement immediately. If missed in the library cache, the statement needs to be re-checked for syntax, valid objects, and security. Also a new execution plan will need to be determined by the optimizer. Note that the parse count statistic is incremented for every parse request whether or not 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 will need 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, the user process uses this parsed representation and executes the statement immediately. If missed in the library cache, the statement needs to be rechecked for syntax, valid objects and security. Also a new execution plan will need 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. Note that 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 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. 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 since memory access is much 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 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.

Accessing the Definition of a Data View

To access the definition for each data view, click on the data view name listed in the Data View Name column, for example, Disk Reads/Logical Reads Ratio. The description of the statistical data returned from the data view is on the General property page located on the right pane of the screen as shown in Figure 7-4. More details are shown in the Items and Options pages.

• General

  Displays the name, short description, and full description of the data view. For the Oracle Server predefined data views, the name reflects the name of the event. The full description is usually used to indicate what type of information is displayed in a data view and when it would be appropriate to use the data view.

• Options

  Shows which statistics will be used to determine sort order, if any. Also displays the number of rows that will be selected by the data view. Limiting the number of rows to be retrieved and displayed can accelerate the display of the data.

• Items

  Lists the statistics that will be displayed by the currently selected data view, as well as all possible statistics for the data view.

Figure 7-4 Predefined Data View - Disk Reads/Logical Reads Ratio

Text description of the illustration Canview.gif

Viewing Data in a Data View

To examine the data defined in each data view, do one of the following:

• Double-click on the View Name, for example, Disk Reads/Logical Reads Ratio.
• With the data view selected, click the Open Data View button.
• Choose Data View=>Open from the Collection window.

Data Viewer then displays the data (see Figure 7-5) identified by criteria defined in the selected data view. In this example, the data view shows summary information for each SQL statement executed, sorted by the Disk to Logical Reads Ratio (for example, column name marked with asterisk (*)).

Figure 7-5 Details of "Disk Reads/Logical Reads Ratio" Data View

Text description of the illustration Detview.gif

The major portions of the screen are:

Data value columns

The columns are displayed in the sort order determined by the value in the column marked with asterisk (*).

SQL Statement

The SQL statement property page displays the SQL statement text for the query associated with the currently selected row of returned statistics. Note that statement text changes as you highlight various rows.

Use the right mouse button to display the popup menu that can be used to select and copy text.

Details

The Details property page lists all statistics associated with the highlighted row in the data view. The statistics shown are for all executions of the current query within the Oracle Trace collection.

Use the right mouse button to display the popup menu that can be used to select and copy text.

Status Bar

Shows the number of rows returned and the number of the currently selected row. The status is displayed in the lower right corner of the window.

Creating Custom Data Views

You can also create a new data view or create a data view like an existing data view. Create a data view when a predefined data view does not contain the information you want to examine. "Using the Data View Wizard" describes the process of creating a data view.

Modifying or Extending Data Views

You can modify a data view to either add or remove statistics from the view.

For example, to add the "Execute rows" statistics to the data view, do the following:

1. Select a data view, for example, "Disk Reads/Logical Reads Ratio," then in the Data View window, choose Data View=>Modify.
2. In the Edit Data View dialog box, select the Items tab, and move the "Execute rows" statistics from the Items list to the Display These Items list.
3. Click OK.

The new information will be added to the view immediately. You can then save this data view to use at another time by choosing File=>Save As. The original view still exists. The modified view appears at the bottom of the navigator tree under the Custom folder.

Note that from the Data View window you can:

• Click on the current sort column to reverse the sort order. Click on a new column to change the sort criteria. The sort column is indicated by an asterisk (*) appended to the front of the header title.

  Changing the sort order causes the database to be queried again for the top rows using the new sort criteria.

• Revise the options, for example, the maximum number for rows retrieved. Click the Options tab to make these changes. (This tab becomes available after you select Data View=>Modify.)
• Modify the name, description, and full description of the data view by clicking the General tab. (This tab becomes available after you select Data View=>Modify.)

Drill-Down of Data Views

For certain events, you can drill down to related events to refine your view of the data. The drill-down option is available if the event that is currently being shown consists of other related events. For example, when examining the SQL Statistics data views, you can drill down to the Parse, Execute, and Fetch statistics for the currently selected query.

Select a row that you wish to analyze further (see Figure 7-5) and choose Data View=>Drill down. This allows you to join this row to other events and items of interest.

Figure 7-6 shows the resulting drill-down window.

Figure 7-6 Intermediate Drill-Down to Selected Data View

Text description of the illustration Drildown.gif

The major portions of the window are:

• List of predefined data views

  Predefined data views are supplied for drilling down to the individual parses, executions, and fetches associated with a selected SQL statement.

• Description

  Displays a short description of the currently selected drill-down data view.

• Full Description

  Displays a more in-depth description of the data returned by the currently selected drill-down data view, as well as a list of the statistics returned by this data view and the statistic that is used to sort it.

Select and double-click a drill-down data view to see individual parse, execute, and fetch statistics for each execution of the SQL statement within the collection. You will see the parse, execution, and fetch statistics within their own scrolling regions, as shown in Figure 7-7.

Figure 7-7 Resulting Drill-Down Data View

Text description of the illustration Chart1.gif

Major portions of the screen are:

• Top part of window

  Lists the detailed information returned by the data view. Each event will have columns containing statistics of items returned by the data view.

• SQL Statement

  The SQL statement property page displays the SQL statement text for the query associated with the currently selected row of returned statistics.

• Details

  The Details property page shows the statistics for all executions of the current query within the Oracle Trace collection.

Selecting a Drill-Down Data View

Predefined data views are supplied for drilling down to the individual parses, executions, and fetches associated with a selected SQL statement. Table 7-2 lists the drill-down data views available in Data Viewer.

Table 7-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 event: CPU time. Elapsed time. Disk I/O. Logical I/O. Number of rows processed.	Parse, Execution, and Fetch statistics like statistics output from TKPROF.
CPU Statistics for Parse/Execute/Fetch	Greatest elapsed CPU time.	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 information, such as whether or not 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 to identify where contention resides and what is causing the contention. P1, P2, and P3 parameters are values that provide more information about a specific wait event. The parameters are foreign keys to views that are wait event dependent. For example, for latch waits, P2 is the latch number which is a foreign key to v$latch. The meaning of each parameter is specific to each wait type.

Modifying Drill-Down Data Views

Drill-down data views can be modified and saved much like top level data views. You can modify a drill-down data view to either add or remove statistics from the view.

Because slightly different statistics are associated with the parse, execution, or fetch events, you must modify each event separately. For example, to add the "Rows" (row count) statistic to the execution and fetch output in the drill-down data view, do the following:

1. Select any row in "Execution I/O Statistics" and choose Data View=>Modify.
2. In the Edit Data View dialog box, select the Items tab, and move the Rows statistics from the Items list to the Display These Items list.
3. Click OK.
4. Select any row in the "Fetch I/O Statistics" and add the "Rows" statistics to that event.

The new information is added to the view immediately. You can then save this data view to use at another time.

Saving As Data Views and Files

From the Data View window, you can save the current data view settings by selecting File=>Save As=>Data View. This is useful if you want to reuse this data view frequently. You must provide a name, description, and full description to save the view. Modified views are listed in the Custom folder in the navigator tree.

Also, you can save the selected rows of a data view to a file by selecting File=>Save As=>File. You can save the data in either of the following formats:

• SQL text (text only) for rows that have SQL text. You can use this file for import into SQL Analyze.
• CSV (comma-separated value format). You can use this format for import into other programs that accept CSV format.

Printing Data Views

From the File menu, there are three printing options available: Print, Print Setup, and Set Printer Font.

The Print option prints the currently active portion of a data view. If the currently active region is a data area, you can print either the entire region (including rows that are not visible on the screen) or selected rows. If the currently active region is the "SQL Statement/Details" area, both the SQL Statement and the Details will be printed.

The Print Setup option displays the standard Print Setup dialog box.

The Set Printer Font option displays the standard Font dialog box with the font selection for the currently selected printer.

Using the Data View Wizard

You can create your own customized data view by using the Oracle Trace Data View wizard (hereafter referred to as the Data View wizard).

To invoke the wizard, choose Data View=>Create or Data View=>Create Like. The wizard's Welcome page appears (see Figure 7-8).

Use the Data View wizard to examine data that you cannot find in a predefined view. However, this data will not be optimized and may take longer to process.

Figure 7-8 Data View Wizard Welcome Page

Text description of the illustration Chwizwel.gif

You can choose to bypass this Welcome page the next time you use the Data View wizard.

Click Next to start the definition of the data view.

Choosing an Event

The Event page is the first page used in the definition of the data view that you are creating. The Event page is as shown in Figure 7-9.

Figure 7-9 Selecting the Event

Text description of the illustration Chwizevt.gif

From the Event page, choose the event on which to base the data view. The list of event names comes from the Oracle Trace formatted data tables. Event names are limited to and truncated at 16 characters.

If you are looking at an Oracle Server collection and you let Data Viewer compute statistics, the SQL Text Segment event allows you to examine statistics from Parse, Execute, and Fetch events.

After you select the event, continue to the Items page by clicking Next.

Choosing Items Associated with the Selected Event

On the Items page, select the items that you want to display in the data view (see Figure 7-10).

Figure 7-10 Selecting and Ordering Items Associated with the Event

Text description of the illustration Chwizitm.gif

Items available are based on the event chosen.

• Choose items to display by selecting them in the Items list and use >> to move the items to the Display These Items list. Use << to remove an item from the Display These Items list. Items prefixed with an asterisk (*) are statistics computed by Data Viewer.
• Order these items by highlighting an item in the Display These Items list and using the up and down arrows to move the item in the list. The first item in the list is the first item displayed in the data view. Each item will be in a column in the data view.

When the item is selected, the Description field displays the description.

You must choose at least one item to generate a data view.

The next step in defining the data view is to define the sort criteria. Click Next to access the Sort By page.

Choosing the Sort Criteria

The Sort By page (see Figure 7-11) allows you to choose the item or statistic on which the sorting will take place. Sort criteria is optional.

Figure 7-11 Selecting Sort Criteria

Text description of the illustration Chwizsrt.gif

The data can be displayed in either ascending or descending order. Descending order is the default.

The next step in defining the data view is to optionally limit the number of rows to display in the data view. Click Next to access the Number of Rows page.

Defining the Number of Rows to Display

The last step in defining the data view is to decide how many rows are to be displayed in the data view (see Figure 7-12). You can either limit the number of rows to display or decide to display all the rows.

Limiting the number of rows returned can improve performance. If you are interested in viewing the highest value of some statistic, limit the number of rows. You can change the number for rows from the data view later on.

Figure 7-12 Limiting Number of Rows

Text description of the illustration Chwizrow.gif

This is the last step in defining the data view. Click Next to view the Summary page.

Reviewing the Summary Page

The Summary page gives you the opportunity to review the choices that you made while defining the data view (see Figure 7-13).

Figure 7-13 Summary Page

Text description of the illustration Chwizsum.gif

You can either accept all of the choices by clicking Finish or go back to change one or more definitions by clicking Back.

Data Viewer Problems

The following section deals with problems specific to Data Viewer.

Could Not View Oracle Trace Formatted Data

To view Oracle Trace formatted data using the Data Viewer, a user account needs the following system privileges:

CREATE SESSION

CREATE SEQUENCE

CREATE TABLE

CREATE PROCEDURE

CREATE ANY INDEX

EXECUTE ANY PROCEDURE

DELETE ANY TABLE

DROP ANY INDEX

DROP ANY SEQUENCE

UNLIMITED TABLESPACE

Need to Restore Formatted Data

Data Viewer adds statistical information to your formatted data. To restore your formatted data to the exact state it was in prior to running Data Viewer, run the ORACLE_HOME\Sysman\Admin\TdvDrop.sql script. Reasons for restoring data include:

• You encountered an insufficient privilege error prior to or during computing statistics.
• You received a database error creating a tablespace extent.

  ORA-01658: unable to create INITIAL extent for segment in tablespace
  MY_TABLESPACE
  
  

• You encountered an unexpected error that appears to be non-recoverable.

Additional Formatted Data Is Missing from Data Viewer

The first time Data Viewer views a collection, you can compute SQL statistics for all queries currently in the collection. Data Viewer does not detect new data added to a collection by a partial format while Data Viewer is viewing that collection. If you choose to partially format your collected data, you can force Data Viewer to recompute statistics using both existing and newly collected data by choosing Options=>Recompute Statistics.

Poor Performance

If multiple collections exist in one set of user tables, computing statistics and subsequent selects may take longer than necessary. Placing a large collection in a new database user account will improve performance.

Table or View Does Not Exist (or No Data in Collection)

When a record is not written to the EPC_FACILITY_REGISTRATION table, the user may see an error like the following:

XP-21016: A database error has occurred:
SELECT DISTINCT FACILITY_NUMBER, FACILITY_VERSION, VENDOR 
FROM EPC_FACILITY_REGISTRATION WHERE COLLECTION_ID - :1
ORA-00942: table or view does not exist

Removing the 'Filtering by User' option on collections targeting Oracle Server release 8.0.4 databases will correct this problem for future collections.

Manually adding an EPC_FACILITY_REGISTRATION record for the collection allows Trace Data Viewer to see that collection's formatted data. For example, insert EPC_FACILITY_REGISTRATION record with the following values:

Collection_ID:    123       [Look in formatted data's EPC_COLLECTION table
                             for the collection_id column value that matches
                             your collection_name]
Vendor:           192216243 [Hard code this value]
Facility_number:  5         [Hard code this value]
Facility_version: '8.0'     [or '7.3' for a collection against a 7.3 database
                             or '8.1' for a collection against an 8.1 database]

Isolating an Oracle Trace Problem

Oracle Server releases 7.3.4 and 8.0.4 and later automatically create the formatter tables. Prior to Oracle Server releases 7.3.4 and 8.0.4, you must run the otrcfmtc.sql script from Oracle Server Manager or Oracle SQL*Plus Worksheet as the user who will be formatting the data. To ensure version compatibility, use the otrcfmtc.sql script located in the ORACLE_HOME of the destination server.

The otrcfmtc.sql script is located in the $ORACLE_HOME/otracenn/admin directory (where nn represents the version of Oracle Trace).

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

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

Look for EPC_COLLECTION.

To check for formatter tables using SQL Worksheet:

connect <username>/<password>@<service name>
describe epc_collection

To create formatter tables, execute the otrcfmtc.sql script from the ORACLE_HOME of the Oracle Server that you connected to. You only need to run this script on Oracle Server releases prior to 7.3.4 and 8.0.4.

@ORACLE_HOME/otrace/admin/otrcfmtc.sql

Isolating a Problem in Oracle Trace Collection Services

Here are a few suggestions to help you isolate problems in the Oracle Trace Collection Services.

Oracle Trace Command-Line Interface

To test the CLI:

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

   To check settings on UNIX:

   printenv ORACLE_HOME
   printenv ORACLE_SID
   
   

   To set settings on UNIX:

   setenv ORACLE_HOME <path>
   setenv ORACLE_SID <sid>
   
   

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

3. Verify that the CLI release matches the database server release.

   The Oracle Trace Collection Services files may be overwritten on NT by other product installations. If this occurs, your Oracle Trace Collection Services files will no longer match the version required by your Oracle Server database and you may receive a memory mapping error.

   On the server node, type the following command on the operating system command line:

   $ORACLE_HOME/bin/otrccol version
   
   

4. Verify if the collection name has not already been used before you start the collection. Look in the .cdf and .dat directories $ORACLE_HOME\otracenn\admin on NT ($ORACLE_HOME/otrace/admin on UNIX).
5. If you want to generate database activity for this collection, connect to the database.
   • For Oracle Server release 7.3.x, connect to the service before you create your collection.
   • For Oracle Server release 8.0, you can connect to the database anytime and the processes will be registered.

     A .dat file and a .cdf file will be created in one of the following directories: $ORACLE_HOME/otrace/admin/cdf or the directory specified by the ORACLE_TRACE_COLLECTION_PATH parameter in your init<sid>.ora file.

   1. Verify that the collection was started.

      otrccol check <collection_name>
      
      

      The collection should show as active.

Oracle7 Stored Procedures

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

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

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

  Networks=>Databases=><your database>=>Schema Objects=>Packages=>SYS
  
  

  Look for stored procedures starting with DBMS_ORACLE_TRACE_xxx.

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

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

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

EPC_ERROR.LOG File

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

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

For general information about causes and actions for most Oracle Trace messages, see the Oracle Enterprise Manager Messages Manual

Information Needed When Reporting a Problem

If you have tried all the previously described suggestions and Oracle Trace is still not working, please call your local Oracle World Wide Support Center. When reporting a problem, have the following information available:

• Version of Oracle Trace
• Operating system and version you are using for the server and the version of the Oracle Server
• Operating system you are using for the client and the version of the client
• Verify whether you have multiple ORACLE_HOME installations. If so, what Oracle components and versions are in each ORACLE_HOME installation?
• Steps to follow to reproduce the problem
• Log files (EPC_ERROR.LOG)
• Steps you have followed to attempt to solve the problem