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.

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:

ALTER SESSION SET SQL_TRACE = TRUE; 

DBMS_SESSION.SET_SQL_TRACE(TRUE);

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

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid,serial#,true);

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.