Oracle Enterprise Manager Oracle Trace User's Guide
Release 1.4.0

A53696_01

Library

Product

Contents

Index

Prev Next

A
Using Oracle Trace for Oracle Server Data Collections

Server performance data can be collected using the Oracle Trace product for Oracle Server release 7.3 or higher. The server performance data that can be collected by Oracle Trace includes:

This appendix contains the following:

Using Oracle Trace for Server Performance Data Collection

You can use Oracle Trace to collect server performance data for a specific database session or for the entire instance. You can also select which server event set you want to collect for.

Oracle Trace allows host application events to be organized into event sets. This allows you to collect data for a specific subset of all potential host application events. Oracle Corporation has defined the following event sets for Oracle Server: ALL, DEFAULT, and EXPERT. The ALL set includes all server events, the DEFAULT set excludes server WAIT events, and the EXPERT set is specifically defined for use in the Oracle Expert tuning application. Oracle Corporation recommends using the DEFAULT event set.

Server collections can be enabled and controlled in the following ways:

The following sections describe the server-based controls.

Using INIT.ORA Parameters to Control Oracle Trace

There are six Oracle Trace parameters that are set up by default to control Oracle Trace. By logging in to the internal account in your database and executing a "show parameters trace" command, you will see the following parameters:

NAME   TYPE   VALUE (Oracle7 Databases)   VALUE (Oracle8 Databases)  

ORACLE_TRACE_COLLECTION_NAME  

string  

oracle  

NULL  

ORACLE_TRACE_COLLECTION_PATH  

string  

?/rdbms/log  

$ORACLE_HOME/otrace/admin/cdf  

ORACLE_TRACE_COLLECTION_SIZE  

integer  

5242880  

5242880  

ORACLE_TRACE_ENABLE  

Boolean  

FALSE  

FALSE  

ORACLE_TRACE_FACILITY_NAME  

string  

oracle  

oracle  

ORACLE_TRACE_FACILITY_PATH  

string  

?/rdbms/admin  

$ORACLE_HOME/otrace/admin/fdf  

The Oracle Trace parameters may be modified and used by adding them to your INITsid.ORA file. To start tracing for a database using these parameters, you must minimally add the following two parameters to your .ORA file:

oracle_trace_enable = TRUE 
oracle_trace_facility_name = oracled  

The "d" selects the server DEFAULT event set. Use oracle for the server ALL event set, and oraclee for the EXPERT event set.

You must then shut down your database and start it up again before the parameters take effect. Once restarted, the database begins collecting data for the class of data that you selected. To stop the collection, you must shut down the database, set the INITsid.ORA parameter ORACLE_TRACE_ENABLE = FALSE, and restart the database.

If, once restarted, the database does not start collecting data, you should check the following:

Oracle Trace INIT.ORA Parameters

ORACLE_TRACE_COLLECTION_NAME

This parameter specifies the Oracle Trace collection name. This parameter is also used in the output filenames, collection definition file (.cdf) and data file (.dat).

Default Value

Operating-system specific. For Oracle Server release 8.0, NULL is the default.

Range of Values

Valid collection name up to 16 characters long

ORACLE_TRACE_COLLECTION_PATH

This parameter specifies the directory pathname where Oracle Trace collection definition and data files are located. If you accept the default, the complete file specification is generally $ORACLE_HOME/rdbms/log/collection_name.cdf and collection_name.dat; however, this specification may be different on systems other than UNIX.

Note:

For Oracle Server release 8.0, the complete file specification is generally $ORACLE_HOME/otrace/admin/collection_name.cdf.  

Default Value

Operating-system specific

Range of Values

Full directory pathname

ORACLE_TRACE_COLLECTION_SIZE

This parameter specifies the maximum size, in bytes, of the Oracle Trace collection file. Once the collection file reaches this maximum, the collection is disabled.

Default Value

5242880

Range of Values

0 to 4294967295 (0 means unlimited size)

ORACLE_TRACE_ENABLE

This parameter disables or enables an Oracle Trace collection. For Oracle Server release 7.3 or higher, if ORACLE_TRACE_ENABLE is set to TRUE, when you start the server you automatically start an Oracle Trace collection. Server event data is collected for all database user sessions. To stop a collection, you must shut down the instance and reset the parameter to FALSE.

