Oracle Enterprise Manager Oracle Trace Developer's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

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 information on Oracle Server events and data that can be collected with Oracle Trace.

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.


Note:

Waits are numerous and consume a lot of space.

 

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 B-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 B-1 Oracle Server Events
Event   Description   Type of Event  

Connection

 

Records each connection to a database.

 

Point

 

Disconnect

 

Records each 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 information equivalent to explain plan.

 

Point

 

SQLSegment

 

Text of SQL statement.

 

Point

 

Wait

 

Records a generic WAIT event. Context is provided in the event strings.

 

Point

 

Parse

 

Event containing SQL query information (actual text of query).

 

Duration

 

Execute

 

Event containing information for execution of SQL query plan.

 

Duration

 

Fetch

 

Event containing actual row retrieval information.

 

Duration

 

LogicalTX

 

Event marking the first time a database update 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 B-2. Items specific to Oracle Server are described in Table B-4.


Note:

These items are platform dependent and may be 0 on some platforms.

 

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

Table B-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 (memory)

 

135

 

NUMBER

 

Cross-Product Items

Oracle Trace provides a set of items called cross-product items. These data items allow programmers to relate events for different products. For example, a 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 transaction.

Cross-product items are reserved for specific products or product types as described in Table B-3. Cross-product item 1 (referred to as CROSS_FAC_1) will only contain data if data is supplied from an instrumented application.

Cross-product 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 that gets matched up in the merging 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-5. You can modify event sets to remove some or all of the CROSS_FAC_x items on the individual server events. If you are not using the items for analysis, you need not specify them.


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 B-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

 

Items Specific to Oracle Server Events

The Oracle Server product definition file defines several items specific to the Oracle Server. The following table describes the Oracle Server-specific items. 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. Table B-4 describes resource utilization items specific to Oracle Server.

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

App_Action

 

Action name set by using the dbms_application_info.set_mo-dule procedure

 

23

 

VARCHAR2(255)

 

App_Module

 

Module name set using the dbms_application_info.set_mo-dule 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 back to Table B-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. 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 B-5.

Table B-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 B-6.

Table B-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 records every time a connection is made to a database. The name of the formatted table is V_192216243_F_5_E_1_7_3. Table B-7 shows the items associated with the Connection event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_2_7_3. Table B-8 shows the items associated with the Disconnect event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_3_7_3. Table B-9 shows the items associated with the ErrorStack event.

Table B-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 formatted table is V_192216243_F_5_E_4_7_3. This event is disabled for Oracle Server release 7.3.2 and higher. Table B-10 shows the items associated with the Migration event.

Table B-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 registers with Oracle Trace where the application is at a certain time. The name of the formatted table is V_192216243_F_5_E_5_7_3. Table B-11 shows the items associated with the ApplReg event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_6_7_3. Table B-12 shows the items associated with the RowSource event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_6_7_3. Table B-13 shows the items associated with the RowSource event specific to Oracle Server Release 8.0.2 and higher.

Table B-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 the text that is equivalent to the information returned by explain plan.

 

SQLSegment Event

The SQLSegment event is a description of a SQL statement. The name of the formatted table is V_192216243_F_5_E_7_7_3. Table B-14 shows the items associated with the SQLSegment event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_13_7_3. Table B-15 shows the items associated with the Wait event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_8_7_3. Table B-16 shows the items associated with the Parse event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_9_7_3. Table B-17 shows the items associated with the Execute event.

Table B-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. The name of the formatted table is V_192216243_F_5_E_10_7_3. Table B-18 shows the items associated with the Fetch event

Table B-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). The name of the formatted table is V_192216243_F_5_E_11_7_3. Table B-19 shows the items associated with the LogicalTX event.

Table B-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.

PhysicalTX Event

The PhysicalTX event logs the start and end of a physical transaction (that is, one in which database status is actually changed). Table B-20 shows the items associated with the PhysicalTX event.

Table B-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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index