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.
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
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]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018