Note:

For Oracle Server release 8.0.3

If ORACLE_TRACE_ENABLE is set to TRUE, the database can have Oracle Trace collections scheduled against it with the Oracle Trace Manager. Automatic collections are controlled through a non-null ORACLE_TRACE_COLLECTION_NAME parameter.  

Default Value

FALSE

Range of Values

TRUE/FALSE

ORACLE_TRACE_FACILITY_NAME

This parameter specifies the Oracle Trace product definition file (.fdf file). The file must be located in the directory pointed to by the ORACLE_TRACE_FACILITY_PATH parameter.

The product definition file contains definition information for all events and data items that can be collected for a product that uses the Oracle Trace data collection API. Products can have multiple product definition files. For example, the Oracle Server has multiple event sets and, therefore, multiple product definition files. Oracle Corporation recommends that you use the DEFAULT event set for Oracle Server collections, oracled.fdf.

Default Value

Operating-system specific

Range of Values

Valid product definition file name up to 16 characters long

ORACLE_TRACE_FACILITY_PATH

This parameter specifies the directory pathname where Oracle Trace product definition files are located.

Default Value

Operating-system specific

Range of Values

Full directory pathname

Using Stored Procedure Packages to Control Oracle Trace

You can invoke an Oracle Trace collection for your own session or for another session by using the Oracle Trace stored procedure packages.

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

dbms_oracle_trace_user.set_oracle_trace(true/false, 
collection_name, server_event_set) 

Example:

EXECUTE dbms_oracle_trace_user.set_oracle_trace(TRUE,'MYCOLL','oracle'); 

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

dbms_oracle_trace_agent.set_oracle_trace_in_session(sid, serial#, 
true/false, collection_name, server_event_set)

Example:

EXECUTE dbms_oracle_trace_agent.set_oracle_trace_in_session 
(8,12,TRUE,'NEWCOLL','oracled');  

If the collection does not occur, you should check the following:

Oracle Trace Collection Results

Running an Oracle Trace collection produces the following collection files located in the directory specified in INIT.ORA or in the default collection path:

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

Oracle Server Events

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. Additionally, the ErrorStack event is useful for error logging purposes.

The Oracle Server Application Registration, Wait, Fetch, Execute, and Parse events can be associated with the transaction and database connection from which they occurred using cross-product items 3 and 4.

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 A-1 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.

Table A-1 Oracle Server Events
Event   Description   Type of Event  

Connection  

Connection to a database.  

Point  

Disconnect  

Disconnection from a database.  

Point  

ErrorStack  

Code stack for core dump.  

Point  

Migration  

Session migration between shared server processes.  

Point  

ApplReg  

Application context information.  

Point  

RowSource  

Row information. For Oracle Server release 8.0.2 and higher, this also includes data about the execution plan.  

Point  

SQLSegment  

Text of SQL statement.  

Point  

Wait  

Generic WAIT event. Context is provided in the event strings.  

Point  

Parse  

SQL query parsing information.  

Duration  

Execute  

Information for execution of SQL query plan.  

Duration  

Fetch  

Actual row retrieval information.  

Duration  

LogicalTX  

The first time a database command is performed that may change the database status.  

Duration  

PhysicalTX  

Event marking a definite change in database status.  

Duration  

Data Items Collected for Events

Specific kinds of information, known as items, are associated with each event. There are three types of items:

Resource Utilization Items

Oracle Trace has a standard set of items, called resource utilization items, that it collects by default 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 A-2. Items specific to Oracle Server are described in Table A-4.

An Oracle Trace collection can be formatted to Oracle tables for access, analysis, and reporting. The last column contains the data type for data items formatted to the Oracle database.

Table A-2 Standard Resource Utilization Items
Item Name   Description   Item ID   Datatype of Formatted Data  

UCPU  

Amount of CPU time in user mode  

129  

NUMBER  

SCPU  

Amount of CPU time in system mode  

130  

NUMBER  

INPUT_IO  

Number of times file system performed input  

131  

NUMBER  

OUTPUT_IO  

Number of times file system performed output  

132  

NUMBER  

PAGEFAULTS  

Number of hard and soft page faults  

