Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-01 |
|
This chapter describes how to use Oracle Trace to collect Oracle server event data.
The topics in this chapter include:
Note: Oracle Trace will be deprecated in a future release. Oracle Corporation strongly advises the use of SQL Trace and TKPROF instead. |
Oracle Trace is a general-purpose event-driven data collection product, which the Oracle server uses to collect performance and resource utilization data, such as SQL parse, execute, and fetch statistics, and wait statistics.
An event is the occurrence of some activity within a product. Oracle Trace collects data for predefined events occurring within a software product instrumented with the Oracle Trace API. That is, the product has embedded calls to the Oracle Trace API. An example of an event is a parse or fetch.
There are two types of events:
Point events represent an instantaneous occurrence of something in the product. An example of a point event is an error occurrence.
Duration events have a beginning and an ending. An example of a duration event is a transaction. Duration events can have other events occur within them; for example, an error can occur within a transaction.
The Oracle server has more than a dozen events. The following are three of these events:
Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.
Each event set is described by its own product definition file (.fdf
). The product definition file is a set of events and their associated data items. The complete set of events defined for an instrumented product is referred to as the ALL event set. Other event sets are then derived from the ALL set. For example, the Oracle Server includes an event set known as the EXPERT set. This set includes SQL event data used by the Oracle Expert tuning application, but excludes other events, such as wait events.
During a collection, Oracle Trace stores event data in memory and periodically writes it to a collection binary file. This method ensures low resource overhead associated with the collection process. You can access event data collected in the binary file by formatting the data to database tables, which makes the data available for fast, flexible access. These database tables are called Oracle Trace formatter tables.
You can collect Oracle Trace data using one of the following mechanisms:
ORACLE_TRACE_
* initialization parametersYou can control Oracle Trace server collections with the Oracle Trace CLI (command-line interface). The CLI is invoked by the OTRCCOL
executable for the following functions:
OTRCCOL
START
job_id input_parameter_fileOTRCCOL
STOP
job_id input_parameter_fileOTRCCOL CHECK
collection_nameOTRCCOL
FORMAT
input_parameter_fileOTRCCOL
DCF
col_name cdf_file
OTRCCOL
DFD
col_name username password service [col_id]The job_id
parameter should be set to a value of 1.
The input parameter file contains specific parameter values required for each function, as shown in the following examples. Col_name
(collection name) and cdf_file
(collection definition file) are initially defined in the START
function input parameter file.
Note: The server parameter |
The OTRCCOL
START
function invokes a collection based on parameter values contained in the input parameter file. For example:
OTRCCOL START 1 my_start_input_file
where file my_start_input_file
contains at least the following input parameters:
col_name= <collection name>cdf_file
= <collection name>.cdfdat_file
= <collection name>.datfdf_file
= <facility definition file>.fdf
The server event sets that can be used as values for the fdf_file
parameter are ORACLE
, ORACLEC
, ORACLED
, ORACLEE
, and ORACLESM
, plus CONNECT
, SQL_ONLY
, SQL_PLAN
, SQL_TXN
, SQLSTATS
, SQLWAITS
, and WAITS
.
See Also:
Table 12-2 for a description of the server event sets |
Collection .cdf
and .dat
files are created in the directory $ORACLE_HOME/otrace/admin/cdf
by default for collections started using the CLI (or PL/SQL procedures), unless overridden by EPC_COLLECTION_PATH
environment variable.
Note: This chapter refers to file path names on UNIX-based systems. For the exact path on other operating systems, see your Oracle platform-specific documentation. A complete discussion of these parameters is provided in Oracle9i Database Reference. |
The <collections name> parameter can be any valid unique filename.
For Oracle database collections, one additional parameter is required:
regid
= 1 192216243 0 0 5 <database SID>
Note: Older CLI versions required this syntax exactly, with no whitespace before '=' but at least some whitespace after '='. This is no longer true: CLI is not whitespace sensitive. |
The regid
parameter record identifies a database by SID where Oracle Trace collection is to be performed. The six elements making up the regid
parameter record are as follows, in this order:
cf_num
cf_val
The cf_num
and cf_val
elements should set to zero in this basic Oracle database collection regid
.
There are several ways of limiting the amount of data collected. For example, additional regid
records can be specified to reduce the amount of collected data, and nonzero cf_num
and cf_val
can be specified in those situations. In the Oracle server, Oracle Trace cross facility item 6 (cf_num
= 6) is reserved to record database userID values.
In the Oracle server, Oracle Trace cross facility item 6 (cf_num
= 6) is reserved to record database userID values. So, for example, if you provide an additional regid
record with cf_num
= 6 and cf_val
= <some DB userID>, then the collection of database event data is limited to only those events performed by that database user.
If you are interested only in collecting database activity for users 23 and 45, then you would provide the following 3 regid
records:
regid
= 1 192216243 0 0 5 ORCLregid
= 1 192216243 6 23 5 ORCLregid
= 1 192216243 6 45 5 ORCL
The input parameter file used by the CLI when starting a collection can also contain the following optional parameters, for both database and nondatabase Oracle Trace collections:
prores= <process restriction> max_cdf= <maximum collection file size>
If no process restriction records are specified, then there are no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process of interest.
The max_cdf
parameter is often useful, in several different modes of use. This parameter specifies the maximum amount of Oracle Trace data that should be collected, in bytes (in other words, size of the <collection>.dat
file).
A zero value indicates that no limit should be imposed; otherwise, a positive value up to 2 GB can be specified to stop the data collection when that size limit is reached. In addition, a negative value can be specified (but not less than -2 GB), which instructs Oracle Trace to collect data in its "circular data file" mode: when <collection>.dat
reaches magnitude(max_cdf
), then save that data (and delete any previously saved dat file), and then start collecting to a new <collection>.dat
file. This limits the total amount of disk space used, but allows Oracle Trace data collection to continue until you manually stop collection.
Verify that the collection was started.
otrccol check <collection_name>
The collection should show as active, not active, or not found.
The OTRCCOL
STOP
function halts a running collection as follows:
OTRCCOL STOP 1 my_stop_input_file
where my_stop_input_file
contains the collection name and cdf_file
name.
The OTRCCOL
FORMAT
function formats the binary collection file to Oracle tables. An example of the FORMAT
statement is:
OTRCCOL FORMAT my_format_input_file
where my_format_input_file
contains the following input parameters:
username
= <database username>password
= <database password>service
= <database service name>cdf_file
= <usually same as collection name>.cdffull_format
= <0/1>
A full_format
value of 1 produces a full format. A full_format
value of 0 produces a partial format, which only formats new data; in other words, data collected since any previous format.
See Also:
"Formatting Oracle Trace Data to Oracle Tables" for more information on formatting an Oracle Trace collection using the |
The OTRCCOL
DCF
(delete collection files) function deletes collection .cdf
and .dat
files for a specific collection. The OTRCCOL
DFD
(delete formatted data) function deletes formatted data from the Oracle Trace formatter tables for a specific collection. You can specify an optional col_id
parameter for a selective DFD, where more than one col_id
has been created for a collection by multiple (full) formats.
Six Oracle database initialization parameters are set up by default to control Oracle Trace. By logging into a privileged account in the database and executing the SHOW
PARAMETER
ORACLE_TRACE
statement, you see the following parameters:
You can modify these Oracle Trace server parameters to allow Oracle Trace collection of server event data and use them by adding them to the initialization file.
However, this method for controlling the Oracle Trace collection is rather inflexible: the collection name cannot be changed without performing a database shutdown. (For Oracle releases prior to 8.1.7, the collection can only be stopped by doing a shutdown, then setting ORACLE_TRACE_ENABLE
= false
before restarting.) However, with ORACLE_TRACE_ENABLE
= true
but ORACLE_TRACE_COLLECTION_NAME
= "" [that is, empty name string], Oracle Trace collections of database event data can be performed using one of the other collection control mechanisms; for example, the Oracle Trace CLI. These other mechanisms are more flexible than the database initialization parameters. In general, they are preferred over using parameters for collection control.
The ORACLE_TRACE_ENABLE
database initialization parameter is false
by default. This disables any collection of Oracle Trace data for that server, regardless of the mechanism used to control the collection.
Setting ORACLE_TRACE_ENABLE
to true
in <DBinit>.ora
enables Oracle Trace collections for the server, but it does not necessarily start a collection when the database instance is started. If the database parameters alone are to be used to start an Oracle Trace collection of database event data, then all 6 ORACLE_TRACE_
* parameters must be specified, or have nonnull values by default. Typically, this means that both ORACLE_TRACE_ENABLE
must be set to true
and a nonnull ORACLE_TRACE_COLLECTION_NAME
must be provided (up to 16 characters in length).
ORACLE_TRACE_ENABLE
is now a dynamic parameter (as of Oracle8i, Release 3), so it can be set to true
or false
while the database is running. This can be done for the current database session or for all sessions (including future ones), using ALTER
SESSION
or ALTER
SYSTEM
statements. When the database is subsequently shut down and then restarted, the <DBinit>.ora
setting for ORACLE_TRACE_ENABLE
is again used to initially enable or disable Oracle Trace collection of database event data.
The ORACLE_TRACE_FACILITY_NAME
database initialization parameter specifies the event set that Oracle Trace collects, if the database parameters are used to control data collection. The default for this parameter is ORACLED
(in other words, Oracle "default" event set).
Note: The |
With database parameters set to start an Oracle Trace Collection, if the database does not begin collecting data, then check the following:
ORACLE_TRACE_FACILITY_NAME
, with an .fdf
extension, should be in the directory specified by the ORACLE_TRACE_FACILITY_PATH
initialization parameter. The exact directory that this parameter specifies is platform-specific.COLLECT
.DAT
, FACILITY
.DAT
(or PROCESS
.DAT
for Oracle 7.3), and REGID
.DAT
. If they do not, then run the OTRCCREF
executable to create or re-create them.EPC_ERROR
.LOG
file to see more information about why a collection failed. Oracle Trace creates the EPC_ERROR
.LOG
file in the current default directory if the Oracle Trace Collection Services OTRCCOL
image must log an error.trc
files in the directory specified by the server USER_DUMP_DEST
initialization parameter. Searching for "epc" in the *.trc
files might give errors. These errors and their descriptions may be found in $ORACLE_HOME/otrace/mesg/epcus.msg
(assuming US installation), depending on availability for a given platform.Oracle provides an additional Oracle Trace library that allows control of both database and nondatabase Oracle Trace collections from PL/SQL.
Both the name and the location of this new library are platform-dependent. On Unix platforms, the library is $ORACLE_HOME/lib/libtracepls9.so.
On Win32 platforms (for example, Windows NT), the library is %ORACLE_HOME%\bin\oratracepls9.dll.
The otrace/admin directory contains two new SQL scripts that can be used to define a database LIBRARY
object for this library, and to define the procedures that can be used to call out to the library from PL/SQL:
In addition, the otrace/demo directory contains several SQL scripts showing PL/SQL examples that start, stop, and then format an Oracle Trace collection. These are respectively:
In the "start collection" example script OTRCPLSSC
.SQL
, the regid_list
contains only a single element: "1 192216243 0 0 5 ORCL". The inner double quotes are required to form a single regid
string from its six components. These components are the following, in the order shown:
cf_num
cf_val
For an Oracle Trace database collection, a regid
string like this example is required, basically to identify the database SID and to specify that you are collecting for an Oracle server. The cf_num
and cf_val
should be zero in this basic regid
record.
Additional regid
records can be specified in order to reduce the amount of collected data. This is when the cf_num
and cf_val
items are used. In the Oracle server, Oracle Trace cross facility item 6 (cf_num
= 6) is reserved to record database userID values. So, if you provide an additional regid
record with cf_num
= 6 and a cf_val
= <some DB userID>, then the collection of database event data is limited to only those events performed by that database user. For example, if you are only interested in collecting database activity for users 23 and 45, then the regid_list
consists of three records:
regid_list VARCHAR2(256) := '"1 192216243 0 0 5 ORCL", "1 192216243 6 23 5 ORCL", "1 192216243 6 45 5 ORCL"';
Similarly, the fdf_list
argument could specify the name of a single .fdf
file (facility definition file). Typically, this is the case. However, more than one .fdf
could be specified in fdf_list
if multiple facilities are involved in the collection. Of course, only one .fdf
can be specified for any given facility; for example, the database.
On the other hand, the process restriction list prores_list
can be empty. This indicates that there are to be no restrictions on which processes can take part in the collection. If process restrictions are used, then one or more process ID (PID) values can be specified, as well as the operating system username for the owner of each process.
Other arguments in the "start collection" example in OTRCPLSSC
.SQL
are single numeric or string values, as shown. For example, the collection name and maximum collection data file size specified by the col_name
and maxsize
variables, respectively.
Running an Oracle Trace collection produces the following collection files:
<
collection name>.cdf
is the binary Oracle Trace collection definition file for the collection. It describes what is to be collected.<
collection name>.dat
is the output file containing the collected Oracle Trace event data in binary form. When newly created, the empty .dat
file contains only 35 bytes of file header information.You can access the Oracle Trace data in the collection files in the following ways:
You can format Oracle Trace binary collection data to Oracle database tables, and you can then access this formatted data using SQL or other tools. The Oracle Trace format produces a separate table for each event type collected; for example, a parse event table is created to store data for all database parse events that were recorded during a server collection.
Note: For Oracle server releases 7.3.4 and later, the Oracle Trace formatter automatically creates the formatter tables as needed. |
Use the following syntax to format an Oracle Trace collection with the OTRCFMT
formatter utility:
OTRCFMT [options] <collection_name>.cdf [user/password@database]
If collection .cdf
and .dat
are not located in the current default directory, then specify the full file path for the .cdf
file.
If you omit user/password@database
(or any part of it, such as password or database), then OTRCFMT
prompts you for this information.
Oracle Trace allows data to be formatted while a collection is occurring. By default, Oracle Trace formats only the portion of the collection that has not been formatted previously. If you want to reformat the entire collection file, then use the optional parameter -f (which generate a new collection ID in the formatter tables)
.
Oracle Trace provides several sample SQL scripts that you can use to access the formatted server event data tables. These are located in OTRCRPT
*.SQL
in the otrace directory tree.
Note: Because there are incompatibilities between at least some versions of the formatter tables, use a separate database schema for each version of the Oracle Trace formatter. |
The Oracle Trace reporting utility displays data for items associated with each occurrence of a server event. These reports can be quite large. You can control the report output by using optional statement qualifiers. Use the following report utility statement syntax:
OTRCREP [options] <collection name>.cdf
If collection .cdf
and .dat
are not located in the current default directory, then specify the full file path for the .cdf
file.
First, you might want to run a report called PROCESS
.txt
. You can produce this report to provide a listing of specific process identifiers for which you want to run another report.
You can manipulate the output of the Oracle Trace reporting utility by using the following optional report qualifiers:
Default OTRCREP
report output, with no optional qualifiers specified, consists of one text file for each event type collected. Data from all participating processes are combined in each of these text files.
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 12-2 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 12-3.
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. For example, currently only CPU times are recorded on Windows NT.
Oracle Trace provides a set of 14 items called cross-product items (also known as cross-facility items for historical reasons). 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 12-4. If you do not use the products for which items are reserved, then you can use those items for your own purposes.
Note: In this version of Oracle Trace, the term "facility" has been changed to "product". Therefore, the items named |
Cross-product item 1 (referred to as CROSS_FAC_1
) contains 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 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 Oracle Net. Oracle Net supplies the connection ID to Oracle Trace through CROSS_FAC_3
. CROSS_FAC_3
is the key element in coordinating client/server or multitier Oracle Trace collections. Oracle Trace uses the Oracle Net global connection ID as the common element to match in the merger, for example the client and server collection files. The global connection ID is the same for the client and the server connection.
Most Oracle Server events record cross-product items 1 through 6. (Cache I/O does not.)
The Oracle Server product (or facility) definition files (that is, *.fdf
) 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 12-5.
Item Name | Description | Item Number | Formatted Datatype |
---|---|---|---|
|
Action name set by using the |
23 |
|
|
Module name set using the |
22 |
|
|
Indicates if a transaction committed or aborted |
24 |
|
|
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 |
|
|
CPU session |
112 |
|
|
Current user ID |
36 |
|
|
Number of cursor associated with SQL statement |
25 |
|
|
Number of blocks changed |
102 |
|
|
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 |
|
|
Value used by Oracle Trace internally |
14 |
|
|
Recursive level at which SQL statement is processed |
32 |
|
|
Depends upon event in which it occurs (for example, wait event description) |
43 |
|
|
Elapsed time for the session |
113 |
|
|
Flag set if data retrieved is last data from query |
38 |
|
|
Address of SQL statement in library cache |
27 |
|
|
Internal ID within the Oracle database that identifies the user ID for the session |
15 |
|
|
Internal ID within the Oracle database that identifies the system account name for the session |
16 |
|
|
Flag set if SQL statement was missing in library cache |
33 |
|
|
Object ID of the row source |
46 |
|
|
Text of the operation |
47 |
|
|
Position of the operation within the execution plan for a statement |
28 |
|
|
Oracle optimizer mode |
35 |
|
|
Oracle command number |
34 |
|
|
Oracle process ID |
11 |
|
|
Operating system image (program name) |
42 |
|
|
Operating system host machine |
20 |
|
|
Operating system terminal |
19 |
|
|
Operating system username |
18 |
|
|
The definition of P1 depends upon the event in which it occurs. |
1 |
|
|
The definition of P2 depends upon the event in which it occurs. |
2 |
|
|
The definition of P3 depends upon the event in which it occurs. |
3 |
|
|
The definition of P4 depends upon the event in which it occurs. |
4 |
|
|
The definition of P5 depends upon the event in which it occurs. |
5 |
|
|
The definition of P6 depends upon the event in which it occurs. |
6 |
|
|
The definition of P7 depends upon the event in which it occurs. |
7 |
|
|
The definition of P8 depends upon the event in which it occurs. |
8 |
|
|
The definition of P9 depends upon the event in which it occurs. |
9 |
|
|
The definition of P10 depends upon the event in which it occurs. |
10 |
|
|
Parent operation |
44 |
|
|
Process Global Area memory |
101 |
|
|
Number of blocks read from disk |
105 |
|
|
Position within events having same parent operation |
45 |
|
|
Position of the operation within the execution plan for a statement |
28 |
|
|
Number of redo entries made by process |
106 |
|
|
Size of redo entries |
107 |
|
|
Number of rows processed |
29 |
|
|
Schema user ID |
37 |
|
|
Oracle session ID |
12 |
|
|
Session serial number |
13 |
|
|
Text version of session ID |
17 |
|
|
Number of disk sorts performed |
110 |
|
|
Number of memory sorts performed |
109 |
|
|
Total number of rows sorted |
111 |
|
|
Text of SQL statement |
31 |
|
|
Pointer to SQL statement |
26 |
|
|
Address of SQL text |
30 |
|
|
Rows processed during full table scans |
108 |
|
|
Unique identifier for a transaction that consists of rollback segment number, slot number, and wrap number |
41 |
|
|
The address of the transaction state object |
40 |
|
|
Type of the transaction. Value is a bitmap (for example, 2 active transaction, 0X10 space transaction, 0X20 recursive transaction). |
39 |
|
|
User Global Area session memory |
100 |
|
|
Elapsed time, in hundredths of seconds, for the wait event |
21 |
|
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 12-5.
When you format data, Oracle Trace creates a table for each event type 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 12-6.
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 the following list:
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 the following list:
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. Oracle 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 the following list.
Session_Index
Session_Serial
Event Statistics BlockOracle_PID
Cross-Product Items 1-6
A Disconnect event corresponds 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 the following list.
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. This event is currently disabled in the Oracle server code.
Items associated with the Migration event are shown in the following list:
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 the following list:
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 the following list:
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 the following list:
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.
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 the following list:
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 is always 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
usually should identify a particular SQL statement for a session. If you add Cursor_Number
, you 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 the following list:
Session_Index
Session_Serial
P1 P2 P3 Description Cross-Product Items 1-7
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 the following lists:
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 the following lists:
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 the following lists:
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 the following lists:
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 the following lists:
The transaction identifier stored in CROSS_FAC_4
should uniquely identify a specific transaction.
Oracle Trace events can be organized into event sets that restrict the data collection to specific events. You can establish event sets for performance monitoring, auditing, diagnostics, or any logical event grouping.
Use the following sections to troubleshoot problems while using Oracle Trace.
If you suspect an Oracle Trace configuration problem:
EPC_ERROR
.LOG
file for details of any logged Oracle Trace errors.$ORACLE_HOME/otrace/admin
(*.dat
files) and run otrccref
to re-create the Oracle Trace *.dat
files if the files do not exist.fdf
files are in the $ORACLE_HOME/otrace/admin/fdf
directory.% $ORACLE_HOME/bin/otrccol version
If the Returned Value Is: | Then the Command-Line Interface Release Is: |
---|---|
1 |
733 |
2 |
803 |
3 |
734 |
4 |
804 |
5 |
805 |
6 |
813 |
7 |
814 |
8 |
815 |
9 |
806 |
10 |
816 |
11 |
817 |
12 |
901 |
% $ORACLE_HOME/bin/otrccol check <collection_name>
To test the CLI:
To check settings on UNIX:
printenv ORACLE_HOME printenv ORACLE_SID
To set settings on UNIX:
setenv ORACLE_HOME <path> setenv ORACLE_SID <sid>
There should be one CLI for each ORACLE_HOME
. For example, if you have two Oracle Server release 7.3.3 instances sharing the same ORACLE_HOME
, there should be only one CLI.
Look for <collection name>.cdf
and .dat
files in:
If you suspect a server environment problem, verify the following:
To solve the immediate problem, stop the collection, and free up space so Oracle Trace can end the collection.
Initially limiting the collection to specific users or wait events also helps to limit the amount of data collected. Limiting users and wait events is available for Oracle Server releases 8.0.4 and higher.
Wait times are collected only if the INIT<
sid>
.ORA
parameter, TIMED_STATISTICS
, is set to true
.
See Also:
"Setting the Level of Statistics Collection" for information about |
If an expected SQL statement does not appear to be in your collection, it may be because a small amount of data in the Oracle Trace data collection buffers may not have been flushed out to the collection data file, even though the collection has been stopped. Additional database activity should flush these buffers to disk, and shutting down the database also forces a flush of these buffers.
There may be times when a collection is too large. Starting with Oracle Server release 8.0.4, you can collect data for specific users and specific wait event types to minimize the size of the collection. Because, almost always, the server is waiting for a latch, lock, or resource, wait event data for a brief collection can be quite extensive.
In Oracle8 databases (prior to Oracle 8.1.7), the ORACLE_TRACE_ENABLE
parameter in the INIT
sid.ORA
file on the server must be set to true
before the database is started. Starting with Oracle 8.1.7 it is dynamic and may be modified through ALTER_SESSION
or ALTER_SYSTEM
. (For Oracle7 the ORACLE_TRACE_ENABLE
parameter should be left as false
, unless you are using the init.ora parameter method to start or stop collections.) You can also see this problem if there are too many collections running concurrently.
On Windows NT systems, if you are running Oracle Trace collections and an error occurs indicating Oracle Trace could not access memory, the collect
.dat
file has become full. You must create a new .dat
file by running the otrccref
.exe
image located in the $ORACLE_HOME/bin
directory. However, database services must be shutdown to release the collect
.dat
file for the otrccref
script to be able to create the new collect
.dat
file. You can also increase the number of collect
.dat
records to more than the default of 36 records (for example, otrccref
-c50
to create a new otrace/admin/collect.dat
file containing 50 records).
If the attempt to collect Oracle Trace data for an Oracle7 database results in the message "Error starting/stopping Oracle7 database collection," this may be due to missing database stored procedures that Oracle Trace uses to start and stop Oracle7 collections.
To check for stored procedures using the Oracle Enterprise Manager console, use the Navigator and the following path:
Networks=>Databases=><your database>=>Schema Objects=>Packages=>SYS
DBMS_ORACLE_TRACE_xxx
.
To check for stored procedures using Oracle Server Manager or Oracle SQL*Plus Worksheet:
select object_name from dba_objects where object_name like '%TRACE%' and object_type = 'PACKAGE'; OBJECT_NAME DBMS_ORACLE_TRACE_AGENT DBMS_ORACLE_TRACE_USER 2 rows selected.
For Oracle7, Oracle Trace required that these stored procedures be installed on the database. These SQL scripts may be automatically run during database installation depending on the platform-specific installation procedures. If they are not executed during database installation, you must run these scripts manually. You can add these stored procedures to the database by running the otrcsvr.sql script from $ORACLE_HOME/otrace/admin
) from a privileged database account (SYS
or INTERNAL
). To run the script, set the default to the path were the script is located. This script runs other scripts that do not have the path specified. These other scripts fail if you are not in the directory where these scripts will run.
The EPC_ERROR
.LOG
file provides information about the collection processing, specifically the Oracle Trace Collection Services errors.
The EPC_ERROR
.LOG
file is created in the current default directory.
See Also:
Oracle Enterprise Manager Messages Manual for general information about causes and actions for most Oracle Trace messages |
Oracle Server releases 7.3.4 and 8.0.4 and later automatically create the formatter tables. Prior to Oracle Server releases 7.3.4 and 8.0.4, you must run the otrcfmtc
.sql
script from Oracle Server Manager or Oracle SQL*Plus Worksheet as the user who will be formatting the data. If you must manually execute otrcfmtc
.sql
to create the formatter tables, use the SQL script from the same Oracle home as your collections to be formatted.
The otrcfmtc
.sql
script is located in the $ORACLE_HOME/otrace/admin
directory.
Formatting error might be due to one of the following causes:
Look for EPC_COLLECTION
.
To check for formatter tables using SQL Worksheet:
CONNECT <username>/<password>@<service name> DESCRIBE epc_collection
|
![]() Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|