Release 1 (9.0.1)
Part Number A87503-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
This chapter describes how to use Oracle Trace to collect Oracle server event data.
The topics in this chapter include:
Oracle Trace is a general-purpose event-driven data collection product, which the Oracle server uses to collect performance and resource utilization data, such as SQL parse, execute, and fetch statistics, and wait statistics.
An event is the occurrence of some activity within a product. Oracle Trace collects data for predefined events occurring within a software product instrumented with the Oracle Trace API. That is, the product has embedded calls to the Oracle Trace API. An example of an event is a parse or fetch.
There are two types of events:
Point events represent an instantaneous occurrence of something in the product. An example of a point event is an error occurrence.
Duration events have a beginning and an ending. An example of a duration event is a transaction. Duration events can have other events occur within them; for example, an error can occur within a transaction.
The Oracle server has more than a dozen events. The following are three of these events:
Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.
Each event set is described by its own product definition file (.fdf). The product definition file is a set of events and their associated data items. The complete set of events defined for an instrumented product is referred to as the ALL event set. Other event sets are then derived from the ALL set. For example, the Oracle Server includes an event set known as the EXPERT set. This set includes SQL event data used by the Oracle Expert tuning application, but excludes other events, such as wait events.
During a collection, Oracle Trace stores event data in memory and periodically writes it to a collection binary file. This method ensures low resource overhead associated with the collection process. You can access event data collected in the binary file by formatting the data to database tables, which makes the data available for fast, flexible access. These database tables are called Oracle Trace formatter tables.
You can collect Oracle Trace data using one of the following mechanisms:
ORACLE_TRACE_* initialization parameters
You can control Oracle Trace server collections with the Oracle Trace CLI (command-line interface). The CLI is invoked by the OTRCCOL executable for the following functions:
OTRCCOL START job_id input_parameter_file
OTRCCOL STOP job_id input_parameter_file
OTRCCOL CHECK collection_name
OTRCCOL FORMAT input_parameter_file
OTRCCOL DCF col_name cdf_file
OTRCCOL DFD col_name username password service [col_id]
The job_id parameter should be set to a value of 1.
The input parameter file contains specific parameter values required for each function, as shown in the following examples. Col_name (collection name) and cdf_file (collection definition file) are initially defined in the START function input parameter file.
The OTRCCOL START function invokes a collection based on parameter values contained in the input parameter file. For example:
OTRCCOL START 1 my_start_input_file
where file my_start_input_file contains at least the following input parameters:
col_name= <collection name>cdf_file= <collection name>.cdfdat_file= <collection name>.datfdf_file= <facility definition file>.fdf
The server event sets that can be used as values for the fdf_file parameter are ORACLE, ORACLEC, ORACLED, ORACLEE, and ORACLESM, plus CONNECT, SQL_ONLY, SQL_PLAN, SQL_TXN, SQLSTATS, SQLWAITS, and WAITS.
Collection .cdf and .dat files are created in the directory $ORACLE_HOME/otrace/admin/cdf by default for collections started using the CLI (or PL/SQL procedures), unless overridden by EPC_COLLECTION_PATH environment variable.
|
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 Oracle9i Database Reference. |
The <collections name> parameter can be any valid unique filename.
For Oracle database collections, one additional parameter is required:
regid= 1 192216243 0 0 5 <database SID>
The regid parameter record identifies a database by SID where Oracle Trace collection is to be performed. The six elements making up the regid parameter record are as follows, in this order:
cf_num (more on this below)
cf_val (more on this below)
The cf_num and cf_val elements should set to zero in this basic Oracle database collection regid above.
There are several ways of limiting the amount of data collected. For example, additional regid records can be specified to reduce the amount of collected data, and nonzero cf_num and cf_val can be specified in those situations. In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values.
In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values. So, for example, if you provide an additional regid record with cf_num = 6 and cf_val = <some DB userID>, then the collection of database event data is limited to only those events performed by that database user.
If you are interested only in collecting database activity for users 23 and 45, then you would provide the following 3 regid records:
regid= 1 192216243 0 0 5 ORCLregid= 1 192216243 6 23 5 ORCLregid= 1 192216243 6 45 5 ORCL
The input parameter file used by the CLI when starting a collection can also contain the following optional parameters, for both database and nondatabase Oracle Trace collections:
prores= <process restriction> max_cdf= <maximum collection file size>
If no process restriction records are specified, then there are no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process of interest.
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.
Verify that the collection was started.
otrccol check <collection_name>
The collection should show as active, not active, or not found.
The OTRCCOL STOP function halts a running collection as follows:
OTRCCOL STOP 1 my_stop_input_file
where my_stop_input_file contains the collection name and cdf_file name.
The OTRCCOL FORMAT function formats the binary collection file to Oracle tables. An example of the FORMAT statement is:
OTRCCOL FORMAT my_format_input_file
where my_format_input_file contains the following input parameters:
username= <database username>password= <database password>service= <database service name>cdf_file= <usually same as collection name>.cdffull_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 an Oracle Trace collection using the |
The OTRCCOL DCF (delete collection files) function deletes collection .cdf and .dat files for a specific collection. The OTRCCOL DFD (delete formatted data) function deletes formatted data from the Oracle Trace formatter tables for a specific collection. You can specify an optional col_id parameter for a selective DFD, where more than one col_id has been created for a collection by multiple (full) formats.
Six Oracle database initialization parameters are set up by default to control Oracle Trace. By logging into a privileged account in the database and executing the SHOW PARAMETER ORACLE_TRACE statement, you see the following parameters:
You can modify these Oracle Trace server parameters to allow Oracle Trace collection of server event data and use them by adding them to the initialization file.
However, this method for controlling the Oracle Trace collection is rather inflexible: the collection name cannot be changed without performing a database shutdown. (For Oracle releases prior to 8.1.7, the collection can only be stopped by doing a shutdown, then setting ORACLE_TRACE_ENABLE = false before restarting.) However, with ORACLE_TRACE_ENABLE = true but ORACLE_TRACE_COLLECTION_NAME = "" [that is, empty name string], Oracle Trace collections of database event data can be performed using one of the other collection control mechanisms; for example, the Oracle Trace CLI. These other mechanisms are more flexible than the database initialization parameters. In general, they are preferred over using parameters for collection control.
The ORACLE_TRACE_ENABLE database initialization parameter is false by default. This disables any collection of Oracle Trace data for that server, regardless of the mechanism used to control the collection.
Setting ORACLE_TRACE_ENABLE to true in <DBinit>.ora enables Oracle Trace collections for the server, but it does not necessarily start a collection when the database instance is started. If the database parameters alone are to be used to start an Oracle Trace collection of database event data, then all 6 ORACLE_TRACE_* parameters must be specified, or have nonnull values by default. Typically, this means that both ORACLE_TRACE_ENABLE must be set to true and a nonnull ORACLE_TRACE_COLLECTION_NAME must be provided (up to 16 characters in length).
ORACLE_TRACE_ENABLE is now a dynamic parameter (as of Oracle8i, Release 3), so it can be set to true or false while the database is running. This can be done for the current database session or for all sessions (including future ones), using ALTER SESSION or ALTER SYSTEM statements. When the database is subsequently shut down and then restarted, the <DBinit>.ora setting for ORACLE_TRACE_ENABLE is again used to initially enable or disable Oracle Trace collection of database event data.
The ORACLE_TRACE_FACILITY_NAME database initialization parameter specifies the event set that Oracle Trace collects, if the database parameters are used to control data collection. The default for this parameter is ORACLED (in other words, Oracle "default" event set).
With database parameters set to start an Oracle Trace Collection, if the database does not begin collecting data, then check the following:
ORACLE_TRACE_FACILITY_NAME, with an .fdf extension, should be in the directory specified by the ORACLE_TRACE_FACILITY_PATH initialization parameter. The exact directory that this parameter specifies is platform-specific.
COLLECT.DAT, FACILITY.DAT (or PROCESS.DAT for Oracle 7.3), and REGID.DAT. If they do not, then run the OTRCCREF executable to create or recreate them.
EPC_ERROR.LOG file to see more information about why a collection failed. Oracle Trace creates the EPC_ERROR.LOG file in the current default directory if the Oracle Trace Collection Services OTRCCOL image must log an error.
trc files in the directory specified by the server USER_DUMP_DEST initialization parameter. Searching for "epc" in the *.trc files might give errors. These errors and their descriptions may be found in $ORACLE_HOME/otrace/mesg/epcus.msg (assuming US installation), depending on availability for a given platform.
Oracle provides an additional Oracle Trace library that allows control of both database and nondatabase Oracle Trace collections from PL/SQL.
Both the name and the location of this new library are platform-dependent. On Unix platforms, the library is $ORACLE_HOME/lib/libtracepls9.so.
On Win32 platforms (for example, Windows NT), the library is %ORACLE_HOME%\bin\oratracepls9.dll.
The otrace/admin directory contains two new SQL scripts that can be used to define a database LIBRARY object for this library, and to define the procedures that can be used to call out to the library from PL/SQL:
In addition, the otrace/demo directory contains several SQL scripts showing PL/SQL examples that start, stop, and then format an Oracle Trace collection. These are respectively:
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:
cf_num (see below)
cf_val (see below)
For an Oracle Trace database collection, a regid string like this example is required, basically to identify the database SID and to specify that you are collecting for an Oracle server. The cf_num and cf_val should be zero in this basic regid record.
Additional regid records can be specified in order to reduce the amount of collected data. This is when the cf_num and cf_val items are used. In the Oracle server, Oracle Trace cross facility item 6 (cf_num = 6) is reserved to record database userID values. So, if you provide an additional regid record with cf_num = 6 and a cf_val = <some DB userID>, then the collection of database event data is limited to only those events performed by that database user. For example, if you are only interested in collecting database activity for users 23 and 45, then the regid_list consists of three records:
regid_list VARCHAR2(256) := '"1 192216243 0 0 5 ORCL", "1 192216243 6 23 5 ORCL", "1 192216243 6 45 5 ORCL"';
Similarly, the fdf_list argument could specify the name of a single .fdf file (facility definition file). Typically, this is the case. However, more than one .fdf could be specified in fdf_list if multiple facilities are involved in the collection. Of course, only one .fdf can be specified for any given facility; for example, the database.
On the other hand, the process restriction list prores_list can be empty. This indicates that there are to be no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process.
Other arguments in the "start collection" example in OTRCPLSSC.SQL are single numeric or string values, as shown. For example, the collection name and maximum collection data file size specified by the col_name and maxsize variables, respectively.
Running an Oracle Trace collection produces the following collection files:
<collection name>.cdf is the binary Oracle Trace collection definition file for the collection. It describes what is to be collected.
<collection name>.dat is the output file containing the collected Oracle Trace event data in binary form. When newly created, the empty .dat file contains only 35 bytes of file header information.
You can access the Oracle Trace data in the collection files in the following ways:
You can format Oracle Trace binary collection data to Oracle database tables, and you can then access this formatted data using SQL or other tools. The Oracle Trace format produces a separate table for each event type collected; for example, a parse event table is created to store data for all database parse events that were recorded during a server collection.
Use the following syntax to format an Oracle Trace collection with the OTRCFMT formatter utility:
OTRCFMT [options] <collection_name>.cdf [user/password@database]
If collection .cdf and .dat are not located in the current default directory, then specify the full file path for the .cdf file.
If you omit user/password@database (or any part of it, such as password or database), then OTRCFMT prompts you for this information.
Oracle Trace allows data to be formatted while a collection is occurring. By default, Oracle Trace formats only the portion of the collection that has not been formatted previously. If you want to reformat the entire collection file, then use the optional parameter -f (which generate a new collection ID in the formatter tables).
Oracle Trace provides several sample SQL scripts that you can use to access the formatted server event data tables. These are located in OTRCRPT*.SQL in the otrace directory tree.
The Oracle Trace reporting utility displays data for items associated with each occurrence of a server event. These reports can be quite large. You can control the report output by using optional statement qualifiers. Use the following report utility statement syntax:
OTRCREP [options] <collection name>.cdf
If collection .cdf and .dat are not located in the current default directory, then specify the full file path for the .cdf file.
First, you might want to run a report called PROCESS.txt. You can produce this report to provide a listing of specific process identifiers for which you want to run another report.
You can manipulate the output of the Oracle Trace reporting utility by using the following optional report qualifiers:
Default OTRCREP report output, with no optional qualifiers specified, consists of one text file per event type collected. Data from all participating processes are combined in each of these text files.
The following sections describe events that have been instrumented in Oracle Server. Most of the events are useful for performance analysis and tuning and workload analysis by Oracle Expert.
There are two types of events: point events and duration events. Point events represent an instantaneous occurrence of something in the instrumented product. An example of a point event is an error occurrence. Duration events have a beginning and ending. An example of a duration event is a transaction. Duration events can have other events occur within them; for example, the occurrence of an error within a transaction.
Table 12-2 lists the Oracle Server events instrumented for Oracle Trace. For more detailed descriptions, refer to the section for the event in which you are interested.
Specific kinds of information, known as items, are associated with each event. There are three types of items:
Oracle Trace has a standard set of items, called resource utilization items, that it can collect for any instrumented application, including the Oracle Server. In addition, all duration events in the Oracle Server include items for database statistics specific to the Oracle Server.
The standard resource utilization items are described in Table 12-3.
An Oracle Trace collection can be formatted into Oracle tables for access, analysis, and reporting. The last column contains the data type for data items formatted to the Oracle database.
The implementation of the item is platform specific; if the item is not implemented, the value is 0. For example, currently only CPU times are recorded on Windows NT.
Oracle Trace provides a set of 14 items called cross-product items (also known as cross-facility items for historical reasons). These data items allow programmers to relate events for different products. For example, a business transaction may generate events in two products: an application and the database. The cross-product data items allow these disparate events to be joined for analysis of the entire business transaction.
Cross-product items are reserved for specific products or product types as described in Table 12-4. If you do not use the products for which items are reserved, then you can use those items for your own purposes.
Cross-product item 1 (referred to as CROSS_FAC_1) contains data only if data is supplied by an instrumented application.
Cross-product item 2 (CROSS_FAC_2) is reserved for use by a future release of Oracle Forms. Instrumented applications and Oracle Forms pass identification data to the Oracle Server collection through these cross-product items.
Cross-product item 3 (CROSS_FAC_3) is reserved for use by Oracle Net. Oracle Net supplies the connection ID to Oracle Trace through CROSS_FAC_3. CROSS_FAC_3 is the key element in coordinating client/server or multitier Oracle Trace collections. Oracle Trace uses the Oracle Net global connection ID as the common element to match in the merger, for example the client and server collection files. The global connection ID is the same for the client and the server connection.
Most Oracle Server events record cross-product items 1 through 6. (Cache I/O does not.)
The Oracle Server product (or facility) definition files (that is, *.fdf) defines items specific to the Oracle Server. Use the item's number to locate it within the list. The formatted datatype describes how the Oracle Trace formatter defines the item when it formats data into an Oracle database.
The Oracle Server items are listed in Table 12-5.
| Item Name | Description | Item Number | Formatted Datatype |
|---|---|---|---|
App_Action |
Action name set by using the |
23 |
(1020) |
App_Module |
Module name set using the |
22 |
(1020) |
Commit_Abort |
Indicates if a transaction committed or aborted |
24 |
number |
Consistent_ Gets |
Number of blocks retrieved in consistent mode (did not change the data and therefore did not create any locks or conflicts with other users) |
104 |
number |
CPU_Session |
CPU session |
112 |
number |
Current_UID |
Current user ID |
36 |
number |
Cursor_Number |
Number of cursor associated with SQL statement |
25 |
number |
DB_Block_ Change |
Number of blocks changed |
102 |
number |
DB_Block_Gets |
Number of blocks retrieved in current mode. For large queries, this item tells how many sections of the database (logical pages) were fetched to retrieve all needed records. |
103 |
number |
Deferred_ Logging |
Value used by Oracle Trace internally |
14 |
number |
Depth |
Recursive level at which SQL statement is processed |
32 |
number |
Description |
Depends upon event in which it occurs (for example, wait event description) |
43 |
(1020) |
Elapsed_ Session |
Elapsed time for the session |
113 |
number |
End_of_Fetch |
Flag set if data retrieved is last data from query |
38 |
number |
Lib_Cache_Addr |
Address of SQL statement in library cache |
27 |
varchar2(64) |
Login_UID |
Internal ID within the Oracle database that identifies the user ID for the session |
15 |
number |
Login_UName |
Internal ID within the Oracle database that identifies the system account name for the session |
16 |
(1020) |
Missed |
Flag set if SQL statement was missing in library cache |
33 |
number |
Object_IDFoot 1 |
Object ID of the row source |
46 |
number |
OperationFootref 1 |
Text of the operation |
47 |
(1020) |
Operation_IDFootref 1 |
Position of the operation within the execution plan for a statement |
28 |
number |
Optimizer_Mode |
Oracle optimizer mode |
35 |
varchar2(128) |
Oracle_Cmd_ Type |
Oracle command number |
34 |
number |
Oracle PID |
Oracle process ID |
11 |
number |
OS_Image |
Operating system image (program name) |
42 |
long |
OS_Mach |
Operating system host machine |
20 |
(1020) |
OS_Term |
Operating system terminal |
19 |
(1020) |
OS_UName |
Operating system username |
18 |
(1020) |
P1 |
The definition of P1 depends upon the event in which it occurs. |
1 |
number |
P2 |
The definition of P2 depends upon the event in which it occurs. |
2 |
number |
P3 |
The definition of P3 depends upon the event in which it occurs. |
3 |
number |
P4 |
The definition of P4 depends upon the event in which it occurs. |
4 |
number |
P5 |
The definition of P5 depends upon the event in which it occurs. |
5 |
number |
P6 |
The definition of P6 depends upon the event in which it occurs. |
6 |
number |
P7 |
The definition of P7 depends upon the event in which it occurs. |
7 |
number |
P8 |
The definition of P8 depends upon the event in which it occurs. |
8 |
number |
P9 |
The definition of P9 depends upon the event in which it occurs. |
9 |
number |
P10 |
The definition of P10 depends upon the event in which it occurs. |
10 |
number |
Parent_Op_IDFootref 1 |
Parent operation |
44 |
number |
PGA_Memory |
Process Global Area memory |
101 |
number |
Physical Reads |
Number of blocks read from disk |
105 |
number |
PositionFootref 1 |
Position within events having same parent operation |
45 |
number |
Position_IDFoot 2 |
Position of the operation within the execution plan for a statement |
28 |
number |
Redo_Entries |
Number of redo entries made by process |
106 |
number |
Redo_Size |
Size of redo entries |
107 |
number |
Row_Count |
Number of rows processed |
29 |
number |
Schema_UID |
Schema user ID |
37 |
number |
Session_Index |
Oracle session ID |
12 |
number |
Session_Serial |
Session serial number |
13 |
number |
SID |
Text version of session ID |
17 |
(1020) |
Sort_Disk |
Number of disk sorts performed |
110 |
number |
Sort_Memory |
Number of memory sorts performed |
109 |
number |
Sort_Rows |
Total number of rows sorted |
111 |
number |
SQL_Text |
Text of SQL statement |
31 |
long |
SQL_Text_Hash |
Pointer to SQL statement |
26 |
number |
SQL_Text_ Segment |
Address of SQL text |
30 |
number |
T_Scan_Rows_ Got |
Rows processed during full table scans |
108 |
number |
TX_ID |
Unique identifier for a transaction that consists of rollback segment number, slot number, and wrap number |
41 |
varchar2(72) |
TX_SO_Addr |
The address of the transaction state object |
40 |
varchar2(64) |
TX_Type |
Type of the transaction. Value is a bitmap (for example, 2 active transaction, 0X10 space transaction, 0X20 recursive transaction). |
39 |
number |
UGA_Memory |
User Global Area session memory |
100 |
number |
Wait_Time |
Elapsed time, in hundredths of seconds, for the wait event |
21 |
number |
|
1
Item specific to Oracle Server release 8.0.2 and higher 2 Replaced by Operation_ID for Oracle Server release 8.0.2 and higher |
The following sections describe each event in more detail and provide tables that list the items associated with each event. For item descriptions, refer to Table 12-5.
When you format data, Oracle Trace creates a table for each event type collected. The name of the event data table is V_vendor#_F_product#_E_event#_version, where version is the number of the Oracle Server release. Any periods in the product version are replaced with underscores. You can use the otrcsyn.sql script to create synonyms for these tables.
The Oracle Trace formatter creates a column for each event item. For point events, the column name is the same as the item name. For duration events, the items for the start event have _START appended to the item name and the items for the end event have _END appended to the item name.
The formatter automatically includes additional columns for collection number, process identifier, and timestamp information as described in Table 12-6.
Items relating to database performance appear in several events. For convenience, these items are referenced as the Event Statistics Block. The items in the Event Statistics block are shown in Table 12-7:
UGA_Memory |
PGA_Memory |
DB_Block_Change |
DB_Block_Gets |
Consistent_Gets |
Physical_Reads |
Redo_Entries |
Redo_Size |
T_Scan_Rows_Got |
Sort_Memory |
Sort_Disk |
Sort_Rows |
CPU_Session |
Elapsed_Session |
|
The Connection event (event=1) records every time a connection is made to a database. Items associated with the Connection event are shown in Table 12-8. The name of the formatter table is V_192216243_F_5_E_1_7_3 (for Oracle Server release 7.3).
Session_Index |
Session_Serial |
Oracle_PID |
Login_UID |
Login_UName |
SID |
OS_UName |
OS_Term |
OS_Mach |
OS_Image |
Cross-Product Items 1-6 |
|
The Oracle Server uses the combination of Session_Index and Session_Serial to uniquely identify a connection. Oracle Net uses the connection ID, stored in CROSS_FAC_3, to uniquely identify a connection.
The Disconnect event records every time a database disconnection is made. Items associated with the Disconnect event are shown in Table 12-9. The name of the formatter table is V_192216243_F_5_E_2_7_3.
Session_Index |
Session_Serial |
Event Statistics Block |
Oracle_PID |
Cross-Product Items 1-6 |
|
A Disconnect event corresponds to at most one Connection event. Therefore, the same fields uniquely identify a disconnect: either the combination of Session_Index and Session_Serial, or CROSS_FAC_3.
The ErrorStack event identifies the process that has the error. Items associated with the ErrorStack event are shown in Table 12-10. The name of the formatter table is V_192216243_F_5_E_3_7_3.
Session_Index |
Session_Serial |
Oracle_PID |
|
P1 |
P2 |
P3 |
|
P4 |
P5 |
P6 |
|
P7 |
P8 |
Cross-Product Items 1-6 |
The ErrorStack event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific ErrorStack event.
The Migration event is logged each time a session migrates to a shared server process. The name of the formatter table is V_192216243_F_5_E_4_7_3. This event is currently disabled in the Oracle server code.
Items associated with the Migration event are shown in Table 12-11.
Session_Index |
Session_Serial |
Oracle_PID |
|
Cross-Product Items 1-6 |
|
|
The Migration event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific Migration event.
The ApplReg event (event=5) registers with Oracle Trace where the application is at a certain point in time. Items associated with the ApplReg event are shown in Table 12-12. The name of the formatter table is V_192216243_F_5_E_5_7_3.
Session_Index |
Session_Serial |
App_Module |
App_Action |
Cross-Product Items 1-6 |
|
The ApplReg event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Timestamp, and Timestamp_Nano should uniquely identify a specific ApplReg event.
The RowSource event logs the number of rows processed by a single row source within an execution plan. Items associated with the RowSource event are shown in Table 12-13. The name of the formatter table is V_192216243_F_5_E_6_7_3.
Session_Index |
Session_Serial |
Cursor_Number |
Position_ID |
Row_Count |
Cross-Product Items 1-5 |
The combination of Session_Index, Session_Serial, Cursor_Number, and Position_ID uniquely identifies a RowSource event.
The RowSource event logs the number of rows processed by a single row source within an execution plan. Items associated with the RowSource event for Oracle Server release 8.0.2 or higher are shown in Table 12-14. The name of the formatter table is V_192216243_F_5_E_6_8_0.
Session_Index |
Session_Serial |
Cursor_Number |
Operation_ID |
Row_Count |
Parent_Op_ID |
Position |
Object_ID |
Operation |
|
Cross-Product Items 1-6 |
|
|
The combination of Session_Index, Session_Serial, Cursor_Number, and Operation_ID uniquely identifies a RowSource event.
The SQLSegment event is a description of a SQL statement. Items associated with the SQLSegment event are shown in Table 12-15. The name of the formatter table is V_192216243_F_5_E_7_7_3.
Session_Index |
Session_Serial |
Cursor_Number |
SQL_Text_Hash |
Lib_Cache_Addr |
SQL_Text_Segment |
SQL_Text |
Cross-Product Items 1-6 |
|
A SQL segment does not have an explicit identifier. The SQL_Text_Hash field is always the same for each occurrence of a SQL statement but multiple statements can have the same hash value. If a statement is forced out of the library cache and then swapped back in, the same statement can have multiple values for Lib_Cache_Addr. The combination of Session_Index, Session_Serial, SQL_Text_Hash, and Lib_Cache_Addr usually should identify a particular SQL statement for a session. If you add Cursor_Number, you identify a particular occurrence of a SQL statement within the session.
The wait event shows the total waiting time in hundredths of seconds for all responses. Items associated with the wait event are shown in Table 12-16. The name of the formatter table is V_192216243_F_5_E_13_7_3.
Session_Index |
Session_Serial |
Wait_Time |
|
P1 |
P2 |
P3 |
|
Description |
Cross-Product Items 1-7 |
|
The wait event does not have an explicit identifier. The combination of Session_Index, Session_Serial, Description, Timestamp, and Timestamp_Nano should uniquely identify a specific wait event.
The Parse event records the start and end of the parsing phase during the processing of a SQL statement. The parsing phase occurs when the SQL text is read in and broken down (parsed) into its various components. Tables and fields are identified, as well as which fields are sort criteria and which information needs to be returned. Items associated with the parse event are shown in Table 12-17. The name of the formatter table is V_192216243_F_5_E_8_7_3.
The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific parse event.
The Execute event is where the query plan is executed. That is, the parsed input is analyzed to determine exact access methods for retrieving the data, and the data is prepared for fetch if necessary. Items associated with the Execute event are shown in Table 12-18. The name of the formatter table is V_192216243_F_5_E_9_7_3.
The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific Execute event.
The Fetch event is the actual return of the data. Multiple fetches can be performed for the same statement to retrieve all the data. Items associated with the Fetch event are shown in Table 12-19. The name of the formatter table is V_192216243_F_5_E_10_7_3.
The combination of Session_Index, Session_Serial, Cursor_Number, SQL_Text_Hash, Timestamp, and Timestamp_Nano uniquely identifies a specific Fetch event.
The LogicalTX event logs the start and end of a logical transaction (that is, statements issued that may cause a change to the database status). Items associated with the LogicalTX event are shown in Table 12-20. The name of the formatter table is V_192216243_F_5_E_11_7_3.
The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction. Or, use Session_Index, Session_Serial, and TX_SO_Addr.
The PhysicalTX event logs the start and end of a physical transaction (that is, statements issued that caused a change in database status). Items associated with the PhysicalTX event are shown in Table 12-21. The name of the formatter table is V_192216243_F_5_E_12_7_3.
The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction.
Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.
Use the following sections to troubleshoot problems while using Oracle Trace.
If you suspect an Oracle Trace configuration problem:
EPC_ERROR.LOG file for details of any logged Oracle Trace errors.
$ORACLE_HOME/otrace/admin (*.dat files) and run otrccref to recreate the Oracle Trace *.dat files if the files do not exist.
fdf files are in the $ORACLE_HOME/otrace/admin/fdf directory.
% $ORACLE_HOME/bin/otrccol version
| If the Returned Value Is: | Then the Command-Line Interface Release Is: |
|---|---|
|
1 |
733 |
|
2 |
803 |
|
3 |
734 |
|
4 |
804 |
|
5 |
805 |
|
6 |
813 |
|
7 |
814 |
|
8 |
815 |
|
9 |
806 |
|
10 |
816 |
|
11 |
817 |
|
12 |
901 |
% $ORACLE_HOME/bin/otrccol check <collection_name>
To test the CLI:
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.
Look for <collection name>.cdf and .dat files in:
If you suspect a server environment problem, verify the following:
To solve the immediate problem, stop the collection, and free up space so Oracle Trace can end the collection.
Initially limiting the collection to specific users and/or wait events also helps to limit the amount of data collected. Limiting users and wait events is available for Oracle Server releases 8.0.4 and higher.
Wait times are collected only if the INIT<sid>.ORA parameter, TIMED_STATISTICS, is set to true.
If an expected SQL statement does not appear to be in your collection, it may be because a small amount of data in the Oracle Trace data collection buffers may not have been flushed out to the collection data file, even though the collection has been stopped. Additional database activity should flush these buffers to disk, and shutting down the database also forces a flush of these buffers.
There may be times when a collection is too large. Starting with Oracle Server release 8.0.4, you can collect data for specific users and specific wait event types to minimize the size of the collection. Because, almost always, the server is waiting for a latch, lock, or resource, wait event data for a brief collection can be quite extensive.
In Oracle8 databases (prior to Oracle 8.1.7), the ORACLE_TRACE_ENABLE parameter in the INITsid.ORA file on the server must be set to true before the database is started. Starting with Oracle 8.1.7 it is dynamic and may be modified via ALTER_SESSION or ALTER_SYSTEM. (For Oracle7 the ORACLE_TRACE_ENABLE parameter should be left as false, unless you are using the init.ora parameter method to start or stop collections.) You can also see this problem if there are too many collections running concurrently.
On Windows NT systems, if you are running Oracle Trace collections and an error occurs indicating Oracle Trace could not access memory, the collect.dat file has become full. You must create a new .dat file by running the otrccref.exe image located in the $ORACLE_HOME/bin directory. However, database services must be shutdown to release the collect.dat file for the otrccref script to be able to create the new collect.dat file. You can also increase the number of collect.dat records above the default of 36 records (for example, otrccref -c50 to create a new otrace/admin/collect.dat file containing 50 records).
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.
To check for stored procedures using the Oracle Enterprise Manager console, use the Navigator and the following path:
Networks=>Databases=><your database>=>Schema Objects=>Packages=>SYS
Look for stored procedures starting with DBMS_ORACLE_TRACE_xxx.
To check for stored procedures using Oracle Server Manager or Oracle SQL*Plus Worksheet:
select object_name from dba_objects where object_name like '%TRACE%' and object_type = 'PACKAGE'; OBJECT_NAME DBMS_ORACLE_TRACE_AGENT DBMS_ORACLE_TRACE_USER 2 rows selected.
For Oracle7, Oracle Trace required that these stored procedures be installed on the database. These SQL scripts may be automatically run during database installation depending on the platform-specific installation procedures. If they are not executed during database installation, you must run these scripts manually. You can add these stored procedures to the database by running the otrcsvr.sql script from $ORACLE_HOME/otrace/admin) from a privileged database account (SYS or INTERNAL). To run the script, set the default to the path were the script is located. This script runs other scripts that do not have the path specified. These other scripts fail if you are not in the directory where these scripts will run.
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.
Oracle Server releases 7.3.4 and 8.0.4 and later automatically create the formatter tables. Prior to Oracle Server releases 7.3.4 and 8.0.4, you must run the otrcfmtc.sql script from Oracle Server Manager or Oracle SQL*Plus Worksheet as the user who will be formatting the data. If you must manually execute otrcfmtc.sql to create the formatter tables, use the SQL script from the same Oracle home as your collections to be formatted.
The otrcfmtc.sql script is located in the $ORACLE_HOME/otrace/admin directory.
Formatting error might be due to one of the following causes:
Look for EPC_COLLECTION.
To check for formatter tables using SQL Worksheet:
CONNECT <username>/<password>@<service name> DESCRIBE epc_collection
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|