133  

NUMBER  

PAGEFAULT_IO  

Number of hard page faults  

134  

NUMBER  

MAXRS_SIZE  

Maximum resident set size used  

135  

NUMBER  

The implementation of the item is platform specific; if the item is not implemented, the value is 0.

Cross-Product Items

Oracle Trace provides a set of 14 items called cross-product items. 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 A-3. If you do not use the products for which items are reserved, you may use those items for your own purposes.

Cross-product item 1 (referred to as CROSS_FAC_1) will contain 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 will 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 SQL*Net. SQL*Net supplies the connection ID to Oracle Trace through CROSS-_FAC_3. CROSS_FAC_3 is the key element in coordinating client/server Oracle Trace collections. Oracle Trace uses the SQL*Net global connection ID as the common element to match in the merger of the client and server collection files. The global connection ID is the same for the client and the server connection. It is used as the Oracle Trace registration ID that gets logged with the CROSS_FAC_3 event collection.

Each Oracle Server event will record cross-product items 1 through 5.

Note:

In this version of Oracle Trace, the term "facility" has been changed to "product". Therefore, the items named CROSS_FAC_x are cross-product items.  

Table A-3 Cross-Product Items
Item Name   Layer   Description   Item ID   Datatype of Formatted Data  

CROSS_FAC_1  

Application  

Application ID. For use by high-level applications such as Oracle Financials, third-party or customer applications  

136  

NUMBER  

CROSS_FAC_2  

Oracle Forms  

Oracle Forms ID  

137  

NUMBER  

CROSS_FAC_3  

SQL*Net  

Remote node connection ID  

138  

NUMBER  

CROSS_FAC_4  

Oracle Server  

Transaction ID  

139  

NUMBER  

CROSS_FAC_5  

Oracle Server  

Hash_ID of SQL statement  

140  

NUMBER  

CROSS_FAC_6  

n/a  

Not reserved  

141  

NUMBER  

CROSS_FAC_7  

n/a  

Not reserved  

142  

NUMBER  

CROSS_FAC_8  

n/a  

Not reserved  

143  

NUMBER  

CROSS_FAC_9  

n/a  

Not reserved  

144  

NUMBER  

CROSS_FAC_10  

n/a  

Not reserved  

145  

NUMBER  

CROSS_FAC_11  

n/a  

Not reserved  

146  

NUMBER  

CROSS_FAC_12  

n/a  

Not reserved  

147  

NUMBER  

CROSS_FAC_13  

n/a  

Not reserved  

148  

NUMBER  

CROSS_FAC_14  

n/a  

Not reserved  

149  

NUMBER  

Items Specific to Oracle Server Events

The Oracle Server product definition file defines items specific to the Oracle Server.

The Edit Product function of the Oracle Trace Manager displays items in the order of their item number. 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 A-4.

Table A-4 Oracle Server Items
Item Name   Description   Item Number   Formatted Datatype  

App_Action  

Action name set by using the dbms_application_info.set_module procedure  

23  

VARCHAR2(255)  

App_Module  

Module name set using the dbms_application_info.set_module procedure  

22  

VARCHAR2(255)  

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  

43  

VARCHAR2(255)  

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(16)  

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  

VARCHAR2(255)  

Missed  

Flag set if SQL statement was missing in library cache  

33  

NUMBER  

Object_ID1  

Object ID of the row source  

46  

NUMBER  

Operation2  

Text of the operation  

47  

VARCHAR2(255)  

Operation_ID3  

Position of the operation within the execution plan for a statement  

28  

NUMBER  

Optimizer_Mode  

Oracle optimizer mode  

35  

VARCHAR2(32)  

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  

VARCHAR2(255)  

OS_Term  

Operating system terminal  

19  

VARCHAR2(255)  

OS_UName  

Operating system username  

18  

VARCHAR2(255)  

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_ID4  

Parent operation  

44  

NUMBER  

PGA_Memory  

Process Global Area memory  

101  

NUMBER  

Physical Reads  

Number of blocks read from disk  

105  

NUMBER  

Position5  

Position within events having same parent operation  

45  

NUMBER  

Position_ID6  

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  

VARCHAR2(255)  

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(18)  

TX_SO_Addr  

