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:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020