Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

Using the Trace Analyzer Utility in Oracle
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Andrew Kerber, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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)
 


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.