The address of the transaction state object  

40  

VARCHAR2(16)  

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 Item specific to Oracle Server release 8.0.2 and higher
3 Item specific to Oracle Server release 8.0.2 and higher
4 Item specific to Oracle Server release 8.0.2 and higher
5 Item specific to Oracle Server release 8.0.2 and higher
6 Replaced by Operation_ID for Oracle Server release 8.0.2 and higher

Items Associated with Each Event

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 A-4.

When you format data, Oracle Trace creates a table for each event 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.

Note:

The following tables use Oracle7 Server names for example purposes.  

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 A-5.

Table A-5 Additional Columns Included by Oracle Trace Formatter
Column Name   Description   Datatype  

collection_ID  

collection number, automatically assigned by the formatter  

NUMBER(4)  

epid  

process ID number  

NUMBER(8)  

timestamp  

logged time for point events  

DATE  

timestamp_nano  

nanoseconds of logged time for point events  

NUMBER  

timestamp_start  

duration event start time  

DATE  

timestamp_nano_start  

nanoseconds of duration event start time  

NUMBER  

timestamp_end  

duration event end time  

DATE  

timestamp_nano_end  

nanoseconds of duration event end time  

NUMBER  

Event Statistics Block

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 A-6:

Table A-6 Event Statistics Block

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  

 

Connection Event

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 A-7. The name of the formatter table is V_192216243_F_5_E_1_7_3.

Table A-7 Items Associated with the Connection Event

Session_Index  

Session_Serial  

Oracle_PID  

Login_UID  

Login_UName  

SID  

OS_UName  

OS_Term  

OS_Mach  

OS_Image  

Cross-Product Items 1-5  

 

The Oracle Server uses the combination of Session_Index and Session_Serial to uniquely identify a connection. SQL*Net uses the connection ID, stored in CROSS_FAC_3, to uniquely identify a connection.

Disconnect Event

The Disconnect event records every time a database disconnection is made. Items associated with the Disconnect event are shown in Table A-8. The name of the formatter table is V_192216243_F_5_E_2_7_3.

Table A-8 Items Associated with the Disconnect Event

Session_Index  

Session_Serial  

Event Statistics Block  

Oracle_PID  

Cross-Product Items 1-5  

 

A Disconnect event will correspond 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.

ErrorStack Event

The ErrorStack event identifies the process that has the error. Items associated with the ErrorStack event are shown in Table A-9. The name of the formatter table is V_192216243_F_5_E_3_7_3.

Table A-9 Items Associated with the ErrorStack Event

Session_Index  

Session_Serial  

Oracle_PID  

P1  

P2  

P3  

P4  

P5  

P6  

P7  

P8  

Cross-Product Items 1-5  

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.

Migration 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 was disabled for Oracle Server release 7.3.2, but is enabled for all releases after 7.3.2.

Items associated with the Migration event are shown in Table A-10.

Table A-10 Items Associated with the Migration Event

Session_Index  

Session_Serial  

Oracle_PID  

Cross-Product Items 1-5  

 

 

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.

ApplReg 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 A-11. The name of the formatter table is V_192216243_F_5_E_5_7_3.

Table A-11 Items Associated with the ApplReg Event

Session_Index  

Session_Serial  

App_Module  

App_Action  

Cross-Product Items 1-5  

 

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.

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 are shown in Table A-12. The name of the formatter table is V_192216243_F_5_E_6_7_3.

Table A-12 Items Associated with the RowSource Event

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.

RowSource Event (Specific to Oracle Server Release 8.0.2 and Higher)

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 A-13. The name of the formatter table is V_192216243_F_5_E_6_8_0.

Table A-13 Items Associated with the RowSource Event

Session_Index  

Session_Serial  

Cursor_Number  

Operation_ID  

Row_Count  

Parent_Op_ID  

Position  

Object_ID  

Operation  

Cross-Product Items 1-5  

 

 

The combination of Session_Index, Session_Serial, Cursor_Number, and Operation_ID uniquely identifies a RowSource event.

Note:

The text in the Operation item is equivalent to information about the execution plan, which is similar to data that can be obtained by running explain plan.  

SQLSegment Event

