Release 1.4.0
A53696_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Trace User's Guide Release 1.4.0 A53696_01 |
|
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:
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.
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:
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:
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).
Operating-system specific. For Oracle Server release 8.0, NULL is the default.
Valid collection name up to 16 characters long
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. |
Operating-system specific
Full directory pathname
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.
5242880
0 to 4294967295 (0 means unlimited size)
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.
FALSE
TRUE/FALSE
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.
Operating-system specific
Valid product definition file name up to 16 characters long
This parameter specifies the directory pathname where Oracle Trace product definition files are located.
Operating-system specific
Full directory pathname
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:
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:
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.
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 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.
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 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. |
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.
| 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 |
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.
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:
|
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 A-7. The name of the formatter table is V_192216243_F_5_E_1_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-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.
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.
|
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.
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.
|
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.
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.
|
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.
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.
|
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.
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.
|
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 A-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-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. |
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.
|
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.
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.
|
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.
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.
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 A-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 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.
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, 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.
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, 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.
The transaction identifier stored in CROSS_FAC_4 should uniquely identify a specific transaction.
