Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

trcsess Tips

Oracle Database Tips by Burleson Consulting

September 28, 2015

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:

$ trcsess [options] trace_files

Where the options are:

< output=

< Consolidated output trace file

< session=

< Consolidates the trace information by session id

< clientid=

< Consolidates the trace information by client id

< service=

< Consolidates the trace information by service name

< action=

< Consolidates the trace information by action name

< module=

< Consolidates the trace information by module name

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:

C:\Temp>trcsess output=one_big.trc service=ORDB1 *.trc

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

one_big.trc consolidated trace file

*** [ Windows thread id: 4860 ]

*** 2015-08-03 10:06:16.796

*** 2015-08-03 10:06:16.796

*** 2015-08-03 10:06:16.796

*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_4860.trc ***

*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_3412.trc ***

*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_1072.trc ***

*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_1716.trc ***

*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_2432.trc ***

 

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.

  1. 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.

  2. 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:

 

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

 

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).

 

SQL>@d:\trcanlzr.sql UDUMP asg920xr_ora_13033.trc

 

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:

 

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.

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.

 

trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>
output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' supported.

 

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.

 

..\udump> tkprof hr_report.trc
output=hr_trc_report SORT=(EXEELA, PRSELA,FCHELA)
 


 
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.

 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster