Skip to content

Performance Tuning Basics 8 : Trace File Analyzer (TRCA)

Just like TKPROF, Trace Analyzer (TRCA) tool can help you analyze one or several SQL trace(s) generated by Event 10046 to produce an output diagnostics report which can be used to diagnose a poorly performing SQ statement. TRCA can produce output  in two formats (html and text).

TRCA identifies expensive SQL, generates their explain plan and collects some other useful information like Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the query performance.

This post is part 4 of the below posts on trace and tkprof. Please refer other posts to read related information.

Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool


STEPS TO USE TRCA

Unlike TKPROF which comes bundled with database, you need to install TRCA separately. Below are the basic steps to get it installed and generate reports.

1. DOWNLOAD THE SOFTWARE

Go to below Oracle Note ID to download the software

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)

 

2. INSTALL THE SOFTWARE

NOTE: For best output results TRCA must be installed and used in the same system and database where the trace is generated. Note that TRCA is capable of analyzing a trace using a different system.

2.1) Unzip trca.zip in server

$ unzip trca.zip

This will create directory named trca

2.2) Connect as SYSDBA

$ cd ./trca/install
$ sqlplus / as sysdba

2.3) Execute trca/install/tacreate.sql

SQL> @tacreate.sql

2.4)  Input required parameters

Parameters asked during the installation

i) Password for user TRCANLZR (case sensitive in 11g)
ii) Tablespaces to be used by TRCANLZR ( You can choose any tablespace except SYSTEM)
iii) TRCA user (“The main application user of TRCA is the schema owner that generated the SQL Trace to be analyzed. For example, on an EBS application you would enter APPS. You will not be asked to enter its password. To add more TRCA users after this installation is completed simply grant them the TRCA_USER_ROLE role.”)
iv) Flag to create staging tables as permanent or temporary objects (Temporary “T” is recommended and default value.)

 

3. GENERATE  THE TRCA REPROT

Generating TRCA report is also a simple process

$ cd trca/run
$ sqlplus [apps user]/[apps pwd]
SQL> start trcanlzr.sql ora_123_xyz.trc <== This is your trace file for which you want TRCA report to be generated

 

4) UNINSTALL THE SOFTWARE

To uninstall the TRCA software, Steps are simple

i) Connect as SYSDBA
ii) Execute trca/install/tadrop.sql

 

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

[catlist name="Performance Tuning Series"]

 

[Post Views: 120]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *

error: Alert: Content is protected !!