The SQLSegment event is a description of a SQL statement. Items associated with the SQLSegment event are shown in Table A-14. The name of the formatter table is V_192216243_F_5_E_7_7_3.

Table A-14 Items Associated with the SQLSegment Event

Session_Index  

Session_Serial  

Cursor_Number  

SQL_Text_Hash  

Lib_Cache_Addr  

SQL_Text_Segment  

SQL_Text  

Cross-Product Items 1-5  

 

A SQL segment does not have an explicit identifier. The SQL_Text_Hash field will always be 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 should usually identify a particular SQL statement for a session. If you add Cursor_Number, you will identify a particular occurrence of a SQL statement within the session.

Wait Event

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 A-15. The name of the formatter table is V_192216243_F_5_E_13_7_3.

Table A-15 Items Associated with the Wait Event

Session_Index  

Session_Serial  

Wait_Time  

P1  

P2  

P3  

Description  

Cross-Product Items 1-5  

 

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.

Parse 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 A-16. The name of the formatter table is V_192216243_F_5_E_8_7_3.

Table A-16 Items Associated with the Parse Event
Items for Start of Parse Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Resource Items  

Cross-Product Items 1-5  

Items for End of Parse Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Depth  

Missed  

Oracle_Cmd_Type  

Optimizer_Mode  

Current_UID  

Schema_UID  

SQL_Text_Hash  

Lib_Cache_Addr  

Resource Items  

 

 

The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific Parse event.

Execute 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 A-17. The name of the formatter table is V_192216243_F_5_E_9_7_3.

Table A-17 Items Associated with the Execute Event
Items for Start of Execute Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Resource Items  

Cross-Product Items 1-5  

 

 

 

Items for End of Execute Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Depth  

Missed  

Row_Count  

SQL_Text_Hash  

Lib_Cache_Addr  

Resource Items  

 

 

The combination of Session_Index, Session_Serial, Cursor_Number, and SQL_Text_Hash uniquely identifies a specific Execute event.

Fetch Event

The Fetch event is the actual return of the data. Multiple fetches can be performed from the same statement to retrieve all the data. Items associated with the Fetch event are shown in Table A-18. The name of the formatter table is V_192216243_F_5_E_10_7_3.

Table A-18 Items Associated with the Fetch Event
Items for Start of Fetch Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Resource Items  

Cross-Product Items 1-5  

Items for End of Fetch Event  

Session_Index  

Session_Serial  

Event Statistics Block  

Cursor_Number  

Depth  

Row_Count  

End_of_Fetch  

SQL_Text_Hash  

Lib_Cache_Addr  

Resource Items  

 

 

The combination of Session_Index, Session_Serial, Cursor_Number, SQL_Text_Hash, Timestamp, and Timestamp_Nano uniquely identifies a specific Fetch event.

LogicalTX Event

The LogicalTX event logs the start and end of a logical transaction (that is, a statement issued that may cause a change to the database status). Items associated with the LogicalTX event are shown in Table A-19. The name of the formatter table is V_192216243_F_5_E_11_7_3.

Table A-19 Items Associated with the LogicalTX Event
Items for Start of LogicalTX Event  

Session_Index  

Session_Serial  

Event Statistics Block  

TX_Type  

TX_SO_Addr  

Resource Items  

Cross-Product Items 1-5  

 

 

Items for End of LogicalTX Event  

Session_Index  

Session_Serial  

Event Statistics Block  

TX_Type  

TX_SO_Addr  

Resource Items  

The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction. Or, use Session_Index, Session_Serial, and TX_SO_Addr.

PhysicalTX Event

The PhysicalTX event logs the start and end of a physical transaction (that is, one in which database status is actually changed). Items associated with the PhysicalTX event are shown in Table A-20. The name of the formatter table is V_192216243_F_5_E_12_7_3.

Table A-20 Items Associated with the PhysicalTX Event
Items for Start of PhysicalTX Event  

Session_Index  

Session_Serial  

Event Statistics Block  

TX_Type  

TX_ID  

Resource Items  

Cross-Product Items 1-5  

 

 

Items for End of PhysicalTX Event  

Session_Index  

Session_Serial  

Event Statistics Block  

TX_Type  

TX_ID  

Commit_Abort  

Resource Items  

 

 

The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index