Release 2.0
A67837-01
 Library |
 Product |
 Contents |
 Index |
| Oracle(R) Enterprise Manager Oracle(R) Trace User's Guide Release 2.0 A67837-01 |
|
Though Oracle Trace Manager (graphical user interface) is the primary interface to Oracle Trace, optionally you can perform a manual collection of Oracle Trace data. This can be done by using a command-line interface, using initialization parameters, or executing stored procedures.
Another option for controlling Oracle Trace server collections is the Oracle Trace CLI (Command-line Interface). The CLI is invoked by the OTRCCOL command for the following functions:
The parameter JOB_ID can be any numeric value. 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.
The OTRCCOL START command invokes a collection based upon parameter values contained in the input parameter file. For example:
otrccol start 1234 my_start_input_file
Where my_start_input_file contains the following input parameters:
col_name= my_collection dat_file= <usually same as collection name>.dat cdf_file= <usually same as collection name>.cdf fdf_file= <server event set>.fdf regid= 1 192216243 0 0 5 <database SID> [Use the database SID for an Oracle8 database collection or service name if this is an Oracle7 database collection.]
These parameter file records are all required for database collection and the regid record must contain the values shown including your SID or service name value. The server event sets that can be used as values for the fdf_file are oracle, oraclec, oracled, oraclee, and oraclesm. See "Using Initialization Parameters to Control Oracle Trace" for more information on the server event sets.
The OTRCCOL STOP command halts a running collection as follows:
otrccol stop 1234 my_stop_input_file
Where my_stop_input_file contains the collection name and cdf_file name. You can use the same database input parameter file as used with the START command.
The OTRCCOL FORMAT command formats the binary collection file to Oracle tables. An example of the FORMAT command 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>.cdf full_format= <0/1>
A full_format value of 1 produces a full format; a value of 0 produces a partial format. (You may find it easier to use the Oracle Trace formatter image, otrcfmt, rather than the CLI format.) See "Formatting Oracle Trace Data to Oracle Tables" for information on formatting part or all of an Oracle Trace collection and for other important information on creating the Oracle Trace formatting tables prior to running the format command.
The OTRCCOL DCF command deletes collection files for a specific collection. The OTRCCOL DFD command deletes formatted data from the Oracle Trace formatter tables for a specific collection.
There are six Oracle Trace database initialization parameters that are set up by default to control Oracle Trace. By logging in to the internal (or other privileged) 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 (or uncomment) the ORACLE_TRACE_ENABLE=TRUE parameter to your .ORA file. You also need to provide a non-null value to the ORACLE_TRACE_COLLECTION_NAME parameter (which is null for Oracle8). These parameters are in addition to default parameter values which are provided.
Note that the ORACLE_TRACE_ENABLE parameter is set to FALSE by default. A value of FALSE disables any use of Oracle Trace for the Oracle8 server. (For Oracle7, the parameter only needs to be TRUE if you are using INIT.ORA to start an Oracle Trace database collection.)
To enable Oracle Trace collections for the server, the parameter is set to TRUE. Having the parameter set to TRUE does not start an Oracle Trace collection, but allows Oracle Trace to be used for that server. Oracle Trace can then be started in one of the following ways:
The default value for this parameter is NULL. A collection name can be up to 16 characters in length. You must then shut down your database and start it up again to make the parameters take effect. If a collection name is specified, when you start the server you automatically start an Oracle Trace collection for all database sessions.
To stop the collection that was started using the ORACLE_TRACE_COLLECTION_NAME parameter, shut down the server instance and reset the ORACLE_TRACE_COLLECTION_NAME to NULL. The collection name specified in this value is also used in two collection output file names: the collection definition file (collection_name.cdf) and the binary data file (collection_name.dat).
The ORACLE_TRACE_FACILITY_NAME determines the event set that Oracle Trace collects.
Possible values for the ORACLE_TRACE_FACILITY_NAME parameter are:
You must then shut down your database and start it up again before the parameters take effect. If you chose the ALL event set oracle, you may want to set TIMED_STATISTICS=TRUE in your INIT.ORA file to enable collection of wait times.
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:
You can invoke an Oracle Trace collection for your own session or for another session by using the Oracle Trace stored procedure packages. This method only works for database sessions that are currently active.
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 the following ways:
Your Oracle Trace server collection can be formatted to Oracle tables for more flexible access by any SQL reporting tool. This section describes the command-line interface for formatting.
Oracle Trace produces a separate table for each event collected. For example, a "parse" event table is created to store data for all parse events that occur during a server collection. If the version of Oracle Trace is prior to 7.3.4 or 8.0.4, then before you can format data, you must first set up the Oracle Trace formatter tables by executing the OTRCFMTC.SQL script on the server host machine while logged into the database as the user who will own the formatted data.
Use the following command to format an Oracle Trace collection:
OTRCFMT [-f] [-c#] collection_name.cdf [user/password@database]
If user/password@database is omitted, you will be prompted for this information.
The optional formatting parameters are defined as follows:
Formats the entire data file, regardless of whether or not portions of the file have been formatted at a previous time. This is useful if you have data that was previously formatted to another database and you want to format it to a new database.
If you omit the -f parameter from the command line, only data that has not previously been formatted is processed. This is useful when you want to format data for a collection while that collection is still in progress. You can later format only new data for the collection by omitting the -f parameter from the command line.
Specifies the commit interval (the number of inserts into the database performed before a commit is made). If you omit the -c parameter, a system default is used.
Specifies the user name, password, and database. If you do not enter this information on the command line, you are prompted for it. Descriptions of these items follow.
Enter the Oracle user name under which the Oracle Trace formatter tables were created, or enter the owner of a schema containing formatter tables.
Enter the password for the user name.
Enter a service name for the database. Do not enter a system identifier (SID). If you enter nothing, Oracle Trace will use the default database.
The otrcfmt image for Oracle Server releases 7.3.4 and 8.0.4 adds two new columns to the event data tables: elapsed_secs and elapsed_nano_secs. These columns are added to all duration events.
Because of the new columns, there is a compatibility issue using older otrcfmt images to format to the same database user.
The 7.3.4 and 8.0.4 otrcfmt image can format data into a database that contains previously formatted data from any prior version of Oracle Trace. However, when a table has been updated by the 7.3.4 and 8.0.4 otrcfmt image, it can no longer be formatted by an 8.0.3, 7.3.3, or prior otrcfmt image.
The otrcfmt image executes on the collection target database. The version of the image depends on the collection target database, not the format target database. If you are collecting data for databases with different versions, you will be using multiple versions of the otrcfmt image.
If you plan to have multiple versions of the otrcfmt image directing output to the same database, use separate database user accounts to differentiate the formatted data. For example, you could create user_803 and user_804 to keep the data separate.
If you do have this conflict but you do not need to keep the formatted data, delete the old formatted data tables using otrcfmtd.sql and then re-create the format tables using the otrcfmtc.sql script located in the otrace/admin directory. If subsequent formatting will be done using the 7.3.4, 8.0.4 or later otrcfmt image, the image will create the format tables for you and you do not need to execute the otrcfmtc.sql script.
The Oracle Trace statistics reporting utility displays statistics for all items associated with each occurrence of a server event. These reports can be quite large. You can control the report output by using command parameters. Use the following command and optional parameters to produce a report:
otrcrep [optional parameters] collection_name.CDF
The first step you may want to take is to generate a report called "collection_PROCESS.txt." You can produce this report first to give you a listing of specific process identifiers for which you want to run other reports.
You can manipulate the output of the Oracle Trace reporting utility by using the following optional report parameters:
| output_path | Specifies a full output path for the report files. If not specified, the files will be placed in the current directory. |
| -p [<pid>] | Organizes event data by process. If you specify a process ID (pid), you will have one file with all the events generated by that process in chronological order. If you omit the process ID, you will have one file for each process that participated in the collection. The output files are named collection_Ppid.txt. |
| -P | Produces a report called collection_PROCESS.txt that lists all processes that participated in the collection. It does not include event data. You could produce this report first to determine the specific processes for which you might want to produce more detailed reports. |
| -w# | Sets report width, such as -w132. The default is 80 characters. |
| -l# | Sets the number of report lines per page. The default is 63 lines per page. |
| -h | Suppresses all event and item report headers, producing a shorter report. |
| -s | Used with Net8 data only (or SQL*Net for Oracle7). |
| -a | Creates a report containing all the events for all products, in the order they occur in the data collection (.dat) file. |
