Oracle(R) Enterprise Manager Oracle(R) Trace User's Guide Release 2.0 A67837-01 |
|
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 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.
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 B-2.
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.
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 B-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 Net8. Net8 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 Net8 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 6.
The Oracle Server product definition file 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 B-4.
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 |
Operation1 |
Text of the operation |
47 |
VARCHAR2(255) |
Operation_ID1 |
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_ID1 |
Parent operation |
44 |
NUMBER |
PGA_Memory |
Process Global Area memory |
101 |
NUMBER |
Physical Reads |
Number of blocks read from disk |
105 |
NUMBER |
Position1 |
Position within events having same parent operation |
45 |
NUMBER |
Position_ID2 |
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 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 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, 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 B-5.
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:
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 B-7. 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. Net8 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 B-8. 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 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.
The ErrorStack event identifies the process that has the error. Items associated with the ErrorStack event are shown in Table B-9. 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 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 B-10.
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 B-11. 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 B-12. 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 B-13. 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 B-14. 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 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.
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 B-15. 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-6 |
|
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 B-16. 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 B-17. 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 B-18. 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 B-19. 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 B-20. 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.