Oracle Enterprise Manager Oracle Trace Developer's Guide
Release 1.4.0
A53697_01

Library

Product

Contents

Index

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

SQL statements and statistics on the frequency, duration, and resources used for all parse, execution, and fetch events
logical and physical database transactions, including the frequency, duration, and resources used by each transaction event
a set of statistics associated with each of these duration events, including: UGA and PGA memory, block changes, block gets, consistent gets, physical reads, redo entries, redo size, sorts in memory and disk
resource usage for each database event measured in CPU time, file I/Os and page faults

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
cross-product items
product-specific 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_ID¹	Object ID of the row source	46	NUMBER
Operation²	Text of the operation	47	VARCHAR2(255)
Operation_ID³	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_ID⁴	Parent operation	44	NUMBER
PGA_Memory	Process Global Area memory	101	NUMBER
Physical Reads	Number of blocks read from disk	105	NUMBER
Position⁵	Position within events having same parent operation	45	NUMBER
Position_ID⁶	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

¹ Item specific to Oracle Server release 8.0.2 and higher
² Item specific to Oracle Server release 8.0.2 and higher
³ Item specific to Oracle Server release 8.0.2 and higher
⁴ Item specific to Oracle Server release 8.0.2 and higher
⁵ Item specific to Oracle Server release 8.0.2 and higher
⁶ 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.

Library

Product

Contents

Index

B Using Oracle Trace for Oracle Server Data Collections

Oracle Server Events

Table B-1 Oracle Server Events

Data Items Collected for Events

Resource Utilization Items

Table B-2 Standard Resource Utilization Items

Cross-Product Items

Table B-3 Cross-Product Items

Items Specific to Oracle Server Events

Table B-4 Oracle Server Items

Items Associated with Each Event

Table B-5 Additional Columns Included by Oracle Trace Formatter

Event Statistics Block

Table B-6 Event Statistics Block

Connection Event

Table B-7 Items Associated with the Connection Event

Disconnect Event

Table B-8 Items Associated with the Disconnect Event

ErrorStack Event

Table B-9 Items Associated with the ErrorStack Event

Migration Event

Table B-10 Items Associated with the Migration Event

ApplReg Event

Table B-11 Items Associated with the ApplReg Event

RowSource Event

Table B-12 Items Associated with the RowSource Event

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

Table B-13 Items Associated with the RowSource Event

SQLSegment Event

Table B-14 Items Associated with the SQLSegment Event

Wait Event

Table B-15 Items Associated with the Wait Event

Parse Event

Table B-16 Items Associated with the Parse Event

Execute Event

Table B-17 Items Associated with the Execute Event

Fetch Event

Table B-18 Items Associated with the Fetch Event

LogicalTX Event

Table B-19 Items Associated with the LogicalTX Event

PhysicalTX Event

Table B-20 Items Associated with the PhysicalTX Event

B
Using Oracle Trace for Oracle Server Data Collections