trcsess Tips

When solving tuning problems, session traces are very useful and offer vital information.  Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.

Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.

The trcsess command-line utility offers the DBA a way to combine or consolidate several trace files into a single trace file based upon the following criteria:

• Session Id

• Client Id

• Service name

• Action name

• Module name

When using dedicated server processes, monitoring only a single session and not doing parallel operations, there is little need for the trcsess utility. But when using shared server processes, monitoring several sessions concurrently, and/or doing parallel DML, the workload can span multiple trace files. In fact, it can be located on different nodes in a RAC environment if the parallel operations cross nodes. The command syntax is as follows:

Where the options are:

In the following example, all the trace files in the temporary directory are consolidated into one big trace file and are doing so just for SELECT statements:

Examining the contents of the resulting one_big.trc file, this shows that the trcsess utility has consolidated five separate trace files into one.

Also note that beginning with Oracle 10g, Oracle Trace functionality is no longer available. For tracing database activity, use SQLTrace or TKPROF instead.

The syntax for the trcsess utility is:

trcsess [output=output_file_name]
[session=session_Id]
[clientid=client_Id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]

where:

• output specifies the file where the output is generated. When this option is not specified, the standard output is used for the output.

• session consolidates the trace information for the session specified. The session Id is a combination of session index and session serial number.

• clientid consolidates the trace information given client Id.

• service consolidates the trace information for the given service name.

• action consolidates the trace information for the given action name.

• module consolidates the trace information for the given module name.

• trace_files is a list of all trace file names, separated by spaces, in which trcsess will look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are checked by trcsess.

Once the trace files have been consolidated, tkprof can be run against the consolidated trace file for reporting purposes.

Utilities for Analyzing Oracle Trace Files

There are several utilities for analyzing Oracle trace files. These include trace assist (trcasst), session tracer (trcsess), trace analyzer (trcanlzr.sql) and tkprof. Many DBAs are very familiar with the Oracle trace facility, but just in case, here are some brief instructions for using this powerful Oracle utility. Before tracing can be enabled, the environment must first be configured by performing the following steps:

1. Enable Timed Statistics: This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
   ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

2. Check the User Dump Destination Directory: The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.

3. Turn Tracing On: The next step in the process is to enable tracing. By default, tracing is disabled due to the burden (5-10%) it places on the database. Tracing can be defined at the session level:

A DBA may enable tracing for another user's session by using the following statement:

The sid (Session ID) and serial# can be obtained from the v$session view. Once tracing with Oracle tkprof is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific.

4. Enable Oracle tkprof tracing only on those sessions that are having problems. Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.

5. When tracing a session, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.

Tkprof does not control the contents of a trace file, it simply formats them. Oracle provides multiple ways to actually generate the trace file. Tkprof is valuable for detailed trace file analysis. For those DBAs that prefer a simpler tracing mechanism with instant feedback, the autotrace utility should be used.

The trace assist (trcasst) utility is used to analyze Oracle trace files generated by most Oracle error messages. This utility will analyze the trace file and put it into a readable format.

Using the Trace Analyzer Utility

Trace Analyzer (trcanlzr) is an application from Oracle with much the same purpose as tkprof.  It is also designed to help analyze the trace files generated by SQL tracing.  Trace Analyzer offers enhancements over tkprof in a number of areas.  Several of the key improvements are as follows:

• Trace Analyzer provides a more detailed list of wait events for every SQL statement that is part of the trace file. Only in recent versions has tkprof provided at least limited wait information.  Older versions provide no information on wait events regardless of the trace data.

• Trace Analyzer reports totals for statements that execute multiple times; whereas tkprof would report each execution separately.  This is important when tracing a process that is updating many records, but doing it one row at a time.  Identifying this with tkprof requires more manual effort.

• Trace Analyzer provides the values used by bind variables, as long as the trace file was generated at a level that includes bind variables; whereas this feature is not available with tkprof.

Installation of Trace Analyzer is fairly straightforward as long as the instructions are followed completely.  It is very similar to installing Statspack. Metalink document 224270.1 provides an adequate explanation for finding the files to accomplish the installation as well as how to install and use it.  Be very careful to follow the instructions exactly.

Executing Trace Analyzer

First, tracing needs to be enabled at the appropriate level. For example, to provide maximum trace data, a Level 12 trace can be started for the current session:

After the session executes for enough time to gain needed data, the trcanlzr.sql script can be executed. It requires the name of the directory object. This object points to the physical operating system directory for the user_dump_dest. The installation of the utility will automatically create the directory object required (named UDUMP).

Once executed, the output will be displayed on the screen and a spool file is created in the current directory.  It is possible to change the output spool file by modifying the trcanlzr.sql script.

Using the trcsess utility

When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.

The trcsess command-line utility consolidates trace information from selected trace files based on specified criteria. The criteria include session id, client id, service name, action name and module name.  This allows the compilation of multiple trace files into a single output file.

The syntax for the trcsess utility is:

Where:

• output specifies the file where the output is generated. When this option is not specified, the standard output is used for the output.

• session consolidates the trace information for the session specified. The session ID is a combination of session index and session serial number.

• clientid consolidates the trace information given client ID.

• service consolidates the trace information for the given service name.

• action consolidates the trace information for the given action name.

• module consolidates the trace information for the given module name.

• trace_files is a list of all trace file names, separated by spaces, in which trcsess will look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are checked by trcsess.

Activating trace on multiple sessions means that trace information is spread throughout many trace files.  For this reason, Oracle 10g introduced the trcsess utility, allowing trace information from multiple trace files to be identified and consolidated into a single trace file.  The trcsess usage is listed below.

Use Oracle's trcsess command-line utility to consolidate the information from all the trace files into a single output file.

Then run tkprof against the consolidated trace file to generate a report. It is recommended that one experiments some with tkprof to become familiarized with the options available in this useful utility.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.